OpenXML SDK:如何识别单元格的数据类型? [英] OpenXML SDK: How to identify data type of cell?

问题描述

我正在使用OPenXML SDK来与excel一起使用.目前，我面临一个问题，如何识别单元格，日期时间或数字的数据类型.因为，如果单元格的类型是日期，我们需要再次将双精度值转换为日期时间.

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.

推荐答案

基于我从OpenXML SDK帮助文件中找到的信息，我编写了以下代码，可以回答您的问题

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();
    }
}

这篇关于OpenXML SDK:如何识别单元格的数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！