Check an Excel Cell Data Type

Question

Is it possible to determine the Data Type and Format of an Excel cell?

I know there is .NumberFormat but it returns the formatting not the type...

I need to know if it's custom, then it should return custom, currency should return Currency and etc.

Solution

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