jnpf-common-v6x/jnpf-boot-common/jnpf-common-office/target/classes/jnpf/excel/ExcelHelper.java

package jnpf.excel;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import jnpf.excel.handle.ExcelCommentHandle;
import jnpf.excel.handle.ExcelDataValidation;
import jnpf.excel.handle.ExcelRequireRedColor;
import jnpf.model.ExcelColumnAttr;
import jnpf.model.ExcelModel;
import jnpf.util.ExcelUtil;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.util.*;
import java.util.stream.Collectors;

@Data
@NoArgsConstructor
public class ExcelHelper {
    // 是否生成示例数据
    public boolean needExampleData = false;
    boolean initPre = false;
    boolean initPost = false;
    /**
     * 控件信息
     */
    List<ExcelColumnAttr> models;
    /**
     * 控件信息和excel导出的key对应关系
     */
    Map<String, ExcelColumnAttr> modelMap = new HashMap<>();
    /**
     * excel参数
     */
    ExportParams exportParams;
    /**
     * 导出字段信息
     */
    List<ExcelExportEntity> entities;
    private Workbook workbook;
    private List<ExcelFunction> preHandleFunctions = new ArrayList<>();
    private List<ExcelFunction> postHandleFunctions = new ArrayList<>();
    private Map<String, String[]> optionMap;
    /**
     * 额外参数
     */
    private Map<String, Object> extraParams = new HashMap<>();
    /**
     * 存在子表
     */
    private boolean complexTable;

    private void preDataHandle() {
        for (ExcelExportEntity entity : this.entities) {
            if (CollectionUtils.isNotEmpty(entity.getList())) {
                this.complexTable = true;
                break;
            }
        }
        // 控件映射
        for (ExcelColumnAttr model : this.models) {
            String key = model.getKey();
            modelMap.put(key, model);
        }
    }

    public void init(Workbook workbook, ExportParams exportParams, List<ExcelExportEntity> entities, ExcelModel excelModel) {
        this.workbook = workbook;
        this.exportParams = exportParams != null ? exportParams : new ExportParams();
        this.models = excelModel.getModels() != null ? excelModel.getModels() : new ArrayList<>();
        this.entities = entities != null ? entities : new ArrayList<>();
        this.optionMap = excelModel.getOptionMap() != null ? excelModel.getOptionMap() : new HashMap<>();
        this.preDataHandle();
    }

    private void initPreHandle() {
        addPreHandle(new ExcelCommentHandle()::execute);
        this.initPre = true;
    }

    private void initPostHandle() {
        addPostHandle(new ExcelDataValidation()::execute);
        addPostHandle(new ExcelRequireRedColor()::execute);
        this.initPost = true;
    }

    public void doPreHandle() {
        if (!initPre) {
            this.initPreHandle();
        }
        addPreHandle(new ExcelCommentHandle()::execute);
        preHandleFunctions.stream().filter(Objects::nonNull).collect(Collectors.toList()).forEach(item -> item.apply(this, this.extraParams));
    }

    public void doPostHandle() {
        if (!initPost) {
            this.initPostHandle();
        }
        postHandleFunctions.stream().filter(Objects::nonNull).collect(Collectors.toList()).forEach(item -> item.apply(this, this.extraParams));
        // 移除标题的括号文字
        this.removeTitleConclusion();
        // 添加border线
        this.addCellBorder();
    }

    private void addCellBorder() {
        Sheet sheet = workbook.getSheet(exportParams.getSheetName());
        int rowLen = sheet.getLastRowNum();
        int startRowLen = complexTable ? 2 : 1;
        int lastCellNum = sheet.getRow(startRowLen - 1).getLastCellNum();
        CellStyle style = workbook.createCellStyle();
        ExcelExportStyler excelExportStyler = new ExcelExportStyler(workbook);
        for (int i = startRowLen; i <= rowLen; i++) {
            Row row = sheet.getRow(i);

            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                if (cell == null) cell = row.createCell(j);
                excelExportStyler.setBlackBorder(style);
                //设置单元格为文本格式
                DataFormat dataFormat = workbook.createDataFormat();
                style.setDataFormat(dataFormat.getFormat("@"));
                cell.setCellStyle(style);
            }
        }
    }

    private void removeTitleConclusion() {
        Sheet sheet = workbook.getSheet(exportParams.getSheetName());
        int rowLen = complexTable ? 2 : 1;
        for (int i = 0; i < rowLen; i++) {
            Row headerRow = sheet.getRow(i);
            int lastCellNum = headerRow.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = headerRow.getCell(j);
                if (cell == null) continue;
                String name = ((XSSFCell) cell).getRichStringCellValue().getString();
                if (ExcelUtil.matcherFind(name)) {
                    ((XSSFCell) cell).getRichStringCellValue().setString(name.substring(0, name.lastIndexOf("(")));
                }
            }
        }
    }

    public void addPreHandle(ExcelFunction... functions) {
        Collections.addAll(preHandleFunctions, functions);
    }

    public void addPostHandle(ExcelFunction... functions) {
        Collections.addAll(postHandleFunctions, functions);
    }


}