NPOI导出多行表头、多个Sheet页

最新推荐文章于 2025-03-10 10:42:55 发布

qq_24428069

最新推荐文章于 2025-03-10 10:42:55 发布

阅读量625

点赞数 12

文章标签： asp.net .netcore excel

本文链接：https://blog.youkuaiyun.com/qq_24428069/article/details/143003051

版权

一、NPOI导出多行表头、多个Sheet页

说明：提供多行表头、多个Sheet页的帮助类,支持每个表头合并单元格，当然单行表头、单个Sheet也可以用此方法

1、帮助类

public static class ExcelHelper
{
    /// <summary>
    /// 导出多sheet页Excel
    /// </summary>
    /// <param name="headersLst">key:sheet页名称 value表头集合</param>
    /// <param name="data"></param>
    /// <returns></returns>
    public static byte[] ExcelSheetByDataTable(Dictionary<string, List<ExcelHead>> headersLst,
        Dictionary<string, ExcelDataTable> data)
    {
        //创建Excel
        IWorkbook workbook = new XSSFWorkbook();
        

        foreach (var itemSheetName in headersLst.Keys)
        {
            //创建sheet页
            ISheet sheetTable = workbook.CreateSheet(itemSheetName);
            #region 创建表头
            //创建表头
            foreach (var item in headersLst[itemSheetName])
            {
                IRow row = sheetTable.GetRow(item.StartRow);
                if (row == null)
                {
                    row = sheetTable.CreateRow(item.StartRow);
                    //行高，避免自动换行的内容将行高撑开
                    row.HeightInPoints = 24;
                }
                //创建列并设置值
                ICell cell = row.CreateCell(item.StartCol);
                cell.SetCellValue(item.Name);
                //设置跨行跨列
                if (item.StartRow != item.EndRow || item.StartCol != item.EndCol)
                {
                    sheetTable.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(item.StartRow, item.EndRow,
                        item.StartCol, item.EndCol));
                }
                //设置列样式 可以提取出来公共方法
                ICellStyle cellStyle1 = workbook.CreateCellStyle();
                cellStyle1.Alignment = HorizontalAlignment.Center;
                //垂直居中
                cellStyle1.VerticalAlignment = VerticalAlignment.Center;
                cellStyle1.WrapText = true;
                IFont font = workbook.CreateFont();
                font.FontName = "宋体";
                font.FontHeightInPoints = 14;
                font.IsBold = true;
                cellStyle1.SetFont(font);
                cell.CellStyle = cellStyle1;
            }
            #endregion
    
            //DataTable有数据
            if (data.ContainsKey(itemSheetName))
            {
                var dataSource = data[itemSheetName].Data;
                foreach (DataRow itemRow in dataSource.Rows)
                {
                    //数据开始行
                    IRow dataRow = sheetTable.GetRow(data[itemSheetName].StartRow);
                    if (dataRow == null)
                    {
                        dataRow = sheetTable.CreateRow(data[itemSheetName].StartRow);
                        //行高，避免自动换行的内容将行高撑开
                        dataRow.HeightInPoints = 20;
                    }
                    foreach (DataColumn column in dataSource.Columns)
                    {
                        ICell cell = dataRow.CreateCell(column.Ordinal);
                        var columnValue = itemRow[column].ToString();
                        switch (column.DataType.ToString())
                        {
                            case "System.String": //字符串类型
                                cell.SetCellValue(columnValue);
                                break;
                            case "System.DateTime": //日期类型
                                DateTime dateV;
                                DateTime.TryParse(columnValue, out dateV);
                                cell.SetCellValue(dateV);
                                ICellStyle dateStyle = workbook.CreateCellStyle();
                                IDataFormat format = workbook.CreateDataFormat();
                                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
                                cell.CellStyle = dateStyle; //格式化显示
                                break;
                            case "System.Boolean": //布尔型
                                bool.TryParse(columnValue, out bool boolV);
                                cell.SetCellValue(boolV);
                                break;
                            case "System.Int16": //整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int.TryParse(columnValue, out int intV);
                                cell.SetCellValue(intV);
                                break;
                            case "System.Decimal": //浮点型
                            case "System.Double":
                                double.TryParse(columnValue, out double doubV);
                                cell.SetCellValue(doubV);
                                break;
                            case "System.DBNull": //空值处理
                                cell.SetCellValue("");
                                break;
                            default:
                                cell.SetCellValue(columnValue);
                                break;
                                //精确到小数点后两位
                                //dataCell.CellStyle.DataFormat = dataformat.GetFormat("0.00");
                                //添加千分位分割,并保留两位小数
                                //dataCell.CellStyle.DataFormat = dataformat.GetFormat("text");
                                //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");
                        }
    
    
                        //设置列样式
                        ICellStyle cellStyle1 = workbook.CreateCellStyle();
                        cellStyle1.Alignment = HorizontalAlignment.Center;
                        cellStyle1.WrapText = true;
                        IFont font = workbook.CreateFont();
                        font.FontName = "宋体";
                        font.FontHeightInPoints = 12;
                        cellStyle1.SetFont(font);
                        cell.CellStyle = cellStyle1;
                    }
                    data[itemSheetName].StartRow++;
                }
            }
        }
    
        //创建内存流
        MemoryStream ms = new MemoryStream();
        //写入到excel
        //var ms = new MemoryStream();
        Console.WriteLine(ms.CanRead);
        Console.WriteLine(ms.CanWrite);//防止流写入失败
        //可以写入文件看效果
        //using (FileStream file = new FileStream(@"E:/ccccc.xlsx", FileMode.Create))
        //{
        //    workbook.Write(file);
        //}
        workbook.Write(ms, true);//写入ms
        ms.Flush();//清空流
        ms.Seek(0, SeekOrigin.Begin);
        byte[] bytes = ms.ToArray();//转byte类型(非必要)
        return bytes;
    }
}

2、实体

public class ExcelHead
{
    /// <summary>
    /// Excel表头名称
    /// </summary>
    public string Name { get; set; }
    /// <summary>
    /// 开始行，索引0开始
    /// </summary>
    public int StartRow { get; set; }
    /// <summary>
    /// 结束行，索引0开始
    /// </summary>
    public int EndRow { get; set; }
    /// <summary>
    /// 开始列，索引0开始
    /// </summary>
    public int StartCol { get; set; }
    /// <summary>
    /// 结束列，索引0开始
    /// </summary>
    public int EndCol { get; set; }
}
//数据实体
public class ExcelDataTable
{
    /// <summary>
    /// 表数据起始行，索引0开始 
    /// </summary>
    public int StartRow { get; set; }

    /// <summary>
    /// 数据集合
    /// </summary>
    public DataTable Data { get; set; }
}

3：调用

//组装表头
Dictionary<string, List<ExcelHead>> keyValuePairs2 = new Dictionary<string, List<ExcelHead>>();
List<ExcelHead> excelHeads = new List<ExcelHead>()
{
     new ExcelHead(){ Name="序号", StartRow=0, EndRow=1, StartCol=0, EndCol=0},
     new ExcelHead(){ Name="信息", StartRow=0, EndRow=0, StartCol=1, EndCol=2},
     new ExcelHead(){ Name="姓名", StartRow=1, EndRow=1, StartCol=1, EndCol=1},
     new ExcelHead(){ Name="年龄", StartRow=1, EndRow=1, StartCol=2, EndCol=2}
};
keyValuePairs2.Add("多表头", excelHeads);
excelHeads = new List<ExcelHead>()
{
     new ExcelHead(){ Name="姓名", StartRow=0, EndRow=0, StartCol=0, EndCol=0},
     new ExcelHead(){ Name="年龄", StartRow=0, EndRow=0, StartCol=1, EndCol=1}
};
keyValuePairs2.Add("单表头", excelHeads);

//组装数据
Dictionary<string, ExcelDataTable> keyValuePairs = new Dictionary<string, ExcelDataTable>();
DataTable dataTable = new DataTable();
DataColumn dataColumn = new DataColumn("Id", typeof(string));
DataColumn dataColumn1 = new DataColumn("Name", typeof(string));
DataColumn dataColumn2 = new DataColumn("Age", typeof(int));
dataTable.Columns.Add(dataColumn);
dataTable.Columns.Add(dataColumn1);
dataTable.Columns.Add(dataColumn2);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = "1";
dataRow[1] = "张三";
dataRow[2] = "13";
dataTable.Rows.Add(dataRow);
dataRow = dataTable.NewRow();
dataRow[0] = "2";
dataRow[1] = "里斯";
dataRow[2] = "22";
dataTable.Rows.Add(dataRow);
ExcelDataTable excelDataTable = new ExcelDataTable() { StartRow = 2, Data = dataTable };
keyValuePairs.Add("多表头", excelDataTable);

//调用
ExcelHelper.ExcelSheetByDataTable(keyValuePairs2, keyValuePairs);

4、效果

二、扩展支持更多方式

说明：：有的同学会说了，咦，数据结构是实体类集合怎么办？这里提供2种实现：

1、List扩展方法转换为DataTable

public static class ListExtensions
{
    public static DataTable ToDataTable<T>(this List<T> items)
    {
        DataTable dataTable = new DataTable(typeof(T).Name);

        // 获取所有的属性
        PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
        foreach (PropertyInfo prop in Props)
        {
            // 设置列的名字和数据类型
            dataTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        }
        // 遍历所有的对象
        foreach (T item in items)
        {
            var values = new object[Props.Length];
            for (int i = 0; i < Props.Length; i++)
            {
                // 插入属性值到datatable
                values[i] = Props[i].GetValue(item, null);
            }
            dataTable.Rows.Add(values);
        }
        return dataTable;
    }
}
如何调用呢？
public class Student{
    /// <summary>
    /// 姓名
    /// </summary>
    public string Name { get; set; }
    
    /// <summary>
    /// 年龄
    /// </summary>
    public int Age { get; set; }
}
List<Student> students = new List<Student>()
{
     new Student(){ Name="岳不群",Age=1},
      new Student(){  Name="令狐冲",Age=2}
};
students.ToDataTable())

2、导出支持List<>

public static class ExcelHelper
{
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="headersLst"></param>
    /// <param name="dataLst"></param>
    /// <returns></returns>
    public static byte[] ExcelSheetByList<T>(Dictionary<string, List<ExcelHead>> headersLst,
        Dictionary<string, ExcelDataLst<T>> dataLst)
    {
        //创建Excel
        IWorkbook workbook = new XSSFWorkbook();
    
        foreach(var itemSheetName in headersLst.Keys)
        {
            //创建sheet页
            ISheet sheetTable = workbook.CreateSheet(itemSheetName);
            #region 创建表头
            //创建表头
            foreach (var item in headersLst[itemSheetName])
            {
                IRow row = sheetTable.GetRow(item.StartRow);
                if (row == null)
                {
                    row = sheetTable.CreateRow(item.StartRow);
                    //行高，避免自动换行的内容将行高撑开
                    row.HeightInPoints = 24;
                }
                ICell cell = row.CreateCell(item.StartCol);
                cell.SetCellValue(item.Name);
                //设置跨行跨列
                if (item.StartRow != item.EndRow || item.StartCol != item.EndCol)
                {
                    sheetTable.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(item.StartRow, item.EndRow,
                        item.StartCol, item.EndCol));
                }
                //设置列样式
                ICellStyle cellStyle1 = workbook.CreateCellStyle();
                cellStyle1.Alignment = HorizontalAlignment.Center;
                //垂直居中
                cellStyle1.VerticalAlignment = VerticalAlignment.Center;
                cellStyle1.WrapText = true;
                IFont font = workbook.CreateFont();
                font.FontName = "宋体";
                font.FontHeightInPoints = 14;
                font.IsBold = true;
                cellStyle1.SetFont(font);
                cell.CellStyle = cellStyle1;
            }
            #endregion
    
            //Lst有数据
            if (dataLst.ContainsKey(itemSheetName))
            {
                var type = dataLst[itemSheetName].Data.First()?.GetType();//获取列表的字段的属性
                var properties = type?.GetProperties();
                foreach (var item in dataLst[itemSheetName].Data)
                {
                    //数据开始行
                    IRow dataRow = sheetTable.GetRow(dataLst[itemSheetName].StartRow);
                    if (dataRow == null)
                    {
                        dataRow = sheetTable.CreateRow(dataLst[itemSheetName].StartRow);
                        //行高，避免自动换行的内容将行高撑开
                        dataRow.HeightInPoints = 20;
                    }
                    var startCol = 0;
                    foreach (PropertyInfo column in properties)
                    {
                        ICell cell = dataRow.CreateCell(startCol);
                        var columnValue = column.GetValue(item);
                        if (column.PropertyType == typeof(string))
                        {
                            cell.SetCellValue(columnValue?.ToString());
                            IDataFormat dataformat = workbook.CreateDataFormat();
                            cell.CellStyle.DataFormat = dataformat.GetFormat("text");
                        }
                        else if (column.PropertyType == typeof(int)
                            || column.PropertyType == typeof(decimal)
                            || column.PropertyType == typeof(double)
                            || column.PropertyType == typeof(float))
                        {
                            double dataValueD = 0;
                            double.TryParse(columnValue?.ToString(), out dataValueD);
                            cell.SetCellValue(dataValueD);
                            IDataFormat dataformat = workbook.CreateDataFormat();
                            cell.CellStyle.DataFormat = dataformat.GetFormat("text");
                        }
                        if (column.PropertyType == typeof(DateTime))
                        {
                            DateTime.TryParse(columnValue?.ToString(), out DateTime date);
                            cell.SetCellValue(date);
                            IDataFormat dataformat = workbook.CreateDataFormat();
                            cell.CellStyle.DataFormat = dataformat.GetFormat("yyyy-MM-dd");
                        }
    
                        //设置列样式
                        ICellStyle cellStyle1 = workbook.CreateCellStyle();
                        cellStyle1.Alignment = HorizontalAlignment.Center;
                        cellStyle1.WrapText = true;
                        IFont font = workbook.CreateFont();
                        font.FontName = "宋体";
                        font.FontHeightInPoints = 12;
                        cellStyle1.SetFont(font);
                        cell.CellStyle = cellStyle1;
                        //列加1
                        startCol++;
                    }
                    dataLst[itemSheetName].StartRow++;
                    startCol = 0;
                }
            }
        }
    
        //创建内存流
        MemoryStream ms = new MemoryStream();
        //写入到excel
        //var ms = new MemoryStream();
        Console.WriteLine(ms.CanRead);
        Console.WriteLine(ms.CanWrite);//防止流写入失败
        //using (FileStream file = new FileStream(@"E:/cccc.xlsx", FileMode.Create))
        //{
        //    workbook.Write(file);
        //}
        workbook.Write(ms, true);//写入ms
        ms.Flush();//清空流
        ms.Seek(0, SeekOrigin.Begin);
        byte[] bytes = ms.ToArray();//转byte类型(非必要)
        return bytes;
    }
}

2、实体

public class ExcelDataLst<T>
{
    /// <summary>
    /// 表数据起始行，索引0开始 
    /// </summary>
    public int StartRow { get; set; }

    /// <summary>
    /// 数据集合
    /// </summary>
    public List<T> Data { get; set; }
}
public class Student
{
    /// <summary>
    /// 姓名
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 年龄
    /// </summary>
    public int Age { get; set; }
}

3、调用

Dictionary<string, List<ExcelHead>> keyValuePairs2 = new Dictionary<string, List<ExcelHead>>();
List<ExcelHead> excelHeads = new List<ExcelHead>()
{
     new ExcelHead(){ Name="序号", StartRow=0, EndRow=1, StartCol=0, EndCol=0},
     new ExcelHead(){ Name="信息", StartRow=0, EndRow=0, StartCol=1, EndCol=2},
     new ExcelHead(){ Name="姓名", StartRow=1, EndRow=1, StartCol=1, EndCol=1},
     new ExcelHead(){ Name="年龄", StartRow=1, EndRow=1, StartCol=2, EndCol=2}
};
keyValuePairs2.Add("多表头", excelHeads);
excelHeads = new List<ExcelHead>()
{
     new ExcelHead(){ Name="姓名", StartRow=0, EndRow=0, StartCol=0, EndCol=0},
     new ExcelHead(){ Name="年龄", StartRow=0, EndRow=0, StartCol=1, EndCol=1}
};
keyValuePairs2.Add("单表头", excelHeads);

//组装数据
Dictionary<string, ExcelDataLst<Student>> keyValuePairs1 = new Dictionary<string, ExcelDataLst<Student>>();
List<Student> students = new List<Student>()
{
     new Student(){ Name="岳不群",Age=1},
      new Student(){  Name="令狐冲",Age=2}
};
ExcelDataLst<Student> excelDataLst = new ExcelDataLst<Student>() { StartRow = 1, Data = students };
keyValuePairs1.Add("单表头", excelDataLst);

ExcelHelper.ExcelSheetByList(keyValuePairs2, keyValuePairs1);