OpenXML SDK: How to identify data type of cell?

Question

I am working on OPenXML SDK to work with excel. Currently, I am facing with one issue, how to identify data type of cell, datetime or numeric. Because, in case cell's type is date, we need to convert double value to datetime again.

Answer 1

Based on information I have found from the OpenXML SDK help files I have written the following code which should answer your question

public class ExcelEngine : IExcelEngine
{
    private readonly SpreadsheetDocument _wb;
    private WorkbookPart _wbp;
    private SharedStringTablePart _sstp;

    // Used to cache the lookup of worksheet parts
    private Dictionary<string, WorksheetPart> _wsParts = new Dictionary<string, WorksheetPart>();

    #region Constructors

    public ExcelEngine(Stream stream)
    {
        Contracts.IsNotNull(stream);

        _wb = SpreadsheetDocument.Open(stream, false);
        Initialise();
    }

    public ExcelEngine(string fileName)
    {
        Contracts.IsNullOrWhiteSpace(fileName);

        _wb = SpreadsheetDocument.Open(fileName, false);
        Initialise();
    }

    #endregion

    #region IExcelEngine

    /// <summary>
    /// Get the list of sheet names from the spreadsheet
    /// </summary>
    /// <returns></returns>
    public IList<string> GetSheetNames()
    {
        return _wbp.Workbook
            .Descendants<Sheet>()
            .Select(s => s.Name.Value)
            .ToList<String>();
    }

    /// <summary>
    /// Given a sheet name and a cell reference, return the contents of the cell
    /// </summary>
    /// <param name="sheetName"></param>
    /// <param name="addressName"></param>
    /// <returns></returns>        
    public string GetCellValue(string sheetName, string addressName)
    {
        return GetCellValueLocal(sheetName, addressName);
    }


    /// <summary>
    /// Given a sheet name and a cell reference, return the contents of the cell as a boolean value
    /// </summary>
    /// <param name="sheetName"></param>
    /// <param name="addressName"></param>
    /// <returns></returns>
    public bool GetCellBool(string sheetName, string addressName)
    {
        var value = GetCellValueLocal(sheetName, addressName);

        bool result;
        bool.TryParse(value, out result);

        return result;
    }

    #endregion

    #region Private Methods

    private void Initialise()
    {
        _wbp = _wb.WorkbookPart;
        _sstp = _wbp.GetPartsOfType<SharedStringTablePart>().First();
    }


    private string GetCellValueLocal(string sheetName, string addressName)
    {
        string value = null;

        WorksheetPart wsPart = GetWorkSheetPart(sheetName);

        // Use its Worksheet property to get a reference to the cell 
        // whose address matches the address you supplied.
        Cell cell = wsPart.Worksheet.Descendants<Cell>().
            Where(c => c.CellReference == addressName).FirstOrDefault();

        if (cell != null)
        {
            value = cell.InnerText;

            if (cell.DataType != null)
                switch (cell.DataType.Value)
                {
                    case CellValues.SharedString:
                        int ssid = int.Parse(cell.CellValue.Text);
                        value = _sstp.SharedStringTable.ElementAt(ssid).InnerText;
                        break;

                    case CellValues.Boolean:
                        switch (value)
                        {
                            case "0":
                                value = "FALSE";
                                break;
                            default:
                                value = "TRUE";
                                break;
                        }
                        break;

                    case CellValues.Date:
                        break;

                    case CellValues.String:
                        break;
                }
            Debug.WriteLine($"Cell {cell.CellReference}: '{value}'");
        }

        return value;
    }

    private WorksheetPart GetWorkSheetPart(string sheetName)
    {
        // Does it exist in the cache? If not, load it
        if (!_wsParts.ContainsKey(sheetName))
        {
            // Find the sheet with the supplied name, and then use that 
            // Sheet object to retrieve a reference to the first worksheet.
            Sheet theSheet = _wbp.Workbook.Descendants<Sheet>()
                .Where(s => s.Name == sheetName)
                .FirstOrDefault();

            // If not sheet throw an exception
            if (theSheet == null)
                throw new ArgumentException($"Sheet {sheetName} not found in workbook");

            // Retrieve a reference to the worksheet part.
            _wsParts.Add(sheetName, (WorksheetPart) (_wbp.GetPartById(theSheet.Id)));
        }

        return _wsParts[sheetName];
    }

    #endregion

    public void Dispose()
    {
        _wb.Close();
        _wb.Dispose();
    }
}