ClosedXML、DocumentFormat.OpenXml导出DataTable到Excel （支持多行合并列标题 ）

在CLosedXML提供了很简单的DataTable导出。

请参考：C# CLosedXML四句代码搞定DataTable数据导出到Excel https://www.cnblogs.com/MorganMa/p/13139418.html

public void Export(DataTable data)
{
    // 这种方法导出方便，但 首行自动启用了筛选，自动用了 套用表格格式
    XLWorkbook wb = new XLWorkbook();
    wb.Worksheets.Add(data);
    wb.SaveAs("Export.xlsx");
}

 

---

 

在很多系统中都用到导出，使用过多种导出方式，觉得ClosedXML插件的导出简单又方便。

并且ClosedXML、DocumentFormat.OpenXml都是MIT开源。

首先通过 Nuget 安装 ClosedXML 插件，同时会自动安装 DocumentFormat.OpenXml 插件。

以下就是导出相关的代码：

1、MVC控制器中的导出

// MVC 控制器中
/// <summary>
/// 导出 （无论是否需要返回值，都有 Response）
/// </summary>
public void Export1() // 传入搜索条件
{
    // 1、根据条件查询到想要的数据
    DataTable data = new DataTable();
    // 2、设置表名(对应sheet名)、列名(对应列名)
    data.TableName = "XX统计";
    // 3、列宽设置（拖动Excel列宽度时，会显示｛宽度：10.00 (75像素)|宽度：20.00 (145像素)｝推算宽度1为7像素，每列自加5像素；默认大小的汉字，一个字宽约15.3px）
    int[] colsWidth = new int[] { 160, 200, 300, 160 };
    // 4、生成导出文件
    byte[] filedata = ExportHelper.ExportExcel(data);
    // 5、输出文件流
    HttpContext.Output(filedata, "XX统计_导出" + DateTime.Today.ShowDate() + ".xlsx");
}

2、生成导出文件

方法一：遍历表格，逐个单元格设置。

using ClosedXML.Excel;
using System.Data;
using System.IO;

/// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="data">导出的数据</param>
/// <param name="colsWidth">列宽</param>
/// <returns></returns>
public static byte[] ExportExcel(DataTable data, int[] colsWidth = null)
{
    using (XLWorkbook workbook = new XLWorkbook())
    {
        IXLWorksheet worksheet = workbook.AddWorksheet(data.TableName);

        // 处理列
        for (int i = 0; i < data.Columns.Count; i++)
        {
            worksheet.Cell(1, i + 1).Value = data.Columns[i].ColumnName;
            worksheet.Cell(1, i + 1).Style.Font.Bold = true;
        }

        // 处理列宽
        if (colsWidth != null)
        {
            for (int j = 1; j <= colsWidth.Length; j++)
            {
                worksheet.Columns(j, j).Width = colsWidth[j - 1];
            }
        }

        // 处理数据
        int r = 2;// 第二行开始
        foreach (DataRow dr in data.Rows)
        {
            // 第一列开始
            for (int c = 1; c <= data.Columns.Count; c++)
            {
                worksheet.Cell(r, c).SetValue<string>(dr[c-1].ToString());
                // worksheet.Cell(r, c).Value = dr[c-1].ToString(); // 存在数据类型隐患，自动转换数字、日期、时间格式的数据，显示出来的可能不是想要的 
                // worksheet.Cell(r, c).DataType = XLDataType.Text; // 1、先设置格式，赋值后，会自动根据数据重新改变格式，2、先赋值，后设置格式，存在日期变为数字情况；
            }
            r++;
        }

        // 缓存到内存流，然后返回
        using (MemoryStream stream = new MemoryStream())
        {
            workbook.SaveAs(stream);
            return stream.ToArray();
        }
    }
}

方案二：整个DataTable直接导出（但格式调整是个问题，我还没找到怎么改，如果有知道的请留言）

public static byte[] ExportExcel(DataTable data, int[] colsWidth = null)
{
    using (XLWorkbook workbook = new XLWorkbook())
    {
        // 这种方法导出方便，但 首行自动启用了筛选，自动用了 套用表格格式
        IXLWorksheet worksheet = workbook.AddWorksheet(data);

        // 处理列宽（拖动Excel列宽度时，会显示｛宽度：10.00 (75像素)|宽度：20.00 (145像素)｝推算宽度1为7像素，每列自加5像素）
        if (colsWidth != null)
        {

            for (int j = 1; j <= colsWidth.Length; j++)
            {
                // 默认 8.43=64px
                worksheet.Columns(j, j).Width = colsWidth[j - 1];
            }
        }

        // 这种快捷的导出数据，自动开启了筛选功能（Excel 数据-->筛选）
        // 首行的筛选功能去除（以下已试过，都对 workbook.AddWorksheet(data); 无效。）
        //worksheet.AutoFilter.Clear();
        //worksheet.AutoFilter.IsEnabled = false;
        //worksheet.RangeUsed().SetAutoFilter(false);
        //worksheet.SetAutoFilter(false);

        // 这种快捷的导出数据，使用了套用表格格式（Excel 开始-->套用表格格式）
        // 更改/取消 套用表格格式（以下已试过，都对 workbook.AddWorksheet(data); 无效。）
        //worksheet.Clear(XLClearOptions.AllFormats);
        //worksheet.RangeUsed().Clear(XLClearOptions.AllFormats);
        //worksheet.Style = XLWorkbook.DefaultStyle;
        //worksheet.RangeUsed().Style = XLWorkbook.DefaultStyle;

        // 用XLColor.NoColor 无颜色无法去除颜色，需要用白色才能覆盖 套用表格格式 的颜色
        worksheet.RangeUsed().Style.Fill.SetBackgroundColor(XLColor.White);

        // 自定义样式部分
        worksheet.RangeUsed().Style.Alignment.WrapText = true;
        worksheet.RangeUsed().Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
        worksheet.RangeUsed().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
        worksheet.RangeUsed().Style.Border.BottomBorder = XLBorderStyleValues.Thin;
        worksheet.RangeUsed().Style.Border.RightBorder = XLBorderStyleValues.Thin;
                
        //// 设置首航行高(推算高度1为4/3px≈1.333px)
        ////worksheet.Row(1).Height = 15; // 默认15=20px;

        // 根据内容调整所有行的高度(不加这个，自动换行还能撑高行高)
        //worksheet.Rows().AdjustToContents();

        // 缓存到内存流，然后返回
        using (MemoryStream stream = new MemoryStream())
        {
            workbook.SaveAs(stream);
            return stream.ToArray();
        }
    }
}

 

3、输出文件流

using System.Text;
using System.Text.RegularExpressions;
using System.Web;

/// <summary>
/// 输出文件流
/// </summary>
/// <param name="httpContext">Http上下文</param>
/// <param name="filedata">文件数据</param>
/// <param name="fileName">文件名(要后缀名)</param>
public static void Output(this HttpContextBase httpContext, byte[] filedata, string fileName)
{
    //文件名称效验 文件名不能包含\/:*?<>| 其中\需要两次转义
    if (Regex.IsMatch(fileName, @"[\\/:*?<>|]"))
    {
        fileName = Regex.Replace(fileName, @"[\\/:*?<>|]", "_");
    }

    //判断是否为火狐浏览器（下载时，有些浏览器中文名称乱码，有些浏览器中文名正常，但不能编码，不会自动解码，如火狐）
    if (httpContext.Request.Browser.Browser != "Firefox")
    {
        fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8);
    }

    // 没有定义类型，用二进制流类型的MIME
    string MIME = "application/octet-stream";
    httpContext.Response.Clear();
    httpContext.Response.Buffer = true; //该值指示是否缓冲输出，并在完成处理整个响应之后将其发送
    httpContext.Response.ContentType = MIME;
    httpContext.Response.ContentEncoding = Encoding.UTF8;
    httpContext.Response.Charset = Encoding.UTF8.HeaderName;
    httpContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
    httpContext.Response.AddHeader("Accept-Language", "zh-cn");
    httpContext.Response.AddHeader("Content-Length", filedata.LongLength.ToString());
    httpContext.Response.BinaryWrite(filedata);
    httpContext.Response.Flush();
    httpContext.Response.End();
}

通过ClosedXML导出操作方便。

---

对以上导出Excel方式一进行行优化，使其支持多行标题和合并标题。

/// <summary>
/// 导出Excel文件
/// <para>
/// 【注意】当使用多行合并表头时；第一：注意表头和数据列要对应，避免数据错位；第二：有多行合并时，对于之后的行，要跳过多行合并占用的列；
/// </para>
/// </summary>
/// <param name="data">导出的数据</param>
/// <param name="colsWidth">列宽</param>
/// <param name="ehrs">支持多行有合并表头</param>
/// <returns></returns>
public static byte[] ExportExcel2(DataTable data, int[] colsWidth = null, params ExcelHeaderRow[] ehrs)
{
    if (data == null)
    {
        return null;
    }

    using (XLWorkbook workbook = new XLWorkbook())
    {
        // 工作表名称不能包含以下字符：\:/?*[]
        IXLWorksheet worksheet = workbook.AddWorksheet(data.TableName);
        // 行计数（Excel都是从1开始）
        int rIndex = 1;

        // 处理表头
        if (ehrs != null)
        {
            foreach (ExcelHeaderRow row in ehrs)
            {
                int cIndex = 1; // 列计数（Excel都是从1开始）
                foreach (ExcelHeaderColumn col in row.cols)
                {
                    if (string.IsNullOrEmpty(col.colname))
                    {
                        // 跳过列
                        cIndex += col.colspan;
                        continue;
                    }
                    worksheet.Cell(rIndex, cIndex).Value = col.colname;
                    worksheet.Cell(rIndex, cIndex).Style.Font.Bold = true;
                    worksheet.Cell(rIndex, cIndex).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                    worksheet.Cell(rIndex, cIndex).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    worksheet.Cell(rIndex, cIndex).Style.Alignment.WrapText = true;

                    if (col.rowspan > 1 || col.colspan > 1)
                    {
                        worksheet.Range(rIndex, cIndex, rIndex + col.rowspan - 1, cIndex + col.colspan - 1).Merge();
                    }
                    cIndex += col.colspan;
                }
                if (row.RowHeight > 0)
                {
                    worksheet.Row(rIndex).Height = row.RowHeight;
                }
                rIndex++; // 行加1
            }
        }
        else
        {
            // 采用DataTable列名做表头
            for (int i = 0; i < data.Columns.Count; i++)
            {
                worksheet.Cell(rIndex, i + 1).Value = data.Columns[i].ColumnName;
                worksheet.Cell(rIndex, i + 1).Style.Font.Bold = true;
                worksheet.Cell(rIndex, i + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                worksheet.Cell(rIndex, i + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                worksheet.Cell(rIndex, i + 1).Style.Alignment.WrapText = true;
            }
            // 设置首航行高(推算高度1为4/3px≈1.333px)
            //worksheet.Row(1).Height = 15; // 默认15=20px;
            rIndex++; // 行加1
        }

        // 处理列宽（拖动Excel列宽度时，会显示｛宽度：10.00 (75像素)|宽度：20.00 (145像素)｝推算宽度1为7像素，每列自加5像素）
        if (colsWidth != null)
        {
            for (int j = 1; j <= colsWidth.Length; j++)
            {
                // 默认 8.43=64px
                worksheet.Columns(j, j).Width = colsWidth[j - 1];
            }
        }

        // 处理数据
        foreach (DataRow dr in data.Rows)
        {
            // 第一列开始
            for (int cIndex = 1; cIndex <= data.Columns.Count; cIndex++)
            {
                // 明确指定值类型，避免格式问题
                worksheet.Cell(rIndex, cIndex).SetValue(dr[cIndex - 1].ToString());
                worksheet.Cell(rIndex, cIndex).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
                worksheet.Cell(rIndex, cIndex).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                worksheet.Cell(rIndex, cIndex).Style.Alignment.WrapText = true;
            }
            rIndex++;
        }
        // 小数格式(未测试)
        //worksheet.Cell(r, c).Style.NumberFormat.Format = "$ #,##0.00";
        // 日期格式(未测试)
        //worksheet.Cell(r, c).Style.DateFormat.Format = "yyyy/MM/dd";

        // 根据内容调整所有行的高度(不加这个，自动换行还能撑高行高)
        //worksheet.Rows().AdjustToContents();
        // 缓存到内存流，然后返回
        using (MemoryStream stream = new MemoryStream())
        {
            workbook.SaveAs(stream);
            return stream.ToArray();
        }
    }
}

支持以上方法需要的支撑类：

/// <summary>
/// Excel Header Row
/// </summary>
public class ExcelHeaderRow
{
    /// <summary>
    /// 行高 (默认0自动；推算高度1为4/3px≈1.333px；默认15=20px;)
    /// </summary>
    public int RowHeight { get; set; }
    public List<ExcelHeaderColumn> cols { get; set; }
}

/// <summary>
/// Excel Header Column
/// </summary>
public class ExcelHeaderColumn
{
    /// <summary>
    /// 列名（为null是表示调过，特别在多行合并表头的情况，注意第二行调过多少colspan一定不要遗漏）
    /// </summary>
    public string colname { get; set; }
    public int rowspan { get; set; } = 1;
    public int colspan { get; set; } = 1;

    public ExcelHeaderColumn() { }
    public ExcelHeaderColumn(string cname, int rs = 1, int cs = 1)
    {
        colname = cname;
        rowspan = rs;
        colspan = cs;
    }
}

多行多列合并的使用方式：

// 列标题
ExcelHeaderRow[] headers = new ExcelHeaderRow[]
{
    new ExcelHeaderRow // 第一行
    {
        cols = new List<ExcelHeaderColumn>
        {
            new ExcelHeaderColumn("多行1", 2),
            new ExcelHeaderColumn("多行2", 2),
            new ExcelHeaderColumn("多列1", 1,2),
            new ExcelHeaderColumn("多列2", 1,2),
            new ExcelHeaderColumn("多列3", 1,2),
            new ExcelHeaderColumn("多行3", 2),
        }
    },
    new ExcelHeaderRow // 第二行
    {
        cols = new List<ExcelHeaderColumn>
        {
            new ExcelHeaderColumn(null, 2), // 跳过被多行合并占用的列
            new ExcelHeaderColumn("子列1"),
            new ExcelHeaderColumn("子列2"),
            new ExcelHeaderColumn("子列3"),
            new ExcelHeaderColumn("子列4"),
            new ExcelHeaderColumn("子列5"),
            new ExcelHeaderColumn("子列6"),
            // 最后的多行合并可以不管
        }
    }
};

 调用方式：

ExcelHelper.ExportExcel2(dt, colsWidth, headers);

 标题效果如下：