C#Npoi实现DataTable数据导出到Excel支持多表头

至于Npoi的引用就省略了

1.相关类的代码

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;


/// <summary>
/// Excel文件扩展名
/// </summary>
public enum ExcelExtension
{
    XLSX,
    XLS
}

/// <summary>
/// Npoi操作Excel类
/// </summary>
public class NpoiExcelHelper
{
    /// <summary>
    /// 获取Excel文件的后缀
    /// </summary>
    /// <param name="fileName">文件路径/文件名称（含后缀名）</param>
    /// <returns></returns>
    private static ExcelExtension GetExcelExtension(string fileName)
    {
        string extension = Path.GetExtension(fileName);
        switch (extension.ToLowerInvariant())
        {
            case ".xlsx":
                return ExcelExtension.XLSX;
            case ".xls":
            default:
                return ExcelExtension.XLS;
        }
    }

    /// <summary>
    /// 判断Excel文件的后缀是否xlsx
    /// </summary>
    /// <param name="fileName">文件路径/文件名称（含后缀名）</param>
    /// <returns></returns>
    private static bool GetExcelIsXlsx(string fileName)
    {
        return GetExcelExtension(fileName) == ExcelExtension.XLSX;
    }

    /// <summary>
    /// 根据Excel文件类型返回IWorkbook
    /// </summary>
    /// <param name="fileName">文件路径/文件名称（含后缀名）</param>
    /// <param name="rowNum">Excel行数</param>
    /// <param name="colNum">Excel列数</param>
    /// <returns></returns>
    private static IWorkbook GetWorkbook(string fileName, out int rowNum, out int colNum)
    {
        /*
        Excel 97到Excel 2003，最大行数为65,536行（2^16）。然而，随着Excel的更新和更高版本的发布，行数限制得到了扩展。
        自Excel 2007起，Excel的行数限制提高到了1048576行（2^20），这个版本被称为Excel 2007以及更高版本，包括Excel 2010、Excel 2013、Excel 2016和最新版本的Excel。
        早期版本的Excel中，最大列数为256列（2^8）。然而，随着Excel的更新和更高版本的发布，列数的限制也得到了扩展。
        自Excel 2007起，Excel的最大列数提高到了16384列（2^14），这个版本被称为Excel 2007以及更高版本，包括Excel 2010、Excel 2013、Excel 2016和最新版本的Excel。
        */

        bool isXlsx = GetExcelIsXlsx(fileName);

        if (isXlsx)
        {
            rowNum = 1048576;
            colNum = 16384;
        }
        else
        {
            rowNum = 65536;
            colNum = 256;
        }
            
        if (File.Exists(fileName))
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                if (isXlsx)
                {
                    return new XSSFWorkbook(fs);
                }
                else
                {
                    return new HSSFWorkbook(fs);
                }
            }
        }
        else
        {
            if (isXlsx)
            {
                return new XSSFWorkbook();
            }
            else
            {
                return new HSSFWorkbook();
            }
        }
    }

    /// <summary>
    /// 将DataTable中的数据导入到excel中
    /// 支持根据DataTable数据及Excel自动分页（多个Sheet）
    /// </summary>
    /// <param name="fileName">文件名称（含后缀名）</param>
    /// <param name="headers">表头</param>
    /// <param name="data">数据DataTable（只包含需要导出的数据的列）</param>
    /// <param name="sheetName">Excel中Sheet名称（多个sheet时 名字后面自动加上数字序号）</param>
    /// <param name="sheetPageSize">每个sheet显示多少条数据，默认为0时或者超出时会自动计算</param>
    /// <param name="StartRow">表起始行，索引0开始</param>
    /// <param name="StartCol">表起始列，索引0开始</param>
    /// <returns></returns>
    public static byte[] DataTableToExcel(string fileName, List<ExcelHead> headers, DataTable data, string sheetName = "Sheet", int sheetPageSize = 0, int StartRow = 0, int StartCol = 0)
    {
        byte[] bytes = new byte[0];
        try
        {
            int rowNumExcel = 0;
            int colNumExcel = 0;

            IWorkbook workbook = GetWorkbook(fileName, out rowNumExcel, out colNumExcel);

            //在这里先定义好单元格样式  防止报错 The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook
            ICellStyle headerStyle = GetHeaderStyle(workbook);
            ICellStyle dataStyleDefaut = GetDataStyle(workbook);

            IDataFormat dataformat = workbook.CreateDataFormat();

            ICellStyle dataStyleText = GetDataStyle(workbook);
            dataStyleText.DataFormat = dataformat.GetFormat("text");

            ICellStyle dataStyleDateTime = GetDataStyle(workbook);
            dataStyleDateTime.DataFormat = dataformat.GetFormat("yyyy-MM-dd hh:mm:ss");

            if (headers.Count > 0)
            {
                int maxCol = headers.Max(item => item.LastCol) + 1;
                int maxRow = headers.Max(item => item.LastRow) + 1;

                // 筛选出名字不为空的用户
                List<string> fieldList = new List<string>();
                foreach (var head in headers)
                {
                    if (!string.IsNullOrEmpty(head.Field))
                    {
                        if (!fieldList.Contains(head.Field))
                        {
                            fieldList.Add(head.Field);
                        }
                    }
                }

                //计算Excel的sheet除去表头的行，还有多少行数据
                int rowNumAll = rowNumExcel - maxRow;

                //计算每个sheet要显示多少条数据
                int rowNum = rowNumAll;
                if (sheetPageSize > 0)
                {
                    if (sheetPageSize < rowNumAll)
                    {
                        rowNum = sheetPageSize;
                    }
                }
                //数据总记录数
                var recordNum = data.Rows.Count;
                //计算多个页数据
                int totalPage = recordNum % rowNum == 0 ? recordNum / rowNum : recordNum / rowNum + 1;

                //计算表格的起始行
                var startRowNum = 0;
                if (StartRow > 0)
                {
                    //起始行数 + 表头的行数 + 数据的行数  不能超过 Excel的行数
                    if (((StartRow + 1) + maxRow + rowNum) < rowNumExcel)
                    {
                        startRowNum = StartRow + 1;
                    }
                }
                //计算表格的起始列
                var startColNum = 0;
                if (StartCol > 0)
                {
                    //起始列数 + 数据的列数  不能超过 Excel的列数
                    if (((StartCol + 1) + maxCol) < colNumExcel)
                    {
                        startColNum = StartCol + 1;
                    }
                }

                for (var i = 0; i < totalPage; i++)
                {
                    string sheetNameNew = sheetName;
                    if (totalPage > 1)
                    {
                        sheetNameNew = sheetName + (i + 1).ToString();
                    }

                    ISheet sheet = workbook.CreateSheet(sheetNameNew);//创建工作表

                    #region 表头
                    foreach (var item in headers)
                    {
                        IRow headerRow = sheet.GetRow(item.FirstRow + startRowNum);
                        if (headerRow == null)
                        {
                            headerRow = sheet.CreateRow(item.FirstRow + startRowNum);
                            //行高，避免自动换行的内容将行高撑开
                            headerRow.HeightInPoints = 24;
                        }
                        ICell headerCell = headerRow.CreateCell(item.FirstCol + startColNum);
                        headerCell.SetCellValue(item.Name);
                        //设置跨行
                        if (item.FirstRow != item.LastRow || item.LastCol != item.FirstCol)
                        {
                            //CellRangeAddress(开始行,结束行,开始列,结束列)
                            //行列索引由0开始
                            var region = new CellRangeAddress(item.FirstRow + startRowNum, item.LastRow + startRowNum, item.FirstCol + startColNum, item.LastCol + startColNum);
                            sheet.AddMergedRegion(region);
                        }
                        headerCell.CellStyle = headerStyle;
                    }
                    #endregion

                    #region 计算分页数据的开始和结束索引
                    int startIndex = i * rowNum;
                    int endindex = (i + 1) * rowNum - 1;
                    if (endindex >= recordNum)
                    {
                        endindex = recordNum - 1;
                    }
                    #endregion

                    #region 表格数据
                    //数据的起始行
                    var startRow = maxRow + startRowNum;
                    foreach (DataRow item in data.Rows)
                    {
                        // 获取当前DataRow的索引
                        int index = item.Table.Rows.IndexOf(item);
                        if (index >= startIndex && index <= endindex)
                        {
                            IRow dataRow = sheet.GetRow(startRow);
                            if (dataRow == null)
                            {
                                dataRow = sheet.CreateRow(startRow);
                                //行高，避免自动换行的内容将行高撑开
                                dataRow.HeightInPoints = 20;
                            }

                            var startCol = StartCol + startColNum;
                            foreach (var field in fieldList)
                            {
                                ICell dataCell = dataRow.CreateCell(startCol);
                                dataCell.CellStyle = dataStyleDefaut;

                                DataColumn column = data.Columns[field];
                                var dataValue = item[column];
                                switch (column.DataType.ToString())
                                {
                                    case "System.String":
                                        dataCell.SetCellValue(dataValue.ToString());
                                        dataCell.CellStyle = dataStyleText;
                                        break;
                                    case "System.DateTime":
                                        DateTime.TryParse(dataValue?.ToString(), out DateTime date);
                                        dataCell.SetCellValue(date);
                                        dataCell.CellStyle = dataStyleDateTime;
                                        break;
                                    case "System.Boolean":
                                        bool.TryParse(dataValue?.ToString(), out bool boolValue);
                                        dataCell.SetCellValue(boolValue);
                                        break;
                                    case "System.Int16":
                                    case "System.Int32":
                                    case "System.Int64":
                                        int.TryParse(dataValue?.ToString(), out int intValue);
                                        dataCell.SetCellValue(intValue);
                                        break;
                                    case "System.Byte":
                                        int.TryParse(dataValue?.ToString(), out int byteValue);
                                        dataCell.SetCellValue(byteValue);
                                        break;
                                    case "System.Decimal":
                                    case "System.Double":
                                        double.TryParse(dataValue?.ToString(), out double doubleValue);
                                        dataCell.SetCellValue(doubleValue);
                                        //精确到小数点后两位
                                        //dataCell.CellStyle.DataFormat = dataformat.GetFormat("0.00");
                                        //添加千分位分割,并保留两位小数
                                        //dataCell.CellStyle.DataFormat = dataformat.GetFormat("#,##0.00");
                                        //金钱格式-千分位分割,并保留两位小数
                                        //dataCell.CellStyle.DataFormat = dataformat.GetFormat("￥#,##0.00");
                                        //中文大写(会有四舍五入的情况)
                                        //dataCell.CellStyle.DataFormat = dataformat.GetFormat("[DbNum2][$-804]0");
                                        //科学计数法
                                        //dataCell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                                        break;
                                    case "System.DBNull": //空值
                                        dataCell.SetCellValue("");
                                        break;
                                    default:
                                        dataCell.SetCellValue(dataValue?.ToString());
                                        break;
                                }
                                startCol++;
                            }
                            startRow++;
                        }
                    }
                    #endregion
                }
            }
            #region 输出Excel
            using (MemoryStream stream = new MemoryStream())
            {
                workbook.Write(stream);
                bytes = stream.ToArray();
            }
            #endregion
        }
        catch (Exception ex)
        { 
                
        }
        return bytes;
    }

    /// <summary>
    /// 将Excel文件中的数据读出到DataTable
    /// </summary>
    /// <param name="fileName">文件路径（含文件名称和后缀名）</param>
    /// <returns></returns>
    public static DataTable ExcelToTable(string fileName, int sheetIndex = 0)
    {
        DataTable dt = new DataTable();

        try
        {
            int rowNumExcel = 0;
            int colNumExcel = 0;

            var isXlsx = GetExcelIsXlsx(fileName);

            IWorkbook workbook = GetWorkbook(fileName, out rowNumExcel, out colNumExcel);

            dt = GetExcelDataTable(workbook);

            // 获取sheet的数量
            int numberOfSheets = workbook.NumberOfSheets;
            for (int index = 0; index < numberOfSheets; index++)
            {
                ISheet sheet = workbook.GetSheetAt(index);

                //数据
                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);

                    if (row != null)
                    {
                        DataRow dr = dt.NewRow();
                        bool hasValue = false;
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            ICell cell = row.GetCell(j);
                            dr[j] = GetValueType(cell);
                            #region 增加格式解析
                            if (cell != null)
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.Numeric:
                                        if (DateUtil.IsCellDateFormatted(cell))//日期类型
                                        {
                                            dr[j] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss:fff");
                                        }
                                        else//其他数字类型
                                        {
                                            dr[j] = cell.NumericCellValue;
                                        }
                                        break;
                                    case CellType.Blank:
                                        dr[j] = string.Empty;
                                        break;
                                    case CellType.Formula:
                                        if (isXlsx)
                                        {
                                            IFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook);
                                            if (eva.Evaluate(cell).CellType == CellType.Numeric)
                                            {
                                                if (DateUtil.IsCellDateFormatted(cell))//日期类型
                                                {
                                                    dr[j] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss:fff");
                                                }
                                                else//其他数字类型
                                                {
                                                    dr[j] = cell.NumericCellValue;
                                                }
                                            }
                                            else
                                            {
                                                dr[j] = eva.Evaluate(cell).StringValue;
                                            }
                                        }
                                        else
                                        {
                                            IFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
                                            if (eva.Evaluate(cell).CellType == CellType.Numeric)
                                            {
                                                if (DateUtil.IsCellDateFormatted(cell))//日期类型
                                                {
                                                    dr[j] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss:fff");
                                                }
                                                else//其他数字类型
                                                {
                                                    dr[j] = cell.NumericCellValue;
                                                }
                                            }
                                            else
                                            {
                                                dr[j] = eva.Evaluate(cell).StringValue;
                                            }
                                        }
                                        break;
                                    default:
                                        dr[j] = cell.StringCellValue;
                                        break;

                                }
                            }
                            #endregion

                            if (dr[j] != null && dr[j].ToString() != string.Empty)
                            {
                                hasValue = true;
                            }
                        }
                        if (hasValue)
                        {
                            dt.Rows.Add(dr);
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        { 
                
        }
        return dt;
    }

    /// <summary>
    /// 将excel中的数据导入到DataTable中(第一行是标题)
    /// 支持多个sheet数据导入（建议多个sheet的数据格式保持一致，将没有数据的sheet删除）
    /// </summary>
    /// <param name="fileName">文件路径（含文件名称后缀名）</param>
    /// <param name="columnFieldText">字段对应中文 顺序需要跟Excel中数据顺序一致</param>
    /// <param name="sheetName">指定Excel中Sheet名称 如果为null时，读取所有sheet中的数据</param>
    /// <returns>返回的DataTable</returns>
    public static DataTable ExcelToDataTable(string fileName, string[,] columnFieldText = null, string sheetName = null)
    {
        DataTable data = new DataTable();
        int rowNum = 0;
        int colNum = 0;
        IWorkbook workbook = GetWorkbook(fileName, out rowNum, out colNum);

        for (int e = 0; e < workbook.NumberOfSheets; e++)
        {
            ISheet sheet = workbook.GetSheetAt(e);
            if (sheet != null)
            {
                var currentSheetIndex = 0;
                if (!string.IsNullOrEmpty(sheetName))
                {
                    if (sheet.SheetName == sheetName)
                    {
                        currentSheetIndex = e;
                    }
                }

                IRow firstRow = sheet.GetRow(0);
                if (firstRow != null)
                {
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    var dataColumn = columnFieldText != null ? columnFieldText.GetLength(0) : cellCount;
                    int startRow = sheet.FirstRowNum;
                    if (dataColumn <= colNum)
                    {
                        if (e == currentSheetIndex)
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                ICell cell = firstRow.GetCell(i);
                                if (cell != null)
                                {
                                    string cellValue = cell.StringCellValue;
                                    if (cellValue != null)
                                    {
                                        DataColumn column = new DataColumn((columnFieldText != null ? columnFieldText[i, 0] : cellValue));
                                        data.Columns.Add(column);
                                    }
                                }
                            }
                        }

                        startRow = sheet.FirstRowNum + 1;

                        //最后一列的标号
                        int rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row == null) continue; //没有数据的行默认是null　　　　　　　

                            DataRow dataRow = data.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                    dataRow[j] = row.GetCell(j).ToString();
                            }
                            data.Rows.Add(dataRow);
                        }
                    }
                    else
                    {
                        //数据列数超过了Excel的列数
                    }
                }

                if (!string.IsNullOrEmpty(sheetName))
                {
                    if (sheet.SheetName == sheetName)
                    {
                        break;
                    }
                }
            }
        }
        return data;
    }

    /// <summary>
    /// 表头单元格样式
    /// </summary>
    private static ICellStyle GetHeaderStyle(IWorkbook workbook)
    {
        ICellStyle style = workbook.CreateCellStyle();
        //居中
        style.Alignment = HorizontalAlignment.Center;
        //垂直居中
        style.VerticalAlignment = VerticalAlignment.Center;
        //自动换行 
        style.WrapText = true;
        //边框
        //style.BorderBottom = BorderStyle.Thin;
        //style.BorderLeft = BorderStyle.Thin;
        //style.BorderRight = BorderStyle.Thin;
        //style.BorderTop = BorderStyle.Thin;
        //边框颜色
        //style.TopBorderColor = HSSFColor.Black.Index;
        //style.BottomBorderColor = HSSFColor.Black.Index;
        //style.RightBorderColor = HSSFColor.Black.Index;
        //style.LeftBorderColor = HSSFColor.Black.Index;
        //字体
        IFont font = workbook.CreateFont();
        font.FontHeightInPoints = 12;
        font.FontName = "宋体";
        font.IsBold = true;
        style.SetFont(font);
        return style;
    }
    /// <summary>
    /// 数据单元格样式
    /// </summary>
    private static ICellStyle GetDataStyle(IWorkbook workbook)
    {
        ICellStyle style = workbook.CreateCellStyle();
        style.Alignment = HorizontalAlignment.Center; //居中
        style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
        style.WrapText = true;//自动换行
        //边框
        //style.BorderBottom = BorderStyle.Thin;
        //style.BorderLeft = BorderStyle.Thin;
        //style.BorderRight = BorderStyle.Thin;
        //style.BorderTop = BorderStyle.Thin;
        // 字体
        IFont font = workbook.CreateFont();
        font.FontHeightInPoints = 12;
        font.FontName = "宋体";
        style.SetFont(font);

        return style;
    }

    /// <summary>
    /// 根据单元格将内容返回为对应类型的数据
    /// </summary>
    /// <param name="cell">单元格</param>
    /// <returns>数据</returns>
    private static object GetValueType(ICell cell)
    {
        if (cell == null)
            return null;
        switch (cell.CellType)
        {
            case CellType.Blank: //BLANK:
                return null;
            case CellType.Boolean: //BOOLEAN:
                return cell.BooleanCellValue;
            case CellType.Numeric: //NUMERIC:
                return cell.NumericCellValue;
            case CellType.String: //STRING:
                return cell.StringCellValue;
            case CellType.Error: //ERROR:
                return cell.ErrorCellValue;
            case CellType.Formula: //FORMULA:
            default:
                return "=" + cell.CellFormula;
        }
    }

    /// <summary>
    /// 获取Excel文件中的数据的DataTable列
    /// </summary>
    /// <param name="workbook"></param>
    /// <returns></returns>
    private static DataTable GetExcelDataTable(IWorkbook workbook)
    {
        DataTable dt = new DataTable();

        // 获取sheet的数量
        int numberOfSheets = workbook.NumberOfSheets;
        for (int index = 0; index < numberOfSheets; index++)
        {
            ISheet sheet = workbook.GetSheetAt(index);

            IRow row = sheet.GetRow(sheet.FirstRowNum);
            for (int i = 0; i < row.LastCellNum; i++)
            {
                var columnName = "Columns" + i.ToString();
                if (!dt.Columns.Contains(columnName))
                {
                    dt.Columns.Add(new DataColumn(columnName));
                }
            }
        }
        return dt;
    }

}

/// <summary>
/// 表头格式
/// </summary>
public class ExcelHead
{
    /// <summary>
    /// 字段名（为空时代表是多表头）
    /// </summary>
    public string Field { set; get; }
    /// <summary>
    /// 表头名称
    /// </summary>
    public string Name { set; get; }
    /// <summary>
    /// 开始行，索引0开始
    /// </summary>
    public int FirstRow { set; get; }
    /// <summary>
    /// 结束行，索引0开始
    /// </summary>
    public int LastRow { set; get; }
    /// <summary>
    /// 开始列，索引0开始
    /// </summary>
    public int FirstCol { set; get; }
    /// <summary>
    /// 结束列，索引0开始
    /// </summary>
    public int LastCol { set; get; }
}

2.导出实例代码

//创建DataTable
DataTable dt = new DataTable();

#region 创建DataTable及数据

//创建自增长的ID列
DataColumn dc = dt.Columns.Add("ID", Type.GetType("System.Int32"));
dc.AutoIncrement = true;  //自动增加
dc.AutoIncrementSeed = 1; //起始为1
dc.AutoIncrementStep = 1; //步长为1
dc.AllowDBNull = false;  //非空

//创建其它列表
dt.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("Sex", Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("IsLogin", Type.GetType("System.Boolean")));
dt.Columns.Add(new DataColumn("CreateTime", Type.GetType("System.DateTime")));

DataRow dr;
int record = 200000;
for (int i = 0; i < record; i++)
{
    dr = dt.NewRow();
    dr["Name"] = "名字" + i.ToString();
    dr["Sex"] = "性别" + i.ToString();
    dr["IsLogin"] = true;
    dr["CreateTime"] = DateTime.Now;
    dt.Rows.Add(dr);
}
#endregion 创建DataTable及数据


string fileName = "客户明细_" + DateTime.Now.ToString("MMddhhmmss") + ".xls";
string sheetName = "客户明细";

List<ExcelHead> headList = new List<ExcelHead>() {
    new ExcelHead() { Field = "ID", Name = "编号", FirstRow = 0, LastRow = 1, FirstCol = 0, LastCol = 0 },
    new ExcelHead() { Field = "", Name = "信息", FirstRow = 0, LastRow = 0, FirstCol = 1, LastCol = 2 },    //多表头
    new ExcelHead() { Field = "Name", Name = "名称", FirstRow = 1, LastRow = 1, FirstCol = 1, LastCol = 1 },
    new ExcelHead() { Field = "Sex", Name = "性别", FirstRow = 1, LastRow = 1, FirstCol = 2, LastCol = 2 },
    new ExcelHead() { Field = "CreateTime", Name = "创建时间", FirstRow = 0, LastRow = 1, FirstCol = 3, LastCol = 3 }
};
var buffer = NpoiExcelHelper.DataTableToExcel(fileName, headList, dt, sheetName);

3.读取Excel的内容

            //文件路径
            string file = Server.MapPath("客户明细_1125103958.xls");
            var dt = NpoiExcelHelper.ExcelToTable(file);

以上代码，基本上可以实现Exce数据的导入以及DataTable数据导出到Excel。