java生成Excel文件

版本一：

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, int pollSize) 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"));        // 多线程处理
        ExecutorService executorService = Executors.newFixedThreadPool(pollSize);
        CountDownLatch latch = new CountDownLatch((int) Math.ceil((double) data.size() / batchSize));
        // 填充数据行，分片处理数据
        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);            executorService.execute(() -> {
                fillDataRows(sheet, headers, batchData, amountStyle, dateStyle);
                latch.countDown();
            });
        }
        // 等待所有任务完成
        latch.await();
        executorService.shutdown();
        // 生成二进制数据流;
        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参数。

版本三：修改使用方法==>sxssfworkbook:用于操作 xlsx 格式的 Excel,支持超过 2^20 行的数据,实际数据存储在临时文件中,只将部分数据加载到内存中；并多线程处理。

import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class ExcelGenerator {

    @SneakyThrows
    public static byte[] getFile(String fileName, List<Map<String, String>> headers, List<Map<String, String>> data, int batchSize) {
        // 创建Excel工作簿
        Workbook workbook = new SXSSFWorkbook();
        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];
        // 指定临时目录
        File tempDir = Files.createTempDirectory("poifiles").toFile();
        try {
            ByteArrayOutputStream stream = new ByteArrayOutputStream();
            workbook.write(stream);
            binaryData = stream.toByteArray();
            System.out.println("二进制流数据生成成功！");
            stream.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            workbook.close();
            // 删除临时文件及目录
            Files.deleteIfExists(tempDir.toPath());
        }
        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); // 其他类型的数据直接填入原始值
                }
            });
        });
    }

}

ENTRYPOINT ["java","-Djasypt.encryptor.password=密钥","-Djava.io.tmpdir=/home/upload/","-jar","-Xms2g","-Xmx3g","/app.jar"," > /logs/app.log"]

由于SXSSFWorkbook不是线程安全的，单线程环境下使用，或者使用线程安全的XSSFWorkbook。