乘风破浪，遇见最佳跨平台跨终端框架.Net Core/.Net生态 - 微软开源组件Open-XML-SDK，解析Office文件
Open-XML-SDK
Open XML SDK提供了用于处理Office Word、Excel和PowerPoint文档的工具。
它支持以下情况。
- 高性能地生成文字处理文档、电子表格和演示文稿。
- 文档修改，如添加、更新和删除内容和元数据。
- 使用正则表达式搜索和替换内容。
- 将一个文件分割（粉碎）为多个文件，并将多个文件合并为一个文件。
- 更新Word/PowerPoint中图表的缓存数据和嵌入式电子表格。
获取Nuget包
dotnet add package DocumentFormat.OpenXml.Linq
简单使用
读取Excel文件
public class ReadSheetCommand : IRequest<HandlerResult<IEnumerable<string>>>
{
/// <summary>
/// 目标路径
/// </summary>
public string TargetPath { get; private set; }
/// <summary>
/// 目标页面
/// </summary>
public string TargetSheet { get; private set; }
/// <summary>
/// 构造函数
/// </summary>
/// <param name="targetPath">目标路径</param>
/// <param name="targetSheet">目标页面</param>
public ReadSheetCommand(string targetPath, string targetSheet)
{
TargetPath = targetPath;
TargetSheet = targetSheet;
}
}
internal class ReadSheetCommandHandler : IRequestHandler<ReadSheetCommand, HandlerResult<IEnumerable<string>>>
{
public async Task<HandlerResult<IEnumerable<string>>> Handle(ReadSheetCommand request, CancellationToken cancellationToken)
{
if(request is null || string.IsNullOrEmpty(request.TargetPath) || File.Exists(request.TargetPath) == false)
{
return await Task.FromResult(HandlerResult<IEnumerable<string>>.Failure("打开文件不存在"));
}
var allLines = new List<string>();
try
{
// 创建SpreadsheetDocument对象，打开文件并且只读
using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(request.TargetPath, false))
{
var wbPart = mySpreadsheet.WorkbookPart;
Sheet targetSheet;
if(!string.IsNullOrEmpty(request.TargetSheet))
{
targetSheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(x => x.Name == request.TargetSheet);
}
else
{
targetSheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault();
}
var wsPart = wbPart.GetPartById(targetSheet.Id) as WorksheetPart;
if(wsPart is not null)
{
var wsRows = wsPart.Worksheet.Descendants<Row>();
foreach (var wsRow in wsRows)
{
var sb = new StringBuilder();
foreach (Cell wsCell in wsRow)
{
var wsValue = GetCellValue(wbPart, wsCell);
sb.Append(wsValue);
sb.Append(',');
}
allLines.Add(sb.ToString());
}
}
}
}
catch (Exception ex)
{
if(ex.Message.Contains("End of Central Directory record could not be found."))
{
return HandlerResult<IEnumerable<string>>.Failure("文件打开失败，已损坏或者格式错误");
}
else if (ex.Message.Contains("because it is being used by another process."))
{
return HandlerResult<IEnumerable<string>>.Failure("文件打开失败，它被另外一个进程占用了");
}
System.Console.WriteLine(ex.Message);
}
return await Task.FromResult(HandlerResult<IEnumerable<string>>.OK(allLines));
}
public static string GetCellValue(WorkbookPart wbPart, Cell theCell)
{
string value = theCell.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;
}
}
return value;
}
}
