Excel 匹配模板处理工具

import com.alibaba.fastjson.JSONObject;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;

import java.util.Iterator;
import java.util.List;
import java.util.regex.Pattern;

/**
 * Excel 匹配模板处理工具  通配包含  ${}和#{}
 * ${}  字段模板 将${}及里面的内容更换成指定json的内容
 * #{}  集合模板 将#{}及里面的内容更换成指定json的内容
 *      compile group: 'org.apache.poi', name: 'poi', version: '4.1.2'
 *      compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'
 *      compile group: 'org.apache.poi', name: 'poi-ooxml-schemas', version: '4.1.2'
 * @Description
 * @Author 不爱写代码的码农
 * @Date 2020/7/13 14:03
 */
@Slf4j
public class ExcelProcessUtils {

    private final static Pattern fieldPatternAll = Pattern.compile("\\$\\{(.+?)}");
    private final static Pattern listPatternAll = Pattern.compile("\\#\\{(.+?)}");
    private final static String fieldMatchAll = "\\$\\{[^\\}]+\\}";
    private final static String listMatchAll = "\\#\\{[^\\}]+\\}";

    /**
     * 行复制功能
     *
     * @param wb            工作簿
     * @param fromRow       从哪行开始
     * @param toRow         目标行
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public static void copyRow(Workbook wb, Row fromRow, Row toRow, boolean copyValueFlag) {
        toRow.setHeight(fromRow.getHeight());

        for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
            Cell tmpCell = cellIt.next();
            Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell, copyValueFlag);
        }

        Sheet worksheet = fromRow.getSheet();

        for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
            if (cellRangeAddress.getFirstRow() == fromRow.getRowNum()) {
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(),
                        (toRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                        cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
                worksheet.addMergedRegionUnsafe(newCellRangeAddress);
            }
        }
    }


    /**
     * 复制单元格
     *
     * @param wb            wb
     * @param srcCell       被复制cell
     * @param distCell      复制cell
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    private static void copyCell(Workbook wb, Cell srcCell, Cell distCell, boolean copyValueFlag) {
        CellStyle newStyle = wb.createCellStyle();
        CellStyle srcStyle = srcCell.getCellStyle();

        newStyle.cloneStyleFrom(srcStyle);
        newStyle.setFont(wb.getFontAt(srcStyle.getFontIndex()));

        // 样式
        distCell.setCellStyle(newStyle);

        // 内容
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }

        // 不同数据类型处理
        CellType srcCellType = srcCell.getCellType();
        distCell.setCellType(srcCellType);

        if (copyValueFlag) {
            if (srcCellType == CellType.NUMERIC) {
                if (DateUtil.isCellDateFormatted(srcCell)) {
                    distCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    distCell.setCellValue(srcCell.getNumericCellValue());
                }
            } else if (srcCellType == CellType.STRING) {
                distCell.setCellValue(srcCell.getRichStringCellValue());
            } else if (srcCellType == CellType.BLANK) {

            } else if (srcCellType == CellType.BOOLEAN) {
                distCell.setCellValue(srcCell.getBooleanCellValue());
            } else if (srcCellType == CellType.ERROR) {
                distCell.setCellErrorValue(srcCell.getErrorCellValue());
            } else if (srcCellType == CellType.FORMULA) {
                distCell.setCellFormula(srcCell.getCellFormula());
            }
        }
    }

    /**
     * cell处理
     *
     * @param cell       处理的cell
     * @param jsonObject 所有数据
     */
    public static void cellProcess(Cell cell, JSONObject jsonObject, List<ExcelProcessDto> excelProcessDtos, Row row) {
        String content = getCellStringValue(cell);
        if (fieldPatternAll.matcher(content).find()) {
            // 匹配到字段
            String cellValue = fieldProcess(content, jsonObject);
            cell.setCellValue(cellValue);
        } else if (listPatternAll.matcher(content).find()) {
            // 匹配到列表
            String listName = ExcelProcessUtils.getListName(content);
            int rowNum = row.getRowNum();
            ExcelProcessDto excelProcessDto = new ExcelProcessDto();
            if (CollectionUtils.isEmpty(excelProcessDtos)) {
                excelProcessDto.setListName(listName);
                excelProcessDto.setRowNum(rowNum);
                excelProcessDtos.add(excelProcessDto);
                return;
            }
            for