com.xinjump.easyexcel.write.selected.SelectedSheetWriteHandler Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of xinjump-easyexcel Show documentation
Xinjump easyexcel
The newest version!
package com.xinjump.easyexcel.write.selected;

import com.alibaba.excel.annotation.ExcelProperty;
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.xinjump.base.util.collect.MapUtils;
import com.xinjump.easyexcel.annotation.ExcelSelected;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.lang.reflect.Field;
import java.util.Map;
import java.util.Objects;

public class SelectedSheetWriteHandler implements SheetWriteHandler {

    private final Map selectedMap;
    private final String hiddenName = "selected_sheet_hidden";

    public SelectedSheetWriteHandler(Class clazz) {
        this.selectedMap = this.resolveSelectedAnnotation(clazz);
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        // 针对多sheet，将隐藏的sheet放在最后，避免easyexcel按顺序被读取到
        int hiddenIndex = workbook.getSheetIndex(hiddenName);
        if (hiddenIndex >= 0) {
            workbook.removeSheetAt(hiddenIndex);
        }
        Sheet hidden = workbook.createSheet(hiddenName);
        selectedMap.forEach((k, v) -> {
            CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
            String excelLine = this.getExcelLine(k);
            String[] values = v.getSource();
            for (int i = 0, length = values.length; i < length; i++) {
                hidden.createRow(i).createCell(k).setCellValue(values[i]);
            }
            String refers = "=" + hiddenName + "!$" + excelLine + "$1:$" + excelLine + "$" + (values.length + 1);
            DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
            DataValidation validation = helper.createValidation(constraint, rangeList);
            // 阻止输入非下拉选项的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示", "请输入下拉选项中的内容");
            sheet.addValidationData(validation);
        });
        hiddenIndex = workbook.getSheetIndex(hiddenName);
        if (hiddenIndex >= 0 && !workbook.isSheetHidden(hiddenIndex)) {
            workbook.setSheetHidden(hiddenIndex, true);
        }
    }

    /**
     * 解析表头类中的下拉注解
     *
     * @param head 表头类
     * @param   泛型
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    private  Map resolveSelectedAnnotation(Class head) {
        Map selectedMap = MapUtils.newHashMap();
        if (Objects.nonNull(head)) {
            Field[] fields = head.getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];
                // 解析注解信息
                ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
                if (Objects.isNull(selected)) {
                    continue;
                }
                ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve(selected);
                if (excelSelectedResolve.isValid()) {
                    ExcelProperty property = field.getAnnotation(ExcelProperty.class);
                    if (Objects.nonNull(property) && property.index() >= 0) {
                        selectedMap.put(property.index(), excelSelectedResolve);
                    } else {
                        selectedMap.put(i, excelSelectedResolve);
                    }
                }
            }
        }
        return selectedMap;
    }

    private String getExcelLine(int num) {
        String line = "";
        int first = num / 26;
        int second = num % 26;
        if (first > 0) {
            line = (char) ('A' + first - 1) + "";
        }
        line += (char) ('A' + second) + "";
        return line;
    }

}