POI Excel实现内容替换

客官°小女子只卖身不卖艺 2023-09-30 04:26 15阅读 0赞

**目录**

1 Maven依赖

2 实现代码

3 调试代码

4 模板内容

5 调试结果

注：

--------------------

## 1 Maven依赖 ##

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.8</version>
            </dependency>
            
            <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();
            }
        }

## 4 模板内容 ##

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_20_color_FFFFFF_t_70_g_se_x_16][]

## 5 调试结果 ##

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

## 注： ##

（1）以$\{\}符号表示需要替换的内容。

（2）源码请查看Gitee。

[xudongbase: 主要是项目中可以用到的共通方法![favicon-9007bd527d8a7851c8330e783151df58.ico][]https://gitee.com/xudong\_master/xudongbase][xudongbase_ _favicon-9007bd527d8a7851c8330e783151df58.ico_https_gitee.com_xudong_master_xudongbase]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/ee527fa102c94581a8afa6ebe0cb93c5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5pet5Lic5oCq,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/4eaa65720f2649b3ab3cbd53c5e977bf.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5pet5Lic5oCq,size_20,color_FFFFFF,t_70,g_se,x_16
[favicon-9007bd527d8a7851c8330e783151df58.ico]: https://assets.gitee.com/assets/favicon-9007bd527d8a7851c8330e783151df58.ico
[xudongbase_ _favicon-9007bd527d8a7851c8330e783151df58.ico_https_gitee.com_xudong_master_xudongbase]: https://gitee.com/xudong_master/xudongbase