打开 XML SDK 2.0 使用公式读取 Excel 单元格值

Question

我正在使用openXML sdk读取excel文件。一些 excel 单元格包含公式，我想读取单元格的值，但是当我使用以下代码时，它会获取公式。如何获取值。

WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
using(SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == "D16").FirstOrDefault();
value = theCell.InnerText;
}

在这里，我正在阅读包含公式的特定单元格"D16"。

需要更多帮助，无法读取单元格的日期。我得到了一些数字。 对于日期单元格，数据类型为 Null，因此下面的代码不起作用。 我从单元格中获取日期谷吗

if (theCell != null)
{
value = theCell.CellValue.InnerText;
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
}

Answer 1

试试单元格的 CellValue 属性。

WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
using(SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == "D16").FirstOrDefault();
value = theCell.CellValue;
}

我假设您正在硬编码单元格引用以进行调试。 否则，我强烈建议不要这样做。

Answer 2

试试这个：

WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
using(SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == "D16").FirstOrDefault();
var value = theCell.CellValue.InnerText;
}