package com.chinagas.modules.schsf.utils.listener;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;
import java.util.Map;

public class McBasicCmtySheetWriteHandler implements SheetWriteHandler {

    private Map<Integer, List<String>> selectDataMap;

    public McBasicCmtySheetWriteHandler(Map<Integer, List<String>> selectDataMap) {
        this.selectDataMap = selectDataMap;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        int globalIndex = 1;
        // 获取第一个sheet页
        Sheet sheet = writeSheetHolder.getCachedSheet();
        // 获取sheet页的数据校验对象
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 获取工作簿对象，用于创建存放下拉数据的字典sheet数据页
        Workbook workbook = writeWorkbookHolder.getWorkbook();

        for (Map.Entry<Integer, List<String>> entry : selectDataMap.entrySet()) {
            // 设置存放下拉数据的字典sheet，并把这些sheet隐藏掉，这样用户交互更友好
            String dictSheetName = "dict_hide_sheet" + globalIndex;
            Sheet dictSheet = workbook.createSheet(dictSheetName);
            // 隐藏字典sheet页
            workbook.setSheetHidden(globalIndex, true);

            // 创建一个名为“dictSheetName”的名称，指向字典表中的数据区域
            String refers = dictSheetName + "!$A$1:$A$" + (entry.getValue().size() + 1);
            Name name = workbook.createName();
            name.setNameName(dictSheetName);
            name.setRefersToFormula(refers);

            // 向字典sheet写数据，从第一行开始写
            for (int i = 0; i < entry.getValue().size(); i++) {
                Row row = dictSheet.createRow(i);
                Cell cell = row.createCell(0);
                cell.setCellValue(entry.getValue().get(i));
            }

            // 设置下拉列表覆盖的行数，从第一行开始到最后一行
            CellRangeAddressList infoList = new CellRangeAddressList(1, Short.MAX_VALUE, entry.getKey(), entry.getKey());

            // 创建下拉列表约束
            DataValidationConstraint constraint = helper.createFormulaListConstraint(dictSheetName);
            DataValidation dataValidation = helper.createValidation(constraint, infoList);
            sheet.addValidationData(dataValidation);

            globalIndex++;
        }
    }
}