POI Excel实现内容替换

最新推荐文章于 2025-06-10 15:52:09 发布

原创最新推荐文章于 2025-06-10 15:52:09 发布 · 3.2k 阅读

11 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#java

文档处理工具专栏收录该内容

106 篇文章

订阅专栏

1 Maven依赖

        <!-- easyExcel  Excel文档处理工具     -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.8</version>
        </dependency>
        <!-- hutool工具包       -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.5.7</version>
        </dependency>

2 实现代码

    /**
     * 替换Excel模板文件内容
     *
     * @param replaceMap 文档数据
     */
    public static void replaceSheet(Map<String, Object> replaceMap, Workbook workbook, Sheet sheet, Integer endRowIndex) {
        try {
            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                Row row = (Row) rows.next();
                if (row == null) {
                    continue;
                }
                int num = row.getLastCellNum();
                if (endRowIndex != null && row.getRowNum() > endRowIndex) {
                    break;
                }
                for (int i = 0; i < num; i++) {
                    Cell cell = row.getCell(i);
                    if (cell == null || cell.getCellTypeEnum() == NUMERIC || cell.getStringCellValue() == null) {
                        continue;
                    }
                    String value = cell.getStringCellValue();
                    if (StrUtil.isNotEmpty(value) && value.indexOf("${") != -1) {
                        cell.setCellValue(replaceValue(value, replaceMap));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 替换内容 ${}
     *
     * @param value
     * @param replaceMap
     * @return
     */
    public static String replaceValue(String value, Map<String, Object> replaceMap) {
        String str = value.substring(value.indexOf("${") + 2, value.indexOf('}'));
        if (StrUtil.isEmpty(str)) {
            return value;
        }
        value = value.replace("${" + str + "}", (String) replaceMap.get(str));
        return value;
    }

3 调试代码


    @Test
    public void testReplace() {
        try {
            //读文件
            ClassPathResource classPathResource = new ClassPathResource("/doc/excel/replaceTemplate.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook(classPathResource.getFile());
            Sheet sheet = workbook.getSheetAt(0);
            File file = new File("D:/easyexcel/testReplace.xlsx");
            FileUtil.createNewFile(file);
            Map<String, Object> replaceMap = new HashMap<>();
            replaceMap.put("test1", "测试1");
            replaceMap.put("test2", "测试2");
            //替换内容
            POIExcelUtil.replaceSheet(replaceMap, workbook, sheet, null);
            workbook.write(new FileOutputStream(file));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }