java导出带图片excel

1.导出excel文件效果

2.说明

        先要制作一个空的excel模板（也可以全由代码写成，使用excel模板的好处是，不用代码去实现复杂的排版，只需要填写内容就可），注意文件内容不能太大，否则在生成图片时可能会导致内存溢出（内容非常多的建议不要导出带图片的）。

		<!-- poi处理 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>

		<dependency>
			<groupId>com.github.ulisesbocchio</groupId>
			<artifactId>jasypt-spring-boot-starter</artifactId>
			<version>3.0.3</version>
		</dependency>

3.代码

        controller层

    // 导出用户购物车明细 带图片
    @RequestMapping("/exportusertruckdetail")
    public ResponseEntity<AbstractResource> exportUserTruckDetail(@RequestParam(value="data") String data)
    {
        logger.debug("ExportController.exportUserTruckDetail in, param:{}", data);
        try {
            JSONObject json = JSON.parseObject(data);
            String userId = json.getString("userId");
            Date startDate = json.getDate("startDate");
            Date endDate = json.getDate("endDate");
            String companyId = json.getString("companyId");
            boolean picFlag = json.getBooleanValue("picFlag");

            File excel = exportService.exportUserTruckDetail(companyId, userId, startDate, endDate, picFlag);
            return HttpUtils.export(excel);
        } catch (Exception e) {
            logger.error("errorCode : filemanage_exportusertruckdetail_sys,  errorMessages : 导出用户购物车明细异常！", e);
            return HttpUtils.exportFailed(e.getMessage());
        }
    }
    
    // HttpUtils.export
    public static ResponseEntity<AbstractResource> export(File file) {
        if (file == null) {
            return null;
        } else {
            HttpHeaders headers = new HttpHeaders();
            headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
            headers.add("Content-Disposition", "attachment; filename=" + file.getName());
            headers.add("Pragma", "no-cache");
            headers.add("Expires", "0");
            headers.add("Last-Modified", (new Date()).toString());
            headers.add("ETag", String.valueOf(System.currentTimeMillis()));
            return ((BodyBuilder)ResponseEntity.ok().headers(headers)).contentLength(file.length()).contentType(MediaType.parseMediaType("application/octet-stream")).body(new FileSystemResource(file));
        }
    }

    // HttpUtils.exportFailed
    public static ResponseEntity<AbstractResource> exportFailed(String message) {
        InputStream ins = new ByteArrayInputStream(message.getBytes());
        return ResponseEntity.ok().contentType(MediaType.parseMediaType("application/json;charset=UTF-8")).body(new InputStreamResource(ins));
    }

        service层

    @Override
    public File exportUserTruckDetail(String companyId, String userId, Date startDate, Date endDate, boolean picFlag) throws Exception {
        List<Map<String, Object>> detailList = unusualMapper.truckUserDetail(companyId, startDate, endDate, userId);
        List<JSONObject> excelDataList = new ArrayList<>(detailList.size());

        String sheetName = null;
        for (Map<String, Object> detailMap : detailList) {
            if(StringUtils.isBlank(sheetName)){
                sheetName = detailMap.get("phone").toString();
            }
            JSONObject retJson = ExcelUtil.getStandardSummary(detailMap.get("standardDetail").toString());
            detailMap.putAll(retJson);
            JSONObject excelData = JsonUtils.bean2JSON(detailMap);
            excelDataList.add(excelData);
        }
        if(StringUtils.isBlank(sheetName)){
            sheetName = "无";
        }

        return createUserTruckExcel(excelDataList, sheetName, startDate, endDate, picFlag);
    }


    private File createUserTruckExcel(List<JSONObject> excelDataList, String sheetName, Date startDate, Date endDate, boolean picFlag)
    {
        long startTime = System.currentTimeMillis();
        // 复制文件
        Date now = new Date();
        String basePath = new ApplicationHome(this.getClass()).getSource().getParentFile().getPath()
                + File.separator + "files" + File.separator;
        File exportFile = new File(basePath + "ymhc_user_truck_" + DateUtils.getStrDate(now,"yyyyMMdd_HHmmssSSS") + ".xlsx");
        try {
            Files.copy(new File(basePath + "ymhc_user_truck.xlsx").toPath(),
                    exportFile.toPath());

            FileInputStream fileInputStream = new FileInputStream(exportFile);
            XSSFWorkbook book = new XSSFWorkbook(fileInputStream);
            // 处理sheet名字
            XSSFSheet sheet = book.getSheetAt(0);
            book.setSheetName(0, sheetName);

            CellStyle style = book.createCellStyle();
            style.setBorderBottom(BorderStyle.THIN); //下边框
            style.setBorderLeft(BorderStyle.THIN);//左边框
            style.setBorderTop(BorderStyle.THIN);//上边框
            style.setBorderRight(BorderStyle.THIN);//右边框
            style.setVerticalAlignment(VerticalAlignment.CENTER); // 水平居中
            style.setAlignment(HorizontalAlignment.CENTER); // 垂直居中
            style.setWrapText(true);
            XSSFFont font = book.createFont();
            font.setFontHeightInPoints((short) 10);
            font.setFontName("宋体");
            style.setFont(font);

            CellStyle numStyle = book.createCellStyle();
            numStyle.setBorderBottom(BorderStyle.THIN); //下边框
            numStyle.setBorderLeft(BorderStyle.THIN);//左边框
            numStyle.setBorderTop(BorderStyle.THIN);//上边框
            numStyle.setBorderRight(BorderStyle.THIN);//右边框
            numStyle.setWrapText(true);
            XSSFDataFormat df = book.createDataFormat();
            numStyle.setDataFormat(df.getFormat("#,#0.00"));

            // 导出时间
            XSSFRow row0 = sheet.getRow(1);
            XSSFCell cell1 = row0.getCell(14);
            XSSFFont font10 = book.createFont();
            font10.setFontHeightInPoints((short) 11);
            font10.setFontName("宋体");
            font10.setBold(true);
            CellStyle style01 = book.createCellStyle();
            style01.setFont(font10);
            style01.setAlignment(HorizontalAlignment.CENTER);
            style01.setVerticalAlignment(VerticalAlignment.CENTER);
            String cell01 = DateUtils.getStrDate(startDate, "yyyy/MM/dd") + " 至 " + DateUtils.getStrDate(endDate, "yyyy/MM/dd");
            cell1.setCellValue(cell01);
            cell1.setCellStyle(style01);

            int ossPicTotal = 0; // excel图片
            XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch(); // 插入图片
            Map<String, String> localPicMap = new HashMap<>(); // 本地图片缓存 itemId localPath
            XSSFRow row;
            int orderRowLength = excelDataList.size();
            for (int j = 0; j < orderRowLength; j++) {
                JSONObject excelData = excelDataList.get(j);
                int rowIndex = j + 3;
                row = sheet.createRow(rowIndex);
                row.setHeight((short)(100*20));
                JSONObject parseDataJson = parseTruckUserData(excelData);
                // 1 序号
                row.createCell(0).setCellValue(j+1);
                // 2 店铺名
                row.createCell(1).setCellValue(excelData.getString("company_name"));
                // 3 产品款号
                row.createCell(2).setCellValue(excelData.getString("productCode"));
                // 4 商品图片
                int picIndex = 3;
                boolean insertPicFlag = false;
                String briefPath = excelData.getString("brief_path");
                String productId = excelData.getString("product_id");
                try{
                    row.createCell(picIndex).setCellValue("");
                    if(picFlag && ossPicTotal < 1000){ // 导出图片最大
                        String productLocalPic = basePath + "pic" + File.separator + productId + briefPath.substring(briefPath.lastIndexOf("."));
                        if(localPicMap.containsKey(productId)){
                            productLocalPic = localPicMap.get(productId);
                            logger.debug("本地已下载图片:{}", productLocalPic);
                        }else{
                            // 下载到本地，并缓存
                            String httpUrlPath = constantDataUtil.signaturePicture(briefPath,250, 90);
                            if(!ExcelUtil.downloadPic(httpUrlPath, productLocalPic, false)){
                                productLocalPic = ""; // 下载图片失败，缓存空，后续不再下载
                            }
                            localPicMap.put(productId, productLocalPic);
                        }

                        if(StringUtils.isNotBlank(productLocalPic)){// 插入图片
                            ExcelUtil.insertExcelPic(book, drawingPatriarch, rowIndex, picIndex, productLocalPic);
                            ossPicTotal++;
                            insertPicFlag = true;
                        }
                    }
                }catch (Exception e){
                    logger.error("{}图片插入失败!", briefPath);
                }
                if(!insertPicFlag){ // 导出有效期为24小时的图片链接
                    row.createCell(picIndex).setCellValue(constantDataUtil.signaturePicture(briefPath,800,90,24*60));
                }
                // 5 商品名称
                row.createCell(4).setCellValue(excelData.getString("product_name"));
                // 6 选购规格
                row.createCell(5).setCellValue(excelData.getString("selectStandardStr"));
                // 7 选购件数
                row.createCell(6).setCellValue(excelData.getString("goods_amount"));
                // 8 选购价格
                row.createCell(7).setCellValue(excelData.getString("showPrice"));
                // 9 所有颜色
                row.createCell(8).setCellValue(parseDataJson.getString("colorStr"));
                // 10 所有尺码
                row.createCell(9).setCellValue(parseDataJson.getString("sizeStr"));
                // 11 价格范围
                row.createCell(10).setCellValue(parseDataJson.getString("allPrice"));
                // 12 供应商名称
                row.createCell(11).setCellValue(excelData.getString("supplierName"));
                // 13 供应商地址
                row.createCell(12).setCellValue(excelData.getString("address"));
                // 14 商品状态
                row.createCell(13).setCellValue(ExcelUtil.getItemState(excelData.getString("item_state")));
                // 15 加购时间
                row.createCell(14).setCellValue(excelData.getString("create_date"));
                // 16 备注
                row.createCell(15).setCellValue("");
                for(int k = 0; k < 16; k++) {
                    row.getCell(k).setCellStyle(style);
                }
            }

            OutputStream os = new FileOutputStream(exportFile);
            os.flush();
            book.write(os);
            book.close();
            os.close();
            fileInputStream.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        logger.debug("导出文件完成，花费时间:{}", System.currentTimeMillis()-startTime);
        return exportFile;
    }

        ExcelUtil.insertExcelPic

    /**
     * <一句话功能简述> excel插入图片
     * <功能详细描述>
     * author: zhanggw
     * 创建时间:  2022/5/25
     * @param book poi book对象
     * @param drawingPatriarch 用于图片插入Represents a SpreadsheetML drawing
     * @param rowIndex 图片插入的单元格第几行
     * @param colIndex 图片插入的单元格第几列
     * @param localPicPath 本地图片路径
     */
    public static void insertExcelPic(XSSFWorkbook book, XSSFDrawing drawingPatriarch, int rowIndex, int colIndex, String localPicPath) throws IOException {
        // 获取图片后缀格式
        String fileSuffix = localPicPath.substring(localPicPath.lastIndexOf(".") + 1);
        fileSuffix = fileSuffix.toLowerCase();

        // 将图片写入到字节数组输出流中
        BufferedImage bufferImg;
        ByteArrayOutputStream picByteOut = new ByteArrayOutputStream();
        bufferImg = ImageIO.read(new File(localPicPath));
        ImageIO.write(bufferImg, fileSuffix, picByteOut);

        // 将图片字节数组输出流写入到excel中
        XSSFClientAnchor anchor = new XSSFClientAnchor(12, 3, 0, 0,
                (short) colIndex, rowIndex, (short) colIndex + 1, rowIndex + 1);
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        drawingPatriarch.createPicture(anchor, book.addPicture(picByteOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        picByteOut.close();
    }

          ExcelUtil.downloadPic

    public static boolean downloadPic(String httpUrlPath, String localPath, boolean forceDownload){
        try{
            logger.trace("开始下载文件{}到{}", httpUrlPath, localPath);
            String suffix = localPath.substring(localPath.lastIndexOf(".")+1);
            if(suffix.equalsIgnoreCase("jpg") || suffix.equalsIgnoreCase("png")){
                if(FileUtil.isExist(localPath)){
                    if(forceDownload){
                        new File(localPath).delete();
                    }else{
                        logger.trace("{}图片缓存，不下载!", localPath);
                        return true;
                    }
                }

                boolean ret = FileUtil.download(httpUrlPath, localPath);
                if(ret){
                    logger.trace("下载文件{}到{}成功", httpUrlPath, localPath);
                    return true;
                }else{
                    logger.debug("下载文件{}到{}失败", httpUrlPath, localPath);
                }
            }
        }catch (Exception e){
            logger.error("下载图片异常"+httpUrlPath, e);
        }
        return false;
    }

    // 通过url下载图片保存到本地
    public static boolean download(String urlString, String localPath) {
        try{
            // 构造URL
            URL url = new URL(urlString);
            // 打开连接
            URLConnection con = url.openConnection();
            // 输入流
            InputStream is = con.getInputStream();
            // 1K的数据缓冲
            byte[] bs = new byte[1024];
            // 读取到的数据长度
            int len;
            // 输出的文件流
            File file = new File(localPath);
            FileOutputStream os = new FileOutputStream(file, true);
            // 开始读取
            while ((len = is.read(bs)) != -1) {
                os.write(bs, 0, len);
            }
            // 完毕，关闭所有链接
            os.close();
            is.close();
            return true;
        }catch (Exception e){
            logger.error("下载图片到本地异常!", e);
        }
        return false;
    }