springboot导出excel,左边固定，右边(年份，征收，次数)是List动态
导出的类结构：FundsReturnSummary
@Data
public class FundsReturnSummary {
/**
* 所属工会_val1
*/
@Excel(name = "所属工会_val1")
private String localOrgVal;
/**
* 年均
*/
@Excel(name = "年均")
private BigDecimal averageAmount;
/**
* 纳税代码1
*/
@Excel(name = "纳税代码1")
private String taxCode;
/**
* 纳税人名称key1
*/
@Excel(name = "纳税人名称key1")
private Long payingUnitNameKey;
/**
* 纳税人名称val1
*/
@Excel(name = "纳税人名称val1")
private String payingUnitNameVal;
private List<FundsReturnSummaryYearData> fundsReturnSummaryYearDataList;
}@Data
public class FundsReturnSummaryYearData {
/**
* 年
*/
private Integer year;
/**
* 金额
*/
private BigDecimal amount;
/**
* 次数
*/
private Integer count;
}controller层
@PostMapping("/objInsPayingYear/fundsReturnSummaryExport")
public void fundsReturnSummaryExport(@RequestBody @Validated ObjInsPayingYearParam objInsPayingYearParam, HttpServletResponse response) {
objInsPayingYearService.fundsReturnSummaryExport(objInsPayingYearParam,response);
}service层
@Override
public void fundsReturnSummaryExport(ObjInsPayingYearParam objInsPayingYearParam, HttpServletResponse response) {
List<FundsReturnSummary> fundsReturnSummaries = this.listFundsReturnSummary(objInsPayingYearParam);
try {
Workbook workbook = createWorkbookForDownload(fundsReturnSummaries);
try {
this.out(response, workbook, "单位明细分析.xlsx");
} catch (Exception e) {
throw new RuntimeException(e);
}
} catch (IOException e) {
throw new RuntimeException(e);
}
}导出实现
public Workbook createWorkbookForDownload(List<FundsReturnSummary> fundsReturnSummaries) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("缴费单位明细分析");
writeInitialHeaders(sheet, "单位名称", "统一信用代码", "上级工会", "年均（万）");
adjustHeaderWidthsWithCustomSize(sheet, 10, 10, 10, 5); // 前三列至少30汉字，第四列至少5汉字宽度
int rowIndex = 0;
for (FundsReturnSummary fundsReturnSummary : fundsReturnSummaries) {
Row row = sheet.createRow(++rowIndex);
row.createCell(0).setCellValue(fundsReturnSummary.getPayingUnitNameVal());
row.createCell(1).setCellValue(fundsReturnSummary.getTaxCode());
row.createCell(2).setCellValue(fundsReturnSummary.getLocalOrgVal());
row.createCell(3).setCellValue(
Optional.ofNullable(fundsReturnSummary.getAverageAmount())
.map(avg -> avg.divide(BigDecimal.valueOf(10000), 2, RoundingMode.HALF_UP))
.orElse(BigDecimal.ZERO)
.toString()
);
// 对于每个FundsReturnSummaryYearData，添加对应的年份、征收、次数列头和数据
List<FundsReturnSummaryYearData> yearDataList = fundsReturnSummary.getFundsReturnSummaryYearDataList();
for (int i = 0; i < yearDataList.size(); i++) {
if (i == 0 || rowIndex == 1) { // 只在每行开始或首次迭代时添加表头
writeYearDataHeaders(sheet, rowIndex, i);
}
FundsReturnSummaryYearData yearData = yearDataList.get(i);
row.createCell(4 + i * 3).setCellValue(yearData.getYear());
row.createCell(5 + i * 3).setCellValue(
Optional.ofNullable(yearData.getAmount())
.map(amount -> amount.divide(BigDecimal.valueOf(10000), 2, RoundingMode.HALF_UP))
.orElse(BigDecimal.ZERO)
.toString()
);
row.createCell(6 + i * 3).setCellValue(yearData.getCount());
}
}
for (int i = 0; i < sheet.getRow(rowIndex).getLastCellNum(); i++) {
sheet.autoSizeColumn(i);
adjustColumnWidthWithCustomSize(sheet, i, i < 3 ? 10 : 5); // 前三列至少10汉字，其余至少5汉字宽度
}
return workbook;
}
// 更新方法以适应不同列的中文宽度需求
private void adjustHeaderWidthsWithCustomSize(Sheet sheet, int... charWidthsInChinese) {
for (int i = 0; i < charWidthsInChinese.length; i++) {
int widthInChars = charWidthsInChinese[i];
int width = widthInChars * 256 * 2; // 每个汉字约0.5个字符，乘以2以适应汉字宽度，再乘以256转换为Excel单位
sheet.setColumnWidth(i, width);
}
}
// 更新方法以适应不同列的最小中文显示宽度
private void adjustColumnWidthWithCustomSize(Sheet sheet, int columnIndex, int minCharsInChinese) {
int minWidth = minCharsInChinese * 256 * 2; // 同上逻辑计算最小宽度
int currentWidth = sheet.getColumnWidth(columnIndex);
if (currentWidth < minWidth) {
sheet.setColumnWidth(columnIndex, minWidth);
}
}
private void writeInitialHeaders(Sheet sheet, String... headers) {
Row headerRow = sheet.createRow(0);
IntStream.range(0, headers.length).forEach(i -> {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
});
}
private void writeYearDataHeaders(Sheet sheet, int rowIndex, int iteration) {
if (rowIndex == 1) { // 在第一行添加完整的表头
Row headerRow = sheet.getRow(rowIndex - 1);
headerRow.createCell(4 + iteration * 3).setCellValue("年份");
headerRow.createCell(5 + iteration * 3).setCellValue("征收（万）");
headerRow.createCell(6 + iteration * 3).setCellValue("次数");
} else { // 在后续行仅对新增的年份数据添加表头
Row currentRow = sheet.getRow(rowIndex - 1);
if (currentRow.getLastCellNum() < 4 + iteration * 3) {
sheet.getRow(rowIndex).createCell(4 + iteration * 3).setCellValue("年份");
sheet.getRow(rowIndex).createCell(5 + iteration * 3).setCellValue("征收（万）");
sheet.getRow(rowIndex).createCell(6 + iteration * 3).setCellValue("次数");
}
}
}
private void out(HttpServletResponse response, Workbook workbook, String fileName) throws Exception {
BufferedOutputStream out;
response.reset();
fileName = fileName == null ? "exportName" : fileName;
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes(StandardCharsets.UTF_8), "ISO8859-1") + ".xls");
out = new BufferedOutputStream(response.getOutputStream());
workbook.write(out);
out.flush();
out.close();
}
