import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.function.Function;

/**
 * 基于構(gòu)建器模式的Excel導(dǎo)出工具類
 * 提供流暢API接口，支持Web導(dǎo)出和文件導(dǎo)出兩種方式
 */
public class ExcelExporter {
    
    // 參數(shù)封裝內(nèi)部類 - 隱藏實現(xiàn)細(xì)節(jié)
    private static class ExportContext<T> {
        Object target;                   // 導(dǎo)出目標(biāo)（文件或輸出流）
        String sheetName = "Sheet1";     // 默認(rèn)工作表名稱
        String mainTitle = "數(shù)據(jù)報表";    // 默認(rèn)主標(biāo)題
        List<String> headers;            // 列標(biāo)題
        List<T> dataList;                // 數(shù)據(jù)列表
        Function<T, List<Object>> dataMapper; // 數(shù)據(jù)映射函數(shù)
        HorizontalCellStyleStrategy styleStrategy; // 樣式策略
        boolean autoDateSuffix = true;   // 是否自動添加日期后綴
        int batchSize = 5000;            // 分批寫入批次大小
    }
    
    /**
     * 創(chuàng)建構(gòu)建器實例 - 泛型方法確保類型安全
     * @param clazz 數(shù)據(jù)類型（用于類型推斷）
     * @return 構(gòu)建器對象
     */
    public static <T> Builder<T> builder(Class<T> clazz) {
        return new Builder<>();
    }
    
    /**
     * 構(gòu)建器類 - 實現(xiàn)流暢API
     */
    public static class Builder<T> {
        private final ExportContext<T> context = new ExportContext<>();
        
        /**
         * 設(shè)置導(dǎo)出目標(biāo)為Web響應(yīng)
         * @param response HttpServletResponse對象
         * @param baseFileName 基礎(chǔ)文件名（不含擴(kuò)展名）
         * @return 當(dāng)前構(gòu)建器
         */
        public Builder<T> toWeb(HttpServletResponse response, String baseFileName) {
            try {
                String fileName = buildFileName(baseFileName);
                setResponseHeaders(response, fileName);
                context.target = response.getOutputStream();
            } catch (IOException e) {
                throw new RuntimeException("創(chuàng)建輸出流失敗", e);
            }
            return this;
        }
        
        /**
         * 設(shè)置導(dǎo)出目標(biāo)為文件系統(tǒng)
         * @param filePath 完整文件路徑（含文件名）
         * @return 當(dāng)前構(gòu)建器
         */
        public Builder<T> toFile(String filePath) {
            return toFile(null, filePath);
        }
        
        /**
         * 設(shè)置導(dǎo)出目標(biāo)為文件系統(tǒng)（指定目錄和基礎(chǔ)文件名）
         * @param directory 目錄路徑
         * @param baseFileName 基礎(chǔ)文件名
         * @return 當(dāng)前構(gòu)建器
         */
        public Builder<T> toFile(String directory, String baseFileName) {
            String fileName = buildFileName(baseFileName);
            String fullPath = (directory != null) ? 
                    directory + File.separator + fileName + ".xlsx" : 
                    fileName + ".xlsx";
            
            ensureDirectoryExists(fullPath);
            context.target = new File(fullPath);
            return this;
        }
        
        // 確保目錄存在
        private void ensureDirectoryExists(String fullPath) {
            File file = new File(fullPath);
            File parentDir = file.getParentFile();
            if (parentDir != null && !parentDir.exists()) {
                parentDir.mkdirs();
            }
        }
        
        // 其他配置方法（sheetName, mainTitle, headers等）...
        // 完整代碼參考前文實現(xiàn)
        
        /**
         * 執(zhí)行導(dǎo)出操作 - 核心入口
         */
        public void execute() {
            validateContext();
            applyDefaultStyleIfNeeded();
            doExport(context);
        }
        
        // 參數(shù)校驗確保健壯性
        private void validateContext() {
            if (context.target == null) {
                throw new IllegalStateException("導(dǎo)出目標(biāo)未設(shè)置");
            }
            if (context.headers == null || context.headers.isEmpty()) {
                throw new IllegalStateException("列標(biāo)題未設(shè)置");
            }
            if (context.dataMapper == null) {
                throw new IllegalStateException("數(shù)據(jù)映射函數(shù)未設(shè)置");
            }
        }
        
        // 應(yīng)用默認(rèn)樣式
        private void applyDefaultStyleIfNeeded() {
            if (context.styleStrategy == null) {
                context.styleStrategy = createDefaultStyleStrategy();
            }
        }
    }
    
    // 獲取當(dāng)前年月字符串（yyyyMM格式）
    private static String getCurrentYearMonth() {
        return YearMonth.now().format(DateTimeFormatter.ofPattern("yyyyMM"));
    }
    
    // 設(shè)置Web響應(yīng)頭
    private static void setResponseHeaders(HttpServletResponse response, 
                                          String fileName) throws IOException {
        String encodedFileName = URLEncoder.encode(fileName, "UTF-8").replace("+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-disposition", 
                "attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
    }
    
    // 創(chuàng)建默認(rèn)樣式策略 - 專業(yè)美觀的默認(rèn)樣式
    private static HorizontalCellStyleStrategy createDefaultStyleStrategy() {
        // 主標(biāo)題樣式 - 天藍(lán)色背景，16號加粗字體，居中顯示
        WriteCellStyle titleStyle = new WriteCellStyle();
        titleStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        titleStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        WriteFont titleFont = new WriteFont();
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        titleStyle.setWriteFont(titleFont);

        // 表頭樣式 - 淺黃色背景，12號加粗字體，居中顯示
        WriteCellStyle headerStyle = new WriteCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        headerStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        WriteFont headerFont = new WriteFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBold(true);
        headerStyle.setWriteFont(headerFont);

        // 內(nèi)容樣式 - 左對齊，自動換行
        WriteCellStyle contentStyle = new WriteCellStyle();
        contentStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        contentStyle.setWrapped(true); // 自動換行

        return new HorizontalCellStyleStrategy(titleStyle, headerStyle, contentStyle);
    }
    
    // 執(zhí)行導(dǎo)出核心邏輯
    private static <T> void doExport(ExportContext<T> context) {
        List<List<String>> headList = prepareHeaders(context.mainTitle, context.headers);
        List<List<Object>> data = prepareData(context.dataList, context.dataMapper);

        try (ExcelWriter excelWriter = createExcelWriter(context)) {
            WriteSheet writeSheet = EasyExcel.writerSheet(context.sheetName).build();
            writeDataInBatches(excelWriter, writeSheet, data, context.batchSize);
        }
    }
    
    // 創(chuàng)建ExcelWriter實例
    private static <T> ExcelWriter createExcelWriter(ExportContext<T> context) {
        return EasyExcel.write(context.target)
                .registerWriteHandler(context.styleStrategy)
                .head(prepareHeaders(context.mainTitle, context.headers))
                .build();
    }
    
    // 分批寫入數(shù)據(jù) - 優(yōu)化內(nèi)存使用
    private static void writeDataInBatches(ExcelWriter excelWriter, 
                                          WriteSheet writeSheet,
                                          List<List<Object>> data,
                                          int batchSize) {
        int total = data.size();
        int pages = (int) Math.ceil((double) total / batchSize);

        for (int i = 0; i < pages; i++) {
            int fromIndex = i * batchSize;
            int toIndex = Math.min((i + 1) * batchSize, total);
            excelWriter.write(data.subList(fromIndex, toIndex), writeSheet);
        }
    }
    
    // 準(zhǔn)備表頭數(shù)據(jù)
    private static List<List<String>> prepareHeaders(String mainTitle, List<String> headers) {
        List<List<String>> headList = new ArrayList<>();
        headList.add(Collections.singletonList(mainTitle)); // 主標(biāo)題（跨所有列）
        headList.add(new ArrayList<>(headers)); // 列標(biāo)題
        return headList;
    }
    
    // 準(zhǔn)備表格數(shù)據(jù)
    private static <T> List<List<Object>> prepareData(
            List<T> dataList,
            Function<T, List<Object>> dataMapper) {
        
        if (dataList == null || dataList.isEmpty()) {
            return Collections.emptyList();
        }

        List<List<Object>> result = new ArrayList<>(dataList.size());
        for (T item : dataList) {
            result.add(dataMapper.apply(item));
        }
        return result;
    }
}

// 清晰的導(dǎo)出流程：目標(biāo)→配置→數(shù)據(jù)→執(zhí)行
ExcelExporter.builder(User.class)
    .toWeb(response, "用戶報表")     // 設(shè)置導(dǎo)出目標(biāo)
    .sheetName("用戶數(shù)據(jù)")          // 配置工作表名
    .mainTitle("2024年用戶分析報告") // 設(shè)置主標(biāo)題
    .headers("ID", "姓名", "郵箱")  // 設(shè)置列標(biāo)題
    .data(users, user -> Arrays.asList( // 提供數(shù)據(jù)和映射
        user.getId(),
        user.getName(),
        user.getEmail()
    ))
    .batchSize(2000)              // 優(yōu)化大數(shù)據(jù)量處理
    .execute();                   // 執(zhí)行導(dǎo)出

// Web導(dǎo)出 - 直接輸出到HttpServletResponse
.toWeb(response, "用戶報表")

// 文件導(dǎo)出到指定目錄
.toFile("/reports", "月度銷售")

// 文件導(dǎo)出到當(dāng)前目錄
.toFile("臨時報告")

private void validateContext() {
    // 必須參數(shù)校驗
    if (context.target == null) throw ...;
    if (context.headers == null) throw ...;
    if (context.dataMapper == null) throw ...;
    
    // 數(shù)據(jù)空值保護(hù)
    if (dataList == null) return Collections.emptyList();
}

@GetMapping("/export/users")
public void exportUsers(HttpServletResponse response) {
    List<User> users = userService.findActiveUsers();
    
    ExcelExporter.builder(User.class)
        .toWeb(response, "活躍用戶")
        .headers("ID", "用戶名", "注冊郵箱", "最后登錄時間")
        .data(users, user -> Arrays.asList(
            user.getId(),
            user.getUsername(),
            user.getEmail(),
            formatDateTime(user.getLastLogin())
        ))
        .execute();
}

// 日期格式化輔助方法
private String formatDateTime(LocalDateTime dateTime) {
    return dateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));
}

public void generateDailySalesReport() {
    List<SalesRecord> records = salesService.getYesterdayRecords();
    
    ExcelExporter.builder(SalesRecord.class)
        .toFile("/reports/sales", "日銷售報告")
        .withoutDateSuffix() // 禁用日期后綴
        .sheetName("銷售明細(xì)")
        .mainTitle("昨日銷售匯總")
        .headers("銷售員", "產(chǎn)品", "數(shù)量", "單價", "總金額")
        .data(records, record -> Arrays.asList(
            record.getSalesperson(),
            record.getProductName(),
            record.getQuantity(),
            record.getUnitPrice(),
            record.getTotalAmount()
        ))
        .batchSize(10000) // 大數(shù)據(jù)量優(yōu)化
        .execute();
}

public void exportFinancialReport(HttpServletResponse response) {
    // 創(chuàng)建專業(yè)財務(wù)樣式
    WriteCellStyle moneyStyle = createMoneyStyle();
    WriteCellStyle headerStyle = createFinanceHeaderStyle();
    
    List<FinancialData> data = financeService.getQuarterlyReport();
    
    ExcelExporter.builder(FinancialData.class)
        .toWeb(response, "Q1財務(wù)報表")
        .headers("科目", "1月", "2月", "3月", "季度合計")
        .data(data, item -> Arrays.asList(
            item.getAccount(),
            item.getJanuary(),
            item.getFebruary(),
            item.getMarch(),
            item.getQuarterTotal()
        ))
        .customStyle(
            createFinanceTitleStyle(),
            headerStyle,
            moneyStyle
        )
        .execute();
}

// 創(chuàng)建貨幣樣式
private WriteCellStyle createMoneyStyle() {
    WriteCellStyle style = new WriteCellStyle();
    style.setDataFormat((short) 4); // 貨幣格式
    style.setHorizontalAlignment(HorizontalAlignment.RIGHT);
    WriteFont font = new WriteFont();
    font.setColor(IndexedColors.DARK_GREEN.getIndex());
    style.setWriteFont(font);
    return style;
}

private static void writeDataInBatches(ExcelWriter excelWriter, 
                                      WriteSheet writeSheet,
                                      List<List<Object>> data,
                                      int batchSize) {
    int total = data.size();
    int pages = (int) Math.ceil((double) total / batchSize);

    for (int i = 0; i < pages; i++) {
        int fromIndex = i * batchSize;
        int toIndex = Math.min((i + 1) * batchSize, total);
        excelWriter.write(data.subList(fromIndex, toIndex), writeSheet);
    }
}

// 樣式對象池
public class StylePool {
    private static final Map<String, WriteCellStyle> pool = new ConcurrentHashMap<>();
    
    public static WriteCellStyle getStyle(String key, Supplier<WriteCellStyle> creator) {
        return pool.computeIfAbsent(key, k -> creator.get());
    }
}

// 使用樣式池
WriteCellStyle headerStyle = StylePool.getStyle("financeHeader", () -> {
    WriteCellStyle style = new WriteCellStyle();
    // ... 樣式配置
    return style;
});

@GetMapping("/export/large")
public ResponseEntity<Void> exportLargeData() {
    CompletableFuture.runAsync(() -> {
        ExcelExporter.builder(Data.class)
            .toFile("/reports", "bigdata")
            .data(largeData, ...)
            .execute();
    }, taskExecutor);
    
    return ResponseEntity.accepted()
            .header("Location", "/export/status/123")
            .build();
}

private List<List<String>> prepareMultiLevelHeaders() {
    List<List<String>> headList = new ArrayList<>();
    
    // 第一級：主標(biāo)題（跨所有列）
    headList.add(Collections.singletonList("2024年度銷售分析報告"));
    
    // 第二級：分類標(biāo)題
    headList.add(Arrays.asList("基本信息", "", "業(yè)績指標(biāo)"));
    
    // 第三級：詳細(xì)列名
    headList.add(Arrays.asList("區(qū)域", "銷售員", "銷售額", "同比增長", "目標(biāo)達(dá)成率"));
    
    return headList;
}

// 在customStyle后添加列寬策略
builder.customStyle(...)
       .registerColumnWidthHandler((sheet, cell, head, relativeRowIndex, isHead) -> {
           if (cell.getColumnIndex() == 2) {
               sheet.setColumnWidth(cell.getColumnIndex(), 20 * 256); // 20字符寬
           }
       });