poi-tl实现导出word功能+phantomjs后台生成echarts截图

谁借莪１个温暖的怀抱￠ 2022-09-09 12:45 779阅读 0赞

本文主要说明一下如何通过poi-tl实现word导出，并在导出文档中插入表格、echarts的图片。

1）下载phantomjs安装包，下载地址为https://phantomjs.org/download.html ，根据操作系统下载对应的安装包即可；windows操作系统安装配置参考资料https://blog.csdn.net/qq\_42543250/article/details/81542878，linux系统安装配置参考资料https://blog.csdn.net/wanght89/article/details/78320375

2）若应用部署在linux系统上，导出的word文档可能会出现字体不显示的问题，可以将windows下的字体放到linux系统中，具体操作可参考资料：https://www.jb51.net/article/136288.htm

3）将echarts.min.js ，echarts-convert.js ，jquery-2.1.4.min.js三个js文件放到同一目录下，主要是用于后台生成echarts图标然后通过phantomjs截图到本地保存。  
echarts-convert.js内容如下：

4）添加所需依赖包，如下：

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.1.0</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.1.0</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>4.1.0</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-scratchpad</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>ooxml-schemas</artifactId>
                <version>1.4</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>ooxml-security</artifactId>
                <version>1.1</version>
                <exclusions>
                    <exclusion>
                        <groupId>org.apache.xmlbeans</groupId>
                        <artifactId>xmlbeans</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
            
    
            
            <dependency>
                <groupId>com.deepoove</groupId>
                <artifactId>poi-tl</artifactId>
                <version>1.6.0</version>
            </dependency>
            <dependency>
                <groupId>org.seleniumhq.selenium</groupId>
                <artifactId>selenium-chrome-driver</artifactId>
                <version>2.45.0</version>
            </dependency>
            <dependency>
                <groupId>org.seleniumhq.selenium</groupId>
                <artifactId>selenium-firefox-driver</artifactId>
                <version>2.45.0</version>
            </dependency>
            <dependency>
                <groupId>org.seleniumhq.selenium</groupId>
                <artifactId>selenium-ie-driver</artifactId>
                <version>2.45.0</version>
            </dependency>
            <dependency>
                <groupId>org.seleniumhq.selenium</groupId>
                <artifactId>selenium-safari-driver</artifactId>
                <version>2.45.0</version>
            </dependency>
            <dependency>
                <groupId>org.seleniumhq.selenium</groupId>
                <artifactId>selenium-support</artifactId>
                <version>2.45.0</version>
            </dependency>
            <dependency>
                <groupId>org.seleniumhq.selenium</groupId>
                <artifactId>selenium-remote-driver</artifactId>
                <version>2.45.0</version>
            </dependency>
            <dependency>
                <groupId>org.seleniumhq.selenium</groupId>
                <artifactId>selenium-api</artifactId>
                <version>2.45.0</version>
            </dependency>
            <dependency>
                <groupId>org.seleniumhq.selenium</groupId>
                <artifactId>selenium-java</artifactId>
                <version>2.45.0</version>
            </dependency>
            <dependency>
                <groupId>com.codeborne</groupId>
                <artifactId>phantomjsdriver</artifactId>
                <version>1.2.1</version>
                <exclusions>
                    <exclusion>
                        <groupId>org.seleniumhq.selenium</groupId>
                        <artifactId>selenium-java</artifactId>
                    </exclusion>
                    <exclusion>
                        <groupId>org.seleniumhq.selenium</groupId>
                        <artifactId>selenium-remote-driver</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
            <dependency>
                <groupId>ch.ethz.ganymed</groupId>
                <artifactId>ganymed-ssh2</artifactId>
                <version>262</version>
            </dependency>

5）项目application.yml文件中添加配置：

phantomjs:
      host: 182.168.1.1   // 安装phantomjs的服务器地址
      port: 22            // 安装phantomjs的服务器端口号
      username: root      // 安装phantomjs的服务器用户名
      password: root      // 安装phantomjs的服务器密码
      jsPath: /root/echarts/echarts-convert.js   // 安装phantomjs的服务器js文件路径
      echartsImg: /root/echartsImg/              // 安装phantomjs的服务器生成echarts截图存放路径
      dockerDocxPath: /app/poiDocx/              // 应用服务器生成word文档存放路径

6）本人部署应用用的是docker容器，所以需要将容器内路径和宿主机路径做一个映射，因为我的phantomjs是部署在宿主机的。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56iL5bqP54y_dnPnoIHlhpw_size_19_color_FFFFFF_t_70_g_se_x_16]

7）word模板如下，  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56iL5bqP54y_dnPnoIHlhpw_size_20_color_FFFFFF_t_70_g_se_x_16]

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56iL5bqP54y_dnPnoIHlhpw_size_11_color_FFFFFF_t_70_g_se_x_16]

8）controller层 ：

@ApiOperation("生成安全生产违章分析报告")
        @GetMapping("/generateSafeReportFile")
        public Response generateSafeReportFile(HttpServletResponse response,String startDate,String endDate) { 
    
            try { 
                Date currentDate = new Date();
                long random = currentDate.getTime();
                String docFilePath = PoiCommonUtil.getDocFilePath("safeReport"+random);
    
                // 生成word文档数据
                Map<String,Object> objMap = supervisorService.getSafeReportMap(startDate,endDate);
    
                //渲染word文档中的表格，需要绑定
                Configure config = Configure.newBuilder().bind("sceneList", new DetailTablePolicy()).
                                                          bind("weizhangTable",new DetailTablePolicy()).
                                                          bind("weizhangDetailTable",new DetailTablePolicy()).
                                                          bind("sceneAndWeizhangTable",new SceneAndWeizhangTablePolicy()).
                                                          bind("weizhangAnalyseTable",new NormalTablePolicy()).build();
                poiCommonUtil.exportFile(PoiConstant.SAFE_REPORT_TEMPLATE, objMap, docFilePath, config);
    
    
                //导出word
                if (StringUtils.isNotEmpty(docFilePath)) { 
                    OperationUtil.exportFileStreamByFilePath(response,docFilePath,PoiConstant.SAFE_REPORT);
                }
    
    
                return Response.success("导出成功！");
            } catch (Throwable e) { 
                log.error("POI生成错误：", e);
                return Response.failure("导出失败！");
            }
    
        }

9）PoiCommonUtil工具类：

package com.zhxd.cloud.ksh.utils.poi;
    
    import ch.ethz.ssh2.Connection;
    import ch.ethz.ssh2.Session;
    import ch.ethz.ssh2.StreamGobbler;
    import com.deepoove.poi.XWPFTemplate;
    import com.deepoove.poi.config.Configure;
    import com.deepoove.poi.data.PictureRenderData;
    import com.google.common.collect.Maps;
    import com.zhxd.cloud.ksh.supervisor.constant.PoiConstant;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.boot.system.ApplicationHome;
    import org.springframework.stereotype.Service;
    
    import java.io.*;
    import java.util.Map;
    
    /** * @Auther: mm * @Date: 2019/12/17 09:04 * @Description: */
    @Service
    @Slf4j
    public class PoiCommonUtil { 
    
        /** * 获取导出文件全路径 * 如果对应文件存在则删除 * * @return */
        public static String getDocFilePath(String docFileNamePrefix) { 
            try { 
                ApplicationHome h = new ApplicationHome(PoiCommonUtil.class);
                File jarF = h.getSource();
                String docOutDir = jarF.getParentFile().toString() + File.separator + "poiDocx" + File.separator;
    
                File docFile = new File(docOutDir);
                if (!docFile.exists()) { 
                    docFile.mkdirs();
                }
    
                String filePath = docOutDir + docFileNamePrefix + ".docx";
                File dayReportFile = new File(filePath);
                if (dayReportFile.exists()) { 
                    dayReportFile.delete();
                }
    
                log.info("filePath={}", filePath);
                return filePath;
            } catch (Exception e) { 
                log.error("获取文件目录报错:", e);
                return "";
            }
        }
    
        /** * @param templateFileName * @param obj * @return */
        public String exportFile(String templateFileName, Object obj, String outPath, Configure config ) { 
            try { 
                XWPFTemplate template = null;
                if(config!=null){ 
                    template = XWPFTemplate.compile(this.getClass().getResourceAsStream(PoiConstant.DOCX_TEMPLATE_PATH +templateFileName),config);
                }else{ 
                    template = XWPFTemplate.compile(this.getClass().getResourceAsStream(PoiConstant.DOCX_TEMPLATE_PATH + templateFileName));
                }
                template.render(obj);
                FileOutputStream out = new FileOutputStream(outPath);
                template.write(out);
                out.flush();
                out.close();
                template.close();
                return outPath;
            } catch (IOException e) { 
                log.error("poi生成文件报错：{}", e);
                return "";
            }
        }
    
        public static PictureRenderData generateEChart(String JSpath,String echartsImg,String hostname,int port,String username,String password,String options, String imgName,int width,int height) { 
    
            ApplicationHome h = new ApplicationHome(PoiCommonUtil.class);
            File jarF = h.getSource();
            String dockerEchartsImg = jarF.getParentFile().toString() + File.separator + "echartsImg" + File.separator;
    
            writeFile(dockerEchartsImg,options,imgName.substring(0,imgName.indexOf(".")));
            String echartsImgPath =  echartsImg + imgName;
            String echartsJsonPath =  echartsImg + imgName.substring(0,imgName.indexOf(".")) + ".json";
            try { 
    
                String cmd = "phantomjs " + JSpath + " -infile " + echartsJsonPath + " -outfile " + echartsImgPath + " -width " + width + " -height " + height;
                execCommand(hostname,port,username,password,cmd);
    
                PictureRenderData brd = new PictureRenderData(width, height, new File(dockerEchartsImg+imgName));
    
                return brd;
            } catch (Exception e) { 
    
                log.error("生成echarts截图报错了，{}",e);
    
                return null;
            }
        }
    
        public static void execCommand(String hostname,int port,String username,String password,String execStr) { 
            log.info(execStr + "------------------------");
            try{ 
                Connection conn = new Connection(hostname,port);
                conn.connect();
                boolean isAuthenticated = conn.authenticateWithPassword(username, password);
    
                if (isAuthenticated == false) { 
                    throw new IOException("Authentication failed.");
                }
    
                Session sess = conn.openSession();
                sess.execCommand(execStr);
    
                InputStream stdout = new StreamGobbler(sess.getStdout());
                BufferedReader br = new BufferedReader(new InputStreamReader(stdout));
                while (true){ 
                    String line = br.readLine();
                    if (line == null)
                        break;
                }
    
                sess.close();
                conn.close();
    
            } catch (Exception e) { 
                System.exit(2);
            }
        }
    
        public static void createNewFile(File file) throws IOException { 
            if (!file.exists()) {    //文件不存在则创建文件，先创建目录
                File dir = new File(file.getParent());
                dir.mkdirs();
                file.createNewFile();
            }
        }
    
        /* * * options生成文件存储 */
        public static void writeFile(String echartsConvertPath,String options, String imgName) { 
            String dataPath = echartsConvertPath + imgName + ".json";
            try { 
                /* option写入文本文件 用于执行命令*/
                File writename = new File(dataPath);
                createNewFile(writename);
                BufferedWriter out = new BufferedWriter(new FileWriter(writename));
                out.write(options);
                out.flush(); // 把缓存区内容压入文件
                out.close(); // 最后关闭文件
            } catch (IOException e) { 
                e.printStackTrace();
            }
        }
    
        public static Map<String,Object> getLabelMapOfEcharts() { 
            Map<String,Object> labelMap = Maps.newHashMap();
    
            labelMap.put("show",true);
            labelMap.put("position","top");
    
            return labelMap;
        }
    
        public static Map<String,Object> getLineStyleMapOfLine(String color,int width) { 
            Map<String,Object> lineStyleMap = Maps.newHashMap();
            lineStyleMap.put("color",color);
            lineStyleMap.put("width",width);
            lineStyleMap.put("type","dashed");
    
            return lineStyleMap;
        }
    }

10）OperationUtil工具类：

package com.zhxd.cloud.ksh.utils;
    
    import com.google.common.base.Strings;
    import lombok.extern.slf4j.Slf4j;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    
    @Slf4j
    public class OperationUtil { 
    
    
        /** * 根据文件路径下载/导出文件 * @param response * @param docFilePath * @param moduleName */
        public static void exportFileStreamByFilePath(HttpServletResponse response,String docFilePath, String moduleName){ 
            try { 
                String fileName ;
                if (!Strings.isNullOrEmpty(docFilePath)) { 
                    fileName = docFilePath.substring(docFilePath.lastIndexOf(File.separator)+1);
                    response.addHeader("Content-Disposition", "attachment;filename=\""
                            + new String(fileName.getBytes("UTF-8"),"ISO-8859-1") + "\"");
                    response.setContentType("application/msword; charset=UTF-8");
                    byte[] buffer = new byte[4096];
                    BufferedOutputStream output = null;
                    BufferedInputStream input = null;
                    try { 
                        File file = new File(docFilePath);
                        if(!file.exists()){ 
                            log.error("不存在对应路径的文件！");
                            return;
                        }
                        output = new BufferedOutputStream(response.getOutputStream());
                        input = new BufferedInputStream(new FileInputStream(docFilePath));
                        int n = -1;
                        while ((n = input.read(buffer, 0, 4096)) != -1) { 
                            output.write(buffer, 0, n);
                        }
                        response.flushBuffer();
                    } catch (Exception e) { 
                        log.error("根据路径导出{}文件报错：{}",moduleName,e);
                    } finally { 
                        try { 
                            if (input != null)
                                input.close();
                            if (output != null)
                                output.close();
                        } catch (IOException e) { 
                            log.error("根据路径导出{}文件报错：{}",moduleName,e);
                        }
                    }
                }
            } catch (Exception e) { 
                log.error("文件导出错误：" ,e);
            }
        }
    
    }

11）serviceImpl实现类代码：

@Override
        public Map<String, Object> getSafeReportMap(String startDate, String endDate) throws Exception { 
            // 表格样式
            TableStyle rowStyle = new TableStyle();
            rowStyle.setAlign(STJc.CENTER);
    
            // 返回前端的数据
            Map<String,Object> objMap = new HashMap<>();
            objMap.put("startDate",startDate);
            objMap.put("endDate",endDate);
    
            // 1， 表格数据开始 --- 根据部门分组统计现场督查情况
            List<RtRiskSupervisor> sceneList = riskSupervisorMapper.listSupervisorGroupByDept(startDate,endDate);
    
            // 构建表内容
            List<RowRenderData> rowDataList = Lists.newArrayList();
    
            if (!CollectionUtils.isEmpty(sceneList)) { 
                for (RtRiskSupervisor riskSupervisor:sceneList) { 
    
                    // 处理表格的数据
                    RowRenderData rowData1 = RowRenderData.build("测试","数量",
                            "1","1",
                            "1","1",
                            "1","1",
                            "1","1",
                            "1");
    
                    RowRenderData rowData2 = RowRenderData.build("测试","占比",
                            "1","1",
                            "1","1",
                            "1","1",
                            "1","1",
                            "1");
    
                    rowData1.setRowStyle(rowStyle);
                    rowData2.setRowStyle(rowStyle);
    
                    rowDataList.add(rowData1);
                    rowDataList.add(rowData2);
            }
            objMap.put("sceneList",rowDataList);
            // 1， 表格数据结束 --- 根据部门分组统计现场督查情况
    
    
            // 2,echarts截图开始 --- 统计违章数据-柱形图
            List<Map<String,Object>> weizhangList = riskSupervisorMapper.listWeizhangNumGroupByDept(startDate,endDate);
    
            PictureRenderData pictureRenderData = null;// 违章柱形图
            if (!CollectionUtils.isEmpty(weizhangList)) { 
                List<String> xDataList = new ArrayList<>();// 违章柱图的x轴数据
                List<Object> yDataList = new ArrayList();// 违章柱图的y轴数据
                for (Map<String,Object> weizhangMap:weizhangList) { 
                    xDataList.add((String) weizhangMap.get("orgInspectDept"));
                    yDataList.add(weizhangMap.get("problemNum"));
                }
    
                pictureRenderData = createEchartsBarImg(xDataList,yDataList,PoiConstant.WEIZHANG_BAR_IMG,400,300);
            }
    
            objMap.put("weizhangBarImg",pictureRenderData);
            // 2,echarts截图结束 --- 统计违章数据-柱形图
    
            return objMap;
        }
    
      // 柱图
        private PictureRenderData createEchartsBarImg(List<String> xDataList,List<Object> yDataList,String imgName,int width,int height) { 
            JSONObject weizhangJson = new JSONObject();// 违章柱图的json数据
    
            Map<String,Object> xAxisMap = new HashMap<>();
            xAxisMap.put("type","category");
            xAxisMap.put("data",xDataList);
    
            Map<String,Object> yAxisMap = new HashMap<>();
            yAxisMap.put("type","value");
    
            List<Map<String,Object>> seriesList = Lists.newArrayList();
            Map<String,Object> seriesMap = new HashMap<>();
            Map<String,Object> labelMap = new HashMap<>();
            labelMap.put("show",true);
            labelMap.put("position","top");
    
            seriesMap.put("data",yDataList);
            seriesMap.put("type","bar");
            seriesMap.put("label",labelMap);
            seriesList.add(seriesMap);
    
            weizhangJson.put("xAxis",xAxisMap);
            weizhangJson.put("yAxis",yAxisMap);
            weizhangJson.put("series",seriesList);
            weizhangJson.put("color","#5470c6");
    
            String weizhangJsonStr = JSONObject.toJSONString(weizhangJson);
            PictureRenderData pictureRenderData = PoiCommonUtil.generateEChart(phantomjsConfig.getJsPath(),phantomjsConfig.getEchartsImg(),phantomjsConfig.getHost(),phantomjsConfig.getPort(),phantomjsConfig.getUsername(),phantomjsConfig.getPassword(),weizhangJsonStr, imgName,width,height);
    
            return pictureRenderData;
        }

12）phantomjsConfig代码如下：

package com.zhxd.cloud.ksh.supervisor.constant;
    
    import lombok.Data;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.context.annotation.Configuration;
    
    @Data
    @Configuration
    public class PhantomjsConfig { 
    
        @Value("${phantomjs.host}")
        private String host;
    
        @Value("${phantomjs.port}")
        private int port;
    
        @Value("${phantomjs.username}")
        private String username;
    
        @Value("${phantomjs.password}")
        private String password;
    
        @Value("${phantomjs.jsPath}")
        private String jsPath;
    
        @Value("${phantomjs.echartsImg}")
        private String echartsImg;
    
        @Value("${phantomjs.dockerDocxPath}")
        private String dockerDocxPath;
    }

13）DetailTablePolicy文件需要继承DynamicTableRenderPolicy重写方法，这样可以自己定义单元格合并的规则，代码如下：

package com.zhxd.cloud.ksh.utils.poi;
    
    import com.deepoove.poi.data.RowRenderData;
    import com.deepoove.poi.policy.DynamicTableRenderPolicy;
    import com.deepoove.poi.policy.MiniTableRenderPolicy;
    import com.deepoove.poi.util.TableTools;
    import org.apache.poi.xwpf.usermodel.XWPFTable;
    import org.apache.poi.xwpf.usermodel.XWPFTableCell;
    import org.apache.poi.xwpf.usermodel.XWPFTableRow;
    import org.springframework.util.CollectionUtils;
    
    import java.util.List;
    
    public class DetailTablePolicy extends DynamicTableRenderPolicy { 
    
        // 填充数据所在行数
        int listsStartRow = 1;
    
        @Override
        public void render(XWPFTable table, Object data) { 
            if (null == data) return;
            List<RowRenderData> dataList = (List<RowRenderData>) data;
    
            if (!CollectionUtils.isEmpty(dataList)) { 
                table.removeRow(listsStartRow);
                // 循环插入行
                for (int i = dataList.size()-1; i >=0; i--) { 
                    RowRenderData row = dataList.get(i);
    
                    XWPFTableRow insertNewTableRow = table.insertNewTableRow(listsStartRow);
                    for (int j = 0; j < row.getCellDatas().size(); j++){ 
                        insertNewTableRow.createCell();
                    }
    
                    MiniTableRenderPolicy.Helper.renderRow(table, listsStartRow, row);
                }
                //处理合并，定义单元格合并规则
                for (int i=0;i<dataList.size();i++) { 
    
                    if (i%2 == 0) { 
                        TableTools.mergeCellsVertically(table, 0, i+1, i+2);
                        XWPFTableCell cell = table.getRow(i+1).getCell(0);
                        cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中
                    }
                }
            }
        }
    }

14）生成word效果如图  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56iL5bqP54y_dnPnoIHlhpw_size_20_color_FFFFFF_t_70_g_se_x_16 1]

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56iL5bqP54y_dnPnoIHlhpw_size_20_color_FFFFFF_t_70_g_se_x_16 2]

15）说明，我们的服务是部署在docker容器中的所以生成截图以及获取文件需要做路径映射，还有其他的兼容配置。下边是我生成表格和echarts截图所参考的博客，供大家对照学习，

https://blog.csdn.net/leftwukaixing/article/details/89449071 ，生成echarts截图 ；  
https://blog.csdn.net/qq\_26383975/article/details/112238802 ，生成动态表格

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56iL5bqP54y_dnPnoIHlhpw_size_19_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/db8431a4d9114aa49e6062dda0eaf687.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56iL5bqP54y_dnPnoIHlhpw_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/d17fc0a40f444c87bb87db3c3107bff3.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56iL5bqP54y_dnPnoIHlhpw_size_11_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/1a155f18c4244647b5597480b3d32fde.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56iL5bqP54y_dnPnoIHlhpw_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220829/f02dee8a6f04417f96949d1d03770d1d.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56iL5bqP54y_dnPnoIHlhpw_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220829/3182756b5e274363a5247773382d81b0.png