Set custom Number formatting in MS Excel

Aspose.Cells Product Family
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](#,##0.00). 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 Aspose.Cells for .NET (Latest Version) .

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](#,##0.000);

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.

6

@Cherian,
Aspose.Excel is discontinued now and no more development is done for it. A new product Aspose.Cells has replaced it which supports all the legacy features of Aspose.Excel as well as the latest features available in different versions of MS Excel. You can format data using this new product also as shown in the following sample code:

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();

// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];

// Adding the current system date to "A1" cell
worksheet.Cells["A1"].PutValue(DateTime.Now);

// Getting the style of A1 cell
Style style = worksheet.Cells["A1"].GetStyle();

// Setting the custom display format to show date as "d-mmm-yy"
style.Custom = "d-mmm-yy";

// Applying the style to A1 cell
worksheet.Cells["A1"].SetStyle(style);

// Adding a numeric value to "A2" cell
worksheet.Cells["A2"].PutValue(20);

// Getting the style of A2 cell
style = worksheet.Cells["A2"].GetStyle();

// Setting the custom display format to show value as percentage
style.Custom = "0.0%";

// Applying the style to A2 cell
worksheet.Cells["A2"].SetStyle(style);

// Adding a numeric value to "A3" cell
worksheet.Cells["A3"].PutValue(2546);

// Getting the style of A3 cell
style = worksheet.Cells["A3"].GetStyle();

// Setting the custom display format to show value as currency
style.Custom = "£#,##0;[Red]$-#,##0";

// Applying the style to A3 cell
worksheet.Cells["A3"].SetStyle(style);

// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);

Have a look at the following article for more information on formatting the data:
Data Formatting

Here is the link to the latest free trial version of this product:
Aspose.Cells for .NET (Latest Version)

A ruunable solution is available here which can be used to test different features of this product without writing ay code.