excel导出

点击查看

import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

@Service
public class LargeDataExcelExportService {

    // 分页查询每页大小
    private static final int PAGE_SIZE = 5000;
    // keep 100 rows in memory, exceeding rows will be flushed to disk
    private static final int SXSSF_WINDOW_SIZE = 100;
    // 每个 Sheet 最多 100 万行(标题行也算在内)
    private static final int MAX_ROWS_PER_SHEET = 100_0000;
    // Sheet名称最大长度
    private static final int MAX_SHEET_NAME_LENGTH = 31;
    // 总条数
    private int totalRecords;

    // 样式缓存
    private final Map<String, CellStyle> STYLE_CACHE = new ConcurrentHashMap<>();
    private String HEADER_STYLE = "HEADER_STYLE";
    private String DATE_STYLE = "DATE_STYLE";
    private String NUMBER_STYLE = "NUMBER_STYLE";
    private String DEFAULT_STYLE = "DEFAULT_STYLE";

    @Resource
    private LargeDataExcelExportMapper largeDataExcelExportMapper;

    public void export(HttpServletResponse response, int totalRows) throws IOException {
        setupResponse(response, "large_data_excel");

        SXSSFWorkbook workbook = new SXSSFWorkbook(SXSSF_WINDOW_SIZE);
        // 初始化共享样式（每个工作簿只创建一次）
        initializeSharedStyles(workbook);

        try (OutputStream out = response.getOutputStream()) {
            int totalSheets = calculateTotalSheets(totalRows);

            System.out.println("开始导出，总数据量: " + totalRows + " 条，需要拆分 " + totalSheets + " 个Sheet");

            int currentPage = 0;
            int globalRowIndex = 0; // 全局行索引
            int currentSheetIndex = 0; // 当前Sheet索引

            String[] headers = {"ID", "姓名", "年龄", "手机号", "邮箱", "创建时间"};
            Sheet currentSheet = this.createNewSheet(workbook, currentSheetIndex, headers);
            int currentSheetRowCount = 1; // 当前Sheet的行数（从1开始，包含标题行）

            while (globalRowIndex < totalRows) {
                // 检查是否需要创建新Sheet
                if (currentSheetRowCount >= MAX_ROWS_PER_SHEET) {
                    currentSheetIndex++;
                    currentSheet = this.createNewSheet(workbook, currentSheetIndex, headers);
                    currentSheetRowCount = 1; // 重置行计数
                    System.out.println("创建新Sheet: " + getSheetName(currentSheetIndex));
                }

                // 获取当前页数据
                List<Map<String, Object>> currentPageData = largeDataExcelExportMapper.fetchPageData(currentPage, PAGE_SIZE, totalRows);

                if (currentPageData.isEmpty()) {
                    break;
                }

                // 处理当前页数据到当前Sheet
                currentSheetRowCount = processPageDataWithSharedStyles(currentSheet, currentPageData, currentSheetRowCount);

                globalRowIndex += currentPageData.size();
                currentPage++;

                // 定期刷新
                if (currentPage % 10 == 0) {
                    ((SXSSFSheet) currentSheet).flushRows(PAGE_SIZE * 3);
                }

                // 进度监控
                if (currentPage % 50 == 0 || currentPage <= 5) {
                    logProgress(currentPage, currentSheetIndex, globalRowIndex, totalRows);
                }
            }

            workbook.write(out);
            out.flush();

            System.out.println("导出完成！总页数: " + currentPage +
                    ", 总Sheet数: " + (currentSheetIndex + 1) +
                    ", 总行数: " + globalRowIndex);

        } finally {
            STYLE_CACHE.clear();
            workbook.dispose();
            workbook.close();
        }
    }

    /**
     * 计算需要的Sheet总数
     */
    private int calculateTotalSheets(int totalRecords) {
        return (int) Math.ceil((double) totalRecords / MAX_ROWS_PER_SHEET);
    }

    /**
     * 创建新Sheet
     */
    private Sheet createNewSheet(SXSSFWorkbook workbook, int sheetIndex, String[] headers) {
        String sheetName = getSheetName(sheetIndex);
        Sheet sheet = workbook.createSheet(sheetName);

        // 设置默认列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.setColumnWidth(i, 4000);
        }

        // 创建标题行
        createHeaderRow(sheet, headers, workbook);

        return sheet;
    }

    /**
     * 生成Sheet名称（处理Excel的名称限制）
     */
    private String getSheetName(int sheetIndex) {
        String baseName = "数据";
        String sheetName;

        if (sheetIndex == 0) {
            sheetName = baseName;
        } else {
            sheetName = baseName + (sheetIndex + 1);
        }

        // 确保名称不超过Excel限制
        if (sheetName.length() > MAX_SHEET_NAME_LENGTH) {
            sheetName = sheetName.substring(0, MAX_SHEET_NAME_LENGTH);
        }

        // 检查并处理无效字符
        sheetName = sheetName.replaceAll("[\\\\/*\\[\\]?：]", "");

        return sheetName;
    }


    private void createHeaderRow(Sheet sheet, String[] headers, Workbook workbook) {
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(STYLE_CACHE.get(HEADER_STYLE));
        }
    }


    /**
     * 初始化共享样式（每个工作簿只调用一次）
     */
    private void initializeSharedStyles(SXSSFWorkbook workbook) {
        // 标题样式
        CellStyle headerStyle = createHeaderStyle(workbook);

        // 日期样式
        CellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));

        // 数字样式
        CellStyle numberStyle = workbook.createCellStyle();
        numberStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));

        // 默认样式
        CellStyle defaultStyle = workbook.createCellStyle();

        STYLE_CACHE.put(HEADER_STYLE, headerStyle);
        STYLE_CACHE.put(DATE_STYLE, dateStyle);
        STYLE_CACHE.put(NUMBER_STYLE, numberStyle);
        STYLE_CACHE.put(DEFAULT_STYLE, defaultStyle);

    }

    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        return style;
    }

    /**
     * 使用共享样式处理数据
     */
    private int processPageDataWithSharedStyles(Sheet sheet, List<Map<String, Object>> pageData, int startRow) {
        for (int i = 0; i < pageData.size(); i++) {
            if (startRow + i >= MAX_ROWS_PER_SHEET) break;

            Row row = sheet.createRow(startRow + i);
            Map<String, Object> record = pageData.get(i);

            int colIndex = 0;
            for (Object value : record.values()) {
                Cell cell = row.createCell(colIndex++);
                setCellValueWithSharedStyle(cell, value);
            }
        }
        return startRow + Math.min(pageData.size(), MAX_ROWS_PER_SHEET - startRow);
    }

    /**
     * 使用共享样式设置单元格值
     */
    private void setCellValueWithSharedStyle(Cell cell, Object value) {
        CellStyle defaultStyle = STYLE_CACHE.get(DEFAULT_STYLE);
        CellStyle numberStyle = STYLE_CACHE.get(NUMBER_STYLE);
        CellStyle dateStyle = STYLE_CACHE.get(DATE_STYLE);
        if (value == null) {
            cell.setCellValue("");
            cell.setCellStyle(defaultStyle);
        } else if (value instanceof Number) {
            if (value instanceof Integer || value instanceof Long) {
                cell.setCellValue(((Number) value).doubleValue());
                cell.setCellStyle(defaultStyle);
            } else if (value instanceof Double) {
                cell.setCellValue((Double) value);
                cell.setCellStyle(numberStyle); // 复用数字样式
            }
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
            cell.setCellStyle(dateStyle); // 复用日期样式
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
            cell.setCellStyle(defaultStyle);
        } else {
            cell.setCellValue(value.toString());
            cell.setCellStyle(defaultStyle);
        }
    }

    private void setupResponse(HttpServletResponse response, String fileName) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        String encodedFileName;
        try {
            encodedFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            encodedFileName = fileName;
        }
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFileName + ".xlsx");
    }

}