package com.njcn.device.pq.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.njcn.device.pq.pojo.vo.dataClean.DataVerifyExcel;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.util.*;

/**
 * 修复版：按数据量动态排序Excel列，解决列挤在一行的问题
 * 关键改动：表头构建方式 + 列宽匹配逻辑
 */
public class FixedDynamicExcelExport {


    public static void main(String[] args) throws Exception {
        // 1. 模拟测试数据（相电压数据量最多，频率次之，频率偏差最少）
        List<DataVerifyExcel> dataList = EasyExcel.read("F:\\usr\\response.xlsx")
                .head(DataVerifyExcel.class)
                .doReadAllSync();
        // 2. 导出Excel（替换为你的本地路径，比如桌面）
        dataList.sort(Comparator.comparing((DataVerifyExcel item) -> item.getCity() + "_" + item.getStationName()+"_"+item.getDevName())
                .thenComparing(DataVerifyExcel::getAllTime, Comparator.reverseOrder())
        );
        exportExcelByDataSize(dataList, "D:/dynamic_excel_fixed.xlsx");
        System.out.println("导出完成！打开桌面的「测试导出.xlsx」查看效果");
    }

    /**
     * 核心方法：按数据量排序导出Excel
     */
    public static void exportExcelByDataSize(List<DataVerifyExcel> dataList, String outputPath) throws Exception {
        // 步骤1：统计每个字段的有效数据量
        Map<String, Integer> fieldCount = countValidData(dataList);
        // 步骤2：按数据量降序排序字段名
        List<String> sortedFields = sortFields(fieldCount);
        // 步骤3：构建正确的动态表头
        List<List<String>> head = buildCorrectHead(sortedFields);
        // 步骤4：构建对应顺序的行数据
        List<List<Object>> data = buildRowData(dataList, sortedFields);

        // 步骤5：导出Excel（含列宽设置）
        try (FileOutputStream out = new FileOutputStream(outputPath)) {
            EasyExcel.write(out)
                    .head(head)
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                    .registerWriteHandler(new FreezeHeaderHandler()) // 用修复后的表头
                    .sheet("数据统计")
                    .doWrite(data);
        }
    }

    public static Map<String, List> exportExcelByDataSize(List<DataVerifyExcel> dataList) {
        Map<String, List> map = new HashMap<>(2);
        Map<String, Integer> fieldCount = countValidData(dataList);
        List<String> sortedFields = sortFields(fieldCount);
        map.put("head", buildCorrectHead(sortedFields));
        map.put("data", buildRowData(dataList, sortedFields));
        return map;
    }

    public static class FreezeHeaderHandler implements SheetWriteHandler {
        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            // 表格创建前无需操作
        }

        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            Sheet sheet = writeSheetHolder.getSheet();
            // freezePane(c, r)：c=冻结列数，r=冻结行数；这里r=1表示冻结第1行（表头），c=0表示不冻结列
            // 比如 freezePane(1, 1) 表示冻结第一列+第一行
            sheet.createFreezePane(0, 1);
        }
    }

    public static HorizontalCellStyleStrategy writeCenterStyle() {
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

        WriteFont bodyWriteFont = new WriteFont();
        bodyWriteFont.setFontHeightInPoints((short) 11);
        bodyWriteFont.setBold(false);
        bodyWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(bodyWriteFont);

        // 头部
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置字体居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        WriteFont headWriteFont = new WriteFont();
        // 设置字体大小为20
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteFont.setBold(false);
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);

        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
    // ★ 修复点1：构建正确的表头（每个列名独立成List）
    private static List<List<String>> buildCorrectHead(List<String> allFields) {
        List<List<String>> head = new ArrayList<>();
        head.add(Collections.singletonList("供电公司"));
        head.add(Collections.singletonList("所属变电站"));
        head.add(Collections.singletonList("终端名称"));
        head.add(Collections.singletonList("监测点名称"));
        head.add(Collections.singletonList("IP"));
        head.add(Collections.singletonList("干扰源类型"));
        head.add(Collections.singletonList("监测对象名称"));
        head.add(Collections.singletonList("电网侧变电站"));
        head.add(Collections.singletonList("厂商"));
        head.add(Collections.singletonList("电网标志"));
        head.add(Collections.singletonList("总指标异常时间"));
        List<String> sortedFields = allFields.subList(11, allFields.size());
        for (String field : sortedFields) {
            // 每个列名单独封装成List，EasyExcel才能识别为不同列
            head.add(Collections.singletonList(DataVerifyExcel.FIELD_COLUMN_MAP.get(field)));
        }
        return head;
    }


    // 统计有效数据量（无改动，保留）
    private static Map<String, Integer> countValidData(List<DataVerifyExcel> dataList) {
        // 1. 初始化计数字典（反射自动提取所有Time结尾的Integer字段，初始值0）
        Map<String, Integer> countMap = initCountMap();
        // 2. 判空，避免空指针异常
        if (dataList == null || dataList.isEmpty()) {
            return countMap;
        }
        // 3. 获取DataStatistic类的所有字段
        Field[] fields = DataVerifyExcel.class.getDeclaredFields();
        try {
            // 4. 遍历每个数据对象
            for (DataVerifyExcel data : dataList) {
                // 5. 遍历每个字段，累加数值
                for (Field field : fields) {
                    // 过滤条件：Integer类型 + 以Time结尾
                    if (field.getType() == Integer.class && field.getName().endsWith("Time") ) {
                        if(!field.getName().equals("allTime")){
                            // 设置可访问私有字段
                            field.setAccessible(true);
                            // 获取当前对象的该字段值
                            Integer value = (Integer) field.get(data);
                            // 非空则累加
                            if (value != null) {
                                String fieldName = field.getName();
                                countMap.put(fieldName, countMap.get(fieldName) + value);
                            }
                        }
                    }
                }
            }
        } catch (IllegalAccessException e) {
            // 捕获反射访问异常，便于排查问题
            throw new RuntimeException("统计字段时反射访问失败", e);
        }
        return countMap;
    }

    // 反射初始化计数字典（复用之前的逻辑）
    private static Map<String, Integer> initCountMap() {
        Map<String, Integer> countMap = new HashMap<>();
        Field[] fields = DataVerifyExcel.class.getDeclaredFields();
        for (Field field : fields) {
            if (field.getType() == Integer.class && field.getName().endsWith("Time")) {
                if(!field.getName().equals("allTime")){
                    countMap.put(field.getName(), 0);
                }
            }
        }
        return countMap;
    }


    // 按数据量排序字段（无改动，保留）
    private static List<String> sortFields(Map<String, Integer> fieldCount) {
        List<String> fields = new ArrayList<>(fieldCount.keySet());
        fields.sort((f1, f2) -> fieldCount.get(f2) - fieldCount.get(f1)); // 降序
        List<String> fieldAlls=new ArrayList<>();
        fieldAlls.add("city");
        fieldAlls.add("stationName");
        fieldAlls.add("devName");
        fieldAlls.add("lineName");
        fieldAlls.add("ip");
        fieldAlls.add("loadType");
        fieldAlls.add("objName");
        fieldAlls.add("powerSubstationName");
        fieldAlls.add("manufacturer");
        fieldAlls.add("powerFlag");
        fieldAlls.add("allTime");
        fieldAlls.addAll(fields);
        return fieldAlls;
    }

    // 构建行数据（无改动，保留）
    private static List<List<Object>> buildRowData(List<DataVerifyExcel> dataList, List<String> sortedFields)  {
        List<List<Object>> rows = new ArrayList<>();
        for (DataVerifyExcel data : dataList) {
            List<Object> row = new ArrayList<>();
            for (String field : sortedFields) {
                try {
                    Field  f = DataVerifyExcel.class.getDeclaredField(field);
                    f.setAccessible(true);
                    row.add(f.get(data));
                } catch (Exception e) {
                    throw new RuntimeException("导出异常数据转换异常："+e);
                }
            }
            rows.add(row);
        }
        return rows;
    }
}