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

Solution 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";

Solution 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]* ""-""??_ ;_-@_ ";

Comments

[Walter Sharp]

No the values im setting the cells to are numeric. To better understand my question, if you right click on a cell in excel, and click format cell, there is a list of different formats that include "Number" and "Currency" and other types, is there a way for me to set a cells formatting to "Currency" using the excel interop, I tried to do that using NumberFormat but it didnt seem to work

[Charles Williams]

You could try setting the style to Currency.<br/> But I don't think its the numeric formatting that is the problem since there are no green triangle checks for formatting, apart from the one checking for numbers stored as text.<br/> Maybe you should post your code.

[Charles Williams]

BTW, If a number is entered as string/text or formatted as Text then subsequently formatting it as currency does not convert it back to a number.