首页 > 其他分享 >NPOI导出多行表头、多个Sheet页

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

时间：2024-10-17 15:19:20浏览次数：9  
标签：cell Sheet Name NPOI StartRow item 表头 new public

一、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);

标签：cell,Sheet,Name,NPOI,StartRow,item,表头,new,public
From： https://blog.csdn.net/qq_24428069/article/details/143003051

相关文章

  • el-table自定义表头新
    <el-table-columnprop="address"label="333333"min-width="180":show-overflow-tooltip="true"><templateslot......
  • FineReport 标题冻结，冻结表头，冻结行列
    先进行重复标题行和重复标题列设置，然后再进行分页冻结设置1、冻结列SELECT*FROMS人员花名册选定列–右击–设置重复标题列2、冻结行选定行–右击–设置重复标题行3、重复与冻结设置模板–重复与冻结设置冻结重复标题有分页冻结和填报冻结。数据......
  • Springboot使用EasyExcel 的填充模板导出，导出为多Sheet工作簿
    概述Springboot使用EasyExcel的填充模板导出，导出为多Sheet工作簿详细代码Excel数据填充/***使用EasyExcel写入Excel*@paramexcelModelFilePath 模板文件地址*@paramsheetNameAndDataMap Sheet名称与Sheet数据Map集合，key为Sheet名称，value为Sheet数据集合*@ret......
  • 【C#】DevExpress实现复合表头
    https://download.csdn.net/blog/column/9993043/120721622①将创建的GridControl下的GirdView1转化为BandGridView类型；②创建需要展示的列（指定列的名称【Name】、描述【caption】、数据字段名称【FieldName】）③绑定列实现复合表头。注意：如果复合表头有多行，则需要设置新增Band......
  • Vue Javascript 导出Excel：SheetJS/js-xlsx json_to_sheet xlsx 修改表头名称和展示的
     import*asXLSXfrom'xlsx';//展示的顺序，把data中对象的属性按照你想要的顺序排放就可以了，constheader=["pId","dID"]//展示的名称constheaderDisplay={pId:"项编号",dID:"D编号"};//将表头放剑原始数据里面去，要保证表头在数......
  • 【VBA】シート数を取得する【Sheets.Countで取得できます】
    参考元：【VBA】シート数を取得する【Sheets.Countで取得できます】https://daitaideit.com/vba-sheet-count/「Sheets.Count」でシート数を取得SubTEST1()'シート数を取得Debug.PrintSheets.CountEndSubシート数を取得する場面シート数を取得できれば、次......
  • ACST2001 S2 2024 Spreadsheet
    ACST2001S22024SpreadsheetProjectTaskInaspreadsheet,createfourseparatesheets,labelled‘Parta’,‘Partb’,‘Partc’and‘Partd’.Partatocconfirmsomeoftheresultspresentedtoyouinweek7,onslide40-42.Thenanswerthefollowingqu......
  • 比较好用的调节Table表格的表头和内容的宽度的方法
    因为大部分的Table组件的表头宽度不太适合我们所使用的，我们可以通过封装函数来适应表头的宽度。getColumnWidth(label,prop,tableData){   //label表头名称   //prop对应的内容   //tableData表格数据   constminWidth=150;//最小宽度......
  • VN9D DataSheet 阅读记录
    简介VN9D30是一个高边外设，有6个channel，使用24路SPI进行通信，用于汽车领域。Generaldeepcoldcrankingapplications(指深冷启动应用场景)IntegratedPWMenginewithindependentphaseshiftandfrequencygeneration(foreachchannel)IntegratedPWMengine：指的......
  • NPOI设定某个格子的样式
    XSSFCellStyleheadStyle=workBook.CreateCellStyle()asXSSFCellStyle;headStyle.Alignment=NPOI.SS.UserModel.HorizontalAlignment.Center;XSSFFontfont=workBook.CreateFont()asXSSFFont;f......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99