Custom Formatting

Aspose.Cells Product Family
1

As a follow-on to my previous post, I have a question about custom formatting. I’m sure you are quite familiar with the formatting options in Excel as well as those that can be used in .NET, in say a DataGrid. Let’s suppose I want to specify a custom format for a percentage value, say 3.2% (equivalent to 0.032). Now, in Excel, I could use the preset format option of: 0.0%;[Red]-0.0% to yield 3.2%. If my value were -0.032, then Excel would display it as (3.2%) in red. I can likewise specify a format for the value 0.032 to control how it will display in an ASP.NET DataGrid by using something like: {0:#0.0%}.

Is there any property I can set via Aspose.Excel to control the format of an Excel cell value via code? Again, I’ve reviewed the Aspose.Excel documentation and could not find anything that addressed custom formatting.

Thanks!

2


Dear Nathan,

It is second time to request to support custom formatting so we’d love to do that.

Unfortunately we still haven’t figured out an API which allows you to easy to use and at the same time it is also flexible enough.

You know, it’s quite easy to set custom formatting manually in Excel but it’s not so straightforward to do that programmably.

So tt’s greatly appreciated if you can suggest what a good API looks like.

3


Dear Nathan,

We decide to add a CellFormat.CustomNumberFormatString property, which allows you to customize the cell format.

Is it OK for you?

It will be done within 1-2 weeks please let me know if you need it immediately when it is available.


4

@BIXIHJ,
We recommend you to try the following sample code for setting the custom number formats:

// 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);

For more details have a look at the following article:
Data Formatting

Download the latest version of Aspose.Cells for .NET from the following link:
Aspose.Cells for .NET (Latest Version)

You can download the latest demos here.