实现思路:
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方式。