C# Excel 导入导出实战：多层表头解析 + 合并单元格展开 + 必填列校验 + 多 Sheet 合并

做过业务系统的都懂：Excel 导入才是真正的”需求黑洞”。客户给的模板从来不是干净的二维表——标题行占了前两行、表头合并单元格、大类下面挂子列、一个文件里放好几个 Sheet、有的 Sheet 是”新增”有的是”删除”。如果用最朴素的 Cells[0,0] 逐格读取，代码会迅速膨胀成一堆 if (row == 3) continue; 的补丁。

这篇文章拆解一个在生产环境跑了很久的 Excel 封装：ExcelWorkbook / ExcelWorksheet / 静态 ExcelHelper 三层结构。它不是”又一个 Excel 帮助类”，而是把表头定位、合并单元格语义、列名校验、多 Sheet 归并这四类脏活收敛成几个参数。读完之后，你面对再离谱的模板也能用一行调用搞定。

底层依赖是商业库 Aspose.Cells（需要授权），但本文讨论的分层思路和参数设计，换成 NPOI、MiniExcel 也同样适用。

一、为什么不能直接读成 DataTable

先说三个真实场景：

1. 双层表头：第一行是”PCC套餐状态1 / PCC套餐状态2”这种大类，第二行才是具体列名。直接按首行转列名，会得到一堆空列名或 Column1。
2. 合并单元格：Aspose 的 ExportDataTableAsString 导出时，合并区域只有左上角有值，其余是空。下游按列取值时全是 DBNull。
3. 多 Sheet 语义不同：同一个文件里，新增 页签和 删除 页签结构一样但语义相反，需要加一列”来源类型”来区分。

所以封装的核心不是”能读”，而是把模板的视觉结构翻译成程序能理解的结构。

flowchart TD
    A[Excel 文件] --> B[ExcelWorkbook 打开工作簿]
    B --> C{选择 Sheet}
    C --> D[ExcelWorksheet 定位数据区]
    D --> E[拆分合并单元格 + 填充值]
    E --> F[多层表头合成列名]
    F --> G[必填列校验]
    G -->|缺列| H[抛出中文错误]
    G -->|通过| I[导出 DataTable]
    I --> J{多 Sheet?}
    J -->|是| K[裁剪列 + 加来源列 + Merge]
    J -->|否| L[交给业务层]
    K --> L

二、三层结构：工作簿、工作表、静态入口

ExcelWorkbook 持有 Aspose 的 Workbook，只负责”有哪些 Sheet”和”按名字/索引取 Sheet”；ExcelWorksheet 负责单个表的数据区计算；ExcelHelper 是一组静态重载，把常见调用组合成一行。

public sealed class ExcelWorkbook
{
    private readonly Workbook workbook;

    public ExcelWorkbook(string fileName)
    {
        workbook = new Workbook(fileName);
    }

    public string[] SheetNames
    {
        get
        {
            sheetNames = new string[workbook.Worksheets.Count];
            for (int i = 0; i < workbook.Worksheets.Count; i++)
            {
                sheetNames[i] = workbook.Worksheets[i].Name;
            }
            return sheetNames;
        }
    }

    public ExcelWorksheet GetAt(string sheetName)
        => new ExcelWorksheet(workbook, sheetName);
}

这样做有个实际好处：同一个文件只解析一次。很多实现里 Load(fileName, sheet1)、Load(fileName, sheet2) 各开一次文件，10MB 的 xlsx 就要解析好几遍。持有 ExcelWorkbook 后，多个 Sheet 共用一次解析结果：

ExcelWorkbook workbook = new ExcelWorkbook(fileName);
SourceTable = ExcelHelper.Load(workbook, "基本信息", requiredColumns: new[] { "工单号", "文件号" });
SourceTable = ExcelHelper.Load(workbook, "TAC4GConfigData", requiredColumns: columns);

三、数据区定位：MinRow 与 MaxDataRow 的区别

Aspose 有 MaxRow 和 MaxDataRow 两个属性，差别很关键：

• MaxRow：包含带格式的空行的最大行号。模板里给空白行刷了边框，MaxRow 就会虚高几百行。
• MaxDataRow：真正有数据的最大行号。

如果拿 MaxRow 当边界，DataTable 里会塞进一堆空行，后面落库时要额外清洗。正确写法是：

public DataTable Load(bool includeColumnName)
{
    Cells cells = worksheet.Cells;

    if (cells.Count < 1 || cells.MaxDataRow < 0)
    {
        return DataCommon.BuildDataTable(1);
    }

    return Load(
        cells.MinRow,
        cells.MinColumn,
        cells.MaxDataRow - cells.MinRow + 1,
        cells.MaxDataColumn - cells.MinColumn + 1,
        includeColumnName);
}

注意 MinRow / MinColumn 也不能写死成 0——模板左上角经常留白（比如从 B3 开始才是数据），用 MinRow/MinColumn 自动贴合真实起点，能省掉一堆偏移量参数。

空表统一返回 BuildDataTable(1)（一个只有一列的空表），是为了让下游 dataTable.Rows.Count == 0 的判断不抛 NullReferenceException，避免到处写空判断。

四、核心：多层表头怎么合成唯一列名

这是整个封装最有价值的部分。签名如下：

public DataTable Load(
    int columnNameRowIndex,      // 主表头所在行
    int subColumnNameRowIndex,   // 子表头所在行（-1 表示没有）
    IReadOnlyList<string> prefixColumnNames,  // 需要拼接前缀的大类名
    bool joinPrefixColumnName,   // 是否自动把所有上级表头都拼进来
    bool splitMergedCells)       // 是否先拆合并单元格

处理顺序是：先拆合并单元格 → 再合成列名 → 最后按字符串导出。

4.1 拆合并单元格：复制值而不是 UnMerge

很多人第一反应是调用 UnMerge()，但 Aspose 解除合并后只保留左上角的值，其余格子还是空的。正确做法是把左上角的值铺满整个区域：

public void SplitMergedCells()
{
    Cells cells = worksheet.Cells;

    foreach (CellArea area in cells.MergedCells)
    {
        for (int i = area.StartRow; i <= area.EndRow; i++)
        {
            for (int j = area.StartColumn; j <= area.EndColumn; j++)
            {
                cells[i, j].Value = cells[area.StartRow, area.StartColumn].Value;
            }
        }
    }
}

还有一个”只拆表头区域”的重载 SplitMergedCells(int startRow, int endRow)：数据区如果有纵向合并（比如同一个工单号跨多行），铺满反而会破坏业务语义，所以只处理表头那几行就够了。

4.2 两种前缀拼接策略

同一份模板里，大类名往往要拼进子列名，否则 "状态1"、"状态2" 这种子列名会重复。封装提供了两种模式：

模式一：指定前缀列（推荐，最可控）

if (columnName.Length > 0 && subColumnName.Length > 0
    && prefixColumnNames.Contains(columnName))
{
    cells[subColumnNameRowIndex, j].Value = columnName + "-" + subColumnName;
}

调用方明确告诉封装”哪几个大类要拼”，产出 PCC套餐状态1-限速值 这样稳定的列名。业务代码里就能直接写常量：

SourceTable = ExcelHelper.Load(
    excelWorkbook, 0,
    columnNameRowIndex: 0,
    subColumnNameRowIndex: 1,
    requiredColumns: columns,
    prefixColumnNames: new[] { "PCC套餐状态1", "PCC套餐状态2" });

模式二：自动拼接所有上级表头

不知道模板有几个层级时，把主表头行到子表头行之间的非空值全部收集起来拼成 A-B：

var prefixColumnNameList = new List<string>();
for (int j = cells.MinColumn; j <= cells.MaxDataColumn; j++)
{
    for (int k = columnNameRowIndex; k <= subColumnNameRowIndex; k++)
    {
        string name = cells[k, j].Value?.ToString()?.Trim() ?? string.Empty;
        if (name.Length > 0 && !prefixColumnNameList.Contains(name))
        {
            prefixColumnNameList.Add(name);
        }
    }

    if (prefixColumnNameList.Count > 1)
    {
        cells[subColumnNameRowIndex, j].Value = string.Join("-", prefixColumnNameList);
    }

    prefixColumnNameList.Clear();
}

去重用的是 List.Contains 而不是 HashSet——因为表头列数通常只有几十列，保持列内顺序比 O(1) 查找更重要，HashSet 会打乱 A-B 的拼接顺序。

4.3 必须按字符串导出

导出统一走 ExportDataTableAsString，不是 ExportDataTable：

DataTable dataTable = cells.ExportDataTableAsString(
    startRow, cells.MinColumn, rowsToRead, columns, false);

原因很实际：模板里手机号、身份证、长编号一旦被识别成数字，就会变成 1.3812345678E+10 或者丢掉前导零。按字符串导出后，类型转换交给业务层显式处理，反而更安全。

导出后立刻 Trim(true, true)：把 null 转成空串、去掉首尾空格，再 FirtRowToColumnName() 把首行提升为列名。这样列名不会带尾部空格导致 Columns.Contains("工单号") 判断失败。

五、必填列校验：把错误提前到导入瞬间

最烦的 bug 是”导入了 8000 行，跑到第 5000 行才因为列名对不上炸掉”。校验放在 Load 之后、业务处理之前：

if (!requiredColumns.IsNullOrEmpty())
{
    var missingColumns = requiredColumns
        .Where(name => dataTable.Columns.Contains(name) == false);

    if (!missingColumns.IsNullOrEmpty())
    {
        throw new Exception(string.Format(
            "[{0}]表单缺少[{1}]字段。",
            excelWorksheet.Name,
            string.Join(",", missingColumns)));
    }
}

错误信息里带上 Sheet 名 + 缺失列名，运营同学把 Excel 改好再传即可，不用来问你”到底少了哪一列”。这一个小细节能省掉大量来回沟通。

六、多 Sheet 合并：裁剪列 + 来源标记 + 去空行

业务上常见”新增/修改/删除”三个页签，列结构相同但要标记来源。核心逻辑分三步：

1. 取第一个存在的 Sheet 作为基准，用 CopyData(requiredColumns) 只保留需要的列（避免模板里那些说明性列污染结果集）；
2. 给基准表加一列”更新类型”，用 FillColumn 整列填充；
3. 其余 Sheet 逐行裁剪列后追加，最后 RemoveEmptyRows() 清掉模板尾部空行。

resultTable.Columns.Add(updateTypeColumnName);
resultTable.FillColumn(updateTypeColumnName, updateTypes[i]);

调用侧非常短，updateTypes 与 sheetNames 一一对应：

SourceTable = ExcelHelper.Load(
    fileName,
    sheetNames,                       // 如 { "新增", "修改", "删除" }
    UpdateTypeColumn.SourceName,      // 结果表里新增的标识列名
    updateTypes,                      // 如 { "新增", "修改", "删除" }
    requiredColumns);

如果所有页签都不存在，通过 throwException 参数决定是抛异常还是返回 null——有些业务场景”文件里没有这个页签就是不需要更新”，返回 null 让调用方跳过即可。

还有更简单的一种：全量汇总表所有 Sheet 结构完全一致，直接逐页 Merge：

public static DataTable LoadJtSummaryTable(string fileName)
{
    ExcelWorkbook excelWorkbook = new ExcelWorkbook(fileName);

    DataTable dataTable = null;

    foreach (var sheetName in excelWorkbook.SheetNames)
    {
        if (dataTable == null)
        {
            dataTable = ExcelHelper.Load(excelWorkbook, sheetName);
        }
        else
        {
            dataTable.Merge(ExcelHelper.Load(excelWorkbook, sheetName));
        }
    }

    return dataTable;
}

DataTable.Merge 会自动按列名对齐，同名同类型列合并，多出的列会补进结果表。所以前提是各 Sheet 表头必须一致，不然会得到一堆稀疏列。

七、写出：三种数据源 + 列索引映射

导出侧同样用重载消除差异，支持 DataTable、DataView、IDataReader 三种来源。内部统一转成列索引再写单元格，因为列名查找只做一次，比循环里反复 Columns["xxx"] 快很多：

public static int Save(Stream sourceStream, string destFileName, int sheetIndex,
    int firstRow, int firstColumn, FileFormatType type,
    DataTable dataTable, IReadOnlyList<int> columnIndices, bool includeColumnName)
{
    Workbook book = sourceStream != null ? new Workbook(sourceStream) : new Workbook();
    Cells cells = book.Worksheets[sheetIndex].Cells;

    int j = 0;
    if (includeColumnName)
    {
        foreach (int index in columnIndices)
        {
            cells[firstRow, firstColumn + j].PutValue(dataTable.Columns[index].ColumnName);
            j++;
        }
        firstRow++;
    }

    DataRowCollection rows = dataTable.Rows;
    for (int i = 0; i < rows.Count; i++)
    {
        j = 0;
        foreach (int index in columnIndices)
        {
            cells[firstRow + i, firstColumn + j].PutValue(rows[i][index].ToString());
            j++;
        }
    }

    book.Save(destFileName, GetSaveFormat(type));
    return rows.Count;
}

两个设计点值得注意：

• sourceStream 允许传模板流。导出时用已有模板打开，只往指定位置写数据，样式、冻结窗格、列宽都保留，不用在代码里重新画一遍格式。
• PutValue(row.ToString()) 统一按字符串写。日期、金额的最终显示格式交给 Excel 单元格格式控制，避免 DateTime 写进去后被本地化设置改成奇怪的格式。

多 Sheet 导出则把 DataSet 的每个 DataTable.TableName 直接作为 Sheet 名：

public static void Save(string fileName, DataSet dataSet, bool includeColumnName)
{
    Workbook book = new Workbook();

    for (int index = 0; index < dataSet.Tables.Count; index++)
    {
        var dataTable = dataSet.Tables[index];

        while (book.Worksheets.Count <= index)
        {
            book.Worksheets.Add();
        }

        book.Worksheets[index].Name = dataTable.TableName;

        Cells cells = book.Worksheets[index].Cells;
        int firstRow = 0, firstColumn = 0;

        if (includeColumnName)
        {
            for (int j = 0; j < dataTable.Columns.Count; j++)
            {
                cells[firstRow, firstColumn + j].PutValue(dataTable.Columns[j].ColumnName);
            }
            firstRow++;
        }

        DataRowCollection rows = dataTable.Rows;
        for (int i = 0; i < rows.Count; i++)
        {
            for (int j = 0; j < dataTable.Columns.Count; j++)
            {
                cells[firstRow + i, firstColumn + j].PutValue(rows[i][j].ToString());
            }
        }
    }

    book.Save(fileName, SaveFormat.Xlsx);
}

顺带一句：DataTable.TableName 是有长度和字符限制的（Excel Sheet 名不超过 31 字符，且不能含 : \ / ? * [ ]）。如果 TableName 来自用户输入，记得先做一次净化，否则 book.Save 会抛异常。

八、踩过的坑与经验

1. MaxRow 会虚高：模板刷了格式的空行会让它偏大，永远用 MaxDataRow 定数据边界。
2. 合并单元格必须铺值：UnMerge 只清格式不复制值，铺满区域才能被 ExportDataTableAsString 读到。
3. 表头去重要保序：拼接前缀时用 List + Contains，HashSet 会打乱 大类-子列 的顺序，导致列名不稳定。
4. 列名统一 Trim："工单号 " 和 "工单号" 是两个列名，Columns.Contains 会返回 false，校验必须在 Trim 之后做。
5. 空表返回结构完整的空 DataTable：不要返回 null，让下游能安全访问 Rows.Count。
6. 删除行要倒序遍历：清理空行时从 Rows.Count - 1 往前删，正序删除会漏行；同时要跳过 DataRowState.Deleted 的行，访问它的 Current 数据会抛异常。
7. 大文件用分页读取：封装里额外提供了 LoadWithOffset(offset, limit, includeColumnName)，按偏移量分批导出，避免一次把几十万行塞进内存。
8. 一个文件只解析一次：多个 Sheet 共用同一个 ExcelWorkbook 实例，别在每次 Load 里重复 new Workbook(fileName)。

九、小结

这个封装的本质是把”模板长什么样”这件事参数化：表头在第几行、有没有子表头、哪些大类要拼前缀、需要哪些必填列、多个 Sheet 各自什么语义，全部通过方法参数表达，业务层只保留一行调用。

具体到可复用的经验，有三条：

• 结构先于逻辑：先用 MinRow/MinColumn/MaxDataRow/MaxDataColumn 把数据区框出来，再谈取值；
• 校验前置：列名缺失在 Load 阶段就报错，别让它流到第 5000 行；
• 类型后置：Excel 一律按字符串读，类型转换在业务层显式做，能避开科学计数法、前导零、日期本地化这三类经典坑。

同样的分层思路换成 NPOI 也能落地——Sheet.GetRow / GetCell 代替 Cells[i, j]，判断逻辑完全一致。Excel 导入从来不是技术难题，难的是把”客户模板的随意”收敛成”代码约束的确定”，而这正是封装的价值所在。