How to get format type of cell using c# in spreadsheetlight

Question

I am using spreadsheetlight library to read Excel sheet(.xslx) values using c#.

I can read the cell value using following code

 for (int col = stats.StartColumnIndex; col <= stats.EndColumnIndex; col++)
     {              
         var value= sheet.GetCellValueAsString(stats.StartRowIndex, col);  //where sheet is current sheet in excel file    
     }

I am getting the cell value. But how can I get the data type of the cell? I have checked in documentation but didn't find the solution.

Note: For .xls type of excel files i am using ExcelLibrary.dll library where i can easily get the datatype of cells using below code

for (int i = 0; i <= cells.LastColIndex; i++)
     {
         var type = cells[0, i].Format.FormatType;
     }

but there is no similar method in spreadsheetlight.

Answer 1

Well, After a lot of trail and error methods i got to find a solution for this.

Based on the formatCode of a cell we can decide the formatType of the cell.

Using GetCellStyle method we can get the formatcode of the cell. Using this formatCode we can decide the formatType.

var FieldType = GetDataType(sheet.GetCellStyle(rowIndex, columnIndex).FormatCode);


private string GetDataType(string formatCode)
    {
        if (formatCode.Contains("h:mm") || formatCode.Contains("mm:ss"))
        {
            return "Time";
        }
        else if (formatCode.Contains("[$-409]") || formatCode.Contains("[$-F800]") || formatCode.Contains("m/d"))
        {
            return "Date";
        }
        else if (formatCode.Contains("#,##0.0"))
        {
            return "Currency";
        }
        else if (formatCode.Last() == '%')
        {
            return "Percentage";
        }
        else if (formatCode.IndexOf("0") == 0)
        {
            return "Numeric";
        }
        else
        {
            return "String";
        }
    } 

This method worked for 99% of the cases.

Hope it helps you.