65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 Open XML SDK 和对象模型映射将 Excel 数据导入通用列表

刘神威
starIconstarIconstarIconstarIconstarIcon

5.00/5 (7投票s)

2014 年 5 月 8 日

CPOL

9分钟阅读

viewsIcon

68906

downloadIcon

2964

C# 类，用于使用从 Excel 数据文件中检索到的数据填充通用列表

引言

具有底层数据模型的通用列表是数据应用程序开发中最有利的集合结构，因为它具有真正的面向对象编程特性。它也是将数据从 Microsoft Excel 电子表格导入应用程序和数据库的最佳形式。本文和示例应用程序展示了一个 C# 类，该类使用从 Excel 数据文件中检索到的数据来填充通用列表。本文尤其关注以下主题：

  • 使用 Microsoft Open XML SDK 库并将 DLL 文件合并到主程序集中。无需 Office Interop 或其他第三方工具。
  • 从 Excel 工作簿的共享字符串表中获取数据值。
  • 处理行迭代期间的空电子表格单元格问题。
  • 检测已使用的范围以消除空行处理。
  • 将电子表格数据映射到对象模型，使用友好的列名而不是索引号。
  • 标准化字符串到原始数据类型的转换。

运行示例应用程序

下载的源代码包含了在 Visual Studio 2010 - 2013 中运行示例应用程序所需的一切。下载的源代码中包含一个 Excel 文件，其中包含下面所示的示例数据。

这是示例数据的相应纯对象模型。

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int? CategoryID { get; set; }
    public Decimal? UnitPrice { get; set; }                
    public bool OutOfStock { get; set; }
    public DateTime? StockDate { get; set; }    
}

要查看数据导入的实际效果，您可以以调试或发布模式运行示例应用程序。导入的数据结果可以在控制台窗口中显示为格式化字符串，或者使用示例应用程序中包含的 `GenericListOutput` 类在另一个 Excel 工作表窗口中显示。有关更多详细信息，请参阅Shenwei Liu 的 CodeProject 文章。

数据导入功能概述

ExcelReader.cs 类文件中的 `GetDataToList` 函数使用以下语法执行将数据从 Excel 文件导入通用列表的主要任务：

public IList<T> GetDataToList<T>(string filePath, 
       Func<IList<string>, IList<string>, T> dataMappingFunctionName);    
public IList<T> GetDataToList<T>(string filePath, string sheetName, 
       Func<IList<string>, IList<string>, T> dataMappingFunctionName);

该函数设计为一次将数据从单个工作表导入单个通用列表。第一个重载版本始终使用 Excel 文件中的第一个工作表。如果您有多个工作表，可以通过显式传递 `sheetName` 值多次调用第二个重载版本来创建相应的多个 `List` 对象来表示您的模型。

`dataMappingFunctionName` 指向一个在后台执行电子表格字段数据到对象模型映射的函数。稍后我们将详细讨论数据映射的细节。

如示例应用程序所示，要将 Excel 文件中的数据导入到具有 `Product` 模型的 List 中，只需通过传递文件路径（`excelPath`）和委托数据映射函数的名称（`AddProductData`）来调用此行代码。

//Get product list from the spreadsheet
IList<Product> dataList = ExcelReader.GetDataToList(excelPath, AddProductData);

使用 Open XML SDK 库

如果我们开发一个需要从 Excel 文件导入数据的服务器应用程序，使用 Excel 的 Office Interop 库不是一个好的实践。Aspose 等第三方工具功能良好，但有许可限制，如果仅用于从 Excel 读取数据，可能有点大材小用。虽然 Office 的 Open XML SDK 对开发者来说不是那么友好，但它是 Microsoft 开发网络家族中的一个标准化且成熟的工具。如果我们能很好地处理一些麻烦，使用该工具将是 Excel 相关 .NET 编程工作的绝佳选择。

需要两个 Open XML SDK 库程序集：`DocumentFormat.OpenXml.dll` 和 `WindowsBase.dll` 来获取 Excel 数据。示例应用程序在 `lib` 文件夹中包含了两个 Open XML SDK 版本 2.5 的文件。这些文件适用于 Excel 2007 - 2013 电子表格文件以及使用 .NET Framework 4.0 及更高版本的 Visual Studio 2010 - 2013 项目。如果您使用的是低于 4.0 的 .NET Framework 版本，可以下载并安装 Open XML SDK 版本 2.0，用版本 2.0 的文件替换这两个 DLL 文件，然后在项目中重新引用它们。

在某些需要单个合并的可执行文件或 DLL 文件以便于部署的情况下，可以使用自动程序集合并过程，如示例应用程序所示。

  • 将两个 DLL 文件设置为项目 **属性** 页的 **资源** 选项卡上的资源文件。这将在项目的 *Properties* 文件夹下创建一个 `Resources.*` 文件组。

  • 将以下事件处理程序代码添加到启动类中。

    //Merge files specified as resources into one output executable or dll
System.Reflection.Assembly CurrentDomain_AssemblyResolve
                  (object sender, ResolveEventArgs args)
{
    string dllName = args.Name.Contains(',') ? 
    args.Name.Substring(0, args.Name.IndexOf(',')) : args.Name.Replace(".dll", "");
    dllName = dllName.Replace(".", "_");
    if (dllName.EndsWith("_resources")) return null;
    var obj = new Object();
    System.Resources.ResourceManager rm = new System.Resources.ResourceManager
                                 (obj.GetType().Namespace + ".Properties.Resources", 
                                 System.Reflection.Assembly.GetExecutingAssembly());
    byte[] bytes = (byte[])rm.GetObject(dllName);
    return System.Reflection.Assembly.Load(bytes);
}

  • 在启动类的初始化例程或构造函数中引发事件。

    AppDomain.CurrentDomain.AssemblyResolve += 
          new ResolveEventHandler(CurrentDomain_AssemblyResolve);

从共享字符串表获取单元格值

Excel 为了提高性能，会将潜在的重复字符串数据值存储在工作簿的共享字符串表 XML 结构中。工作表单元格被分配了指向共享字符串表节点值的索引号。在使用 Office Interop 时，您不必关心如何从工作表单元格获取实际数据值。但是，在使用 Open XML SDK 库时，您需要检查单元格数据类型属性，并通过存储在单元格中的索引号从共享字符串表中获取数据项。

ExcelReader 类中的 `GetCellValue` 函数检查单元格对象的 `DataType` 属性是否为 `SharedString`，然后从 `SharedStringTable` 对象中检索实际数据项。下面的代码片段显示了该逻辑。

if (cell == null) return null;
string value = cell.InnerText;

//Process values particularly for those data types
if (cell.DataType != null)
{
    switch (cell.DataType.Value)
    {
        //Obtain values from shared string table
        case CellValues.SharedString:                        
            var sstPart = 
                document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
            value = sstPart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            break;
        //Other conditions
        //. . .
    }
}
return value;

将空单元格添加到电子表格行

当使用 `DocumentFormat.OpenXml.Spreadsheet.Row` 类型从对象获取数据行时，由于底层的 XML 节点结构，任何值为空的单元格都不会被包含在内。这在迭代行中的单元格以进行列映射时会导致主要问题。Open XML SDK 库没有为这个问题提供任何解决方案。消费者需要添加自己的代码来处理它。幸运的是，`DocumentFormat.OpenXml.Spreadsheet.Cell` 类有一个 `CellReference` 属性，可以用来匹配内置的列名，从而便于在缺失的位置插入空单元格。

为了方便检测缺失的单元格，在迭代自定义标题行时，所有内置列名首先被缓存到一个通用列表中。循环中调用的 `GetColumnAddress` 是一个简单的函数，用于返回 `Cell.CellReference` 值中的字母部分（详情请参阅下载的源代码）。

var columnLetters = new List<string>();

//Iterate cells of custom header row
foreach (Cell cell in wsPart.Worksheet.Descendants<Row>().ElementAt(0))
{                        
    //Get custom column names
    //. . .
    
    //Get built-in column names by extracting letters from cell references                
    columnLetters.Add(GetColumnAddress(cell.CellReference));                        
}

ExcelReader 类中的 `GetCellsForRow` 函数将会在电子表格行中缺失单元格的位置添加空单元格，并返回一个包含完整单元格数量的可枚举对象。请注意，该逻辑会检查任何特定行开头、中间和末尾的缺失单元格。

private static IEnumerable<Cell> GetCellsForRow(Row row, List<string> columnLetters)
{
    int workIdx = 0;        
    foreach (var cell in row.Descendants<Cell>())
    {
        //Get letter part of cell address
        var cellLetter = GetColumnAddress(cell.CellReference);

        //Get column index of the matched cell  
        int currentActualIdx = columnLetters.IndexOf(cellLetter);

        //Add empty cell if work index smaller than actual index
        for (; workIdx < currentActualIdx; workIdx++)
        {
            var emptyCell = new Cell() 
                            { DataType = null, CellValue = new CellValue(string.Empty) };
            yield return emptyCell;
        }
                
        //Return cell with data from Excel row
        yield return cell;
        workIdx++;

        //Check if it's ending cell but there still is any unmatched columnLetters item   
        if (cell == row.LastChild)
        {
            //Append empty cells to enumerable 
            for (; workIdx < columnLetters.Count(); workIdx++)
            {
                var emptyCell = new Cell() 
                                { DataType = null, CellValue = new CellValue(string.Empty) };
                yield return emptyCell;
            }
        }                
    }                
}

检测已使用的范围

与 Office Interop 不同，Open XML SDK 库中没有 `Range` 对象。根据我的尝试，`WorksheetPart.Worksheet.SheetDimention` 也不起作用。检测和绕过空行是必要的，因为 Excel 工作表在用户删除整个数据行后有时会留下空行。在这种情况下，在数据传输过程中会发生错误或效率低下的迭代。

`GetUsedRows` 函数展示了如何查找至少有一个单元格值的行，并返回这些行作为可枚举对象，无论空行在什么位置。

private static IEnumerable<Row> GetUsedRows(SpreadsheetDocument document, WorksheetPart wsPart)
{
    bool hasValue;        
    //Iterate all rows except the first one.
    foreach (var row in wsPart.Worksheet.Descendants<Row>().Skip(1))
    {
        hasValue = false;
        foreach (var cell in row.Descendants<Cell>())
        {
            //Find at least one cell with value for a row
            if (!string.IsNullOrEmpty(GetCellValue(document, cell)))
            {
                hasValue = true;
                break;
            }
        }
        if (hasValue)
        {
            //Return the row and keep iteration state.
            yield return row;
        }
    }
}

数据映射函数

数据映射功能取决于电子表格列、对象模型属性和数据类型。最优的方法是将映射过程委托给一个作为参数传递给 `ExcelReader.GetDataToList` 函数的函数。在示例应用程序中，`AddProductData` 函数执行数据映射，然后将数据加载到 `Product` 对象中。

//Function for mapping and entering data into Product object.
private static Product AddProductData(IList<string> rowData, IList<string> columnNames)
{
    var product = new Product()
    {
        ProductID = rowData[columnNames.IndexFor("ProductID")].ToInt32(),
        ProductName = rowData[columnNames.IndexFor("ProductName")],
        CategoryID = rowData[columnNames.IndexFor("CategoryID")].ToInt32Nullable(),
        UnitPrice = rowData[columnNames.IndexFor("UnitPrice")].ToDecimalNullable(),
        OutOfStock = rowData[columnNames.IndexFor("OutOfStock")].ToBoolean(),
        StockDate = rowData[columnNames.IndexFor("StockDate")].ToDateTimeNullable()
    };            
    return product;
}

映射函数实际上是在迭代电子表格行的过程中被调用的，并返回一个完整的数据对象，该对象将依次被添加到输出的通用列表中。

//Calls the delegated function to add it to the collection.
resultList.Add(addProductData(rowData, columnNames));

ExcelReader 类提供了一些附加功能，以使数据映射和类型转换更加方便高效。接下来的章节将详细讨论其中两项功能。

使用列名映射数据

使用列名而不是索引来映射和输入数据是一种非常用户友好且易于维护的方法。可以忽略数据源文件和映射过程中的列顺序。为了实现这一点，我们需要创建一个通用列表来缓存自定义列名。

var columnNames = new List<string>();

//Iterate cells of custom header row.
foreach (Cell cell in wsPart.Worksheet.Descendants<Row>().ElementAt(0))
{
    //Get custom column names.
    //Remove spaces, symbols (except underscore), 
    //and make lower cases and for all values in columnNames list.                    
    columnNames.Add(Regex.Replace
                (GetCellValue(document, cell), @"[^A-Za-z0-9_]", "").ToLower());

    //Other code...                        
}

添加到列表项中的列名仅包含字母数字字符，除了下划线（C# 对象属性名允许的字符）之外不包含符号，并且全部是小写。这是一个非常健壮的功能，因为电子表格中的列名可以是任何大小写、任何位置的空格，以及任何符号。回看源 Excel 电子表格截图，您可以看到在列名后附加了星号，例如“Product Name*”，表示必填字段。您还可以看到所有用户友好的列名，例如“Out of Stock”，而不是“OutOfStock”。对于这些示例，映射的输出将正确显示 `Product` 对象的 `ProductName` 和 `OutOfStock` 属性。

通过向 `ExcelReader` 类添加一个 `List` 类扩展方法 `IndexFor`，可以进一步优化不区分大小写的映射步骤，这样我们就可以在数据映射函数中使用扩展方法，而不是为每个项目调用原生的 `IndexOf` 和 `ToLower` 方法。

public static int IndexFor(this IList<string> list, string name)
{        
    int idx = list.IndexOf(name.ToLower());
    if (idx < 0) 
    {
        throw new Exception(string.Format("Missing required column mapped to: {0}.", name));
    }
    return idx;
}

字符串到原始数据类型的转换

从电子表格检索的所有单元格值都是字符串形式，如果底层数据类型不是字符串，则需要将其显式转换为数据模型属性的类型。ExcelReader 类提供了 `String` 类扩展方法，使类型转换代码整洁且集中。

  • ToInt32()
  • ToDouble()
  • ToDecimal()
  • ToDateTime()
  • ToBoolean()
  • ToGuid()

每个方法都有其可空版本，例如 `ToInt32Nullable()`。您可以在前面展示的 `AddProductData` 函数代码中看到转换方法的易用性。如果需要，您也可以添加自己的方法来进行任何其他类型转换。

值得注意的是，对于 Excel 电子表格中的日期/时间条目，如果列或单元格没有明确设置为文本格式，Excel 将输出 OLE 自动化日期/时间（`OADate` 格式，即 `Double` 类型的数字，引用 12/31/1899 午夜）。下面是一个示例，说明转换器如何处理接收到 `OADate` 格式日期/时间的情况。

public static DateTime? ToDateTimeNullable(this string source)
{
    DateTime outDt;        
    if (DateTime.TryParse(source, out outDt))
    {
        return outDt;
    }
    else
    {
        //Check and handle OLE Automation date time
        if (IsNumeric(source))
        {
            return DateTime.FromOADate(source.ToDouble());
        }
        return (DateTime?)null;                       
    }
}

虽然示例 `AddProductData` 函数中的代码仅为简单的对象模型映射数据，但您可以为包含分层结构的复杂对象使用相同的场景进行映射。只需确保模型属性和所有子对象属性与您计划的数据传输的电子表格列匹配即可。

摘要

这里提供的 `ExcelReader` 类易于使用、功能强大，并且不依赖 Office Interop，可以从 Excel 电子表格导入数据到通用列表。我希望本文和示例应用程序能对需要处理类似领域开发的开发者有所帮助。

历史

  • 2014 年 5 月 8 日：初稿
© . All rights reserved.