poi excel 模板 使用{} 替换内容

效果:

替换前

替换后

填入数据

package co.yixiang;

import cn.hutool.core.io.file.FileWriter;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelTest {
    public static void main(String[] args) {
        File file=new File("C:\\Users\\Administrator\\Desktop\\9.10\\aa.xlsx");

        Map map0=new HashMap();
        map0.put("applicant","李四");
        map0.put("Approver","王五");
        map0.put("ApplicationTime","昨天");
        map0.put("ApproverTime","今天");

        List list=new ArrayList();
        for(int i=0;i<10;i++){
            Map map=new HashMap();
            map.put("id",1);
            map.put("name","张三");
            map.put("sex","男");
            map.put("age",55);

            list.add(map);
        }

        byte[] bytes = ExcelUtils.writeExcel(file,map0,list);
        
        File file0=new File("C:\\Users\\Administrator\\Desktop\\9.10\\aa11.xlsx");
        FileWriter writer = new FileWriter(file0);
        writer.write(bytes,0,bytes.length);
    }
}

工具类:

package co.yixiang;

import cn.hutool.core.io.FileUtil;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;


import java.io.ByteArrayOutputStream;
import java.io.File;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtils {
    private static final String REG = "\\{([a-zA-Z_]+)\\}";// 匹配"{exp}"
    private static final String REG_LIST = "\\{\\.([a-zA-Z_]+)\\}";// 匹配"{.exp}"
    private static final Pattern PATTERN = Pattern.compile(REG);
    private static final Pattern PATTERN_LIST = Pattern.compile(REG_LIST);

    private ExcelUtils() {
    }

    /**
     * 根据模板生成Excel文件
     *
     * @param templateFile 模版文件
     * @param context      表头或表尾数据集合
     * @param dataList     列表
     * @return
     */
    public static byte[] writeExcel(File templateFile, Map<String, Object> context,
                                    List<Map<String, Object>> dataList) {
        try (Workbook workbook = WorkbookFactory.create(FileUtil.getInputStream(templateFile))) {
            Sheet sheet = workbook.getSheetAt(0);// 获取配置文件sheet 页
            int listStartRowNum = -1;
            for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        Cell cell = row.getCell(j);
                        if (cell != null && cell.getCellType() == CellType.STRING.getCode()) {
                            String cellValue = cell.getStringCellValue();
                            // 获取到列表数据所在行
                            if (listStartRowNum == -1 && cellValue.matches(REG_LIST)) {
                                listStartRowNum = i;
                            }

                            Object newValue = cellValue;
                            Matcher matcher = PATTERN.matcher(cellValue);
                            while (matcher.find()) {
                                String replaceExp = matcher.group();// 匹配到的表达式
                                String key = matcher.group(1);// 获取key
                                Object replaceValue = context.get(key);
                                if (replaceValue == null) {
                                    replaceValue = "";
                                }
                                if (replaceExp.equals(cellValue)) {// 单元格是一个表达式
                                    newValue = replaceValue;
                                } else {// 以字符串替换
                                    newValue = ((String) newValue).replace(replaceExp, replaceValue.toString());
                                }
                            }
                            setCellValue(cell, newValue);

                        }

                    }

                }
            }
            if (-1 != listStartRowNum) {// 如果不为 -1 说明有需要循环的列表表达式
                Row listStartRow = sheet.getRow(listStartRowNum);
                if (CollectionUtils.isEmpty(dataList)) {// 列表数据为空，清空列表表达式行
                    for (int i = 0; i < listStartRow.getLastCellNum(); i++) {
                        Cell cell = listStartRow.getCell(i);
                        if (cell != null) {
                            cell.setCellValue("");
                        }
                    }
                } else {
                    int lastCellNum = listStartRow.getLastCellNum();
                    if (listStartRowNum + 1 <= sheet.getLastRowNum()) {
                        sheet.shiftRows(listStartRowNum + 1, sheet.getLastRowNum(), dataList.size(), true, false);// 列表数据行后面行下移，留出数据填充区域
                    }
                    for (int i = 0; i < dataList.size(); i++) {// 循环列表数据 生成行
                        Map<String, Object> map = dataList.get(i);// 一行数据
                        int newRowNum = listStartRowNum + i + 1;// 保留表达式行
                        Row newRow = sheet.createRow(newRowNum);// 创建新行
                        for (int j = 0; j < lastCellNum; j++) {// 循环遍历单元格
                            Cell cell = listStartRow.getCell(j);// 列表数据行

                            // 填充数据
                            if (cell != null) {
                                Cell newCell = newRow.createCell(j);
                                newCell.setCellStyle(cell.getCellStyle());// 设置单元格格式

                                if (cell.getCellType() == CellType.STRING.getCode()
                                        && cell.getStringCellValue().matches(REG_LIST)) {// 单元格是一个表达式
                                    String cellExp = cell.getStringCellValue();
                                    Matcher matcher = PATTERN_LIST.matcher(cellExp);
                                    matcher.find();
                                    String key = matcher.group(1);// 获取key
                                    Object newValue = map.get(key);
                                    if (newValue == null) {
                                        newValue = "";
                                    }
                                    setCellValue(newCell, newValue);
                                } else {// 不是表达式复制单元格数据
                                    int cellType = cell.getCellType();
                                    if (cellType == CellType.NUMERIC.getCode()) {
                                        newCell.setCellValue(cell.getNumericCellValue());
                                    } else if (cellType == CellType.BOOLEAN.getCode()) {
                                        newCell.setCellValue(cell.getBooleanCellValue());
                                    } else if (cellType == CellType.STRING.getCode()) {
                                        newCell.setCellValue(cell.getStringCellValue());
                                    } else if (cellType == CellType.FORMULA.getCode()) {
                                        // 处理公式，待实现
                                    } else {
                                        newCell.setCellValue(cell.getStringCellValue());
                                    }
                                }
                            }
                        }
                    }
                    sheet.removeRow(listStartRow);// 删除list表达式行
                    sheet.shiftRows(listStartRowNum + 1, sheet.getLastRowNum(), -1, true, false);// 数据区域上移一行，覆盖表达式行

                    // 合并单元格处理
                    for (int i = 0; i < lastCellNum; i++) {
                        CellRangeAddress mergedRangeAddress = getMergedRangeAddress(sheet, listStartRowNum, i);
                        if (mergedRangeAddress != null) {// 合并的单元格
                            i = mergedRangeAddress.getLastColumn();
                            for (int j = 1; j < dataList.size(); j++) {
                                int newRowNum = listStartRowNum + j;
                                sheet.addMergedRegionUnsafe(new CellRangeAddress(newRowNum, newRowNum,
                                        mergedRangeAddress.getFirstColumn(), mergedRangeAddress.getLastColumn()));
                            }
                        }
                    }
                }
            }
            // 公式生效
            sheet.setForceFormulaRecalculation(true);
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            workbook.write(out);
            return out.toByteArray();
        } catch (Exception e) {
            throw new ExcelException("生成excel失败!", e);
        }
    }

    private static void setCellValue(Cell cell, Object value) {
        if (value instanceof Number) {// 如果是数字类型的设置为数值
            cell.setCellValue(Double.parseDouble(value.toString()));
        } else if (value instanceof Date) {// 如果为时间类型的设置为时间
            cell.setCellValue((Date) value);
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    /**
     * 获取指定行/列的合并单元格区域
     *
     * @param sheet
     * @param row
     * @param column
     * @return CellRangeAddress 不是合并单元格返回null
     */
    private static CellRangeAddress getMergedRangeAddress(Sheet sheet, int row, int column) {
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        for (CellRangeAddress cellAddresses : mergedRegions) {
            if (row >= cellAddresses.getFirstRow() && row <= cellAddresses.getLastRow()
                    && column >= cellAddresses.getFirstColumn() && column <= cellAddresses.getLastColumn()) {
                return cellAddresses;
            }
        }
        return null;
    }

    /**
     * 多个列表支持，按顺序写入excel。 列表数据数量需等于列表表达式数量，不然多余的表达式不会被清空。多余的列表数据不会被写入
     *
     * @param templateFile
     * @param context
     * @param dataLists
     * @return
     */
    public static byte[] writeMultiList(File templateFile, Map<String, Object> context,
                                        List<List<Map<String, Object>>> dataLists) {
        try {
            File temp = templateFile;
            for (List<Map<String, Object>> dataList : dataLists) {
                byte[] tempBytes = writeExcel(temp, context, dataList);
                temp = File.createTempFile("multi_excel", ".excel");
                FileUtils.writeByteArrayToFile(temp, tempBytes);
            }
            return FileUtils.readFileToByteArray(temp);
        } catch (ExcelException e) {
            throw e;
        } catch (Exception e) {
            throw new ExcelException("生成Excel失败！", e);
        }
    }

    static class ExcelException extends RuntimeException {
        /**
         *
         */
        private static final long serialVersionUID = -2772261598232964002L;

        public ExcelException(String msg, Throwable e) {
            super(msg, e);
        }

        public ExcelException(String msg) {
            super(msg);
        }
    }
}