Apply currency format to a cell in excel


#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 http://www.aspose.com/Downloads/Aspose.Excel/Default.aspx.


#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.