基于POI导出Excel支持多Sheet分页导出，保姆级测试用例，开箱即用

package com.datalook.excel;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * @description:
 */
public class TestExcel {
    private static final String DEFAULT_DATE_PATTERN = "yyyy-MM-dd";
    private static final int DEFAULT_COLUMN_WIDTH = 12;

    public static void exportExcel(ArrayList<LinkedHashMap> titleList, JSONArray dataArray, OutputStream os) {
        String datePattern = DEFAULT_DATE_PATTERN;
        int minBytes = DEFAULT_COLUMN_WIDTH;
        int sheetIndex = 1; // Sheet索引

        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);

        CellStyle title1Style = workbook.createCellStyle();
        title1Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        title1Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        title1Style.setFont(titleFont);

        CellStyle title2Style = workbook.createCellStyle();
        title2Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        title2Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font title2Font = workbook.createFont();
        title2Font.setUnderline((byte) 1);
        title2Font.setColor(HSSFColor.BLUE.index);
        title2Style.setFont(title2Font);

        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);

        String title1 = (String) titleList.get(0).get("title1");
        String title2 = (String) titleList.get(0).get("title2");
        LinkedHashMap<String, String> headMap = titleList.get(1);

        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(title1);
        sheet.createFreezePane(0, 3, 0, 3);

        int[] colWidthArr = new int[headMap.size()];
        String[] headKeyArr = new String[headMap.size()];
        String[] headValArr = new String[headMap.size()];
        int i = 0;
        for (Map.Entry<String, String> entry : headMap.entrySet()) {
            headKeyArr[i] = entry.getKey();
            headValArr[i] = entry.getValue();

            int bytes = headKeyArr[i].getBytes().length;
            colWidthArr[i] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(i, colWidthArr[i] * 256);
            i++;
        }

        int rowIndex = 0;
        int dataCount = 0;

        for (Object obj : dataArray) {
            if (dataCount >= 1) {
                sheet = (SXSSFSheet) workbook.createSheet(title1 + "_" + sheetIndex);
                sheet.createFreezePane(0, 3, 0, 3);
                rowIndex = 0;
                dataCount = 0;
                sheetIndex++;
            }

            if (rowIndex == 0) {
                SXSSFRow title1Row = (SXSSFRow) sheet.createRow(rowIndex);
                title1Row.createCell(0).setCellValue(title1);
                title1Row.getCell(0).setCellStyle(title1Style);
                if (headMap.size() - 1 == 0) {
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
                } else {
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
                }

                SXSSFRow title2Row = (SXSSFRow) sheet.createRow(rowIndex + 1);
                title2Row.createCell(0).setCellValue(title2);
                title2Row.getCell(0).setCellStyle(title2Style);
                if (headMap.size() - 1 == 0) {
                    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
                } else {
                    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headMap.size() - 1));
                }

                SXSSFRow headerRow = (SXSSFRow) sheet.createRow(rowIndex + 2);
                for (int j = 0; j < headValArr.length; j++) {
                    headerRow.createCell(j).setCellValue(headValArr[j]);
                    headerRow.getCell(j).setCellStyle(headerStyle);
                }
                rowIndex = 3;
            }

            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);
            for (int k = 0; k < headKeyArr.length; k++) {
                SXSSFCell cell = (SXSSFCell) dataRow.createCell(k);
                Object o = jo.get(headKeyArr[k]);
                String cellValue = "";

                if (o == null) {
                    cellValue = "";
                } else if (o instanceof Date) {
                    cellValue = new SimpleDateFormat(datePattern).format(o);
                } else if (o instanceof Float || o instanceof Double) {
                    cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                } else {
                    cellValue = o.toString();
                }

                if (cellValue.equals("true")) {
                    cellValue = "男";
                } else if (cellValue.equals("false")) {
                    cellValue = "女";
                }

                cell.setCellValue(cellValue);
                cell.setCellStyle(cellStyle);
            }
            rowIndex++;
            dataCount++;
        }

        try {
            workbook.write(os);
            os.flush();
            os.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        // 示例用法
        ArrayList<LinkedHashMap> titleList = new ArrayList<>();
        LinkedHashMap<String, String> titleMap1 = new LinkedHashMap<>();
        titleMap1.put("title1", "商户流水查询");
        titleMap1.put("title2", "报表生成时间：2024-04-25");
        titleList.add(titleMap1);

        LinkedHashMap<String, String> titleMap2 = new LinkedHashMap<>();
        titleMap2.put("username", "姓名");
        titleMap2.put("idserial", "证件号");
        titleMap2.put("poscode", "设备编号");
        titleMap2.put("uploadtime", "交易时间");
        titleMap2.put("txamt", "交易金额（元）");
        titleMap2.put("txname", "交易事件");
        titleList.add(titleMap2);

        JSONArray dataArray = new JSONArray();
        JSONObject dataObj1 = new JSONObject();
        dataObj1.put("username", "张三");
        dataObj1.put("idserial", "1234567890");
        dataObj1.put("poscode", "123456");
        dataObj1.put("uploadtime", "2024-04-25 10:00:00");
        dataObj1.put("txamt", 100.00);
        dataObj1.put("txname", "购物");
        dataArray.add(dataObj1);

        JSONObject dataObj2 = new JSONObject();
        dataObj2.put("username", "李四");
        dataObj2.put("idserial", "0987654321");
        dataObj2.put("poscode", "654321");
        dataObj2.put("uploadtime", "2024-04-26 14:30:00");
        dataObj2.put("txamt", 200.00);
        dataObj2.put("txname", "充值");
        dataArray.add(dataObj2);

        try {
            String filePath = "D:/export.xlsx";
            OutputStream os = new FileOutputStream(filePath);
            exportExcel(titleList, dataArray, os);
            os.close();
            System.out.println("Excel导出完成，文件保存在：" + filePath);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}