Number formatting in Excel


#1

How can the custom format of: “#,##0.000;[Red]-#,##0.000"
can be done on a cell. I tried the following. But none worked.

styleNomRight.Number=40;
styleNomRight.Custom=”#,##0.000;[Red]-#,##0.000";

thanks
Cherian Kurien


#2

Hi Cherian,

If Number is set to 40, the format string is “(#,##0.00);Red”. So to meet your need, you have to set Custom to “#,##0.000;[Red]-#,##0.000”.

Cell cell = excel.Worksheets[0].Cells[“A1”];
cell.PutValue(12.34567);

int styleIndex = excel.Styles.Add();
Aspose.Excel.Style style = excel.Styles[styleIndex];
style.Custom ="#,##0.000;[Red]-#,##0.000";
cell.Style = style;

or:
Cell cell = excel.Worksheets[0].Cells[“A1”];
cell.PutValue(12.34567);
cell.Style.Custom ="#,##0.000;[Red]-#,##0.000";

Both work fine in my machine. You can have a try. Also you can download newest fix here.


#3

Hi Laurence,

Actually the problem was not with the formatting command. It was not working because
the data type of the column which I was trying to format was ‘string’. The data table columns
were populated from an XML string. So all the columns, even when it’s a numbers, were
treated as string.

So I resolved the issue with the following line to convert it to ‘decimal’ type and it worked
fine:
cell.PutValue((decimal)decimal.Parse((string)ds.Tables[0].Rows[i][“AMOUNT”]));

Of course, I put the style command also and applied to the cell:

style.Custom="#,##0.000;Red";

thanks for the prompt response.
Cherian



#4

I think this might help me but I do not understand what is happening here:
(decimal)decimal.

can you help me out?

thanks,


#5

In MS Excel, when you choose “Format Cell”, you can set number format to a cell. There are some built-in number formats. But you may need to set your own custom number format. You can play around MS Excel to see how to set a custom format.