Excel导出带图片详解

Excel导出带图片详解

导出模板

一、引入的jar

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
         </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>

二、控制层

    @ApiOperation(value = "导出通行记录", notes="")
    @GetMapping("/export")
    @Log(action = "导出通行记录")
    public ResponseInfo export(@ModelAttribute RecDto recDto,HttpServletResponse response){
        recService.export(recDto,response);
        return ResponseInfo.ok();
    }

三、service层

void export(RecDto recDto,HttpServletResponse response);

四、impl层

 @Override
    public void export(RecDto recDto, HttpServletResponse response) {
        //查询数据
        if(StringUtils.isNotBlank(recDto.getStartTime())){
            recDto.setStartTime(String.valueOf(DateUtils.stringToDate(recDto.getStartTime(), DateUtils.FORMATTER_L).getTime()));
        }
        if(StringUtils.isNotBlank(recDto.getEndTime())){
            recDto.setEndTime(String.valueOf(DateUtils.stringToDate(recDto.getEndTime(), DateUtils.FORMATTER_L).getTime()));
        }
        List<RecVo> recVosList = recMapper.pageList(recDto);
        //excel标题
        String[] title={"ID","姓名","门禁名称","识别模式","识别结果","抓拍图片","识别时间"};
        //excel名称
        String fileName = "通行记录.xls";
        //sheet名
        String sheetName = "通行记录";
        if(recVosList.size() > 0){
            for (RecVo vo : recVosList) {
                //识别模式
                DicDto dicDto = new DicDto(vo.getRecoginMod(), Constant.DIC_REC_MOD_TYPE);
                List<Dic> dicList = dicService.list(dicDto);
                vo.setRecoginModStr(dicList.isEmpty() ? null : dicList.get(0).getValue());
                //识别结果
                dicDto = new DicDto(vo.getRegResult(), Constant.IDENTIFY_STATUS_TYPE);
                dicList = dicService.list(dicDto);
                vo.setRegResultStr(dicList.isEmpty() ? null : dicList.get(0).getValue());
                vo.setCreateTimeStr(DateUtils.timeStampToDate(vo.getCreateTime(), null));
            }
        }
        HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, recVosList,null,urlFile);
        //响应到客户端
        OutputStream os =null;
        try {
            this.setResponseHeader(response, fileName);
            os = response.getOutputStream();
            wb.write(os);
            os.flush();
        } catch (Exception e) {
            LOGGER.error("导出数据失败"+e);
        }finally {
            try {
                os.close();
            } catch (IOException e) {
               LOGGER.error("关闭流失败"+e);
            }
        }
    }

    /**
    *@Author songmo
    *@Date 2020/5/6 10:03
    *@Description 发送响应流方法
    */
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
               LOGGER.error("文件发送到客户端失败");
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

五、ExcelUtil工具类

 public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, List<RecVo> list, HSSFWorkbook wb,String urlFile) {
        urlFile=urlFile+"/webapps";
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 550);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        //设置居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //声明列对象
        HSSFCell cell = null;
        //创建标题
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 6000);
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            HSSFFont font = wb.createFont();
            font.setFontName("黑体");
            //设置字体大小
            font.setFontHeightInPoints((short) 15);
            style.setFont(font);
            cell.setCellStyle(style);
        }
        //抓拍照片
        BufferedImage bufferImg1 = null;
        try {
            //创建内容
            HSSFCellStyle styleCon = wb.createCellStyle();
            // 创建一个居中格式
            styleCon.setAlignment(HorizontalAlignment.CENTER);
            styleCon.setVerticalAlignment(VerticalAlignment.CENTER);
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 1);
                row.setHeight((short) 1000);
                RecVo recVo = list.get(i);
                //将内容按顺序赋给对应的列对象
                ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                //获取图片路径并且处理
                String baseImage = recVo.getPicUrl();
                //判断图片是否存在
                if (null != baseImage && baseImage.length() > 0) {
                    //linux上放开
                    baseImage =urlFile + baseImage;
                    if(new File(baseImage).exists()){
                        bufferImg1 = ImageIO.read(new File(baseImage));
                        ImageIO.write(bufferImg1,"jpg" , byteArrayOut1);
                        //图片一导出到单元格B6中
                        HSSFClientAnchor anchor1 = new HSSFClientAnchor(400, 30, 700, 220,
                                (short) 5, i + 1, (short) 5, i + 1);
                        patriarch.createPicture(anchor1, wb.addPicture(byteArrayOut1
                                .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                }
                //ID
                cell = row.createCell(0);
                cell.setCellValue(recVo.getRybm());
                cell.setCellStyle(styleCon);
                //姓名
                cell = row.createCell(1);
                cell.setCellValue(recVo.getName());
                cell.setCellStyle(styleCon);
                //门禁名称
                cell = row.createCell(2);
                cell.setCellValue(recVo.getDoorName());
                cell.setCellStyle(styleCon);
                //识别模式
                cell = row.createCell(3);
                cell.setCellValue(recVo.getRecoginModStr());
                cell.setCellStyle(styleCon);
                //识别结果
                cell = row.createCell(4);
                cell.setCellValue(recVo.getRegResultStr());
                cell.setCellStyle(styleCon);
                //识别结果
                cell = row.createCell(6);
                cell.setCellValue(recVo.getCreateTimeStr());
                cell.setCellStyle(styleCon);
            }
            return wb;
        } catch (Exception e) {
            LOGGER.error("导出通行记录数据失败" + e);
        }
        return wb;
    }

linux服务上一定要读取到当前图片的绝对路径！！！！

如：url:/data/tomcat-8.5.4/webapps/picture/d585115bfd374e8a9aafd501835018c9.jpeg

获取路径位置：

@Value("${catalina.home}")
private String urlFile;

导入带图片的功能：https://blog.csdn.net/qq_39381529/article/details/105866121

备注：多个图片可参考：

https://blog.csdn.net/qq_40100817/article/details/82799036?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-4&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-4