Excel number format

[Srinivasa Igur]

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]

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]

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]

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

$ 123.456
$ (123,456)

[Geetha M]

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]

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]

Thanks. This works perfectly.

[Geetha M]

Hi Srinivasa,

Thank you for the update.

Regards,
Geetha