This project has moved and is read-only. For the latest updates, please go here.

Format cell as Currency?

Jul 4, 2010 at 7:51 PM

Is there a way so that I could pass decimal to cell and set it's field to currency? Or some other similar method to make sure it's type of currency. Otherwise if I just pass zł or € as currency symbol Excel doesn't want do any counting on that treating it as string. If i pass it as decimal counting can be done but i loose currency symbol.

 

With regards,


MadBoy

Jul 5, 2010 at 7:23 AM

User the Cells[xx].Style.Numberformat property. You can check out the format in Excel--> Format cells --> Custom

Jul 5, 2010 at 9:50 AM

Hello,

Thanks for your advice but it doesn't seem to work as expected. I've first set the column to Currency, then i checked what's in Custom and it gave me: "# ##0,00 zł"

 

So I've used it.

ws.Cells[dataRows, dataColumns].Value = varValue;
ws.Cells[dataRows, dataColumns].Style.Numberformat.Format = @"# ##0,00 zł";

 

Unfortunetly the results are most of the time rounded up and doesn't show everything. For example if I have value 26920,64 and I change in Excel type to currency i get:   26 920,64zł but when I convert it to custom formatting using code it rounds things up to 26921 zł. This makes things very unpredictable.                        

Jul 5, 2010 at 11:29 AM

Hi,

The number format uses american format (thousand="," Decimal=".")

try...

ws.Cells[dataRows, dataColumns].Value = varValue;
ws.Cells[dataRows, dataColumns].Style.Numberformat.Format = @"#,##0.00zł";

 

If you want to check out the exact number format try this...

Format the sheet in Excel the way you want.

Save as Xlsx.

Rename the workbook *.zip and extract it.

Have a look in the xl\Styles.xml file to see the exact number format.

Jan

Jul 5, 2010 at 11:47 AM
Edited Jul 5, 2010 at 12:03 PM
Lovely. Thanks for the tip wit Styles.xml.

For zł:
ws.Cells[dataRows, dataColumns].Style.Numberformat.Format = @"#,##0.00zł";
For euro:
ws.Cells[dataRows, dataColumns].Style.Numberformat.Format = @"#,##0.00\ [$€-1]";

Thanks again!
Nov 12, 2014 at 8:03 AM
tryt his...
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "€ #,###,###.00";
xlWorkSheet.Cells[1, 1] = "1234567890";
Source....C# Excel cell formatting

Justin