Custom Formats

Aspose.Cells Product Family
,
1

Hi Laurence

I’m dynamically creating custom formats and assiging them them to each cell. For example: excelSheet.Cells[1, 1].Style.Custom = “#,##0.0000”;

The problem occurs when I view the opened excel sheet in the browser. The cells are not immediately reflecting the custom format that I’ve assigned them. If I right-click on each cell and view “Formats Cells…”, I can see that the Custom format has been correctly written and assigned to that cell. Also, if I click on the Formula Bar and then click back into the cell, then the cell immediately reflects the custom format that’s been assigned to it. It’s when the Excel sheet first opens that nothing is happening - it seems to default to a general number pattern. I have to click into the Formula bar and back again into the cell for it to change. Clearly this is not workable for a large spreadsheet.

Any ideas how I can remedy this? Is there some property I have to set to tell Excel to use the custom formats when opening? (The same thing happens if I save the file to disk. I’m using Excel 2000.)

Many thanks

–Seamus

2

Hi Seamus,

I think you put string to this cell, not a numeric value.

For example:

cell.PutValue("123");

not

cell.PutValue(123);

Number format only takes effect to numeric value.

3

Thanks Laurence - that did the trick.

cheers

–Seamus

4

@seamus,
Aspose.Excel is discarded and no more active development is done for it now. A new product Aspose.Cells is introduced which contains all the features of Aspose.Excel and support for the latest features in MS Excel. You can set custom format of cells using this new product as demonstrated 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);

Here is a detailed article which demonstrates formatting the data in cells:
Data Formatting

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

You may get a complete runnable solution here to test different features of Aspose.Cells.