package com.njcn.advance.utils;

import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jetbrains.annotations.NotNull;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.Map;

public class ExcelTemplateReplacer {

    // 用于包装替换值和样式启用状态的类
    public static class ReplacementData {
        private Object value;
        private boolean enableStyle;

        public ReplacementData(Object value, boolean enableStyle) {
            this.value = value;
            this.enableStyle = enableStyle;
        }

        public Object getValue() {
            return value;
        }

        public boolean isEnableStyle() {
            return enableStyle;
        }

        // 便捷的创建方法
        public static ReplacementData create(Object value, boolean enableStyle) {
            return new ReplacementData(value, enableStyle);
        }
    }

    /**
     * 替换Excel模板中的占位符，支持设置是否启用样式
     *
     * @param templatePath 模板文件路径
     * @param outputPath   输出文件路径
     * @param data         替换数据，包含值和样式启用状态
     * @param fixedStyle   固定样式配置
     * @throws IOException IO异常
     */
    public static void replaceTemplate(String templatePath, String outputPath,
                                       Map<String, ReplacementData> data, CellStyleConfig fixedStyle)
            throws IOException {
        try (InputStream is = new FileInputStream(templatePath);
             Workbook workbook = new XSSFWorkbook(is)) {
            // 创建固定样式
            CellStyle style = createFixedStyle(workbook, fixedStyle);
            for (Sheet sheet : workbook) {
                for (Row row : sheet) {
                    if (row == null) continue;
                    for (Cell cell : row) {
                        if (cell == null) continue;

                        String cellValue = getCellValue(cell);
                        if (cellValue != null && !cellValue.isEmpty()) {
                            for (Map.Entry<String, ReplacementData> entry : data.entrySet()) {
                                String placeholder = entry.getKey();
                                ReplacementData replacement = entry.getValue();

                                if (cellValue.contains(placeholder)) {
                                    // 替换占位符内容
                                    setCellValue(cell, replacement.getValue());

                                    // 根据标志决定是否应用样式
                                    if (replacement.isEnableStyle() && style != null) {
                                        cell.setCellStyle(style);
                                    }
                                }
                            }
                        }
                    }
                }
            }

            // 写入输出文件
            try (OutputStream os = new FileOutputStream(outputPath)) {
                workbook.write(os);
            }
        }
    }

    public static void replaceDownTemplate(String templatePath,
                                           String fileName,
                                           Map<String, ReplacementData> data,
                                           CellStyleConfig fixedStyle,
                                           HttpServletResponse response)
            throws IOException {
        try (InputStream is = new ClassPathResource(templatePath).getInputStream();
             Workbook workbook = new XSSFWorkbook(is)) {
            // 创建固定样式
            CellStyle style = createFixedStyle(workbook, fixedStyle);
            for (Sheet sheet : workbook) {
                for (Row row : sheet) {
                    if (row == null) continue;
                    for (Cell cell : row) {
                        if (cell == null) continue;

                        String cellValue = getCellValue(cell);
                        if (cellValue != null && !cellValue.isEmpty()) {
                            for (Map.Entry<String, ReplacementData> entry : data.entrySet()) {
                                String placeholder = entry.getKey();
                                ReplacementData replacement = entry.getValue();

                                if (cellValue.contains(placeholder)) {
                                    // 替换占位符内容
                                    setCellValue(cell, replacement.getValue());

                                    // 根据标志决定是否应用样式
                                    if (replacement.isEnableStyle() && style != null) {
                                        cell.setCellStyle(style);
                                    }
                                }
                            }
                        }
                    }
                }
            }

            // 写入输出文件
            try {
                ServletOutputStream outputStream = response.getOutputStream();
                response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
                response.setContentType("application/octet-stream;charset=UTF-8");
                workbook.write(outputStream);
                outputStream.close();
            } catch (Exception e) {
                System.out.println("评估结果异常" + e);
            } finally {
                if (workbook != null) {
                    workbook.close();
                }
            }
        }
    }

    /**
     * 创建固定样式
     */
    private static CellStyle createFixedStyle(Workbook workbook, CellStyleConfig config) {
        if (config == null) return null;

        CellStyle style = workbook.createCellStyle();

        // 设置背景颜色
        if (config.getBackgroundColor() != null) {
            if (config.getBackgroundColor() instanceof XSSFColor) {
                ((XSSFCellStyle) style).setFillForegroundColor((XSSFColor) config.getBackgroundColor());
            } else if (config.getBackgroundColor() instanceof Short) {
                style.setFillForegroundColor((Short) config.getBackgroundColor());
            }
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        // 可以添加更多固定样式设置，如边框、对齐方式等
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        // 设置字体颜色
        if (config.getFontColor() != null) {
             Font font = workbook.getFontAt((short)0);
            if (config.getFontColor() instanceof XSSFColor) {
                ((XSSFFont) font).setColor((XSSFColor) config.getFontColor());
            } else if (config.getFontColor() instanceof Short) {
                font.setColor((Short) config.getFontColor());
            }
            font.setFontHeightInPoints((short)10);
            font.setBold(true);
            font.setItalic(true);
            font.setUnderline(Font.U_SINGLE_ACCOUNTING);
            style.setFont(font);
            style.setAlignment(HorizontalAlignment.RIGHT);
        }else{
            style.setAlignment(HorizontalAlignment.CENTER);
        }
        return style;
    }

    /**
     * 获取单元格的值
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                }
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    /**
     * 设置单元格的值
     */
    private static void setCellValue(Cell cell, Object value) {
        // 自动判断类型
        if (value instanceof BigDecimal) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(((BigDecimal) value).doubleValue());
        } else if (value instanceof Number) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellType(CellType.BOOLEAN);
            cell.setCellValue((Boolean) value);
        } else {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(value.toString());
        }
    }

    // 单元格样式配置类
    @Data
    public static class CellStyleConfig {
        private Object backgroundColor;  // 支持Short(索引色)或XSSFColor(自定义色)
        private Object fontColor;        // 支持Short(索引色)或XSSFColor(自定义色)
        private short fontSize;        // 支持Short(索引色)或XSSFColor(自定义色)

        public CellStyleConfig(Object backgroundColor, Object fontColor, short fontSize) {
            this.backgroundColor = backgroundColor;
            this.fontColor = fontColor;
            this.fontSize = fontSize;
        }

    }

    public static void main(String[] args) {
        try {
            // 准备替换数据，第三个参数为样式启用标志：true启用，false不启用
            Map<String, ReplacementData> data = new HashMap<>();
            data.put("${name}", ReplacementData.create("2024-10-25", true));  // 不启用样式
            data.put("${noti2}", ReplacementData.create(30.35, true));   // 启用样式
            data.put("${age}", ReplacementData.create(30, true));       // 启用样式

            // 执行替换
            replaceTemplate("C:\\Users\\web2023\\Desktop\\xf.xlsx", "C:\\Users\\web2023\\Desktop\\cc.xlsx", data, getCellStyleConfig());
            System.out.println("Excel模板替换完成！");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 定义固定样式：浅蓝色背景，黑色字体
     *
     * @return
     */
    public static CellStyleConfig getCellStyleConfig() {
        XSSFColor lightBlue = new XSSFColor(new java.awt.Color(166, 165, 165), null);
        CellStyleConfig fixedStyle = new CellStyleConfig(
                lightBlue,
                IndexedColors.BLACK.getIndex(),
                (short)10
        );
        return fixedStyle;
    }
}
