版本一：
import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.ByteArrayOutputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.stream.IntStream; public class ExcelGenerator { public static byte[] getFile(String fileName, List headers, List data) { // 创建Excel工作簿 Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(fileName.length() > 31 ? fileName.substring(0,30) : fileName); // Sheet sheet = workbook.createSheet("Sheet1"); // 创建标题行 Row headerRow = sheet.createRow(0); for (int i = 0; i < headers.size(); i++) { Map<String, String> header = (Map<String, String>) headers.get(i); Cell cell = headerRow.createCell(i); cell.setCellValue(header.get("cn_name")); // 设置单元格宽度 try { sheet.setColumnWidth(i, calculateColumnWidth(headers, data, i)); } catch (Exception e) { // 宽度设置失败，则取值头数据宽度设置 int headerWidth = header.get("cn_name").getBytes().length; sheet.setColumnWidth(i, headerWidth); } } // 单元格样式 CellStyle amountStyle = workbook.createCellStyle(); amountStyle.setAlignment(HorizontalAlignment.RIGHT); amountStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00")); CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setAlignment(HorizontalAlignment.RIGHT); dateStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy/mm/dd")); // 填充数据行 for (int i = 0; i < data.size(); i++) { Map<String, String> rowData = (Map<String, String>) data.get(i); Row dataRow = sheet.createRow(i + 1); for (int j = 0; j < headers.size(); j++) { Map<String, String> header = (Map<String, String>) headers.get(j); Cell cell = dataRow.createCell(j); String controlType = header.get("control_type"); String value = rowData.get(header.get("en_name")); if (controlType.equals("TEXT_WITH_AMOUNT")) { // 对TEXT_WITH_AMOUNT类型的金额进行格式化 try { // 设置公式 // cell.setCellFormula("SUM(" + getExcelColumnName(j) + "2:" + // getExcelColumnName(j) + (data.size() + 1) + ")"); // System.out.println("SUM(" + getExcelColumnName(j) + "2:" + // getExcelColumnName(j) + (data.size() + 1) + ")"); // // 设置公式 // cell.setCellFormula("SUM(" + cell.getAddress() + ")"); // System.out.println("SUM(" + cell.getAddress() + ")"); cell.setCellValue(Double.parseDouble(value)); cell.setCellType(CellType.NUMERIC); cell.setCellStyle(amountStyle); } catch (NumberFormatException e) { cell.setCellValue(value); // 格式化失败时仍然填入原始值 } } else if (controlType.equals("DATEBOX")) { // 对DATEBOX类型的日期进行格式化 try { String formattedValue = value.substring(0, 4) + "/" + value.substring(4, 6) + "/" + value.substring(6); cell.setCellValue(formattedValue); cell.setCellStyle(dateStyle); } catch (Exception e) { cell.setCellValue(value); // 格式化失败时仍然填入原始值 } } else { cell.setCellValue(value); // 其他类型的数据直接填入原始值 } } } // 保存为xlsx文件 try (FileOutputStream fileOut = new FileOutputStream(fileName + ".xlsx")) { workbook.write(fileOut); System.out.println("Excel文件成功生成！"); } catch (IOException e) { System.out.println("生成Excel文件时发生错误：" + e.getMessage()); } // 生成二进制数据流; byte[] binaryData = new byte[0]; try (ByteArrayOutputStream stream = new ByteArrayOutputStream()) { workbook.write(stream); binaryData = stream.toByteArray(); System.out.println("二进制流数据生成成功！"); } catch (IOException e) { e.printStackTrace(); } return binaryData; } // 计算单元格宽度 private static int calculateColumnWidth(List headers, List data, int columnIndex) { int maxWidth = 0; // 计算标题行宽度 for (int i = 0; i < headers.size(); i++) { Map<String, String> header = (Map<String, String>) headers.get(i); String cnName = header.get("cn_name"); int headerWidth = cnName.getBytes().length; if (headerWidth > maxWidth) { maxWidth = headerWidth; } } // 计算数据行宽度 for (int i = 0; i < data.size(); i++) { Map<String, String> rowData = (Map<String, String>) data.get(i); Map<String, String> header = (Map<String, String>) headers.get(columnIndex); String enName = header.get("en_name"); String value = rowData.get(enName); int dataWidth = value.getBytes().length; if (dataWidth > maxWidth) { maxWidth = dataWidth; } } // 添加额外宽度，并转换为Excel中的单位 maxWidth += 2; return maxWidth * 256; } // 将列索引转换为Excel列名 private static String getExcelColumnName(int columnIndex) { int dividend = columnIndex + 1; StringBuilder columnName = new StringBuilder(); while (dividend > 0) { int modulo = (dividend - 1) % 26; columnName.insert(0, (char) ('A' + modulo)); dividend = (dividend - modulo) / 26; } return columnName.toString(); } public static void main(String[] args) { List<Map<String, String>> data = new ArrayList<>(); Map<String, String> dataRow1 = new HashMap<>(); dataRow1.put("no", "2023-3598"); dataRow1.put("amount", "120000"); dataRow1.put("date", "20220111"); dataRow1.put("type", "week"); data.add(dataRow1); Map<String, String> dataRow2 = new HashMap<>(); dataRow2.put("no", "2023-3535"); dataRow2.put("amount", "120000.35"); dataRow2.put("date", "20220131"); dataRow2.put("type", "week2"); data.add(dataRow2); Map<String, String> dataRow3 = new HashMap<>(); dataRow3.put("no", "2023-3555"); dataRow3.put("amount", ""); dataRow3.put("date", ""); dataRow3.put("type", "3"); data.add(dataRow3); Map<String, String> dataRow4 = new HashMap<>(); dataRow4.put("no", "2023-5555"); dataRow4.put("amount", "120000.95"); dataRow4.put("date", "20220331"); dataRow4.put("type", "week4"); data.add(dataRow4); List<Map<String, String>> headers = new ArrayList<>(); Map<String, String> header1 = new HashMap<>(); header1.put("cn_name", "编号"); header1.put("en_name", "no"); header1.put("control_type", "TEXT"); headers.add(header1); Map<String, String> header2 = new HashMap<>(); header2.put("cn_name", "金额"); header2.put("en_name", "amount"); header2.put("control_type", "TEXT_WITH_AMOUNT"); headers.add(header2); Map<String, String> header3 = new HashMap<>(); header3.put("cn_name", "日期"); header3.put("en_name", "date"); header3.put("control_type", "DATEBOX"); headers.add(header3); Map<String, String> header4 = new HashMap<>(); header4.put("cn_name", "类型"); header4.put("en_name", "type"); header4.put("control_type", "TEXT"); headers.add(header4); String file = "业务日期-未税收入-人月类期初收入调整报表admin20230919.xlsx"; byte[] fileByte = getFile(file, headers, data); System.out.println(fileByte); } }
版本二：采用分片处理，并使用forEach替换for，同时减少循环，表格列宽以头数据尺寸为准
import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.ByteArrayOutputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.List; import java.util.Map; public class ExcelGenerator { public static byte[] getFile(String fileName, List<Map<String, String>> headers, List<Map<String, String>> data, int batchSize) throws IOException { // 创建Excel工作簿 Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(fileName.length() > 31 ? fileName.substring(0,30) : fileName); // 创建标题行 Row headerRow = sheet.createRow(0); headers.forEach(header -> { Cell cell = headerRow.createCell(headers.indexOf(header)); cell.setCellValue(header.get("cn_name")); // 设置单元格宽度，取值头数据宽度设置 int headerWidth = (header.get("cn_name").getBytes().length + 20) * 256; sheet.setColumnWidth(headers.indexOf(header), headerWidth); }); // 单元格样式 CellStyle amountStyle = workbook.createCellStyle(); amountStyle.setAlignment(HorizontalAlignment.RIGHT); amountStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00")); CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setAlignment(HorizontalAlignment.RIGHT); dateStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy/mm/dd")); // 填充数据行，分片处理数据 for (int i = 0; i < data.size(); i += batchSize) { int endIndex = Math.min(i + batchSize, data.size()); List<Map<String, String>> batchData = data.subList(i, endIndex); fillDataRows(sheet, headers, batchData, amountStyle, dateStyle); } // 生成二进制数据流; byte[] binaryData = new byte[0]; try { ByteArrayOutputStream stream = new ByteArrayOutputStream(); workbook.write(stream); binaryData = stream.toByteArray(); System.out.println("二进制流数据生成成功！"); stream.close(); } catch (IOException e) { e.printStackTrace(); } finally { workbook.close(); } return binaryData; } private static void fillDataRows(Sheet sheet, List<Map<String, String>> headers, List<Map<String, String>> data, CellStyle amountStyle, CellStyle dateStyle) { int startRowIndex = sheet.getLastRowNum() + 1; System.out.println("startRowIndex==========" + startRowIndex); data.forEach(rowData -> { Row dataRow = sheet.createRow(startRowIndex + data.indexOf(rowData)); headers.forEach(header -> { Cell cell = dataRow.createCell(headers.indexOf(header)); String controlType = header.get("control_type"); String value = rowData.get(header.get("en_name")); if (controlType.equals("TEXT_WITH_AMOUNT") && value != null) { // 对TEXT_WITH_AMOUNT类型的金额进行格式化 try { cell.setCellValue(Double.parseDouble(value)); cell.setCellType(CellType.NUMERIC); cell.setCellStyle(amountStyle); } catch (NumberFormatException e) { cell.setCellValue(value); // 格式化失败时仍然填入原始值 } } else if (controlType.equals("DATEBOX")) { // 对DATEBOX类型的日期进行格式化 try { String formattedValue = value.substring(0, 4) + "/" + value.substring(4, 6) + "/" + value.substring(6); cell.setCellValue(formattedValue); cell.setCellStyle(dateStyle); } catch (Exception e) { cell.setCellValue(value); // 格式化失败时仍然填入原始值 } } else { cell.setCellValue(value); // 其他类型的数据直接填入原始值 } }); }); } }
注意：分片处理为了解决内存溢出问题，最终效果不明显，在application.yml配置的JVM参数，在docker环境没被加载使用，默认最小为8M，最终修改dockerfile：
ENTRYPOINT ["java","-Djasypt.encryptor.password=密钥","-jar","-Xms2g","-Xmx3g","/app.jar"," > /logs/app.log"]
需要关注的是参数需要逐个添加比如上面设置的最大最小JVM参数。