package com.hdkj.lt.bf.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.hdkj.lt.base.constants.CommonConstant;
import com.hdkj.lt.base.exception.BusinessException;
import com.hdkj.lt.bf.constants.SwitchSettingConstants;
import com.hdkj.lt.bf.constants.TransferSchemeConstants;
import com.hdkj.lt.bf.entity.FhzgPlanPowerCut;
import com.hdkj.lt.bf.entity.dto.SwitchInfoListRequest;
import com.hdkj.lt.bf.entity.vo.SwitchInfoVO;
import com.hdkj.lt.bf.service.*;
import com.hdkj.lt.bf.service.voltageLimitViolation.FhzgLimitProcessStateCheckService;
import com.hdkj.lt.bf.service.voltageLimitViolation.FhzgLimitTransferSchemeSwitchingFinalSegCurrentDetailService;
import com.hdkj.lt.core.bizms.modle.dto.FhzgSwitchProtectionSettingsBaseReadExcelConfig;
import com.hdkj.lt.core.bizms.modle.po.FhzgSwitchProtectionSettings;
import com.hdkj.lt.core.bizms.modle.po.fault.FaultPowerCut;
import com.hdkj.lt.core.bizms.modle.po.voltageLimitViolation.FhzgVoltageLimitViolationPowerCut;
import com.hdkj.lt.utils.poi.ExcelReadUtils;
import com.hdkj.lt.utils.poi.POIExcelUtils;
import com.ruoyi.common.security.utils.SecurityUtils;
import com.ruoyi.system.api.model.LoginUser;
import io.swagger.annotations.ApiOperation;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.codec.Charsets;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.HttpHeaders;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author luorui
 * @date 2025/8/23 9:18
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class ExportDataServiceImpl implements ExportDataService {

    private final FhzgPlanTransferSchemeFinalSegmentCurrentDetailService fhzgPlanTransferSchemeFinalSegmentCurrentDetailService;

    private final FhzgFaultTransferSchemeFinalSegmentCurrentDetailService fhzgFaultTransferSchemeFinalSegmentCurrentDetailService;

    private final FhzgLimitTransferSchemeSwitchingFinalSegCurrentDetailService  fhzgLimitTransferSchemeSwitchingFinalSegCurrentDetailService;


    private final FhzgPlanProcessStateCheckService fhzgPlanProcessStateCheckService;

    private final FhzgLimitProcessStateCheckService fhzgLimitProcessStateCheckService;

    private final DwdShbDsSwitchBaseService  dwdShbDsSwitchBaseService;

    private final static String NULL_STR = "-";
    private final static String EXCEL_NAME = "开关信息.xls";
    private final static String EXCEL_TEMP_NAME = "开关信息维护模版.xls";

    @Value("${hussar.file.max-file-size:10}")
    private int maxFileSize;
    /**
     * 导出导线段电流校验数据
     * 导出类型 :
     * "plan_loop";//计划合环校验导线段
     * "plan_final";//计划终态校验导线段
     * "fault_final";//故障终态校验导线段
     *
     * @author luorui
     * @date 2025/8/22 10:18
     **/
    @GetMapping("/export-segment-current")
    @ApiOperation(value = "导出导线段电流校验数据")
    public void exportSegmentCurrentList(HttpServletResponse response, @RequestParam("switchingId") Long switchingId, @RequestParam("exportType") String exportType) {
        if (Objects.isNull(switchingId)) {
            throw new BusinessException("导出数据请求参数switchingId为空");
        }
        if (StrUtil.isBlank(exportType)) {
            throw new BusinessException("导出类型为空");
        }
        switch (exportType) {
            case TransferSchemeConstants.SegmentCurrentExportType.PLAN_LOOP:
                exportPlanLoop(response, switchingId);
                break;
            case TransferSchemeConstants.SegmentCurrentExportType.PLAN_FINAL:
                exportPlanFinal(response, switchingId);
                break;
            case TransferSchemeConstants.SegmentCurrentExportType.FAULT_FINAL:
                exportFaultFinal(response, switchingId);
                break;
            case TransferSchemeConstants.SegmentCurrentExportType.LIMIT_FINAL:
                exportLimitFinal(response, switchingId);
                break;
            case TransferSchemeConstants.SegmentCurrentExportType.LIMIT_LOOP:
                exportLimitLoop(response, switchingId);
                break;
            default:
                throw new BusinessException("导出类型不正确");
        }
    }

    private void exportLimitLoop(HttpServletResponse response, Long switchingId) {
        FhzgVoltageLimitViolationPowerCut voltageLimitViolationPowerCut = fhzgLimitTransferSchemeSwitchingFinalSegCurrentDetailService.getLimitInfoBySwitchingId(switchingId);
        String fileName = "合环路径导线段电流校验" + "结果";
        //文件名称 ：线路名称+事件编码+tab页标签名称+“结果”
        String firstRow = "线路名称：%s" + "\n" + "计划编号：" + "%s";
        if (voltageLimitViolationPowerCut != null) {
            firstRow = String.format(firstRow, voltageLimitViolationPowerCut.getLineName(), voltageLimitViolationPowerCut.getId());
            fileName = voltageLimitViolationPowerCut.getLineName() + voltageLimitViolationPowerCut.getId() + fileName;
        }
        List<Map<String, Object>> dataList = fhzgLimitProcessStateCheckService.getLoopSegCurrentListBySwitchingId(switchingId);
        List<String> headers = Arrays.asList("序号", "导线段名称", "导线段id", "所属线路名称", "导线标定载流量（A）", "合环前电流（A）", "合环中电流（A）", "解环后电流（A）", "校验结果", "裕度值"); // 文档标题内容
        List<String> columns = Arrays.asList("rowNum", "segmentName", "segmentId", "lineName", "segmentRatedAmpacity", "preLoopCloseCurrent", "loopCloseCurrent", "loopOpenCurrent", "segmentCurrentCheck", "marginValue");//文档标题栏对应取值字段
        Map<String, Integer> decimals = new HashMap() {{
            put("rowNum", 0);
            put("preLoopCloseCurrent", 6);
        }}; // 小数位 key字段，value保留小数位，如果保留原始位数，value设置为-1
        POIExcelUtils.POIExcelSetting settings = buildPOIExcelSetting(headers, columns, firstRow, decimals);
        POIExcelUtils.export(response, fileName, dataList, settings);
    }

    private void exportLimitFinal(HttpServletResponse response, Long switchingId) {
        FhzgVoltageLimitViolationPowerCut voltageLimitViolationPowerCut = fhzgLimitTransferSchemeSwitchingFinalSegCurrentDetailService.getLimitInfoBySwitchingId(switchingId);
        String fileName = "转供路径导线段电流校验" + "结果";
        //文件名称 ：线路名称+事件编码+tab页标签名称+“结果”
        String firstRow = "线路名称：%s" + "\n" + "计划编号：" + "%s";
        if (voltageLimitViolationPowerCut != null) {
            firstRow = String.format(firstRow, voltageLimitViolationPowerCut.getLineName(), voltageLimitViolationPowerCut.getId());
            fileName = voltageLimitViolationPowerCut.getLineName() + voltageLimitViolationPowerCut.getId() + fileName;
        }
        List<Map<String, Object>> dataList = fhzgLimitTransferSchemeSwitchingFinalSegCurrentDetailService.getListBySwitchingId(switchingId);
        List<String> headers = Arrays.asList("序号", "导线段名称", "导线段id", "所属线路名称", "导线标定载流量（A）", "转供后24h最大电流（A）", "校验结果", "裕度值"); // 文档标题内容
        List<String> columns = Arrays.asList("rowNum", "segmentName", "segmentId", "lineName", "ratedI", "maxAfter24hI", "segmentCurrentCheck","marginValue");//文档标题栏对应取值字段
        Map<String, Integer> decimals = new HashMap() {{
            put("rowNum", 0);
        }}; // 小数位 key字段，value保留小数位，如果保留原始位数，value设置为-1
        POIExcelUtils.POIExcelSetting settings = buildPOIExcelSetting(headers, columns, firstRow, decimals);
        POIExcelUtils.export(response, fileName, dataList, settings);
    }

    /**
     * @author luorui
     * @date 2025/8/22 17:46
     * @discription 合环校验 合环路径导线段电流校验
     **/
    private void exportPlanLoop(HttpServletResponse response, Long switchingId) {
        FhzgPlanPowerCut fhzgPlanPowerCut = fhzgPlanTransferSchemeFinalSegmentCurrentDetailService.getPlanInfoBySwitchingId(switchingId);
        String fileName = "合环路径导线段电流校验" + "结果";
        //文件名称 ：线路名称+事件编码+tab页标签名称+“结果”
        String firstRow = "线路名称：%s" + "\n" + "计划编号：" + "%s";
        if (fhzgPlanPowerCut != null) {
            firstRow = String.format(firstRow, fhzgPlanPowerCut.getLineName(), fhzgPlanPowerCut.getPlanNo());
            fileName = fhzgPlanPowerCut.getLineName() + fhzgPlanPowerCut.getPlanNo() + fileName;
        }
        List<Map<String, Object>> dataList = fhzgPlanProcessStateCheckService.getLoopSegCurrentListBySwitchingId(switchingId);
        List<String> headers = Arrays.asList("序号", "导线段名称", "导线段id", "所属线路名称", "导线标定载流量（A）", "合环前电流（A）", "合环中电流（A）", "解环后电流（A）", "校验结果", "裕度值"); // 文档标题内容
        List<String> columns = Arrays.asList("rowNum", "segmentName", "segmentId", "lineName", "segmentRatedAmpacity", "preLoopCloseCurrent", "loopCloseCurrent", "loopOpenCurrent", "segmentCurrentCheck", "marginValue");//文档标题栏对应取值字段
        Map<String, Integer> decimals = new HashMap() {{
            put("rowNum", 0);
            put("preLoopCloseCurrent", 6);
        }}; // 小数位 key字段，value保留小数位，如果保留原始位数，value设置为-1
        POIExcelUtils.POIExcelSetting settings = buildPOIExcelSetting(headers, columns, firstRow, decimals);
        POIExcelUtils.export(response, fileName, dataList, settings);
    }

    /**
     * @author luorui
     * @date 2025/8/22 17:46
     * @discription 导出计划终态校验导线段
     **/
    private void exportPlanFinal(HttpServletResponse response, Long switchingId) {
        FhzgPlanPowerCut fhzgPlanPowerCut = fhzgPlanTransferSchemeFinalSegmentCurrentDetailService.getPlanInfoBySwitchingId(switchingId);
        String fileName = "转供路径导线段电流校验" + "结果";
        //文件名称 ：线路名称+事件编码+tab页标签名称+“结果”
        String firstRow = "线路名称：%s" + "\n" + "计划编号：" + "%s";
        if (fhzgPlanPowerCut != null) {
            firstRow = String.format(firstRow, fhzgPlanPowerCut.getLineName(), fhzgPlanPowerCut.getPlanNo());
            fileName = fhzgPlanPowerCut.getLineName() + fhzgPlanPowerCut.getPlanNo() + fileName;
        }
        List<Map<String, Object>> dataList = fhzgPlanTransferSchemeFinalSegmentCurrentDetailService.getListBySwitchingId(switchingId);
        List<String> headers = Arrays.asList("序号", "导线段名称", "导线段id", "所属线路名称", "导线标定载流量（A）", "转供后24h最大电流（A）", "校验结果", "裕度值"); // 文档标题内容
        List<String> columns = Arrays.asList("rowNum", "segmentName", "segmentId", "lineName", "ratedI", "maxAfter24hI", "segmentCurrentCheck","marginValue");//文档标题栏对应取值字段
        Map<String, Integer> decimals = new HashMap() {{
            put("rowNum", 0);
        }}; // 小数位 key字段，value保留小数位，如果保留原始位数，value设置为-1
        POIExcelUtils.POIExcelSetting settings = buildPOIExcelSetting(headers, columns, firstRow, decimals);
        POIExcelUtils.export(response, fileName, dataList, settings);
    }

    /**
     * @author luorui
     * @date 2025/8/22 17:46
     * @discription 导出故障终态校验导线段
     **/
    private void exportFaultFinal(HttpServletResponse response, Long switchingId) {
        FaultPowerCut faultPowerCut = fhzgFaultTransferSchemeFinalSegmentCurrentDetailService.getFaultInfoBySwitchingId(switchingId);
        String fileName = "转供路径导线段电流校验" + "结果";
        //文件名称 ：线路名称+事件编码+tab页标签名称+“结果”
        String firstRow = "线路名称：%s" + "\n" + "故障编号：" + "%s";
        if (faultPowerCut != null) {
            firstRow = String.format(firstRow, faultPowerCut.getLineName(), StrUtil.isBlank(faultPowerCut.getFaultNo()) ? "-" : faultPowerCut.getFaultNo());
            fileName = faultPowerCut.getLineName() + faultPowerCut.getFaultNo() + fileName;
        }
        List<Map<String, Object>> dataList = fhzgFaultTransferSchemeFinalSegmentCurrentDetailService.getListBySwitchingId(switchingId);
        List<String> headers = Arrays.asList("序号", "导线段名称", "导线段id", "所属线路名称", "导线标定载流量（A）", "转供后24h最大电流（A）", "校验结果"); // 文档标题内容
        List<String> columns = Arrays.asList("rowNum", "segmentName", "segmentId", "lineName", "ratedI", "maxAfter24hI", "segmentCurrentCheck");//文档标题栏对应取值字段
        Map<String, Integer> decimals = new HashMap() {{
            put("rowNum", 0);
        }}; // 小数位 key字段，value保留小数位，如果保留原始位数，value设置为-1
        POIExcelUtils.POIExcelSetting settings = buildPOIExcelSetting(headers, columns, firstRow, decimals);
        POIExcelUtils.export(response, fileName, dataList, settings);
    }

    private POIExcelUtils.POIExcelSetting buildPOIExcelSetting(List<String> headers, List<String> columns, String firstRow, Map<String, Integer> decimals) {
        POIExcelUtils.POIExcelSetting settings = POIExcelUtils.POIExcelSetting.builder()
                .sheetName("导线段")
                .noneSetStyle(true)
                .headers(headers)
                .titleName(firstRow)
                .titleStyle("left")
                .titleHeight(40f)
                .columns(columns)
                .percents(new ArrayList<>())
                .decimals(decimals)
                .build();
        return settings;
    }



    /**
     *
     * @author luorui
     * @date 2025/9/15 9:17
     * @discription 导出开关保护定值配置
     *
     **/
    @Override
    public void exportSwitchSetting(HttpServletResponse response, SwitchInfoListRequest request) {
        //设置返回excel
        ExcelWriter writer = buildWriter();
        //根据条件查询数据
        List<SwitchInfoVO> switchInfoVOList = dwdShbDsSwitchBaseService.getSwitchInfoByCondition(request);
        //转换实体数据为list
        if ( CollUtil.isNotEmpty(switchInfoVOList)) {
            List<List<Object>> dataList = caseValue(switchInfoVOList);
            writer.write(dataList);
        }
        executeExport(response, writer, EXCEL_NAME);
    }

    /**
     *
     * @author luorui
     * @date 2025/9/15 13:42
     * @discription 导出模板
     *
     **/
    @Override
    public void exportSwitchSettingTemp(HttpServletResponse response) {
        ExcelWriter writer = buildWriter();
        executeExport(response, writer, EXCEL_TEMP_NAME);
    }


    /**
     * @author luorui
     * @date 2025/9/15 13:42
     * @discription 导入数据
     **/

    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = {Exception.class})
    public int importSwitchSetting(MultipartFile file) {
        if (file.isEmpty()) {
            throw new BusinessException("上传文件不能为空");
        }
        String fileName = file.getOriginalFilename();
        //文件校验
        if (StrUtil.isBlank(fileName) || (!StrUtil.endWithIgnoreCase(fileName, CommonConstant.EXCEL_SUFFIX_XLS) && !StrUtil.endWithIgnoreCase(fileName, CommonConstant.EXCEL_SUFFIX_XLSX))) {
            throw new BusinessException("文件后缀名不正确");
        }
        if (file.getSize() > maxFileSize * 1024 * 1024) {
            throw new BusinessException(String.format("文件大小不能超过%d MB", maxFileSize));
        }
        try {
            int result = 0;
            InputStream inputStream = file.getInputStream();
            JSONObject jsonObject = ExcelReadUtils.readExcel(inputStream, fileName, FhzgSwitchProtectionSettingsBaseReadExcelConfig.class);
            JSONArray array = (JSONArray) jsonObject.get(FhzgSwitchProtectionSettingsBaseReadExcelConfig.SHEET_NAME);
            List<FhzgSwitchProtectionSettingsBaseReadExcelConfig> list = array.toJavaList(FhzgSwitchProtectionSettingsBaseReadExcelConfig.class);
            //校验数据
            for (FhzgSwitchProtectionSettingsBaseReadExcelConfig fhzgSwitchProtectionSettingsBaseReadExcelConfig : list) {
                if (StrUtil.isBlank(fhzgSwitchProtectionSettingsBaseReadExcelConfig.getSwitchId())) {
                    throw new BusinessException("必填项未填，请补充完整");
                }
            }
            //更新数据 只更新保护定值对应的字段
            if (CollUtil.isNotEmpty(list)) {
                List<FhzgSwitchProtectionSettings> fhzgSwitchProtectionSettingsList = caseUpdateProperty(list);
                result = dwdShbDsSwitchBaseService.saveOrUpdateBatchByPrsId(fhzgSwitchProtectionSettingsList);
            } else {
                log.info("导入开关配置数据为空");
            }
            return result;
        } catch (BusinessException e) {
            throw new BusinessException(e.getMessage());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    //只取需要更新的字段，并且转换枚举值
    private List<FhzgSwitchProtectionSettings> caseUpdateProperty(List<FhzgSwitchProtectionSettingsBaseReadExcelConfig> list) {
        List<FhzgSwitchProtectionSettings> fhzgSwitchProtectionSettingsList = new ArrayList<>();
        LoginUser loginUser = SecurityUtils.getLoginUser();
        Long userId = (null != loginUser) ? loginUser.getUserid() : 0L;
        list.forEach(e -> {
            FhzgSwitchProtectionSettings fhzgSwitchProtectionSettings = new FhzgSwitchProtectionSettings();
            fhzgSwitchProtectionSettings.setDevicePrsId(e.getSwitchId());
            fhzgSwitchProtectionSettings.setOc1Value(caseNullValueBack(e.getOc1Value()) == null ? null : e.getOc1Value());
            fhzgSwitchProtectionSettings.setOc1Delay(caseNullValueBack(e.getOc1Delay()) == null ? null : e.getOc1Delay());
            fhzgSwitchProtectionSettings.setOc1Setting(SwitchSettingConstants.SwitchActionEnum.getCodeByName(e.getOc1Setting()));
            fhzgSwitchProtectionSettings.setOc2Value(caseNullValueBack(e.getOc2Value()) == null ? null : e.getOc2Value());
            fhzgSwitchProtectionSettings.setOc2Delay(caseNullValueBack(e.getOc2Delay()) == null ? null : e.getOc2Delay());
            fhzgSwitchProtectionSettings.setOc2Setting(SwitchSettingConstants.SwitchActionEnum.getCodeByName(e.getOc2Setting()));
            fhzgSwitchProtectionSettings.setOc3Value(caseNullValueBack(e.getOc3Value()) == null ? null : e.getOc3Value());
            fhzgSwitchProtectionSettings.setOc3Delay(caseNullValueBack(e.getOc3Delay()) == null ? null : e.getOc3Delay());
            fhzgSwitchProtectionSettings.setOc3Setting(SwitchSettingConstants.SwitchActionEnum.getCodeByName(e.getOc3Setting()));
            fhzgSwitchProtectionSettings.setHazardType(SwitchSettingConstants.HazardTypeEnum.getCodeByName(e.getHazardType()));
            fhzgSwitchProtectionSettings.setHazardDesc(SwitchSettingConstants.HazardTypeEnum.getDescByName(e.getHazardType()));
            fhzgSwitchProtectionSettings.setProtEnableStatus(SwitchSettingConstants.ProtectionActivationEnum.getCodeByName(e.getProtEnableStatus()));
            fhzgSwitchProtectionSettings.setRecloseEnableStatus(SwitchSettingConstants.ProtectionActivationEnum.getCodeByName(e.getRecloseEnableStatus()));
            fhzgSwitchProtectionSettings.setLastTime(new Date());
            fhzgSwitchProtectionSettings.setLastEditor(userId);
            fhzgSwitchProtectionSettingsList.add(fhzgSwitchProtectionSettings);
        });
        return fhzgSwitchProtectionSettingsList;
    }

    //将文件流写入响应对象
    private void executeExport(HttpServletResponse response, ExcelWriter writer, String name) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding(Charsets.UTF_8.name());
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, String.format("attachment;filename=%s", URLEncoder.encode(name, Charsets.UTF_8.name())));
            writer.flush(response.getOutputStream(), true);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            writer.close();
        }

    }

    //设置表头信息
    private ExcelWriter buildWriter() {
        //设置返回excel
        ExcelWriter writer = ExcelUtil.getWriter();
        //跳过前两行
        for (int i = 0; i < 2; i++) {
            List<List<Object>> nullList = new ArrayList<>();
            writer.writeRow(nullList);
        }
        //设置特殊样式
        RichTextString richTextString = createSpecialRichText(writer);
        //合并单元格（表头）
        writer.merge(0, 1, 0, 0, "序号", false);
        writer.merge(0, 1, 1, 1, "所属区县", false);
        writer.merge(0, 1, 2, 2, "所属线路", false);
        writer.merge(0, 1, 3, 3, "开关名称", false);
        writer.merge(0, 1, 4, 4, richTextString, false);
        writer.merge(0, 1, 18, 18, "保护投退", false);
        writer.merge(0, 1, 19, 19, "重合闸", false);

        writer.merge(0, 0, 5, 6, "有效联络维护", false);
        writer.merge(0, 0, 7, 8, "开关隐患信息维护", false);
        writer.merge(0, 0, 9, 11, "过流I段（A/S）", false);
        writer.merge(0, 0, 12, 14, "过流II段（A/S）", false);
        writer.merge(0, 0, 15, 17, "过流III段（A/S）", false);

        writer.writeCellValue(5, 1, "是否联络");
        writer.writeCellValue(6, 1, "联络是否有效");
        writer.writeCellValue(7, 1, "隐患类别");
        writer.writeCellValue(8, 1, "描述");
        writer.writeCellValue(9, 1, "定值");
        writer.writeCellValue(10, 1, "时延");
        writer.writeCellValue(11, 1, "保护动作");
        writer.writeCellValue(12, 1, "定值");
        writer.writeCellValue(13, 1, "时延");
        writer.writeCellValue(14, 1, "保护动作");
        writer.writeCellValue(15, 1, "定值");
        writer.writeCellValue(16, 1, "时延");
        writer.writeCellValue(17, 1, "保护动作");

        //设置下拉数据
        Sheet sheet = writer.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        String[] options1 = {"告警", "跳闸"};
        String[] options2 = {"投入", "退出"};
        String[] options3 = {"存在缺陷", "开关调试", "告警"};
        setDropDownList(sheet, helper, 11, 2, 10000, options1);
        setDropDownList(sheet, helper, 14, 2, 10000, options1);
        setDropDownList(sheet, helper, 17, 2, 10000, options1);
        setDropDownList(sheet, helper, 18, 2, 10000, options2);
        setDropDownList(sheet, helper, 19, 2, 10000, options2);
        setDropDownList(sheet, helper, 7, 2, 10000, options3);
        return writer;
    }


    //设置表头样式
    private CellStyle createCustomHeaderStyle(ExcelWriter writer) {
        Workbook workbook = writer.getWorkbook();
        CellStyle cellStyle = workbook.createCellStyle();
        //设置背景色
        cellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        //设置字体
        //设置边框
        return cellStyle;
    }

    //设置特殊样式
    private RichTextString createSpecialRichText(ExcelWriter writer){
        Workbook workbook = writer.getWorkbook();
        CreationHelper creationHelper = workbook.getCreationHelper();
        RichTextString richTextString = creationHelper.createRichTextString("※开关资源ID");

        Font redfont = workbook.createFont();
        redfont.setColor(IndexedColors.RED.getIndex());
        //设置第一个字符为红色
        richTextString.applyFont(0,1,redfont);
        return richTextString;
    }

    //对空字段合枚举值处理
    private List<List<Object>> caseValue(List<SwitchInfoVO> switchInfoVOList) {
        List<List<Object>> dataList = new ArrayList<>();
        for (int i = 0; i < switchInfoVOList.size(); i++) {
            SwitchInfoVO param = switchInfoVOList.get(i);
            List<Object> row = CollUtil.newArrayList(
                    (i + 1),
                    caseNullValue(param.getMaintOrgName()),
                    caseNullValue(param.getFeederName()),
                    caseNullValue(param.getSwitchName()),
                    caseNullValue(param.getSwitchId()),
                    caseNullValue(SwitchSettingConstants.YesOrNoEnum.getNameByCode((param.getIsContactSwitch()))),
                    caseNullValue(SwitchSettingConstants.YesOrNoEnum.getNameByCode((param.getContactEffective()))),
                    caseNullValue(SwitchSettingConstants.HazardTypeEnum.getNameByCode((param.getHazardType()))),
                    caseNullValue(SwitchSettingConstants.HazardTypeEnum.getDescByCode((param.getHazardType()))),
                    caseNullValue(param.getOc1Value()),
                    caseNullValue(param.getOc1Delay()),
                    caseNullValue(SwitchSettingConstants.SwitchActionEnum.getNameByCode(param.getOc1Setting())),
                    caseNullValue(param.getOc2Value()),
                    caseNullValue(param.getOc2Delay()),
                    caseNullValue(SwitchSettingConstants.SwitchActionEnum.getNameByCode(param.getOc2Setting())),
                    caseNullValue(param.getOc3Value()),
                    caseNullValue(param.getOc3Delay()),
                    caseNullValue(SwitchSettingConstants.SwitchActionEnum.getNameByCode(param.getOc3Setting())),
                    caseNullValue(SwitchSettingConstants.ProtectionActivationEnum.getNameByCode((param.getProtEnableStatus()))),
                    caseNullValue(SwitchSettingConstants.ProtectionActivationEnum.getNameByCode((param.getRecloseEnableStatus())))
            );
            dataList.add(row);
        }
        return dataList;
    }

    //如果字段属性为空则返回“-”
    private Object caseNullValue(Object value) {
        if (Objects.isNull(value)) {
            return NULL_STR;
        } else {
            return value;
        }
    }

    private Object caseNullValueBack(Object value) {
        if (value != null && value instanceof String && StrUtil.equals((String) value, NULL_STR)) {
            return null;
        } else {
            return value;
        }
    }

    private void setDropDownList(Sheet sheet ,DataValidationHelper helper ,int columnIndex ,int firstRow,int lastRow ,String[] options){
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow,lastRow,columnIndex,columnIndex);
        DataValidationConstraint constraint = helper.createExplicitListConstraint(options);
        DataValidation validation = helper.createValidation(constraint,addressList);
        sheet.addValidationData(validation);
    }


}
