java poi导出带图表的excel表格

实现思路：

1.由于前端通过echarts生成图形报表，所以后台没必要通过再弄一个插件生成一次图表;

2.将echarts生成的图片获取base64编码，将编码内容post参数传入后台;

3.后台接收到图片参数进行解码，生成本地图片;

4.利用poi创建的HSSFPatriarch对象.createPicture()方法将图片写入excel单元格中。

导出方法代码：

  1 /**
  2      *导出综合得分统计 
  3      * @param fileName
  4      * @param request
  5      * @param response
  6      * @param headInfo
  7      * @param dataList
  8      * @param evaluationId
  9      * @throws DeException
 10      */
 11     public void exportCompositeScore(String fileName, HttpServletRequest request, HttpServletResponse response,
 12             String[] headInfo,List<Map<String,Object>> dataList,int evaluationId,String imgUrl,String message) throws DeException {
 13         try {
 14             Evaluation evaluation = evaluationDao.getEvaluationInfo(evaluationId);
 15             int completeNum = evaluatorUserDao.getEvalStatusNum(evaluationId,Status.COMPLETED.name());//获取答题结束的数量
 16             int totalNum = evaluatorUserDao.getEvalTotalNum(evaluationId);//获取测评人总数
 17             HSSFWorkbook book = new HSSFWorkbook();// 创建Excel文件
 18             HSSFSheet sheet = book.createSheet(fileName); // 创建一个工作薄
 19             // 设置标题样式
 20             HSSFCellStyle tileStyle = book.createCellStyle();
 21             tileStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平布局：居中
 22             tileStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
 23             tileStyle.setWrapText(true);
 24             HSSFFont nameRowFont = book.createFont();
 25             nameRowFont.setFontName("微软雅黑");
 26             nameRowFont.setFontHeightInPoints((short) 8);// 设置字体大小
 27             nameRowFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
 28             tileStyle.setFont(nameRowFont);
 29             
 30             /*创建问卷标题行*/
 31             CellStyle wrapTextStyle = book.createCellStyle(); //创建自动换行样式
 32             wrapTextStyle.setWrapText(true); //设置换行
 33             HSSFRow row0 = sheet.createRow(0);
 34             row0.setHeight((short) 500);// 设置行高
 35             HSSFCell nameCell = null;
 36             for(int i = 0; i < headInfo.length; i++){
 37                 nameCell = row0.createCell(i);
 38                 nameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
 39                 if(i==0){
 40                     nameCell.setCellStyle(wrapTextStyle);
 41                     nameCell.setCellValue(new HSSFRichTextString(evaluation.getEvaluation_subject()));//问卷标题
 42                 }
 43             }
 44             CellRangeAddress nameCellRange  = new CellRangeAddress(0, 0, 0, headInfo.length-1);//标题合并单元格
 45             sheet.addMergedRegion(nameCellRange);
 46             
 47             /*创建问卷说明行*/
 48             HSSFRow row1 = sheet.createRow(1);
 49             row1.setHeight((short) 500);// 设置行高
 50             String desc = "测评人员："+totalNum+"人"+"   已完成："+completeNum+"人";
 51             for(int i = 0; i < headInfo.length; i++){
 52                 HSSFCell cell = row1.createCell(i);
 53                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
 54                 if(i==0){
 55                     cell.setCellValue(new HSSFRichTextString(desc));
 56                 }
 57             }
 58             CellRangeAddress summaryCellRange  = new CellRangeAddress(1, 1, 0, headInfo.length-1);//说明合并单元格
 59             sheet.addMergedRegion(summaryCellRange);
 60             //加权提示行
 61             HSSFRow row2 = sheet.createRow(2);
 62             HSSFCell messageCell = null;
 63             for(int i = 0; i < headInfo.length; i++){
 64                 messageCell = row2.createCell(i);
 65                 messageCell.setCellType(HSSFCell.CELL_TYPE_STRING);
 66                 if(i==0){
 67                     messageCell.setCellStyle(wrapTextStyle);
 68                     messageCell.setCellValue(message);//问卷标题
 69                 }
 70             }
 71             sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, headInfo.length-1));//合并单元格；起始行，结束行，起始列，结束列
 72             if(StringUtils.isEmpty(message)){
 73                 row2.setZeroHeight(true);//隐藏行
 74             }
 75             // EXCEL第四行表格标题
 76             HSSFRow row3 = sheet.createRow(3);
 77             HSSFCell cell = null;
 78             row3.setHeight((short) 400);// 设置行高
 79             for (int i = 0; i < headInfo.length; i++) {
 80                 /*设置列宽度*/
 81                 if(i==1){
 82                     sheet.setColumnWidth(i, 20 * 256);
 83                 }else if(i>1){
 84                     sheet.setColumnWidth(i, 30 * 256);
 85                 }else{
 86                     sheet.setColumnWidth(i, 12 * 256);
 87                 }
 88                 cell = row3.createCell(i);
 89                 cell.setCellValue(headInfo[i]);
 90                 cell.setCellStyle(tileStyle);
 91             }
 92             // EXCEL正文数据
 93             Map<String,Object> answerObj = null;
 94             HSSFRow dataRow = null;
 95             Cell dataCell = null;
 96             for (int j = 0; j < dataList.size(); j++) {
 97                 answerObj = dataList.get(j);//每一行的记录
 98                 dataRow = sheet.createRow(j+4);
 99                 dataCell = dataRow.createCell(0);//工号
100                 dataCell.setCellValue(answerObj.get("employee_id")+"");
101                 dataCell = dataRow.createCell(1);//姓名
102                 dataCell.setCellValue(answerObj.get("user_name")+"");
103                 dataCell = dataRow.createCell(2);//部门
104                 dataCell.setCellValue(answerObj.get("department")+"");
105                 dataCell = dataRow.createCell(3);//个人平均分
106                 dataCell.setCellValue(Tools.formatDouble(Double.parseDouble(answerObj.get("personal_score")+"")));
107                 dataCell = dataRow.createCell(4);//测评平均分
108                 dataCell.setCellValue(Tools.formatDouble(Double.parseDouble(answerObj.get("avg_score")+"")));
109                 dataCell = dataRow.createCell(5);//分差值
110                 dataCell.setCellValue(Tools.formatDouble(Double.parseDouble(answerObj.get("difference_val")+"")));
111             }
112             /*生成图表*/
113             if(StringUtils.isNotEmpty(imgUrl)) {
114                 String[] imgUrlArr = imgUrl.split("base64,");//拆分base64编码后部分
115                 org.bouncycastle.util.encoders.Base64 decode = new org.bouncycastle.util.encoders.Base64();
116                 byte[] buffer = decode.decode(imgUrlArr[1]);
117                 String picPath = request.getRealPath("")+ "/"+ UUID.randomUUID().toString() +".png"; 
118                 File file = new File(picPath);//图片文件
119                 //生成图片
120                 OutputStream out = new FileOutputStream(file);//图片输出流   
121                 out.write(buffer);
122                 out.flush();//清空流
123                 out.close();//关闭流
124                 ByteArrayOutputStream outStream = new ByteArrayOutputStream(); // 将图片写入流中
125                 BufferedImage bufferImg = ImageIO.read(new File(picPath));
126                 ImageIO.write(bufferImg, "PNG", outStream); // 利用HSSFPatriarch将图片写入EXCEL
127                 HSSFPatriarch patri = sheet.createDrawingPatriarch();
128                 HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) 0, dataList.size() + 5, (short) 6, dataList.size() + 35);
129                 patri.createPicture(anchor, book.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
130                 if(file.exists()){
131                     file.delete();//删除图片
132                 }
133             }
134             
135             //输出excel文件名
136             fileName = Tools.processFileName(request, fileName);// 不同浏览器文件名乱码解决
137             OutputStream os = response.getOutputStream();// 取得输出流
138             response.reset();// 清空输出流
139             response.setHeader("Connection", "close");
140             response.setHeader("Content-Type", "application/octet-stream");
141             response.setHeader("Content-Disposition", "attachment;filename="+fileName);
142             book.write(os);
143             os.flush();
144             os.close();
145         } catch (Exception e) {
146             logger.error("tid={} | 导出综合得分表出现异常", ContextClient.getTid(), e);
147             throw new ServiceException(ServiceExceptionConstant.SYSTEM_EXCEPTION);
148         }finally{
149             
150         }
151     }

方法说明：

HSSFClientAnchor用于创建一个新的端锚，并设置锚的左下和右下坐标,用于图片插入，画线等操作。

HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)

dx1 dy1 起始单元格中的x,y坐标.

dx2 dy2 结束单元格中的x,y坐标

col1,row1 指定起始的单元格，下标从0开始

col2,row2 指定结束的单元格，下标从0开始

来张图更容易说明:

技术分享

最后，看下生成的excel效果：

总结一下：

1.优点：开发快捷，前后端图片显示样式一致

2.缺点：不适合多图片，base64编码参数太长，请求参数太大，只能使用post方式。

HSSFClientAnchor 参数说明