C# NOPI 导出表头需合并的Excel表格
根据实际业务需要,导出Excel表格不可能只有一列表头,可能会有合并单元格的情况。
public async Task<ActionResult> Query_Export() { //数据查询 var result = await ApiService.GetList_Regulatory(); #region 创建excel //创建excel工作薄 HSSFWorkbook wb = new HSSFWorkbook(); //创建excel 单元格式1 ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //水平对齐 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //垂直对齐 cellStyle.VerticalAlignment = VerticalAlignment.Center; //设置字体 IFont font = wb.CreateFont(); font.FontHeightInPoints = 15; font.FontName = "微软雅黑"; font.Boldweight = (short)FontBoldWeight.Bold; cellStyle.SetFont(font); //创建excel 单元格式2 ICellStyle cellStyle1 = wb.CreateCellStyle(); cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //水平对齐 cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //垂直对齐 cellStyle1.VerticalAlignment = VerticalAlignment.Center; //设置字体 IFont font1 = wb.CreateFont(); font1.FontHeightInPoints = 12; font1.FontName = "微软雅黑"; //设置背景颜色 cellStyle1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey40Percent.Index; cellStyle1.FillPattern = FillPattern.SolidForeground; cellStyle1.SetFont(font1); //创建excel 单元格式3 ICellStyle cellStyle2 = wb.CreateCellStyle(); //水平对齐 cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //垂直对齐 cellStyle2.VerticalAlignment = VerticalAlignment.Center; //设置字体 IFont font2 = wb.CreateFont(); font2.FontHeightInPoints = 10; font2.FontName = "微软雅黑"; cellStyle2.SetFont(font2); #endregion #region 创建表 //创建表 ISheet sh = wb.CreateSheet("监管名录统计"); //设置第一列的宽度 //sh.SetColumnWidth(0, 15 * 150); //创建第一行 IRow row = sh.CreateRow(0); //创建七个单元格 ICell rowICell0 = row.CreateCell(0); ICell rowICell1 = row.CreateCell(1); ICell rowICell2 = row.CreateCell(2); ICell rowICell3 = row.CreateCell(3); ICell rowICell4 = row.CreateCell(4); ICell rowICell5 = row.CreateCell(5); ICell rowICell6 = row.CreateCell(6); //给第一单元格添加内容 rowICell0.SetCellValue("监管名录统计"); rowICell0.CellStyle = cellStyle; rowICell1.SetCellValue(""); rowICell1.CellStyle = cellStyle; rowICell2.SetCellValue(""); rowICell2.CellStyle = cellStyle; rowICell3.SetCellValue(""); rowICell3.CellStyle = cellStyle; rowICell4.SetCellValue(""); rowICell4.CellStyle = cellStyle; rowICell5.SetCellValue(""); rowICell5.CellStyle = cellStyle; rowICell6.SetCellValue(""); rowICell6.CellStyle = cellStyle; sh.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6)); //创建第二行 IRow row0 = sh.CreateRow(1); //创建七个单元格 ICell row0ICell0 = row0.CreateCell(0); ICell row0ICell1 = row0.CreateCell(1); ICell row0ICell2 = row0.CreateCell(2); ICell row0ICell3 = row0.CreateCell(3); ICell row0ICell4 = row0.CreateCell(4); ICell row0ICell5 = row0.CreateCell(5); ICell row0ICell6 = row0.CreateCell(6); //给第一单元格添加内容 row0ICell0.SetCellValue("区域"); row0ICell0.CellStyle = cellStyle1; row0ICell1.SetCellValue("生产"); row0ICell1.CellStyle = cellStyle1; row0ICell2.SetCellValue(""); row0ICell2.CellStyle = cellStyle1; row0ICell3.SetCellValue("加工"); row0ICell3.CellStyle = cellStyle1; row0ICell4.SetCellValue(""); row0ICell4.CellStyle = cellStyle1; row0ICell5.SetCellValue("投入品"); row0ICell5.CellStyle = cellStyle1; row0ICell6.SetCellValue(""); row0ICell6.CellStyle = cellStyle1; sh.AddMergedRegion(new CellRangeAddress(1, 1, 1, 2)); sh.AddMergedRegion(new CellRangeAddress(1, 1, 3, 4)); sh.AddMergedRegion(new CellRangeAddress(1, 1, 5, 6)); //创建第三行 IRow row1 = sh.CreateRow(2); //创建七个单元格 ICell row1ICell0 = row1.CreateCell(0); ICell row1ICell1 = row1.CreateCell(1); ICell row1ICell2 = row1.CreateCell(2); ICell row1ICell3 = row1.CreateCell(3); ICell row1ICell4 = row1.CreateCell(4); ICell row1ICell5 = row1.CreateCell(5); ICell row1ICell6 = row1.CreateCell(6); //给第二单元格添加内容 row1ICell0.SetCellValue("区域"); row1ICell0.CellStyle = cellStyle1; row1ICell1.SetCellValue("正常"); row1ICell1.CellStyle = cellStyle1; row1ICell2.SetCellValue("关闭"); row1ICell2.CellStyle = cellStyle1; row1ICell3.SetCellValue("正常"); row1ICell3.CellStyle = cellStyle1; row1ICell4.SetCellValue("关闭"); row1ICell4.CellStyle = cellStyle1; row1ICell5.SetCellValue("正常"); row1ICell5.CellStyle = cellStyle1; row1ICell6.SetCellValue("关闭"); row1ICell6.CellStyle = cellStyle1; //合并单元格 sh.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0)); #endregion #region 表格赋值 for (int i = 0; i < result.Count; i++) { //创建行 //创建七个单元格 IRow rowTemp = sh.CreateRow(i + 3); ICell Cell0 = rowTemp.CreateCell(0); ICell Cell1 = rowTemp.CreateCell(1); ICell Cell2 = rowTemp.CreateCell(2); ICell Cell3 = rowTemp.CreateCell(3); ICell Cell4 = rowTemp.CreateCell(4); ICell Cell5 = rowTemp.CreateCell(5); ICell Cell6 = rowTemp.CreateCell(6); Cell0.SetCellValue(result[i].area_name); Cell0.CellStyle = cellStyle2; Cell1.SetCellValue(result[i].production_count.ToString()); Cell1.CellStyle = cellStyle2; Cell2.SetCellValue(result[i].un_production_count.ToString()); Cell2.CellStyle = cellStyle2; Cell3.SetCellValue(result[i].processing_count.ToString()); Cell3.CellStyle = cellStyle2; Cell4.SetCellValue(result[i].un_processing_count.ToString()); Cell4.CellStyle = cellStyle2; Cell5.SetCellValue(result[i].inputs_count.ToString()); Cell5.CellStyle = cellStyle2; Cell6.SetCellValue(result[i].un_inputs_count.ToString()); Cell6.CellStyle = cellStyle2; } //输出的文件名称 string fileName = "监管名录统计" + ".xls"; //把Excel转为流，输出 //创建文件流 System.IO.MemoryStream bookStream = new System.IO.MemoryStream(); //将工作薄写入文件流 wb.Write(bookStream); //输出之前调用Seek（偏移量，游标位置) 把0位置指定为开始位置 bookStream.Seek(0, System.IO.SeekOrigin.Begin); //Stream对象,文件类型,文件名称 return File(bookStream, "application/vnd.ms-excel", fileName); #endregion }
导出效果：
