package com.ycl.handler;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.ycl.platform.domain.entity.CalculateRule;
import enumeration.ContractRule;
import enumeration.general.RuleDeductCategoryEnum;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class CustomCellWriteHandler implements CellWriteHandler {

    /**
     * 这个处理器其实可以做很多事情
     * 他能拿到整个 Excel 的 WorkHook
     * 也能拿到当前的 sheet
     * 也能拿到当前的 cell
     * 所以能做的自定义操作很丰富
     */
    // static class CustomCellWriteHandler {

        @Override
        public void afterCellDispose(CellWriteHandlerContext context) {
            // 当前的 cell
            Cell cell = context.getCell();
            // 这里可以对cell进行任何操作
            // 这里就要考虑 你要针对哪一列进行个性化处理  一定要记得加判断  因为每个 cell 都会执行到这里 所以要记得区分
            if (BooleanUtils.isTrue(context.getHead()) && cell.getColumnIndex() == 1) {
                // 1 表示 省那一列 要对省市区进行联动下拉处理
                ExcelAreaUtil.writeAreaInfo(context, 0);
            }
        }
    // }

    public static class ExcelAreaUtil {

        @Data
        static class AreaInfo {

            private String name;

            private List<String> areaList;


            public AreaInfo(String name, List<String> areaList) {
                this.name = name;
                this.areaList = areaList;
            }
        }


        /**
         * @param context    handler 的 上下文
         * @param startIndex 省的列号  从0开始的
         */
        public static void writeAreaInfo(CellWriteHandlerContext context, int startIndex) {
            // 获取到当前的 sheet 后续要给省市区列加下拉
            WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
            Sheet sheet = writeSheetHolder.getSheet();

            // 省市区挨着的列号
            int cIndex = startIndex + 1;
            int aIndex = cIndex + 1;

            // 准备点数据
            List<AreaInfo> provinceList = new ArrayList<>();
            for (ContractRule contractRule : ContractRule.getParent()) {
                provinceList.add(new AreaInfo(contractRule.getName(), ContractRule.getChildren(contractRule).stream().map(ContractRule::getName).toList()));
            }

            // 获取到当前的 excel 因为要创建隐藏的 sheet 也就是省市区的实际内容都来自于隐藏的 sheet
            Workbook workbook = context.getWriteSheetHolder().getParentWriteWorkbookHolder().getWorkbook();
            Sheet hideSheet = workbook.createSheet("area");
            workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);
            // 写隐藏 sheet 的省市区信息
            int rowId = 0;
            // 设置第一行，存省的信息
            Row provinceRow = hideSheet.createRow(rowId++);
            provinceRow.createCell(0).setCellValue("省列表");
            for (int i = 0; i < provinceList.size(); i++) {
                Cell provinceCell = provinceRow.createCell(i + 1);
                provinceCell.setCellValue(provinceList.get(i).getName());
            }

            for (AreaInfo pro : provinceList) {
                String pName = pro.getName();
                List<String> cList = pro.getAreaList();
                Row cRow = hideSheet.createRow(rowId++);
                cRow.createCell(0).setCellValue(pName);
                for (int j = 0; j < cList.size(); j++) {
                    String cInfo = cList.get(j);
                    Cell cCell = cRow.createCell(j + 1);
                    cCell.setCellValue(cInfo);
                }

                // 添加名称管理器
                String range = getRange(1, rowId, cList.size());
                Name name = workbook.createName();
                // key不可重复
                name.setNameName(pName);
                String formula = "area!" + range;
                name.setRefersToFormula(formula);
            }

            // 给省添加下拉
            int lastRow = 100;
            setDataValidation(sheet, 1, lastRow, startIndex, startIndex);

            // 给市 区 添加下拉
            // 设置第二列和第三列的有效性
            for (int i = 2; i < lastRow; i++) {
                setDataValidation(CellReference.convertNumToColString(startIndex), sheet, i - 1, i - 1, cIndex, cIndex);
                setDataValidation(CellReference.convertNumToColString(cIndex), sheet, i - 1, i - 1, aIndex, aIndex);
            }
        }

        public static void setDataValidation(Sheet sheetPro, int firstRow, int lastRow, int firstCol, int lastCol) {
            DataValidationHelper dvHelper = sheetPro.getDataValidationHelper();
            String formula = getFormula(1, 1, 1, 34);
            // 省规则
            DataValidationConstraint provConstraint = dvHelper.createFormulaListConstraint(formula);
            // 四个参数分别是：起始行、终止行、起始列、终止列
            CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
            // 验证
            provinceDataValidation.createErrorBox("error", "请选择正确的省份");
            provinceDataValidation.setShowErrorBox(true);
            provinceDataValidation.setSuppressDropDownArrow(true);
            sheetPro.addValidationData(provinceDataValidation);
        }

        public static void setDataValidation(String offset, Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            String formulaString = String.format("INDIRECT($%s%s)", offset, firstRow + 1);
            DataValidation dataValidation = getDataValidationByFormula(formulaString, dvHelper, firstRow, lastRow, firstCol, lastCol);
            sheet.addValidationData(dataValidation);
        }

        private static DataValidation getDataValidationByFormula(String formulaString, DataValidationHelper dvHelper,
                                                                 int firstRow, int lastRow, int firstCol, int lastCol) {
            // 加载下拉列表内容
            // 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
            // 如果A2是江苏省，那么此处就是江苏省下的市信息。
            DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);
            // 设置数据有效性加载在哪个单元格上。
            // 四个参数分别是：起始行、终止行、起始列、终止列
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            // 绑定 数据有效性对象
            XSSFDataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
            dataValidation.setEmptyCellAllowed(true);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
            // 设置输入错误提示信息
            dataValidation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
            return dataValidation;
        }

        private static String getFormula(int firstRow, int lastRow, int firstCol, int lastCol) {
            String ss = String.format("$%s$%s:$%s$%s", CellReference.convertNumToColString(firstCol), firstRow,
                    CellReference.convertNumToColString(lastCol), lastRow);
            return String.format("=%s!%s", "area", ss);
        }

        private static String getRange(int offset, int rowId, int colCount) {
            String columnLetter1 = CellReference.convertNumToColString(offset);
            String columnLetter2 = CellReference.convertNumToColString(offset + colCount - 1);
            return String.format("$%s$%s:$%s$%s", columnLetter1, rowId, columnLetter2, rowId);
        }

    }


    public static void main(String[] args) {
        EasyExcel.write("test.xlsx", CalculateRule.class)
                // 自适应列宽
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                // 下拉框
                .registerWriteHandler(new CustomSheetWriteHandler(Arrays.asList("扣指定分数", "分数乘以数量", "除以数量后乘以分数")))
                .registerWriteHandler(new CustomCellWriteHandler())
                // 标注
                .registerWriteHandler(new CommentWriteHandler())
                .sheet("合同导入模板")
                .doWrite(getExcelData());
    }

    private static List<CalculateRule> getExcelData() {
        ArrayList<CalculateRule> list = new ArrayList<>();
        CalculateRule calculateRule1 = new CalculateRule();
        calculateRule1.setId(1);
        calculateRule1.setRuleName("视频平均在线率");
        calculateRule1.setRuleCondition("≥98%");
        calculateRule1.setMax(98D);
        calculateRule1.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);
        calculateRule1.setCalcFraction(0.00);
        list.add(calculateRule1);
        CalculateRule calculateRule2 = new CalculateRule();
        calculateRule2.setRuleName("视频平均在线率");
        calculateRule2.setRuleCondition("95%≤视频平均在线率＜98%");
        calculateRule2.setMax(97D);
        calculateRule2.setMin(95D);
        calculateRule2.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);
        calculateRule2.setCalcFraction(5.00);
        list.add(calculateRule2);
        CalculateRule calculateRule3 = new CalculateRule();
        calculateRule3.setRuleName("视频平均在线率");
        calculateRule3.setRuleCondition("90%≤视频平均在线率＜95%");
        calculateRule3.setMax(94D);
        calculateRule3.setMin(90D);
        calculateRule3.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);
        calculateRule3.setCalcFraction(10.00);
        list.add(calculateRule3);
        CalculateRule calculateRule33 = new CalculateRule();
        calculateRule33.setRuleName("视频平均在线率");
        calculateRule33.setRuleCondition("＜90%");
        calculateRule33.setMin(89D);
        calculateRule33.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);
        calculateRule33.setCalcFraction(10.00);
        list.add(calculateRule33);
        CalculateRule calculateRule4 = new CalculateRule();
        calculateRule4.setId(2);
        calculateRule4.setRuleName("前端感知源治理工作");
        calculateRule4.setRuleCondition("时钟同步（超过±3秒为不合格）");
        calculateRule4.setDeductCategory(RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY);
        calculateRule4.setCalcFraction(0.1);
        list.add(calculateRule4);
        CalculateRule calculateRule7 = new CalculateRule();
        calculateRule7.setRuleName("前端感知源治理工作");
        calculateRule7.setRuleCondition("OSD标识");
        list.add(calculateRule7);
        CalculateRule calculateRule6 = new CalculateRule();
        calculateRule6.setRuleName("前端感知源治理工作");
        calculateRule6.setRuleCondition("一机一档");
        list.add(calculateRule6);
        CalculateRule calculateRule5 = new CalculateRule();
        calculateRule5.setId(3);
        calculateRule5.setRuleName("后台系统的保障");
        calculateRule5.setRuleCondition("单次故障时长若超出72小时不足144小时的，每超出12小时（不足12小时按12小时计）");
        calculateRule5.setMax(144D);
        calculateRule5.setMin(72D);
        calculateRule5.setDeductCategory(RuleDeductCategoryEnum.MULTIPLY_POINTS_AFTER_DIVIDING_QUANTITY);
        calculateRule5.setCalcFraction(2.00);
        calculateRule5.setCalcUnit(12);
        list.add(calculateRule5);
        return list;
    }

}