乘风破浪，遇见最佳跨平台跨终端框架.Net Core/.Net生态 - 微软开源组件Open-XML-SDK，解析Office文件

Open-XML-SDK

https://github.com/OfficeDev/Open-XML-SDK

Open XML SDK提供了用于处理Office Word、Excel和PowerPoint文档的工具。

它支持以下情况。

• 高性能地生成文字处理文档、电子表格和演示文稿。
• 文档修改，如添加、更新和删除内容和元数据。
• 使用正则表达式搜索和替换内容。
• 将一个文件分割（粉碎）为多个文件，并将多个文件合并为一个文件。
• 更新Word/PowerPoint中图表的缓存数据和嵌入式电子表格。

获取Nuget包

https://www.nuget.org/packages/DocumentFormat.OpenXml.Linq

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

参考

• Open XML SDK
• DocumentFormat.OpenXml.Linq
• (Open XML SDK) 检索电子表格文档中单元格的值
• 用Open XML SDK读取Excel
• 使用Open xml 操作Excel系列之一-读取Excel
• .Net Core中使用ExceptionFilter