• 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 参数说明

  • 相关阅读:
    Ajax的工作原理
    ios 应用多语言自由切换实现
    开源码应用之Eclipse篇
    搜索引擎solr和elasticsearch
    字符串截取进阶
    nginx源代码分析--nginx模块解析
    C#网络编程系列文章(五)之Socket实现异步UDPserver
    mysql存储引擎的种类与差别(innodb与myisam)
    程序的记事本--log4net
    在海思hisiv100nptl平台上交叉编译并安装SRS
  • 原文地址:https://www.cnblogs.com/chenlin1990/p/8695763.html
Copyright © 2020-2023  润新知