We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Excel number format

Thread ID:

Created:

Updated:

Platform:

Replies:

93691 Mar 29,2010 03:34 PM Apr 1,2010 03:14 AM Windows Forms 7
loading
Tags: XlsIO
Srinivasa Igur
Asked On March 29, 2010 03:34 PM

Hi,
I am formatting numbers in an excel cell and I wanted to add "," seprator and $ sign, so this peice of code worked.

IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:C20"].NumberFormat = "$###,###";

I want to format the negative numbers in excel cells as () as opposed to the - sign. How can I acheive this?

SO to be clear I want to display

123456 as $123,456 (which works)
-123456 as $(123,456). Right now I can only get -$123,456 with the above.

Thanks.


Geetha M [Syncfusion]
Replied On March 30, 2010 12:43 AM

Hi Srinivasa,

You may achieve $123,456 and $(123,456) for 123456 and -123456 by using the numbed format below.

_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)

Please try this and let me know if you have any questions.

Regards,
Geetha

Srinivasa Igur
Replied On March 30, 2010 09:41 AM

Thanks. That works however I had to change the - from " " to ' '

I have one more question. How do I apply this to a dynamic range in a worksheet. I do not want to choose the range as (A1:ZZ65000). Also how can I apply this to a column?

Thanks again.

Srinivasa Igur
Replied On March 30, 2010 09:48 AM

oops. The $ sign is left justified and seperated from the amount like shown below:

$ 123.456
$ (123,456)

Geetha M [Syncfusion]
Replied On March 31, 2010 02:02 AM

Hi Srinivasa,

I see the problem.

Here is the modified format that displays without space and alignment

_($#,##0_);_($(#,##0);_($* '-'_);_(@_)

Please try this and let me know if you have any questions.

Regards,
Geetha

Geetha M [Syncfusion]
Replied On March 31, 2010 02:11 AM

Hi Srinivasa,

Regarding applying format to a dynamic range, you can have it as a style and apply to any row or column.

IStyle numberFormat = workBook.Styles.Add("NumberFormatStyle");
numberFormat.NumberFormat = "_($#,##0_);_($(#,##0);_($* '-'_);_(@_)";

sheet.SetDefaultColumnStyle(1, numberFormat);

Please refer to our documentation in the link below.

http://help.syncfusion.com/ug_81/Reporting_XlsIO/DefaultStyles.html

Regards,
Geetha

Srinivasa Igur
Replied On March 31, 2010 09:14 AM

Thanks. This works perfectly.

Geetha M [Syncfusion]
Replied On April 1, 2010 03:14 AM

Hi Srinivasa,

Thank you for the update.

Regards,
Geetha

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;