首先需要添加包引用： DocumentFormat.OpenXml。
DOM 和 SAX
读取Excel有两种方式，一种是DOM（文档对象模型），另一种是SAX（Simple API for XML ）。
DOM 是一次性把文档全部读取到内存中，适用于不太大的文档。
SAX 则是通过流的方式读取，适用于比较大的文档。
DOM 代码如下：
string fileName = "Sample.xlsx";
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart!;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
//逐行读取
foreach (Row r in sheetData.Elements<Row>())
{
//循环行内所有单元格
foreach (Cell cell in r.Elements<Cell>())
{
//读取单元格
}
}
}SAX 代码如下：
string fileName = "Sample.xlsx";
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart!;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
//逐个节点读取
while (reader.Read())
{
if (reader.ElementType == typeof(Row) && reader.IsStartElement)
{
//检测到行节点，则加载整行
Row? r = reader.LoadCurrentElement() as Row;
//循环行内所有单元格
foreach (Cell cell in r!.Elements<Cell>())
{
//读取单元格
}
}
}
}读取单元格的值
通过前面的代码，已经取到了单元格的实例（一个Cell的对象），想象中取值应该很简单了，但其实不然。
通过以下枚举可以知道单元格有不用类型的值
public enum CellValues
{
Boolean,
Number,
Error,
SharedString,
String,
InlineString,
Date
}因此需要根据不同类型的值，采取不同的方法，如下所示：
object? GetCellValue(Cell c)
{
//取到单元格的文本值，如果有公式，则是计算过的值
//其中 c.CellFormula 里面包含了公式的定义
string? value = c.CellValue?.Text;
if (c.DataType != null)
{
//根据不同的DataType采取不同的取值方式
switch (c.DataType.Value)
{
case CellValues.SharedString:
//共享字符串存在单独的文件里面，这里得到的只是一个数字，需要额外的取值方法,将在下面介绍
return (string?)null;
case CellValues.Date:
//日期取到的是个数字，需要转换
if (!string.IsNullOrEmpty(value) && double.TryParse(value, out double dtValue))
{
return System.DateTime.FromOADate(dtValue);
}
return (DateTime?)null;
case CellValues.Boolean:
if (!string.IsNullOrEmpty(value))
{
if (value.Length < 2)
{
return value?.ToUpperInvariant() == "T" || value == "1";
}
if (bool.TryParse(value, out bool boolValue))
{
return boolValue;
}
}
return (bool?)null;
case CellValues.Number:
if (!string.IsNullOrEmpty(value) && decimal.TryParse(value, out decimal number))
{
return number;
}
return (decimal?)null;
default: break;
}
}
return value;
}读取字符串
大部分字符串都是以SharedString的方式保存的，SharedString相当于是一个表，里面保存了所有的字符串，单元格的CellValue保存的只是SharedString里面一个索引值。
因此需要先取到SharedString的表格，然后根据索引去找对应的字符串。
相关代码如下：
//从workbook取SharedStringTable
var stringTable = workbookPart.GetPartsOfType<SharedStringTablePart>()
.FirstOrDefault()?.SharedStringTable;
//根据索引值从SharedStringTable取值
stringTable.ElementAt(strIndex).InnerText读取日期
通过前面的代码知道单元格包含Date类型的值，但实际用的时候发现，对于一个日期字段，DataType 是null 值，但是CellValue 却是数字。
这就很让人费解了，如果事先知道某一列为日期，那还可以通过DateTime.FromOADate方法来转成日期。
如果不知道列的类型，想做动态转换应该怎么做呢？
说实话笔者搜了一圈都没找到合适的方法，真不知道微软为什么留了这么一个bug。
一个折中的办法是通过单元格的格式来判断是否为日期型，即在Excel里面看到的这一块：
通过如下链接
https://github.com/ClosedXML/ClosedXML/wiki/NumberFormatId-Lookup-Table
可以知道14到22为默认的日期格式。
相关代码如下：
//取到样式表
var styleSheet = workbookPart.GetPartsOfType<WorkbookStylesPart>()
.FirstOrDefault()?.Stylesheet;
//根据单元格的StyleIndex取到Format
var cellFormat = styleSheet.CellFormats
.ChildElements[int.Parse(c.StyleIndex.InnerText)] as CellFormat;
//通过判断cellFormat.NumberFormatId.Value在14和22之间即可当然这个方法有很大的局限性，各个国家的日期格式，再加上自定义格式，其数量肯定远远超过14到22这几个数。
最后附上完整取值代码, 也可以在此处查看
https://gitee.com/vdevelopernet/csharppractice/blob/master/OpenXmlPractice/FrmReadExcel.cs
object? GetCellValue(Cell c, SharedStringTable? stringTable, Stylesheet? styleSheet)
{
string? value = c.CellValue?.Text;
if (c.DataType != null)
{
switch (c.DataType.Value)
{
case CellValues.SharedString:
if (!string.IsNullOrEmpty(value) && int.TryParse(value, out int strIndex))
{
if (stringTable != null && stringTable.Count() > strIndex)
{
return stringTable.ElementAt(strIndex).InnerText;
}
}
return (string?)null;
case CellValues.Date:
if (!string.IsNullOrEmpty(value) && double.TryParse(value, out double dtValue))
{
return System.DateTime.FromOADate(dtValue);
}
return (DateTime?)null;
case CellValues.Boolean:
if (!string.IsNullOrEmpty(value))
{
if (value.Length < 2)
{
return value?.ToUpperInvariant() == "T" || value == "1";
}
if (bool.TryParse(value, out bool boolValue))
{
return boolValue;
}
}
return (bool?)null;
case CellValues.Number:
if (!string.IsNullOrEmpty(value) && decimal.TryParse(value, out decimal number))
{
return number;
}
return (decimal?)null;
default: break;
}
}
if (c.StyleIndex?.InnerText != null && styleSheet != null && styleSheet.CellFormats != null)
{
var cellFormat = styleSheet.CellFormats.ChildElements[int.Parse(c.StyleIndex.InnerText)] as CellFormat;
//https://github.com/ClosedXML/ClosedXML/wiki/NumberFormatId-Lookup-Table
if (cellFormat != null && cellFormat.NumberFormatId!.HasValue && cellFormat.NumberFormatId.Value >= 14 && cellFormat.NumberFormatId.Value <= 22)
{
if (!string.IsNullOrEmpty(value))
{
if (double.TryParse(value, out var cellDouble))
{
return DateTime.FromOADate(cellDouble);
}
}
return (DateTime?)null;
}
}
return value;
}引用
- http://www.ericwhite.com/blog/dates-in-spreadsheetml/
- https://learn.microsoft.com/en-us/office/open-xml/how-to-parse-and-read-a-large-spreadsheet
- https://learn.microsoft.com/en-us/office/open-xml/how-to-retrieve-the-values-of-cells-in-a-spreadsheet
- https://github.com/ClosedXML/ClosedXML/wiki/NumberFormatId-Lookup-Table