Under the hood Excel stores values in a special way (most datatypes are actually doubles) and that makes it tricky to detect Cell Formats without the help of Excel.
Hence I recommend you leverage the inbuilt Excel CELL function rather that interrogating the datatypes yourself:
private void button1_Click(object sender, EventArgs e)
{
//C1 is a cell I use to evaluate the Format of Cells A1 thru to A7
using (var rnEvaluate = xlApp.Range["C1:C1"].WithComCleanup())
{
for (int i = 1; i < 8; i++)
{
rnEvaluate.Resource.Value2 = "=CELL(\"format\",A" + i.ToString() + ")";
string cellFormat = GetExcelCellFormat(rnEvaluate.Resource.Value2);
System.Diagnostics.Debug.Write(cellFormat);
}
}
}
private string GetExcelCellFormat(string cellFormat = "G")
{
switch (cellFormat.Substring(0, 1))
{
case "F" :
return "Number";
break;
case "C":
return "Currency";
break;
case "D":
return "Date";
break;
default :
return "General";
break;
}
}
ps The .WithComCleanup() is because I am using VSTO Contrib
After getting the NumberFormat from the Excel range, you can convert it using this table of formatting codes, or this reference from Microsoft. Keep in mind that mixed formats are possible if customized.
If you just want the base values (my defaults from drop down in Excel 2010):
I got these values by recording a macro and modifying the number format for the selected cell.
EDIT:
A little more help to get where you want to go - assuming that you are using the Excel interop library (the only way I know how to access the NumberFormat function):
string fileName = @"c:\Book1.xlsx";
Application app = new Application();
Workbook wb = app.Workbooks.Open(fileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Worksheet sheet = wb.Sheets[1]; //Change to the sheet you care about (1 based index)
var cell = sheet.Cells[1, 1]; //Change to the cell you care about (1 based index)
string cellNumberFormat = cell.NumberFormat; //Number format of cell to compare against known values
Setup for using the interop library found here.
