写一个.net 6的API接口,使用c#语言调用NPOI生成多个Excel文件,每个Excel文件中都包含多个Sheet,表中标题为“我是标题”,标题格式为加粗合并居中,每个sheet里,都要在数据满7行时,空两行再将标题和表头重复一次。所有Excel文件都存为xls格式。
效果:
代码操作:
源码:
using Microsoft.AspNetCore.Mvc;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.IO;
namespace ExcelApi.Controllers
{
[ApiController]
[Route("[controller]")]
public class ExcelController : ControllerBase
{
private const string ExcelFolder = "./GeneratedExcelFiles/";
[HttpPost("generate")]
public IActionResult GenerateExcelFiles()
{
try
{
// Create a folder for storing generated Excel files if not exists
Directory.CreateDirectory(ExcelFolder);
// Generate multiple Excel files with multiple sheets
for (int fileIndex = 1; fileIndex <= 3; fileIndex++) // Example: Generate 3 Excel files
{
string fileName = $"File{fileIndex}.xls";
string filePath = Path.Combine(ExcelFolder, fileName);
using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new HSSFWorkbook();
for (int sheetIndex = 1; sheetIndex <= 2; sheetIndex++) // Example: Each file has 2 sheets
{
string sheetName = $"Sheet{sheetIndex}";
ISheet sheet = workbook.CreateSheet(sheetName);
// Add title row
IRow titleRow = sheet.CreateRow(0);
titleRow.HeightInPoints = 30;
ICell titleCell = titleRow.CreateCell(0);
titleCell.SetCellValue("我是标题");
// Style for title
var titleStyle = workbook.CreateCellStyle();
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)FontBoldWeight.Bold;
titleStyle.SetFont(titleFont);
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
titleCell.CellStyle = titleStyle;
// Merge cells for title row
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 9)); // Assuming 10 columns
// Add header row
IRow headerRow = sheet.CreateRow(2);
headerRow.HeightInPoints = 20;
for (int colIndex = 0; colIndex < 10; colIndex++) // Example: 10 columns
{
ICell headerCell = headerRow.CreateCell(colIndex);
headerCell.SetCellValue($"Header{colIndex + 1}");
}
// Add data rows
for (int rowIndex = 0; rowIndex < 20; rowIndex++) // Example: 20 data rows
{
IRow dataRow = sheet.CreateRow(rowIndex + 3); // Start from row 3 (title + header)
for (int colIndex = 0; colIndex < 10; colIndex++) // Example: 10 columns
{
ICell dataCell = dataRow.CreateCell(colIndex);
dataCell.SetCellValue($"{sheetName} - Row{rowIndex + 1} - Col{colIndex + 1}");
}
// Check if need to repeat title and header
if ((rowIndex + 1) % 7 == 0)
{
// Empty rows
sheet.CreateRow(rowIndex + 4);
sheet.CreateRow(rowIndex + 5);
// Repeat title row
IRow repeatedTitleRow = sheet.CreateRow(rowIndex + 6);
ICell repeatedTitleCell = repeatedTitleRow.CreateCell(0);
repeatedTitleCell.SetCellValue("我是标题");
repeatedTitleCell.CellStyle = titleStyle;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex + 6, rowIndex + 7, 0, 9));
// Repeat header row
IRow repeatedHeaderRow = sheet.CreateRow(rowIndex + 8);
for (int colIndex = 0; colIndex < 10; colIndex++) // Example: 10 columns
{
ICell repeatedHeaderCell = repeatedHeaderRow.CreateCell(colIndex);
repeatedHeaderCell.SetCellValue($"Header{colIndex + 1}");
}
rowIndex += 3; // Skip the next 3 rows (already added)
}
}
}
// Write the workbook to the FileStream
workbook.Write(fs);
}
}
return Ok("Excel files generated successfully.");
}
catch (Exception ex)
{
return StatusCode(500, $"An error occurred: {ex.Message}");
}
}
}
}
好的,下面是一个.NET 6的API接口的示例,使用C#调用NPOI库生成多个Excel文件,每个Excel文件包含多个Sheet。每个Sheet中,当数据满7行时,会空两行再将标题和表头重复一次,并且所有Excel文件都存为xls格式。
准备工作
-
安装NPOI库:确保在.NET 6项目中安装了NPOI库,可以通过NuGet包管理器或者命令行来安装。
dotnet add package NPOI
-
创建.NET 6 Web API项目:确保已经创建了一个.NET 6的Web API项目。
编写API接口
using Microsoft.AspNetCore.Mvc;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.IO;
namespace ExcelApi.Controllers
{
[ApiController]
[Route("[controller]")]
public class ExcelController : ControllerBase
{
private const string ExcelFolder = "./GeneratedExcelFiles/";
[HttpPost("generate")]
public IActionResult GenerateExcelFiles()
{
try
{
// Create a folder for storing generated Excel files if not exists
Directory.CreateDirectory(ExcelFolder);
// Generate multiple Excel files with multiple sheets
for (int fileIndex = 1; fileIndex <= 3; fileIndex++) // Example: Generate 3 Excel files
{
string fileName = $"File{fileIndex}.xls";
string filePath = Path.Combine(ExcelFolder, fileName);
using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new HSSFWorkbook();
for (int sheetIndex = 1; sheetIndex <= 2; sheetIndex++) // Example: Each file has 2 sheets
{
string sheetName = $"Sheet{sheetIndex}";
ISheet sheet = workbook.CreateSheet(sheetName);
// Add title row
IRow titleRow = sheet.CreateRow(0);
titleRow.HeightInPoints = 30;
ICell titleCell = titleRow.CreateCell(0);
titleCell.SetCellValue("我是标题");
// Style for title
var titleStyle = workbook.CreateCellStyle();
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)FontBoldWeight.Bold;
titleStyle.SetFont(titleFont);
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
titleCell.CellStyle = titleStyle;
// Merge cells for title row
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 9)); // Assuming 10 columns
// Add header row
IRow headerRow = sheet.CreateRow(2);
headerRow.HeightInPoints = 20;
for (int colIndex = 0; colIndex < 10; colIndex++) // Example: 10 columns
{
ICell headerCell = headerRow.CreateCell(colIndex);
headerCell.SetCellValue($"Header{colIndex + 1}");
}
// Add data rows
for (int rowIndex = 0; rowIndex < 20; rowIndex++) // Example: 20 data rows
{
IRow dataRow = sheet.CreateRow(rowIndex + 3); // Start from row 3 (title + header)
for (int colIndex = 0; colIndex < 10; colIndex++) // Example: 10 columns
{
ICell dataCell = dataRow.CreateCell(colIndex);
dataCell.SetCellValue($"{sheetName} - Row{rowIndex + 1} - Col{colIndex + 1}");
}
// Check if need to repeat title and header
if ((rowIndex + 1) % 7 == 0)
{
// Empty rows
sheet.CreateRow(rowIndex + 4);
sheet.CreateRow(rowIndex + 5);
// Repeat title row
IRow repeatedTitleRow = sheet.CreateRow(rowIndex + 6);
ICell repeatedTitleCell = repeatedTitleRow.CreateCell(0);
repeatedTitleCell.SetCellValue("我是标题");
repeatedTitleCell.CellStyle = titleStyle;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex + 6, rowIndex + 7, 0, 9));
// Repeat header row
IRow repeatedHeaderRow = sheet.CreateRow(rowIndex + 8);
for (int colIndex = 0; colIndex < 10; colIndex++) // Example: 10 columns
{
ICell repeatedHeaderCell = repeatedHeaderRow.CreateCell(colIndex);
repeatedHeaderCell.SetCellValue($"Header{colIndex + 1}");
}
rowIndex += 3; // Skip the next 3 rows (already added)
}
}
}
// Write the workbook to the FileStream
workbook.Write(fs);
}
}
return Ok("Excel files generated successfully.");
}
catch (Exception ex)
{
return StatusCode(500, $"An error occurred: {ex.Message}");
}
}
}
}
解释说明
- GenerateExcelFiles 方法是API的入口点,负责生成多个Excel文件。在示例中,生成了3个Excel文件,每个文件包含2个Sheet。
- 每个Sheet中,通过循环生成标题行和表头行,并在数据满7行时,空两行再将标题和表头重复一次。
- 使用NPOI库来创建和操作Excel文件,其中包括创建工作簿、工作表,设置单元格内容和样式,以及合并单元格等操作。
- 每个生成的Excel文件会存储在项目的
./GeneratedExcelFiles/
文件夹中。
注意事项
- 示例中假设每个Excel文件有2个Sheet,每个Sheet有10列和20行数据。具体的行数和列数可以根据实际需求进行调整。
- 标题行使用了加粗、居中的样式,并通过合并单元格实现标题的展示效果。
- 在生成Excel文件时,需要确保对文件路径和名称进行适当的处理,以及确保服务器上有写入文件的权限。
通过调用API接口 /excel/generate
,即可生成多个包含多个Sheet的Excel文件,并满足标题和表头重复的需求。