EasyPoi根据模板导出数据，并在Excel中插入多张图片

时间：2024-01-30 09:10:50

1. 引入依赖

<dependency>
     <groupId>cn.afterturn</groupId>
     <artifactId>easypoi-base</artifactId>
     <version>3.2.0</version>
</dependency>
<dependency>
     <groupId>cn.afterturn</groupId>
     <artifactId>easypoi-web</artifactId>
     <version>3.2.0</version>
</dependency>
<dependency>
     <groupId>cn.afterturn</groupId>
     <artifactId>easypoi-annotation</artifactId>
     <version>3.2.0</version>
</dependency>

2.Excel模板

 

 

 3.具体业务代码

        FileOutputStream fileOut;
        BufferedImage bufferImg1;
        BufferedImage bufferImg2;
        Map resMap = new HashMap();
        try {
            //数据库查出的对象 具体根据业务逻辑更改
            EngineeringVo ev = engineeringExplorationMapper.findEngineeringVoByAssetsId(assetsId);
            TemplateExportParams params = new TemplateExportParams(
                    "D:/excel/光交箱勘测表.xls");

　　　　　　  //填充数据
            //注意map中put的值 不要为空binocularOperation()自己写的三目运算为空处理办法
            Map<String, Object> map = new HashMap<>();
            map.put("projectName", binocularOperation(ev.getProjectName()));
            map.put("contactsTelephone", binocularOperation(ev.getContactsTelephone()));
            map.put("assetsCode", binocularOperation(ev.getAssetsCode()));
            map.put("address", binocularOperation(ev.getAddress()));
            map.put("boxSize", binocularOperation(ev.getBoxSize()));
            map.put("capacity", binocularOperation(ev.getCapacity()));
            map.put("operationSurfaces", binocularOperation(ev.getOperationSurfaces()));
            map.put("boxBaseSize", binocularOperation(ev.getBoxBaseSize()));
            map.put("adapters", binocularOperation(ev.getAdapters()));
            map.put("environmentDescription", binocularOperation(ev.getEnvironmentDescription()));
            map.put("expansionDirections", binocularOperation(ev.getExpansionDirections()));
            map.put("capacityExpansion", binocularOperation(ev.getCapacityExpansion()));
            map.put("description", binocularOperation(ev.getDescription()));
            map.put("userName", binocularOperation(ev.getUserName()));
            map.put("tel", binocularOperation(ev.getTel()));
            String time = " ";
            Date createTime = ev.getCreateTime();
            SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
            if (null != createTime) {
                time = sf.format(createTime);
            }
            map.put("createTime", time);

　　　　　　  //这里需要强转 插入图片需要XSSFWorkbook对象
            //Workbook不可以转为HSSFWorkbook 具体逻辑调试看结果集
            XSSFWorkbook workbook = (XSSFWorkbook) ExcelExportUtil.exportExcel(params, map);
            String time2 = " ";
            SimpleDateFormat sf2 = new SimpleDateFormat("yyyyMMddHHmmss");
            if (null != createTime) {
                time2 = sf2.format(createTime);
            }

            String assetsPicture = ev.getAssetsPicture();
            //插入图片
            if (!StringUtil.isEmpty(assetsPicture)) {
                String replace = assetsPicture.replace(ur, di).replace("[", "").replace("]", "").replace("\"", "");
                String[] split = replace.split(",");

                if(split.length == 1){
                    String s = split[0];
                    String picturePath = s.replace(ur, di);
                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                    //加载图片
                    bufferImg1 = ImageIO.read(new File(picturePath));
                    ImageIO.write(bufferImg1, "png", byteArrayOut);
                    XSSFSheet sheet1 = workbook.getSheet("sheet1");
                    XSSFDrawing patriarch = sheet1.createDrawingPatriarch();

                    //图片 1    3 11 7 18 意思为 横轴3 纵轴11 横轴3 纵轴18                                                      
                    XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 11, (short) 7, 18);
                    patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));

                    //图片2
                    anchor = new XSSFClientAnchor(-200, 0, 0, 0, (short) 9, 11, (short) 12, 18);
                    patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
                }else{
                    String s1 = split[0];
                    String picturePath1 = s1.replace(ur, di);

                    String s2 = split[1];
                    String picturePath2 = s2.replace(ur, di);

                    ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();
                    ByteArrayOutputStream byteArrayOut2 = new ByteArrayOutputStream();

                    //加载图片
                    bufferImg1 = ImageIO.read(new File(picturePath1));
                    bufferImg2 = ImageIO.read(new File(picturePath2));
                    log.info("加载图片完成！！");

                    ImageIO.write(bufferImg1, "png", byteArrayOut1);
                    ImageIO.write(bufferImg2, "png", byteArrayOut2);

                    XSSFSheet sheet1 = workbook.getSheet("sheet1");
                    XSSFDrawing patriarch = sheet1.createDrawingPatriarch();

                    log.info("开始写入图片位置！！");
                    //图片 1
                    XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 11, (short) 7, 18);
                    patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut1.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));

                    //图片2
                    XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, (short) 9, 11, (short) 12, 18);
                    patriarch.createPicture(anchor2, workbook.addPicture(byteArrayOut2.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
                }


            }
            String fileName = ev.getAssetsName() + "-" + time2 + ".xlsx";
            fileOut = new FileOutputStream(fileDir + fileName);
            workbook.write(fileOut);
            fileOut.close();
            resMap.put("url", fileUrl + fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }

4. 效果图

 

 5. 遍历多行数据

 

 fe的写法 fe标志 冒号 list数据 单个元素数据（默认t，可以不写） 第一个元素 {{$fe: maplist t t.id }}

 上述获取为list

填充数据

Map<String, Object> map = new HashMap<String, Object>();
List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
        for (int i = 0; i < 4; i++) {
            Map<String, String> lm = new HashMap<String, String>();
            lm.put("id", i + 1 + "");
            lm.put("zijin", i * 10000 + "");
            lm.put("bianma", "A001");
            lm.put("mingcheng", "设计");
            lm.put("xiangmumingcheng", "EasyPoi " + i + "期");
            lm.put("quancheng", "开源项目");
            lm.put("sqje", i * 10000 + "");
            lm.put("hdje", i * 10000 + "");

            listMap.add(lm);
        }
        map.put("maplist", listMap);

具体参考：http://easypoi.mydoc.io/ 中第三点

6.总结：

1) map中的值不能为null，不然会报空指针
2) workbook对象不能转HSSFWorkbook，可以转为XSSFWorkbook

相关文章