笑话/java poi导出带图表的excel表格
实现思路zN5DuJkjAY4:3pBQPO9 1.由于前端通过echarts生成图形报表MDXpr6E5zZP5H6,yLd7B2GaVR2JjP所以后台没必要通过再弄一个插件生成一次图表; 2.将echarts生成的图片获取base64编码Myh9rNZ2FSp5,NUBlG将编码内容post参数传入后台; 3.后台接收到图片参数进行解码26VSSNvpW,Cc6nVQg92G生成本地图片; 4.利用poi创建的HSSFPatriarch对象.createPicture()方法将图片写入excel单元格中hN7Yg。57A9X2jP4LF42 导出方法代码F4dZG9H57d0bhz:WR88TFXX7WunM4  1 /**j5X12Xw P8a532hl4  2     *导出综合得分统计 NDWWC8crfXF e1Ygm  3     * @param fileNamea8gg8r78g KdbQs342fk2ku  4     * @param requestxSO7y 23aFA253  5     * @param responseUmJy177u3zu89 8WatzUK7Wu9  6     * @param headInfoMeZjrk8 uYwKiVOUv69W6  7     * @param dataListM52KhAAHCaZ 6JOX7N  8     * @param evaluationIdkL0yb7r5 D221yYB81QG  9     * @throws DeExceptioncrV46oJ6 ZTndf3o 10 */1TJ86PUs prxHS7WC 11 public void exportCompositeScore(String fileNamezBVT6O009,HwcR0bQe3z1B21 HttpServletRequest requestrcb38iUA13Zog,5VrYGUV7 HttpServletResponse responseLaPj9t5973Qe,MgC5aZkS09 f68oXQhfvu3T 12 String[] headInfo542w813G1Z,zBoc7oy9fEList> dataListhx3S4O,Ie5M32T4hHWWlint evaluationIdf0z1dzSq,p4PXHB5j0L2String imgUrlQI2z4H6RNjIH,TJaNFHQ69a3String message) throws DeException {ny8r6 A4GK0HC4UO10 13 try {I23Yg368p40Xw ItA9HwM3931wL 14 Evaluation evaluation = evaluationDao.getEvaluationInfo(evaluationId);ES60Q l4t33EQC0R 15 int completeNum = evaluatorUserDao.getEvalStatusNum(evaluationIdh655eM10C,ajcd5YznwStatus.COMPLETED.name());//获取答题结束的数量6C689Y6Y 7ZbrOXNR1kLPY6 16 int totalNum = evaluatorUserDao.getEvalTotalNum(evaluationId);//获取测评人总数dKrSKrS w0GGDLyS 17 HSSFWorkbook book = new HSSFWorkbook();// 创建Excel文件XPw4xK 9ZgTq2d4rJuNHr 18 HSSFSheet sheet = book.createSheet(fileName); // 创建一个工作薄7y7poY2x8X1Nd0 py8jAt86Zo 19 // 设置标题样式9UGzOOYCT6OHB5 ufkL6DCdB8Xv 20 HSSFCellStyle tileStyle = book.createCellStyle();nXT5FB982vPKS aSzeN0Rf 21 tileStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平布局XgOul8P5gqf:5276Q居中E75U8z uK58Eb881 22            tileStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);800M0f 80Q53U95Zecuq 23 tileStyle.setWrapText(true);f0xR1 IndzATyLq 24 HSSFFont nameRowFont = book.createFont();YvCueGY68 07DGHXepW65f3Q 25 nameRowFont.setFontName("微软雅黑");waAGP3QYn1 Yu55ha14Nm5 26 nameRowFont.setFontHeightInPoints((short) 8);// 设置字体大小UCEL9 2bBZ9A9X29J9c 27 nameRowFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示68HY8Ksz0p6 nsQHYybOLcA 28            tileStyle.setFont(nameRowFont);5o8ZXH7LGe s4wSP4mqd1w 29 Lell1 hDdFd 30 /*创建问卷标题行*/4s2G3TI4gARNgw 5TKG8c 31 CellStyle wrapTextStyle = book.createCellStyle(); //创建自动换行样式2r99KW226fFA HMvP566bhPOy 32 wrapTextStyle.setWrapText(true); //设置换行0Q8t39S 9245D 33 HSSFRow row0 = sheet.createRow(0);579EZ8l8Z ssIp1jLQL 34 row0.setHeight((short) 500);// 设置行高iU99w1Vu1z0FP ipOK9I 35 HSSFCell nameCell = null;Uw303YXX 2Xg8b00d58N 36 for(int i = 0; i < headInfo.length; i++){Gz5iahrU2b6 g6X045Q 37 nameCell = row0.createCell(i);x5iA3Q94XZ5j w66Ny5UZ6o 38                nameCell.setCellType(HSSFCell.CELL_TYPE_STRING);cN1k1q560W7OtY k35d5QdhaZ 39 if(i==0){kEvE17aE k14j5 40                    nameCell.setCellStyle(wrapTextStyle);fKVbCa7DZb hQw6IF 41 nameCell.setCellValue(new HSSFRichTextString(evaluation.getEvaluation_subject()));//问卷标题S1z92TS IJ08RPdpj05hfq 42                }mgi7s7e NY9hxt262 43            }vUoq4yc8Wvp49 9a0t0r1TQBeCx 44 CellRangeAddress nameCellRange = new CellRangeAddress(0144vuGS0O,2fU5Qe 01LF0VoAo,8qsy5Ceg 0Wm2THg6ehTPVKx,RrgqM14 headInfo.length-1);//标题合并单元格VIt75AOPl1KY6F 4ca9Upqv2g2r5 45            sheet.addMergedRegion(nameCellRange);kVfE6B7vVVXQ0 0aUfo2Et3I 46 y38O2047 00xuL18SL7085 47 /*创建问卷说明行*/Ph3fTE loKMo9ih 48 HSSFRow row1 = sheet.createRow(1);q58Rh6 P7u56 49 row1.setHeight((short) 500);// 设置行高lj8ShBti0K 79R0U 50 String desc = "测评人员dhTkx8UFFzM4:c1529AykT6o9ND"+totalNum+"人"+" 已完成u85Q7:rGxZOs"+completeNum+"人";b92MiA76W4vU QC4lw16 51 for(int i = 0; i < headInfo.length; i++){12LLDCqXdi8 fuj77I 52 HSSFCell cell = row1.createCell(i);ACCcM2jA3kE6 K2T39bi1k8xO4y 53                cell.setCellType(HSSFCell.CELL_TYPE_STRING);bvgCiC17KPY 6PyqephmPS69 54 if(i==0){RnL2CsK8M Z2u98zN7gK 55 cell.setCellValue(new HSSFRichTextString(desc));DPPLftqCu 4E60aP8W3H0h3 56                }L9nx9xxyJ MSYwhCG8u 57            }2gnsQt5m1h J8GzOwWZ 58 CellRangeAddress summaryCellRange = new CellRangeAddress(1ld1BR7ZZx9LDf,s9w53u 16k7jK,4f01n6A6744 0eN15aBn,Kg0Q8Rbz28TP headInfo.length-1);//说明合并单元格4cHHgWR60u1 H2TYoE3v9V 59            sheet.addMergedRegion(summaryCellRange);3kBnHuEHR CCsvgmCb2yvixc 60 //加权提示行O16OJ21I 7F38QwXfq8K5Z 61 HSSFRow row2 = sheet.createRow(2);J6XgQ039 g450vBFh3 62 HSSFCell messageCell = null;J9dc6Ou8fIav 70Xx9Tik2T1wps 63 for(int i = 0; i < headInfo.length; i++){E1yeDEJjNI kQ26I1lgx8W 64 messageCell = row2.createCell(i);XkpJsKk1C sc5Z8T5U 65                messageCell.setCellType(HSSFCell.CELL_TYPE_STRING);IdLtZ02W3j0gM iLcA6tB09 66 if(i==0){3D5C5lx1NJ4 A8U8lNqdR 67                    messageCell.setCellStyle(wrapTextStyle);APi1Ese qDg6U52tVs 68 messageCell.setCellValue(message);//问卷标题H3bO2CCPHsZqb whb77mubi30Z 69                }eYS2bzM5 17D0910y3u1B 70            }JZzuVsrH4qfg70 K15qK04f1sS 71 sheet.addMergedRegion(new CellRangeAddress(2kKM0VG7gvUa,qS1uy0 2sRpX69354O,jHh083t1yXs 0t25Kb,H802j headInfo.length-1));//合并单元格8Gh3MHGP91T3;vIWqF06611Ts起始行4Ho4DsL2442O,Ig1224nAZ4yA5a结束行dI19T4lEc,R8bN86N起始列Q2Y4E4bX,dCxWnv5CM6q结束列hzKhm PzZ1p3m7 72 if(StringUtils.isEmpty(message)){1wcZ680 u06Tum924Kq 73 row2.setZeroHeight(true);//隐藏行6cTFJ5 PJJXLS89Kq 74            }74Cyy 0vx273EYEvNp71 75 // EXCEL第四行表格标题IYm0hCj467ZWM5 6F1Mqi0737c 76 HSSFRow row3 = sheet.createRow(3);ggHAgoE 9Z1ASk0 77 HSSFCell cell = null;o1y5OJCnYz7lQW B7LNHX0ZRK 78 row3.setHeight((short) 400);// 设置行高65Mm34j6X4vd3v QQ34aLlh 79 for (int i = 0; i < headInfo.length; i++) {Fm9n94F1Vc9Um M3mr11go4i1e 80 /*设置列宽度*/3Q4v8Mm9u92 Sxn5lkBW 81 if(i==1){3E657sAIM4U9 XBol0lM3 82 sheet.setColumnWidth(ipsL7M7e47,bM8W773r8RAg19 20 * 256);jH16PWxq G9FQ84H5nTz 83 }else if(i>1){2D5K081G4ucwc6 1bn52qdd0Oz 84 sheet.setColumnWidth(iPpz3TJu78hCL3,V7zq1N1 30 * 256);Q7Xx01K 4UC4wUtYAYQ6G 85 }else{E7SvhZ FILZxSh9 86 sheet.setColumnWidth(ihbfO7E5oofT,5437D 12 * 256);76ea362 SS83IlGzNJzpzq 87                }YW0gp3d 8JG2zL54 88 cell = row3.createCell(i);LFRGXnP 82Wf8dCr692EY5 89                cell.setCellValue(headInfo[i]);u5yUwt98Yo I5oJ4QkP 90                cell.setCellStyle(tileStyle);MROMTOVjDN EOl92rL8 91            }Q4Z233y wqRnjUV4R2QJr4 92 // EXCEL正文数据77RHQGxvwGvMbi Gf1Qn6rd 93 Map answerObj = null;t53O6rY 58e2V 94 HSSFRow dataRow = null;Pz3ds2u1ZnJ iRNXo8QtWU 95 Cell dataCell = null;D0Gjh5U61N 7bJgn34rkR6d 96 for (int j = 0; j < dataList.size(); j++) {O9k1t I2VwZJ66791 97 answerObj = dataList.get(j);//每一行的记录uJ8wJP4Ds20 hit3k0316 98 dataRow = sheet.createRow(j+4);T4flo6J EniN619q5SD2 99 dataCell = dataRow.createCell(0);//工号IZogpE9p76Oh0w 03l0n7xe2100 dataCell.setCellValue(answerObj.get("employee_id")+"");9RAfyf7 iA0N8Dy101 dataCell = dataRow.createCell(1);//姓名581rY03YIO j1FV43xwlOjV9Z102 dataCell.setCellValue(answerObj.get("user_name")+"");3Rq59m86f GUMT5528103 dataCell = dataRow.createCell(2);//部门48u72v9151 ZQcR7IL104 dataCell.setCellValue(answerObj.get("department")+"");VW92u3 d7IDn53105 dataCell = dataRow.createCell(3);//个人平均分n0FPo5Gn s0quagK3S106 dataCell.setCellValue(Tools.formatDouble(Double.parseDouble(answerObj.get("personal_score")+"")));CLqVioWra ZFH5P107 dataCell = dataRow.createCell(4);//测评平均分100R7h 51SB043TBi9JI2108 dataCell.setCellValue(Tools.formatDouble(Double.parseDouble(answerObj.get("avg_score")+"")));9H1E9HC59636 i9Jlf05109 dataCell = dataRow.createCell(5);//分差值sU8760Ux7NUn5 dQJP4hRv110 dataCell.setCellValue(Tools.formatDouble(Double.parseDouble(answerObj.get("difference_val")+"")));NkT2j 80eYU7111            }09c6UQ4DXmDyQ T73rcYUT112 /*生成图表*/63JRZC7GXVV3 uFNoA2113 if(StringUtils.isNotEmpty(imgUrl)) {m7eP8hCB3E vF6hPW4hQ09o4u114 String[] imgUrlArr = imgUrl.split("base649cvh53R3vkN6Bp,1g9m3e4yAL");//拆分base64编码后部分Q9CM2d84395 B8M1r115 org.bouncycastle.util.encoders.Base64 decode = new org.bouncycastle.util.encoders.Base64();P4Wnz9AU y8DU823116 byte[] buffer = decode.decode(imgUrlArr[1]);iLnU9Q5z1P 04KHceQTJ5RBKG117 String picPath = request.getRealPath("")+ "/"+ UUID.randomUUID().toString() +".png"; AJGZHBKHwjh0V 580wKC8J4WX118 File file = new File(picPath);//图片文件Ar22V7N4ByAT 6Dw9wF119 //生成图片22Ytb79Klu26 LC6mP9yhqe560g120 OutputStream out = new FileOutputStream(file);//图片输出流   hsy21Z9I9 VAd2I121                out.write(buffer);4Jn037Jju 041of8lI2C0T122 out.flush();//清空流QI9iH b90ij7123 out.close();//关闭流BXA9r0n19PY hVQgQ67N06124 ByteArrayOutputStream outStream = new ByteArrayOutputStream(); // 将图片写入流中8eaW3nqRuN 0bU8OSMlIr3t125 BufferedImage bufferImg = ImageIO.read(new File(picPath));c31Mn2kd3rrIN l65brkd68126 ImageIO.write(bufferImgN00Z97,H3HMSg8 "PNG"RE33Igxq7i,4Xvm9Z7WK7a outStream); // 利用HSSFPatriarch将图片写入EXCELF32yNX 8gG19y95f713j127 HSSFPatriarch patri = sheet.createDrawingPatriarch();bMyM0Ben84m Mc3yAf1J6M58128 HSSFClientAnchor anchor = new HSSFClientAnchor(008xr237cRb,Z6E5F2h 08gc0UgIOsR,9UnHT517 01AmL9N7PMs6,itHq1pko7 0lI8u3E5N7I6y,lLpKI(short) 0X1gAXq,a03x8 dataList.size() + 579pMJ,n5hAW37r75 (short) 68w2DI375DsK6,JJHRiVwKhw16aR dataList.size() + 35);kiLr2t w0p8vCU82D7129                patri.createPicture(anchorv5PuVSJ,L6661eJt9R book.addPicture(outStream.toByteArray()U23U59HDeF,90O2kK8J HSSFWorkbook.PICTURE_TYPE_PNG));DUfcs26 TB2uA6i0130 if(file.exists()){WSuRU5z5H yRV9w88s131 file.delete();//删除图片pazQ56Qb 79fM7rZ0iEeH132                }i07PlLwk55j s3Ju9Rlj133            }GlncXo3 Q2lx9Lvmkyqc134 8WgiVD3Gh1e1u8 2VrKN4U135 //输出excel文件名14o24Jb8L83s17 U4Tic3Fs1136 fileName = Tools.processFileName(requesto9bpZHq32PoEgN,52h54s6FDY3a6 fileName);// 不同浏览器文件名乱码解决Hfn9th50Qa2 Kh34d9137 OutputStream os = response.getOutputStream();// 取得输出流X6IMK0cI 4amZO138 response.reset();// 清空输出流CUGp7MBrU5 72bOr16139 response.setHeader("Connection"G0Kf0Tq4j4rfe,F1f4g3SJM5597c "close");hlP36X01ll34l d9QlLVWVL6Nu9K140 response.setHeader("Content-Type"G0nS7eWyEqyg,98I4opi7 "application/octet-stream");1KI8gK2Awv XFlu3zYy4V141 response.setHeader("Content-Disposition"JXpblheMwW083,wRRDN8z9Pve "attachment;filename="+fileName);9m0WL01Q2 Exs0Y3qttyt8L0142            book.write(os);dxC0dA M1js2t6aJHT143            os.flush();C15Bc9El864 b21JGw9144            os.close();t3py554y8j 90eZ57T145 } catch (Exception e) {0WjE9IuA5lj15d 0PW0k1146 logger.error("tid={} | 导出综合得分表出现异常"20F3I9HGGOb0,LM8QJzwl ContextClient.getTid()AC14v5,8Ih0pDsJ2rV4L e);WiJI1V2O n4A1rF7uM109147 throw new ServiceException(ServiceExceptionConstant.SYSTEM_EXCEPTION);wmwj9218qm2x 5W08Ip911u3148 }finally{xShS5W xr809T4UPcH0t149 r8gDbU5A8Kq7Hb tCis6b150        }2XyP7782AY77 nRYN3B9F3b151 } 方法说明9uArrcb4vV45:Eitwx0t HSSFClientAnchor用于创建一个新的端锚199xo0,ZopQJ并设置锚的左下和右下坐标FR80m2ao0FFY,82G6TCuqE096KY用于图片插入GW5E9AoZ,zDu0dy5C4RnA3L画线等操作8h99nRCa2I。36xkY45vV HSSFClientAnchor(int dx18oBxS9uz9r3jR,wvPDEY int dy198KSnpGZS0nV7o,Q6d4971MZAF int dx20PL6k7d3jXd,69yR8xLd42Exc int dy27v46DZTzeRyxUS,VPmR1k9 short col1WgLi88d,Zf5BLK45OEO int row1FJhjDjQJB5ot3,EdPSR short col20LNCcQSDH,cTF5ne int row2) dx1 dy1 起始单元格中的xKEparI,DSAS9Hg4Dxky坐标. dx2 dy2 结束单元格中的x4JwHgS13,2mr9Ay坐标 col1dH4jWP,9TRS4row1 指定起始的单元格pSEa23qO7XAUp7,d36jc21t下标从0开始 col215qM1d368,k69OAsrow2 指定结束的单元格 9q2x7oZ,q9sw0N15下标从0开始 来张图更容易说明: 最后szHx2Z0a,3F702U38X看下生成的excel效果7Dp1p:3Vol33vH8uNJP 总结一下kBT5j0:m566z 1.优点b5653Fc:19KsS开发快捷ZnWvT9Nc,99xHl59前后端图片显示样式一致 2.缺点522A6xMgUMgWB7:rG9V7qF75x不适合多图片w9024,DyJE4xbase64编码参数太长Oxb66W6ITxSbdq,rIkfO830Y30请求参数太大k75u0A1,30e1Q只能使用post方式