C# NPOI导出 动态导出为Excel升级版

https://blog.csdn.net/weixin_43965881/article/details/106572220
之前的动态导出，当数据大于xls的最大行数时回出现错误
我在之前的基础上进行了改进

改进的帮助类

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

/// <summary>
/// ExcleHelper 的摘要说明
/// </summary>
public class ExcleHelper
{
    public ExcleHelper()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    /// <summary>
    /// 文件名称
    /// </summary>
    public string fileName { get; set; }
    /// <summary>
    /// sheet,列，数据源
    /// </summary>
    public List<ExcleSheetColumnData> SheetColumnList { get; set; }


    /// <summary>
    /// 导出excle
    /// </summary>
    //public string Excle(string filePath,string fileName, List<ExcleSheetColumnData> SheetColumnList)
    public string Excle(string fileName, List<ExcleSheetColumnData> SheetColumnList)
    {
    	//var newfile=filePath+fileName+".xls";导出文件路径可以作为参数传入
        //设置导出文件路径 
        string path = HttpContext.Current.Server.MapPath("Export/");

        if (!Directory.Exists(path))
        {
            Directory.CreateDirectory(path);
        }

        //设置新建文件路径及名称
        var newFile = path + fileName + ".xls";

        using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
        {
            IWorkbook workbook = new HSSFWorkbook();

            #region 设置内容单元格样式 (这边的单元格样式一定要放在循环外面，否则会造成office下部分样式丢失，wps下没问题)
            var style = (HSSFCellStyle)workbook.CreateCellStyle(); //创建样式
            style.Alignment = HorizontalAlignment.Center; //水平居中
            style.VerticalAlignment = VerticalAlignment.Center; //垂直居中
            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 = "微软雅黑";//设置字体类型
            font.IsBold = true;//加粗
            style.SetFont(font);

            var style1 = (HSSFCellStyle)workbook.CreateCellStyle(); //创建样式
            style1.Alignment = HorizontalAlignment.Center; //水平居中
            style1.VerticalAlignment = VerticalAlignment.Center; //垂直居中
            style1.BorderBottom = BorderStyle.Thin;
            style1.BorderLeft = BorderStyle.Thin;
            style1.BorderRight = BorderStyle.Thin;
            style1.BorderTop = BorderStyle.Thin;
            IFont font1 = workbook.CreateFont();
            font1.FontHeightInPoints = 12;
            font1.FontName = "微软雅黑";//设置字体类型
            style1.SetFont(font1);
            #endregion
            foreach (var SheetColumnModel in SheetColumnList)
            {
                List<ISheet> sheetList = new List<ISheet>();
                // var sheet = workbook.CreateSheet(SheetColumnModel.sheetName);
                var ColumnList = SheetColumnModel.ColumnList;//获取列表
                int j = 0;
                //ISheet sheet = null;
                foreach (DataRow it in SheetColumnModel.DataList.Tables[0].Rows)//后缀xls，单个sheet最大行数为65335
                {
                    int rowMax = 65335;
                    if (j % rowMax == 0)//是否存在余数
                    {
                        var shu = sheetList.Count == 0 ? "" : "" + sheetList.Count;
                        ISheet shee = workbook.CreateSheet(SheetColumnModel.sheetName + "" + shu);

                        //创建第一行 
                        IRow rowColumn = (HSSFRow)shee.CreateRow(0);
                        //列头
                        for (var i = 0; i < ColumnList.Count; i++)
                        {
                            var cell = rowColumn.CreateCell(i);
                            cell.CellStyle = style;

                            cell.SetCellValue(ColumnList[i].ColumnValue);
                            shee.AutoSizeColumn(i);
                            #region 合并标题和列头单元格
                            //shee.AddMergedRegion(new CellRangeAddress(0, 1, i, i));
                            //shee.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
                            //shee.AddMergedRegion(new CellRangeAddress(1, 2, 2, 2));
                            //shee.AddMergedRegion(new CellRangeAddress(1, 2, 3, 3));
                            //shee.AddMergedRegion(new CellRangeAddress(1, 1, 4, 6));
                            //shee.AddMergedRegion(new CellRangeAddress(1, 1, 7, 9));
                            //shee.AddMergedRegion(new CellRangeAddress(1, 2, 10, 10));
                            #endregion
                        }

                        sheetList.Add(shee);
                       // sheet = shee;
                    }
                    //int sheet_i = (j / 65335);
                    int sheet_i = sheetList.Count() - 1;
                    j++;

                    ISheet sheet = sheetList[sheet_i];//获取最后应该sheet

                    int rouNumber = j % rowMax;
                    if (rouNumber==0)
                    {
                        rouNumber = rowMax;
                    }

                    IRow row = (HSSFRow)sheet.CreateRow(rouNumber);//创建数据行

                    //循环填充列
                    for (var i = 0; i < ColumnList.Count; i++)
                    {
                        var cell = row.CreateCell(i);
                        cell.CellStyle = style1;
                        cell.SetCellValue(it[ColumnList[i].ColumnKey].ToString());
                    }
                }
                for (var i = 0; i < ColumnList.Count; i++)
                {
                    foreach (var sheet in sheetList)
                    {
                        SelfWidth(sheet, i);
                    }
                }

            }
            workbook.Write(fs);

            return newFile;
        }

    }

    //public 


    /// <summary>
    /// 自适应宽度
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="columnNum"></param>
    public void SelfWidth(ISheet sheet, int columnNum)
    {
        int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度
        for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)//在这一 列上循环行
        {
            IRow currentRow = sheet.GetRow(rowNum);
            ICell currentCell = currentRow.GetCell(columnNum);
            int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;// 获取当前单元格的内容宽度
            if (columnWidth < length + 1)
            {
                columnWidth = length + 1;
            }
        }

        sheet.SetColumnWidth(columnNum, columnWidth * 256);
    }

    /// <summary>
    /// 自适应高度
    /// </summary>
    /// <param name="sheet"></param>
    public void SelfHeight(ISheet sheet)
    {
        for (int rowNum = 2; rowNum < sheet.LastRowNum; rowNum++)
        {
            IRow currentRow = sheet.GetRow(rowNum);
            ICell currentCell = currentRow.GetCell(4);
            ICell currentCell2 = currentRow.GetCell(4);
            int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;
            int length2 = Encoding.UTF8.GetBytes(currentCell2.ToString()).Length;
            currentRow.HeightInPoints = length > length2 ? 20 * (length / 60 + 2) : 20 * (length2 / 60 + 2);
        }
    }
}

/// <summary>
/// 一个sheet和这个sheet的列
/// </summary>
public class ExcleSheetColumnData
{
    /// <summary>
    /// 展示得sheet
    /// </summary>
    public string sheetName { get; set; }
    /// <summary>
    /// 展示得列
    /// </summary>
    public List<ExcleColumn> ColumnList { get; set; }
    /// <summary>
    /// 数据源
    /// </summary>
    public DataSet DataList { get; set; }
}
/// <summary>
/// 列
/// </summary>
public class ExcleColumn
{
    /// <summary>
    /// 列名对应得数据库字段
    /// </summary>
    public string ColumnKey { get; set; }
    /// <summary>
    /// 列名
    /// </summary>
    public string ColumnValue { get; set; }
}

如何使用帮助类

ExcleHelper excle = new ExcleHelper();
        List<ExcleSheetColumnData> sheetColumns = new List<ExcleSheetColumnData>();
		
		//一个excleSheet代表一个sheet
        ExcleSheetColumnData excleSheet = new ExcleSheetColumnData();
        excleSheet.sheetName = "第一个sheet1";
        excleSheet.DataList = new AppInManager().GetDataSetAppInSum1(Bind());//当前这个sheet所展示的数据 所绑定的是一个DataSet 
        List<ExcleColumn> columns = new List<ExcleColumn>();
        //columns.Add(new ExcleColumn() { ColumnKey = "绑定的字段名称", ColumnValue = "展示的列名" });
        columns.Add(new ExcleColumn() { ColumnKey = "Num", ColumnValue = "序号" });
        columns.Add(new ExcleColumn() { ColumnKey = "AddressType", ColumnValue = "所在镇区" });
        columns.Add(new ExcleColumn() { ColumnKey = "AppName", ColumnValue = "公司名称" });
        columns.Add(new ExcleColumn() { ColumnKey = "AppAddress", ColumnValue = "地址" });
        columns.Add(new ExcleColumn() { ColumnKey = "fa", ColumnValue = "发明数量" });
        excleSheet.ColumnList = columns;
        sheetColumns.Add(excleSheet);

		//ExcleSheetColumnData excleSheet2 = new ExcleSheetColumnData();
		//.....
		//....
		//sheetColumns.Add(excleSheet2);
		//返回的是文档的保存的位置（路径）
        string filePath = excle.Excle("有效专利汇总", sheetColumns);
		
		OutputExcel(filePath);//下载到客户端，自己定义

注意：该帮助类只是把当前导出的数据保存到该路径下了，并没有下载到客户端