@GetMapping("/export/simple")
public void exportSimple(HttpServletResponse response) throws IOException {
    // 1??【風(fēng)險點】全量查詢！數(shù)據(jù)量大必OOM！
    List<User> allUsers = userService.findAllUsers(); 

    // 2?? 設(shè)置響應(yīng)頭 (固定套路)
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("用戶列表", "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

    // 3?? EasyExcel 一鍵寫入
    EasyExcel.write(response.getOutputStream(), User.class)
            .sheet("用戶信息")
            .doWrite(allUsers); // 全量數(shù)據(jù)一次性寫入
}

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import java.io.OutputStream;
import java.util.List;

/**
 * 【核心武器】分頁寫入Excel工具 - 專治各種不服(OOM)
 */
public class PageWriteExcelHelper<T> {

    // ?? 關(guān)鍵接口：定義如何分頁獲取數(shù)據(jù) (由調(diào)用方實現(xiàn))
    public interface PageQuerySupplier<T> {
        List<T> getPage(int pageNum, int pageSize); // 第幾頁? 每頁幾條?
    }

    /**
     * 執(zhí)行分頁寫入
     * @param outputStream  輸出流 (響應(yīng)OutputStream)
     * @param head          數(shù)據(jù)模型Class (如 User.class)
     * @param pageSize      【重要】每批次處理條數(shù) (建議 1000~5000)
     * @param totalCount    總數(shù)據(jù)量 (用于計算總頁數(shù))
     * @param supplier      分頁數(shù)據(jù)提供器 (你的業(yè)務(wù)查詢邏輯)
     */
    public static <T> void writeByPage(OutputStream outputStream,
                                      Class<T> head,
                                      int pageSize,
                                      int totalCount,
                                      PageQuerySupplier<T> supplier) {
        // ?? 1. 初始化 ExcelWriter (EasyExcel 核心寫入器)
        ExcelWriter excelWriter = EasyExcel.write(outputStream, head).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("Sheet1").build(); // 默認(rèn)Sheet

        try {
            // ?? 2. 計算總頁數(shù) (小心除0)
            int totalPage = totalCount > 0 ? (int) Math.ceil((double) totalCount / pageSize) : 1;

            // ?? 3. 分頁循環(huán)：查詢 -> 寫入 -> 釋放
            for (int pageNum = 1; pageNum <= totalPage; pageNum++) {
                // ?? 3.1 獲取當(dāng)前頁數(shù)據(jù) (你的分頁查詢)
                List<T> pageData = supplier.getPage(pageNum, pageSize);

                // ?? 3.2 寫入當(dāng)前頁到 Excel
                excelWriter.write(pageData, writeSheet);

                // ??? 3.3 【關(guān)鍵】立即清空釋放當(dāng)前頁內(nèi)存！
                pageData.clear();
            }
        } finally {
            // ?? 4. 【務(wù)必關(guān)閉】釋放資源 (防止內(nèi)存泄漏)
            if (excelWriter != null) {
                excelWriter.finish(); // 重要?。?！
            }
        }
    }
}

@GetMapping("/export/million")
public void exportMassiveData(HttpServletResponse response) throws IOException {
    // 1?? 獲取總數(shù)據(jù)量 (用于計算分頁)
    int totalUsers = userService.countTotalUsers();

    // 2?? 設(shè)置響應(yīng)頭 (固定套路)
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("百萬用戶數(shù)據(jù)", "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

    // 3?? 【核心調(diào)用】使用分頁工具類導(dǎo)出
    PageWriteExcelHelper.writeByPage(
            response.getOutputStream(), // 響應(yīng)輸出流
            User.class,                 // 導(dǎo)出數(shù)據(jù)模型
            2000,                      // 每頁2000條 (根據(jù)業(yè)務(wù)調(diào)整)
            totalUsers,                 // 總數(shù)據(jù)量
            // ?? Lambda 實現(xiàn)分頁查詢邏輯 (優(yōu)雅!)
            (pageNum, pageSize) -> userService.findByPage(pageNum, pageSize)
    );
}

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;

/**
 * 【開箱即用】EasyExcel 導(dǎo)出增強(qiáng)工具類 (支持普通/分頁模式)
 */
public class ExcelExporter {

    // ============== 【1. 分頁寫入 (大數(shù)據(jù)量首選)】 ==============
    public static <T> void exportByPage(HttpServletResponse response,
                                       String fileName,    // 下載文件名
                                       String sheetName,   // Sheet名稱
                                       Class<T> dataModel, // 數(shù)據(jù)類 (User.class)
                                       int pageSize,       // 每頁條數(shù)
                                       int totalCount,     // 總條數(shù)
                                       PageQuerySupplier<T> pageSupplier) { // 分頁查詢邏輯

        setupResponse(response, fileName); // 設(shè)置響應(yīng)頭

        try (OutputStream out = response.getOutputStream()) {
            // ?? 委托給核心分頁工具執(zhí)行
            PageWriteExcelHelper.writeByPage(out, dataModel, pageSize, totalCount, pageSupplier);
        } catch (Exception e) {
            throw new RuntimeException("導(dǎo)出失敗: " + e.getMessage(), e); // 統(tǒng)一異常處理
        }
    }

    // ============== 【2. 普通導(dǎo)出 (小數(shù)據(jù)量)】 ==============
    public static <T> void exportSimple(HttpServletResponse response,
                                       String fileName,
                                       String sheetName,
                                       Class<T> dataModel,
                                       List<T> dataList) { // 全量數(shù)據(jù)List

        setupResponse(response, fileName);

        try (OutputStream out = response.getOutputStream()) {
            EasyExcel.write(out, dataModel)
                    .sheet(sheetName)
                    .doWrite(dataList); // 全量寫入
        } catch (Exception e) {
            throw new RuntimeException("導(dǎo)出失敗: " + e.getMessage(), e);
        }
    }

    // ============== 【私有方法：響應(yīng)頭設(shè)置 (復(fù)用)】 ==============
    private static void setupResponse(HttpServletResponse response, String fileName) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("UTF-8");
            String encodedFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20"); // 處理空格
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
        } catch (Exception e) {
            throw new RuntimeException("設(shè)置響應(yīng)頭失敗", e);
        }
    }

    // ============== 【內(nèi)部接口：分頁查詢供應(yīng)商】 ==============
    @FunctionalInterface
    public interface PageQuerySupplier<T> {
        List<T> getPage(int pageNum, int pageSize); // 函數(shù)式接口
    }
}

// 場景1: 導(dǎo)出小數(shù)據(jù)量 (<1萬)
@GetMapping("/export/users/small")
public void exportSmallUserList(HttpServletResponse response) {
    List<User> smallList = userService.findRecentUsers(5000); // 查5000條
    ExcelExporter.exportSimple(
            response,
            "最近用戶",
            "用戶數(shù)據(jù)",
            User.class,
            smallList
    );
}

// 場景2: 導(dǎo)出大數(shù)據(jù)量 (10萬+)
@GetMapping("/export/users/large")
public void exportLargeUserList(HttpServletResponse response) {
    int total = userService.countTotalUsers();
    ExcelExporter.exportByPage(
            response,
            "全量用戶數(shù)據(jù)",
            "用戶清單",
            User.class,
            3000, // 每批3000條
            total,
            (pageNum, pageSize) -> userService.findByPage(pageNum, pageSize) // 你的分頁查詢
    );
}

public static int calculateOptimalPageSize(Class<?> clazz) {
    // 1. 估算單條數(shù)據(jù)大小 (字節(jié)) - 根據(jù)業(yè)務(wù)模型調(diào)整邏輯
    long approxBytesPerRow = 500; // 保守估計500字節(jié)/行

    // 2. 獲取當(dāng)前JVM可用內(nèi)存 (轉(zhuǎn)成字節(jié))
    long freeMemoryBytes = Runtime.getRuntime().freeMemory();

    // 3. 【安全策略】僅使用一部分可用內(nèi)存 (例如 40%)
    long safeMemoryToUse = (long) (freeMemoryBytes * 0.4);

    // 4. 計算建議分頁條數(shù)
    int suggestedPageSize = (int) (safeMemoryToUse / approxBytesPerRow);

    // 5. 設(shè)置合理范圍 (防止太大或太小)
    return Math.max(1000, Math.min(suggestedPageSize, 10000)); // 限制在1000~10000條/頁
}

// 使用動態(tài)分頁
int dynamicPageSize = calculateOptimalPageSize(User.class);
ExcelExporter.exportByPage(..., dynamicPageSize, ...);

// 1. 異步導(dǎo)出接口
@GetMapping("/export/async")
public ResultVo<String> triggerAsyncExport() {
    String taskId = "EXPORT_" + System.currentTimeMillis(); // 生成唯一任務(wù)ID
    // ?? 提交異步任務(wù) (使用線程池)
    asyncTaskExecutor.execute(() -> doExportTask(taskId));
    return ResultVo.success("導(dǎo)出任務(wù)已提交，請稍后查詢進(jìn)度", taskId);
}

// 2. 實際導(dǎo)出任務(wù)
private void doExportTask(String taskId) {
    try {
        // 2.1 保存任務(wù)狀態(tài) (進(jìn)行中/0%)
        exportTaskService.save(new ExportTask(taskId, "PROCESSING", 0));

        // 2.2 執(zhí)行分頁導(dǎo)出 (使用我們的ExcelExporter)
        int total = userService.countTotalUsers();
        AtomicInteger exported = new AtomicInteger(0); // 已導(dǎo)出計數(shù)器

        ExcelExporter.exportByPage(
                ..., // response 需要特殊處理 (寫文件)
                ...,
                (pageNum, pageSize) -> {
                    List<User> page = userService.findByPage(pageNum, pageSize);
                    // ?? 更新進(jìn)度
                    int currentExported = exported.addAndGet(page.size());
                    int progress = (int) ((currentExported / (double) total) * 100);
                    exportTaskService.updateProgress(taskId, progress);
                    return page;
                }
        );

        // 2.3 任務(wù)完成 (100%)
        exportTaskService.updateStatus(taskId, "SUCCESS", 100, filePath); // 存儲文件路徑
    } catch (Exception e) {
        // 2.4 任務(wù)失敗
        exportTaskService.updateStatus(taskId, "FAILED", 0, e.getMessage());
    }
}

// 3. 進(jìn)度查詢接口
@GetMapping("/export/progress/{taskId}")
public ResultVo<ExportProgress> getExportProgress(@PathVariable String taskId) {
    ExportProgress progress = exportTaskService.getProgress(taskId);
    return ResultVo.success(progress);
}

// 4. 文件下載接口 (任務(wù)成功后)
@GetMapping("/export/download/{taskId}")
public void downloadExportFile(@PathVariable String taskId, HttpServletResponse response) {
    String filePath = exportTaskService.getFilePath(taskId);
    // ... 實現(xiàn)文件下載邏輯 ...
}

try (ExcelWriter excelWriter = EasyExcel.write(outputStream).build()) {
    List<String> sheetNames = Arrays.asList("用戶信息", "訂單記錄", "操作日志");
    for (int i = 0; i < sheetNames.size(); i++) {
        WriteSheet sheet = EasyExcel.writerSheet(i, sheetNames.get(i)).head(User.class).build(); // 根據(jù)Sheet設(shè)置不同head
        // 對該Sheet進(jìn)行分頁寫入 (復(fù)用前面的分頁邏輯)...
        PageWriteExcelHelper.writeForSheet(excelWriter, sheet, ...);
    }
} // try-with-resources自動關(guān)閉excelWriter

// 1. 提前準(zhǔn)備好帶樣式的 template.xlsx 放在資源目錄
// 2. 模板導(dǎo)出代碼
String templateFile = "/templates/complex-report-template.xlsx";
try (InputStream templateStream = getClass().getResourceAsStream(templateFile);
     ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                                    .withTemplate(templateStream)
                                    .build()) {

    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    // 填充單個數(shù)據(jù)
    excelWriter.fill(new TemplateData(...), writeSheet);
    // 填充列表數(shù)據(jù) (支持分頁填充!)
    excelWriter.fill(new FillWrapper("dataList", pageData), writeSheet); // 'dataList' 是模板里的變量名
    // ... 填充更多數(shù)據(jù) ...
}