Apply currency format to a cell in Excel spreadsheet in .NET

Aspose.Cells Product Family
1

Hi,

I would like to have the currency format applied to cells in excel (eg: $123.44).
I am populating the excel template using smart markers and is then setting the styles (backgroung color, font etc) from another excel template file (called as formatExcel). The user is allowed to edit this formatExcel file, and the changes are reflected in the generated excel.

The code that is used to set the style is

sheet.Cells[i,j].Style = formatCell.Style;

I am setting the format of the cell (in formatExcel) to be of Currency.
But when I use the code given above, only the back ground color, font etc of the formatCell are applied to the sheet.Cells[i,j]. The CURRENCY type is getting applied.

What could be wrong in here?

Thanks,
David.

2

Do you change the style in the format cell? Please try:

sheet.Cells[i,j].Style.Copy(formatCell.Style);

And please download and try v3.1.1 at

3

Actually I have a typo in my question.
I meant the CURRENCY type is NOT getting applied. (Forgot to put NOT in the question before)

Yes I change the style in the format cell and aplly it to the excel file created.
I tried the method that you have given, but it doesn’t work

4

My test code:

Excel excel1 = new Excel();
Excel excel2 = new Excel();
excel1.Open(“d:\book1.xls”);
excel2.Worksheets[0].Cells[“a1”].Style = excel1.Worksheets[0].Cells[“a1”].Style;
excel2.Worksheets[0].Cells[“a1”].PutValue(22.345);
excel2.Save(“result.xls”, SaveType.OpenInExcel, FileFormatType.Default, this.Response);

5

I am populating the values in to the excel sheet first and then applying the styles based on the values in each row. In the test code that you have given, the style is applied first and then the value. In your case it works, but mine doesn’t.

Again, what I want is to populate the data first and apply the styles depending on the value. Let me know how I can add the currency symbol in that case.

Thanks,
David

6

Hi David,

Set value first then set style also works.

Excel excel1 = new Excel();
Excel excel2 = new Excel();
excel1.Open(“d:\book1.xls”);
excel2.Worksheets[0].Cells[“a1”].PutValue(22.345);
excel2.Worksheets[0].Cells[“a1”].Style = excel1.Worksheets[0].Cells[“a1”].Style;

excel2.Save(“result.xls”, SaveType.OpenInExcel, FileFormatType.Default, this.Response);


or you can try:

excel2.Worksheets[0].Cells[“a1”].Style. Number = 7;

If it still doesn’t work, could you please post more of your sample code here? Thank you.

7

Hey,

I found the problem with the currency display. It occurs because, the incomming data from the dataset is of string type, thus the currency formatting does not get applied.

Another question, is how can the column width be applied to the generated excel file from a designer template?

Thx,
david.

8

I am not clear about your question on column width.

When you import a designer template, column width are automatically imported. If you want to change the column width, please try Cells.SetColumnWidth method.

9

There is a strange behaviour.
Could you explain me why I have no ability to change format of cells using toolbars icons (i.e. selecting $ doesn't work to switch number to currency format) in exported document.
But :
I can change the style after copy-paste to another document (in that document)
I can change the style with cell format from menu
Is it Excels bug?
You can reproduce that behaviour on any number cells in the document in attachment.

10

This is really a strange problem. I will check this issue.

11

I’m still facing with an inability to change format of cells using toolbars icons in exported document

Could you plz clarify this issue?

12

Sorry for not replying ealier. Please try this attached fix.

13

Thanks a lot.
It works.

14

The same behaviour if we applay % format from toolbar icon.
Could you, please fix this bug?

Thank you

15

I will check and fix this issue. You will get the fix next week.

16

Please try this new fix.

17

@MedeFinance ,
Aspose.Excel is discontinued and no more actively developed now. It is replaced by a highly efficient and feature-rich product Aspose.Cells that not only supports the features of its predecessor product but also contains different features available in the latest versions of MS Excel. You can format the data including custom formatting like implementing currency format. Here is an example that can be referred to for applying custom formats.

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

// Obtaining the reference of first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Adding the decimal value to "A1" cell
worksheet.Cells["A1"].PutValue(3464123.456789);

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

// Setting the display format to currency "#,##0;-#,##0"
style.Number = 37;

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

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

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

// Setting the display format to currency "#,##0;[Red]-#,##0"
style.Number = 38;

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

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

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

// Setting the display format to currency "#,##0.00;-#,##0.00"
style.Number = 39;

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


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

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

// Setting the display format to currency "#,##0.00;[Red]-#,##0.00"
style.Number = 40;

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


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

For more details about this feature, refer to the following article:
Data formatting
Specify Custom Number Decimal and Group Separators for Workbook

A free trial version of this product can be downloaded here:
Aspose.Cells for .NET (Latest Version)

You can download a complete solution here which contains multiple examples for testing different features of this product.