如何用ClosedXML在C#中为Excel工作表添加自定义表头

问题原因 #

直接通过wb.Worksheets.Add(dt)添加DataTable时，表格会从工作表第一行开始渲染，导致自定义表头被覆盖。需要先手动构建自定义表头，再追加数据内容。

修正后的完整代码 #

// 构建匹配需求的数据表格
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[4] 
{ 
    new DataColumn("Month"),
    new DataColumn("NewEmpCount"),
    new DataColumn("RenewalEmpCount"),
    new DataColumn("TotalEmpCount")
});

List<Student> details = new StudentRepository().GetCount();

foreach (var item in details)
{
    // 注意：实体类属性名避免使用C#关键字（如new），此处假设属性名为New、Renewal、Total
    dt.Rows.Add(
        item.Month,
        item.New, 
        item.Renewal,
        item.Total
    );
}

using (XLWorkbook wb = new XLWorkbook())
{
    var ws = wb.Worksheets.Add("Summary");
    
    // 1. 写入顶部自定义信息
    string currentMonth = DateTime.Now.ToString("MMM yy");
    string generateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
    
    // 第一行：Generated till（跨列合并）
    var row1 = ws.Row(1);
    row1.Cell(1).Value = $"Generated till : {currentMonth}";
    row1.Range(1, 4).Merge(); // 合并A1到D1
    row1.Style.Font.Bold = true;
    
    // 第二行：生成时间（跨列合并）
    var row2 = ws.Row(2);
    row2.Cell(1).Value = $"reported generate time: {generateTime}";
    row2.Range(1, 4).Merge(); // 合并A2到D2
    row2.Style.Font.Bold = true;
    
    // 2. 写入复合表头
    // 表头第一行
    var row3 = ws.Row(3);
    row3.Cell(1).Value = "";
    row3.Cell(2).Value = "current";
    row3.Cell(3).Value = "renewal";
    row3.Cell(4).Value = "Total";
    row3.Style.Font.Bold = true;
    row3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
    
    // 表头第二行
    var row4 = ws.Row(4);
    row4.Cell(1).Value = "Month";
    row4.Cell(2).Value = "Emp.Count";
    row4.Cell(3).Value = "Emp.Count";
    row4.Cell(4).Value = "Emp.Count";
    row4.Style.Font.Bold = true;
    row4.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
    
    // 3. 写入数据行（从第5行开始）
    int startRow = 5;
    foreach (DataRow dr in dt.Rows)
    {
        ws.Row(startRow).Cell(1).Value = dr["Month"].ToString();
        ws.Row(startRow).Cell(2).Value = Convert.ToInt32(dr["NewEmpCount"]);
        ws.Row(startRow).Cell(3).Value = Convert.ToInt32(dr["RenewalEmpCount"]);
        ws.Row(startRow).Cell(4).Value = Convert.ToInt32(dr["TotalEmpCount"]);
        startRow++;
    }
    
    // 4. 写入总计行
    var totalRow = ws.Row(startRow);
    totalRow.Cell(1).Value = "Total";
    // 使用Excel公式自动计算各列总和
    totalRow.Cell(2).FormulaA1 = $"SUM(B{startRow - dt.Rows.Count}:B{startRow - 1})";
    totalRow.Cell(3).FormulaA1 = $"SUM(C{startRow - dt.Rows.Count}:C{startRow - 1})";
    totalRow.Cell(4).FormulaA1 = $"SUM(D{startRow - dt.Rows.Count}:D{startRow - 1})";
    totalRow.Style.Font.Bold = true;
    
    // 自动调整列宽适配内容
    ws.Columns(1, 4).AdjustToContents();
    
    using (MemoryStream stream = new MemoryStream())
    {
        wb.SaveAs(stream);
        return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Summary.xlsx");
    }
}

关键调整说明 #

• 移除DataTable中多余的Billing Description列，匹配需求的表格结构
• 放弃直接添加DataTable到工作表的方式，先手动构建自定义表头（顶部信息+复合表头），再逐行写入数据
• 通过单元格合并实现顶部信息的跨列显示效果
• 用Excel公式自动计算总计，避免手动计算误差
• 为表头和总计行添加加粗、居中样式，提升表格可读性
• 最后自动调整列宽，确保内容完整显示

内容的提问来源于stack exchange，提问作者Kalai Selvi