Excel Interop currency format

Question

I'm attempting to format a cell in excel to the currency format. So I go into excel, record a macro of me converting an ordinary cell to a currency format, take a look at the vb script and see that it outputs the following:

NumberFormat = "$ #,##0.00"

So i take that format and paste it into my code, it works to the extent that im getting the currency character before the values in my excel sheet. However, the format of the cell is still a number and excel places a little green triangle at the bottom left of the cell informing me that the format is incorrect (which it is, cos its supposed to be currency, but its set to number) is there any way in c# to actually set the cell to a "Currency" format?

thanks

Answer 1

The range object has a "Style" property... the intelisense metadata says that it is "Returns an object you can use" only but you can also just set the Style with that property. To get the built-in "Currency" style, use the "Styles" property of (for instance) a Workbook object.

Example:

using Excel = Microsoft.Office.Interop.Excel;

...

var excel = new Excel.Application();
var wb = excel.Workbooks.Add();
var sheet = (Excel.Worksheet)wb.Sheets[1];
((Excel.Range)sheet.Cells[3, 4]).Style = wb.Styles["Currency"];

In fact you can just set it to the string "Currency" which may be what Charles was suggesting.

((Excel.Range)sheet.Cells[3, 4]).Style = "Currency";

Answer 2

Ok i tried many that codes and i found this works better than other on any language/regional settings.

numberFormat = @"_-[$$-409]* #,##0.00_ ;_-[$$-409]* -#,##0.00 ;_-[$$-409]* ""-""??_ ;_-@_ ";