c#中基于Aspose.Cells组件，实现多表头Excel的导入和导出

最近处理了一个需求，把一个Excel文件的数据读取出来做分析，但这个Excel文件是多表头的。经过开发实现了这个功能，和没有表头的读取操作还是有区别的。下面就分享下这个功能的开发思路，基于Aspose.Cells组件

先制作一个测试文件，表头如下图所示

导入多表头Excel文档

导入的方法，要传入很重要的一个参数，就是有几级表头（上图有3级）。只要这个确定了，才能实现导入多表头的功能。思路也简单，就是先遍历出哪些单元格是合并的。读取表头时，如果是合并的就找上一级的单元格，然后所有层的表头再拼到一起

调用的代码

//(ok)多表头的Excel文件，导入
string xlsFile1 = SystemHelper.GetSystemPath(SystemFolderName.Desktop) + @"\" + @"tempfile\多表头测试.xlsx";
DataTable dtData = NS.Utility.Office.ExcelUtil.ImportDataTableByMultiTitle(xlsFile1, "Sheet1", 2);

实现的完整代码

/// <summary>
/// 若是多表头，则需拼接父层级的标题
/// </summary>
/// <param name="workSheet"></param>
/// <param name="titleLevel"></param>
/// <param name="columnIndex"></param>
/// <param name="dicMerge"></param>
/// <returns></returns>
private static string GetColumnNameByMultiTitle(Worksheet workSheet, int titleLevel, int columnIndex,Dictionary<string,List<string>>dicMerge)
{
    string columnName = null;

    if (titleLevel == 0)
    {
        columnName = workSheet.Cells[titleLevel, columnIndex].Value.ToString();
    }
    else
    {
        List<string> listP = new List<string>();

        int level = titleLevel;
        while (level >= 0)
        {
            Aspose.Cells.Cell cell = workSheet.Cells[level, columnIndex];
            object cellValue = cell.Value;
            if (StringHelper.ObjectIsNullOrEmpty(cellValue))
            {
                //若为空，判断是不是合并单元格
                if (cell.IsMerged)
                {
                    string search = string.Format("{0}-{1}", cell.Row, cell.Column);
                    foreach(string key in dicMerge.Keys)
                    {
                        List<string> listValue = dicMerge[key];
                        if (listValue.Contains(search))
                        {
                            //若找到，则使用Key的单元格值
                            string[] arrayKey = key.Split('-');
                            int mergeRow = Convert.ToInt32(arrayKey[0]);
                            int mergeCol = Convert.ToInt32(arrayKey[1]);

                            cellValue = workSheet.Cells[mergeRow, mergeCol].Value;
                            break;
                        }
                    }
                }            
            }

            if (!listP.Contains(cellValue.ToString()))
                listP.Add(cellValue.ToString());

            level--;
        }        

        //需要反转
        listP.Reverse();

        //再拼接
        columnName = string.Join("-", listP);
    }

    return columnName;
}

/// <summary>
/// 导入Excel文件数据，支持多表头
/// </summary>
/// <param name="xlsFile"></param>
/// <param name="sheetName"></param>
/// <param name="titleLevel">表头最明细的行号。注：行号从0开始</param>
/// <returns></returns>
public static DataTable ImportDataTableByMultiTitle(string xlsFile, string sheetName = "Sheet1", int titleLevel = 0)
{
    //参数检测
    if (!System.IO.File.Exists(xlsFile))
    {
        throw new Exception("Excel文件未找到");
    }

    if (string.IsNullOrEmpty(sheetName))
    {
        sheetName = "Sheet1";
    }

    Workbook workBook = new Workbook(xlsFile);
    if (workBook == null || workBook.Worksheets == null || workBook.Worksheets.Count <= 0)
    {
        throw new Exception("获取Sheet页集合失败");
    }

    Worksheet workSheet = workBook.Worksheets[sheetName];
    if (workSheet == null)
    {
        throw new Exception("获取Sheet名为[" + sheetName + "]的页失败");
    }

    DataTable dataTable = null;

    //获取栏目个数,若不存在,则继续循环.注:不知为何,需要加1
    int columnCount = workSheet.Cells.MaxColumn + 1;
    if (columnCount <= 0)
        return dataTable;

    dataTable = new DataTable();

    //先将合并的单元格找出来，用于拼接标题
    List<string> listP2 = new List<string>();
    Dictionary<string, List<string>> dicMerge = new Dictionary<string, List<string>>();
    foreach (var mergeCell in workSheet.Cells.MergedCells)
    {
        //Aspose.Cells.CellArea(A1:A3)[0,0,2,0]
        string mergeCell2 = mergeCell.ToString();
        listP2.Add(mergeCell2);

        string temp1 = StringHelper.Between(mergeCell2, "[", "]");
        string[] array2 = temp1.Split(',');

        int startRow = Convert.ToInt32(array2[0]);
        int startCol = Convert.ToInt32(array2[1]);
        int endRow = Convert.ToInt32(array2[2]);
        int endCol = Convert.ToInt32(array2[3]);
        string key = string.Format("{0}-{1}", startRow, startCol);

        List<string> listValue = new List<string>();
        for(int i = 0; i <= endRow - startRow; i++)
        {
            for (int j = 0; j <= endCol - startCol; j++)
            {
                string temp = string.Format("{0}-{1}", startRow + i, startCol + j);
                listValue.Add(temp);
            }
        }

        dicMerge.Add(key, listValue);
    }

    //生成DataTable对象表结构,即获取第一行单元格值
    List<string> listCol = new List<string>();
    for (int j = 0; j < columnCount; j++)
    {
        string columnName = ExcelUtil.GetColumnNameByMultiTitle(workSheet, titleLevel, j, dicMerge);
        if (StringHelper.ObjectIsNullOrEmpty(columnName))
        {
            columnName = "ColumnName" + (j + 1).ToString();
        }
        dataTable.Columns.Add(columnName.ToString(), typeof(string));

        listCol.Add(columnName.ToString());
    }
    string cols = string.Join(Environment.NewLine, listCol);

    //填充数据
    int rowCount = workSheet.Cells.MaxDataRow + 1 - titleLevel;
    if (rowCount > 0)
    {
        for (int m = 1; m < rowCount; m++)
        {
            DataRow dataRow = dataTable.NewRow();
            for (int n = 0; n < columnCount; n++)
            {
                object cellValue = workSheet.Cells[titleLevel + m, n].Value;
                dataRow[n] = cellValue;
            }
            dataTable.Rows.Add(dataRow);
        }
    }

    return dataTable;
}

导出多表头Excel文档

目前仅支持2层多表头，直接在DataColumn.Caption中体现。有兴趣的小伙伴可以扩展支持3层或无限层。

调用的代码

//(ok)多表头的Excel文件，导出，方法3
DataTable dtData = new DataTable();
dtData.Columns.Add("ShopCode", typeof(string));
dtData.Columns.Add("SaleUser", typeof(string));
dtData.Columns.Add("AliSaleCount", typeof(int));
dtData.Columns.Add("AliSaleAmount", typeof(decimal));
dtData.Columns.Add("SheSaleCount", typeof(int));
dtData.Columns.Add("SheSaleAmount", typeof(decimal));
dtData.Columns.Add("AmaSaleCount", typeof(int));
dtData.Columns.Add("AmaSaleAmount", typeof(decimal));
dtData.Columns.Add("SumSaleCount", typeof(int));
dtData.Columns.Add("SumSaleAmount", typeof(decimal));
dtData.Columns.Add("Remarks", typeof(string));
dtData.Columns[0].Caption = "店铺";
dtData.Columns[1].Caption = "运营人";
dtData.Columns[2].Caption = "速卖通-订单量";
dtData.Columns[3].Caption = "速卖通-销售额";
dtData.Columns[4].Caption = "Shopee-订单量";
dtData.Columns[5].Caption = "Shopee-销售额";
dtData.Columns[6].Caption = "亚马逊-订单量";
dtData.Columns[7].Caption = "亚马逊-销售额";
dtData.Columns[8].Caption = "合计-订单量";
dtData.Columns[9].Caption = "合计-销售额";
dtData.Columns[10].Caption = "备注";

string xlsFile2 = SystemHelper.GetSystemPath(SystemFolderName.Desktop) + @"\" + @"tempfile\多表头测试-export2.xlsx";
if (System.IO.File.Exists(xlsFile2))
    System.IO.File.Delete(xlsFile2);

NS.Utility.Office.ExcelUtil.ExportDataTableByMultiTitle(dtData, xlsFile2);

实现的完整代码

/// <summary>
/// 导出数据至Excel文件，支持多表头
/// 目前仅支持2级
/// </summary>
/// <param name="dtData"></param>
/// <param name="xlsFile"></param>
public static void ExportDataTableByMultiTitle(DataTable dtData, string xlsFile)
{
    int rowLevel = 0;
    List<ExportMultiTitleColumnModel> listCol = new List<ExportMultiTitleColumnModel>();

    //生成列信息，包括是否合并等
    foreach (DataColumn col in dtData.Columns)
    {
        string caption = col.Caption;
        if (caption.IndexOf('-') >= 0)
        {                  
            //表示多表头
            string[] array1 = caption.Split('-');
            rowLevel = Math.Max(rowLevel, array1.Length);

            listCol.Add(new ExportMultiTitleColumnModel()
            {
                Caption = caption,
                ColumnTitle = array1[1],
                ParentColumn = array1[0],
            });
        }
        else
        {
            listCol.Add(new ExportMultiTitleColumnModel()
            {
                Caption = caption,
                ColumnTitle = caption,
            });
        }
    }

    var g1All = listCol.Where(c => !string.IsNullOrEmpty(c.ParentColumn)).GroupBy(c => new { c.ParentColumn });
    foreach (var g1 in g1All)
    {
        string parent = g1.Key.ParentColumn;
        var list1 = listCol.Where(c => c.ParentColumn == parent).ToList();

        //计算有几列？
        listCol.Add(new ExportMultiTitleColumnModel()
        {
            Caption = parent,
            ColumnTitle = parent,
            ColSpan = list1.Count,
        });
    }

    //用于操作的单元格集合
    Workbook workBook = new Workbook();
    workBook.Worksheets.Clear();

    string tableName = dtData.TableName;
    if (string.IsNullOrEmpty(tableName))
        tableName = "Sheet1";

    workBook.Worksheets.Add(tableName);
    Worksheet workSheet = workBook.Worksheets[0];
    
    Aspose.Cells.Cells cells = workSheet.Cells;

    //记录已添加的父级标题
    List<string> listParentCol = new List<string>();

    for(int i = 0; i < dtData.Columns.Count; i++)
    {
        DataColumn dc = dtData.Columns[i];
        
        string caption = dc.Caption;
        var modelCol = listCol.FirstOrDefault(c => c.Caption == caption);

        if (rowLevel > 0)
        {
            //说明存在多表头
            if (string.IsNullOrEmpty(modelCol.ParentColumn))
            {
                cells[0, i].PutValue(modelCol.ColumnTitle);

                //合并单元格
                cells.Merge(0, i, rowLevel, 1);
            }
            else
            {
                if (!listParentCol.Contains(modelCol.ParentColumn))
                {
                    var modelParentCol = listCol.FirstOrDefault(c => c.Caption == modelCol.ParentColumn);

                    cells[0, i].PutValue(modelCol.ParentColumn);
                    cells.Merge(0, i, 1, modelParentCol.ColSpan);

                    listParentCol.Add(modelCol.ParentColumn);
                }

                cells[1, i].PutValue(modelCol.ColumnTitle);
            }
        }
        else
        {
            cells[0, i].PutValue(modelCol.ColumnTitle);
        }
    }

    workBook.Save(xlsFile);            
}

相关阅读

c#中使用Aspose.Cells组件，将图片导出至Excel示例