C#NPOI Excel导出

//读取模板
var fullPath = $"text.xlsx";
using var fileStream = new FileStream(fullPath, FileMode.Open, FileAccess.Read);
IWorkbook excel = Path.GetExtension(fullPath).ToLower() switch
{
	".xlsx" => new XSSFWorkbook(fileStream),
	".xls" => new HSSFWorkbook(fileStream),
	_ => throw new FileLoadException("文件格式有误"),
};
//获取工作区
ISheet sheet = excel.GetSheetAt(0);

//需插入开头数据行数
int count = 7;
int startCount = 7;

//样式
ICellStyle style = null;	
//插入一行(结合页数使用，不需要页数可以不用)
sheet.ShiftRows(count, sheet.LastRowNum, 1, true, false);
//创建一行
var row = sheet.CreateRow(count);
//向单元格插入数据
row.CreateCell(0).SetCellValue(startIndex.ToString());

//操作样式
//ICellStyle remarkStyle = excel.CreateCellStyle(); 新建样式(不能使用同一个样式类来改变样式，一个excel想要不同的样式只能新建样式)
//remarkStyle.CloneStyleFrom(rowRemark.GetCell(0).CellStyle);克隆样式(样式可以从老的继承下来，且修改这个样式不会影响老样式)
style = row.GetCell(0).CellStyle;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.WrapText = true;//设置换行这个要先设置
style.Alignment = HorizontalAlignment.Center;//居中
style.VerticalAlignment = VerticalAlignment.Center;//水平居中

//设置字体
IFont font = excel.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "黑体";
style.SetFont(font);//HEAD 样式

//样式赋值
row.GetCell(0).CellStyle = style;

//循环样式赋值
for (int i = startCount; i < sheet.LastRowNum-1; i++)
{
	var tempRow = sheet.GetRow(i);
	tempRow.Height = 499;//设置行高
	tempRow.GetCell(0).CellStyle = style;
}

//合并行
sheet.AddMergedRegion(new CellRangeAddress(开始行, 结束行, 开始列, 结束列));

//保存文件
using FileStream file = new FileStream("保存路径", FileMode.Create);
excel.Write(file);



//示例
			var dto = GetData();
            var fullPath = $"{Utility.Option.MyOption.Option.WwwRootPath}{Utility.Option.MyOption.Option.TemplateFilePath}啥啥表.xlsx";
            using var fileStream = new FileStream(fullPath, FileMode.Open, FileAccess.Read);

            IWorkbook excel = Path.GetExtension(fullPath).ToLower() switch
            {
                ".xlsx" => new XSSFWorkbook(fileStream),
                ".xls" => new HSSFWorkbook(fileStream),
                _ => throw new FileLoadException("文件格式有误"),
            };

            ISheet sheet = excel.GetSheetAt(0);
            var title = $"啥啥表{DateTime.Now.ToString("yyyyMMddHHmmssfff")}";
            int count = 7;
            int startCount = 7;
            int startIndex = 1;

            ICellStyle style = null;

            bool hasSetStyle = false;

            sheet.GetRow(2).GetCell(0).SetCellValue("统计时期：" + DateTime.Now.ToString("yyyy年M月d日"));
            dto.List.ForEach(item =>
            {
                sheet.ShiftRows(count, sheet.LastRowNum, 1, true, false);
                var row = sheet.CreateRow(count);
                count++;
                row.CreateCell(0).SetCellValue(startIndex.ToString());
                startIndex++;
                row.CreateCell(1).SetCellValue(item.OrganizationObj.Name);
                row.CreateCell(2).SetCellValue((item.HdbzCount ?? null).ToString());
                row.CreateCell(3).SetCellValue("核定："+(item.XzbzCount ?? 0).ToString()+"/"+ (item.ZfbzCount ?? 0).ToString() + "\r\n已用：" + (item.XzbzUseCount ?? 0).ToString() + "/" + (item.ZfbzUseCount ?? 0).ToString());
                row.CreateCell(4).SetCellValue((item.UseBZCount ?? null).ToString());
                row.CreateCell(5).SetCellValue((item.ZzCount ?? null).ToString());
                row.CreateCell(6).SetCellValue((item.ZzUseCount ?? null).ToString());
                row.CreateCell(7).SetCellValue((item.ZzVacancyCount ?? null).ToString());
                row.CreateCell(8).SetCellValue((item.FzcwCount ?? null).ToString());
                row.CreateCell(9).SetCellValue((item.FzcwUseCount ?? null).ToString());
                row.CreateCell(10).SetCellValue((item.FzcwVacancyCount ?? null).ToString());
                row.CreateCell(11).SetCellValue((item.FzzkCount ?? null).ToString());
                row.CreateCell(12).SetCellValue((item.FzzkUseCount ?? null).ToString());
                row.CreateCell(13).SetCellValue((item.FzzkVacancyCount ?? null).ToString());
                row.CreateCell(14).SetCellValue((item.TowDYYCount ?? null).ToString());
                row.CreateCell(15).SetCellValue((item.ThreeDYYCount ?? null).ToString());
                row.CreateCell(16).SetCellValue((item.FourDYYCount ?? null).ToString());

                if (!hasSetStyle)
                {
                    style = row.GetCell(0).CellStyle;
                    style.BorderLeft = BorderStyle.Thin;
                    style.BorderBottom = BorderStyle.Thin;
                    style.BorderTop = BorderStyle.Thin;
                    style.BorderRight = BorderStyle.Thin;
                    style.WrapText = true;//设置换行这个要先设置
                    style.Alignment = HorizontalAlignment.Center;
                    style.VerticalAlignment = VerticalAlignment.Center;
                    IFont font = excel.CreateFont();
                    font.FontHeightInPoints = 10;
                    font.FontName = "黑体";
                    style.SetFont(font);//HEAD 样式
                    hasSetStyle = true;
                }

                if (item.ChildOrganizationList.Count > 0) {
                    row.CreateCell(17).SetCellValue("小计");
                    row.CreateCell(18).SetCellValue("");
                    row.CreateCell(19).SetCellValue("");
                    row.CreateCell(20).SetCellValue((item.SubtotalNSSYRealCount ?? null).ToString());
                    row.CreateCell(21).SetCellValue("");
                    row.CreateCell(22).SetCellValue((item.SubtotalNSZZUseCount ?? null).ToString());
                    row.CreateCell(23).SetCellValue("");
                    row.CreateCell(24).SetCellValue("");
                    row.CreateCell(25).SetCellValue((item.SubtotalNSFZUseCount ?? null).ToString());
                    row.CreateCell(26).SetCellValue("");

                    item.ChildOrganizationList.ForEach(child =>
                    {
                        sheet.ShiftRows(count, sheet.LastRowNum, 1, true, false);
                        var row = sheet.CreateRow(count);
                        count++;
                        row.CreateCell(0).SetCellValue("");
                        row.CreateCell(1).SetCellValue("");
                        row.CreateCell(2).SetCellValue("");
                        row.CreateCell(3).SetCellValue("");
                        row.CreateCell(4).SetCellValue("");
                        row.CreateCell(5).SetCellValue("");
                        row.CreateCell(6).SetCellValue("");
                        row.CreateCell(7).SetCellValue("");
                        row.CreateCell(8).SetCellValue("");
                        row.CreateCell(9).SetCellValue("");
                        row.CreateCell(10).SetCellValue("");
                        row.CreateCell(11).SetCellValue("");
                        row.CreateCell(12).SetCellValue("");
                        row.CreateCell(13).SetCellValue("");
                        row.CreateCell(14).SetCellValue("");
                        row.CreateCell(15).SetCellValue("");
                        row.CreateCell(16).SetCellValue("");
                        row.CreateCell(17).SetCellValue(child.OrganizationObj.Name);
                        row.CreateCell(18).SetCellValue(child.OrganizationLevelObj.Name);
                        row.CreateCell(19).SetCellValue((child.HdbzCount ?? null).ToString());
                        row.CreateCell(20).SetCellValue((child.NssyRealCount ?? null).ToString());
                        row.CreateCell(21).SetCellValue((child.NszzCount ?? null).ToString());
                        row.CreateCell(22).SetCellValue((child.NszzUseCount ?? null).ToString());
                        row.CreateCell(23).SetCellValue((child.NszzVacancyCount ?? null).ToString());
                        row.CreateCell(24).SetCellValue((child.NsfzCount ?? null).ToString());
                        row.CreateCell(25).SetCellValue((child.NsfzUseCount ?? null).ToString());
                        row.CreateCell(26).SetCellValue((child.NsfzVacancyCount ?? null).ToString());
                    });
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 0, 0));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 1, 1));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 2, 2));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 3, 3));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 4, 4));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 5, 5));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 6, 6));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 7, 7));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 8, 8));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 9, 9));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 10, 10));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 11, 11));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 12, 12));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 13, 13));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 14, 14));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 15, 15));
                    sheet.AddMergedRegion(new CellRangeAddress(count -1- item.ChildOrganizationList.Count, count-1, 16, 16));
                }
                else {
                    row.CreateCell(17).SetCellValue("");
                    row.CreateCell(18).SetCellValue("");
                    row.CreateCell(19).SetCellValue("");
                    row.CreateCell(20).SetCellValue("");
                    row.CreateCell(21).SetCellValue("");
                    row.CreateCell(22).SetCellValue("");
                    row.CreateCell(23).SetCellValue("");
                    row.CreateCell(24).SetCellValue("");
                    row.CreateCell(25).SetCellValue("");
                    row.CreateCell(26).SetCellValue("");
                }
                
            });

            sheet.ShiftRows(count, sheet.LastRowNum, 1, true, false);
            var row = sheet.CreateRow(count);
            count++;
            row.Height = 499;
            row.CreateCell(0).SetCellValue("合计");
            row.CreateCell(1).SetCellValue("");
            row.CreateCell(2).SetCellValue("");
            row.CreateCell(3).SetCellValue("已用："+dto.TotalXZBZUseCount.ToString()+"/"+ dto.TotalZFBZUseCount.ToString());
            row.CreateCell(4).SetCellValue("");
            row.CreateCell(5).SetCellValue("");
            row.CreateCell(6).SetCellValue(dto.TotalZZUseCount.ToString());
            row.CreateCell(7).SetCellValue("");
            row.CreateCell(8).SetCellValue("");
            row.CreateCell(9).SetCellValue(dto.TotalFZCWUseCount.ToString());
            row.CreateCell(10).SetCellValue("");
            row.CreateCell(11).SetCellValue("");
            row.CreateCell(12).SetCellValue(dto.TotalFZZKUseCount.ToString());
            row.CreateCell(13).SetCellValue("");
            row.CreateCell(14).SetCellValue("");
            row.CreateCell(15).SetCellValue("");
            row.CreateCell(16).SetCellValue("");
            row.CreateCell(17).SetCellValue("");
            row.CreateCell(18).SetCellValue("");
            row.CreateCell(19).SetCellValue("");
            row.CreateCell(20).SetCellValue(dto.TotalNSSYRealCount.ToString());
            row.CreateCell(21).SetCellValue("");
            row.CreateCell(22).SetCellValue(dto.TotalNSZZUseCount.ToString());
            row.CreateCell(23).SetCellValue("");
            row.CreateCell(24).SetCellValue("");
            row.CreateCell(25).SetCellValue(dto.TotalNSFZUseCount.ToString());
            row.CreateCell(26).SetCellValue("");

            for (int i = startCount; i < sheet.LastRowNum-1; i++)
            {
                var tempRow = sheet.GetRow(i);
                tempRow.Height = 499;
                tempRow.GetCell(0).CellStyle = style;
                tempRow.GetCell(1).CellStyle = style;
                tempRow.GetCell(2).CellStyle = style;
                tempRow.GetCell(3).CellStyle = style;
                tempRow.GetCell(4).CellStyle = style;
                tempRow.GetCell(5).CellStyle = style;
                tempRow.GetCell(6).CellStyle = style;
                tempRow.GetCell(7).CellStyle = style;
                tempRow.GetCell(8).CellStyle = style;
                tempRow.GetCell(9).CellStyle = style;
                tempRow.GetCell(10).CellStyle = style;
                tempRow.GetCell(11).CellStyle = style;
                tempRow.GetCell(12).CellStyle = style;
                tempRow.GetCell(13).CellStyle = style;
                tempRow.GetCell(14).CellStyle = style;
                tempRow.GetCell(15).CellStyle = style;
                tempRow.GetCell(16).CellStyle = style;
                tempRow.GetCell(17).CellStyle = style;
                tempRow.GetCell(18).CellStyle = style;
                tempRow.GetCell(19).CellStyle = style;
                tempRow.GetCell(20).CellStyle = style;
                tempRow.GetCell(21).CellStyle = style;
                tempRow.GetCell(22).CellStyle = style;
                tempRow.GetCell(23).CellStyle = style;
                tempRow.GetCell(24).CellStyle = style;
                tempRow.GetCell(25).CellStyle = style;
                tempRow.GetCell(26).CellStyle = style;
            }

            var rowRemark = sheet.CreateRow(count);
            rowRemark.CreateCell(0).SetCellValue("备注:" + dto.Remark);

            rowRemark.Height = 900;
            ICellStyle remarkStyle = excel.CreateCellStyle();
            remarkStyle.CloneStyleFrom(rowRemark.GetCell(0).CellStyle);
            IFont remarkFont = excel.CreateFont();
            remarkFont.FontHeightInPoints = 11;
            remarkFont.FontName = "宋体";
            remarkStyle.SetFont(remarkFont);//HEAD 样式
            remarkStyle.Alignment = HorizontalAlignment.Left;
            rowRemark.GetCell(0).CellStyle = remarkStyle;

            var path = $"{Utility.Option.MyOption.Option.WwwRootPath}{Utility.Option.MyOption.Option.FilePath}{title}.xlsx";
            using FileStream file = new FileStream(path, FileMode.Create);
            excel.Write(file);
            string url = $"{Utility.Option.MyOption.Option.Url}{Utility.Option.MyOption.Option.FilePath}{title}.xlsx";
            return new View.GetExport() { Url = url };