java poi导出图片到excel

雨点打透心脏的1/2处 2022-03-19 08:52 563阅读 0赞

### 导出效果图 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3draF9fXw_size_16_color_FFFFFF_t_70][]

### 代码实现 ###

**Controller**

/**
     * 导出志愿者/人才数据
     * @param talent_type
     * @return
     */
    @RequestMapping("/exportData")
    public void exportData(Integer talent_type, HttpServletResponse response) {
       String fileId = UUID.randomUUID().toString().replace("-", "");
       Map<String, Object> param = new HashMap<>() ;
       param.put("talent_type", talent_type) ;
       try {
          List<Map<String, Object>> volunteerMapList = volunteerService.getExportData(param) ;
          String rootPath = SysConfigManager.getInstance().getText("/config/sys/rootPath");
          String filePath = rootPath + "/" + fileId + ".xlsx" ;
          volunteerService.exportData(volunteerMapList, filePath) ;
          // 下载
          FileInputStream inputStream = null;
          try{
             //设置发送到客户端的响应内容类型
             response.reset();
             response.setContentLength((int) new File(filePath).length());
             response.setContentType("application/octet-stream");
             response.addHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode("文件名.xlsx", "UTF-8")+ "\"");
             //读取本地图片输入流
             inputStream = new FileInputStream(filePath);
             // 循环取出流中的数据
             byte[] b = new byte[1024];
             int len;
             while ((len = inputStream.read(b)) > 0)
                response.getOutputStream().write(b, 0, len);
          } finally{
             if(inputStream != null){
                inputStream.close();
             }
          }
          logger.debug("导出志愿者/人才数据成功！");
       } catch (Exception e) {
          e.printStackTrace();
          logger.error("导出志愿者/人才数据异常！");
       }
    }

**Service**

public void exportData(List<Map<String, Object>> volunteerMapList, String filePath) throws Exception {
         String[] alias = {"头像", "名称", "个人/团体", "志愿者/人才", "性别", "生日", "手机号",
                 "身份证", "省份", "市", "区/县", "详细地址", "邮箱", "政治面貌", "学历", "民族",
                 "职业", "团队人数", "艺术特长", "介绍"};
         String[] keys = {"photo", "name", "type", "talent_type", "sex", "birth_day", "mobile",
                 "idcard", "province", "city", "county", "address", "email", "political",
                 "education", "nation", "profession", "member_count", "art_spetiality", "content"};
         File file = new File(filePath);
         if (!file.exists()) file.createNewFile();
         FileOutputStream fileOutput = new FileOutputStream(file);
         XSSFWorkbook workbook = new XSSFWorkbook();
         int sheetSize = volunteerMapList.size() + 50;
         double sheetNo = Math.ceil(volunteerMapList.size() / sheetSize);
         String photoImgPath = SysConfigManager.getInstance().getText("/config/sys/rootPath") ;
         for (int index = 0; index <= sheetNo; index++) {
             XSSFSheet sheet = workbook.createSheet();
             workbook.setSheetName(index, "人才、志愿者" + index);
             XSSFRow row = sheet.createRow(0);
             sheet.setColumnWidth(0, 2048);
             XSSFCell cell;
             XSSFCellStyle cellStyle = workbook.createCellStyle();
             XSSFFont font = workbook.createFont();
             font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
             // 居中
             cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
             // 加粗
             cellStyle.setFont(font);
             //创建标题
             for (int i = 0; i < alias.length; i++) {
                 cell = row.createCell(i);
                 cell.setCellValue(alias[i]);
                 cell.setCellStyle(cellStyle);
             }
             int startNo = index * sheetSize;
             int endNo = Math.min(startNo + sheetSize, volunteerMapList.size());
             cellStyle = workbook.createCellStyle();
             // 居中
             cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
             cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
             // 写入各条记录,每条记录对应excel表中的一行
             for (int i = startNo; i < endNo; i++) {
                 int rowNum = i + 1 - startNo ;
                 row = sheet.createRow(rowNum);
                 Map<String, Object> map = (Map<String, Object>) volunteerMapList.get(i);
                 for (int j = 0; j < keys.length; j++) {
                     cell = row.createCell(j);
                     String key = keys[j] ;
                     if (key.equals("photo")){
                         sheet.addMergedRegion(new CellRangeAddress(i + 1,i + 1,i + 1,i + 1)) ;
                         // 头像
                         File photoFile = new File(photoImgPath + map.get(key)) ;
                         if (photoFile.exists()){
                             BufferedImage bufferedImage = ImageIO.read(photoFile) ;
                             ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                             ImageIO.write(bufferedImage, "jpg", byteArrayOut);
                             byte[] data = byteArrayOut.toByteArray();
                             XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch();
                             XSSFClientAnchor anchor = new XSSFClientAnchor(480, 30, 700, 250, (short)0, i + 1, (short) 1, i + 2);
                             drawingPatriarch.createPicture(anchor, workbook.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG));
                             sheet.setColumnWidth((short)500, (short)500);
                             row.setHeight((short)500);
                         } else {
                             cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                             cell.setCellValue("");
                         }
                     } else {
                         cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                         Object value = map.get(key);
                         cell.setCellValue(value == null ? "" : value.toString());
                         cell.setCellStyle(cellStyle);
                     }
                 }
             }
             // 设置列宽
            for (int i = 1; i < alias.length; i++)
                 sheet.autoSizeColumn(i);
             // 处理中文不能自动调整列宽的问题
    this.setSizeColumn(sheet, alias.length);
         }
         fileOutput.flush();
         workbook.write(fileOutput);
         fileOutput.close();
     }
    
     // 自适应宽度(中文支持)
     private void setSizeColumn(XSSFSheet sheet, int size) {
         for (int columnNum = 0; columnNum < size; columnNum++) {
             int columnWidth = sheet.getColumnWidth(columnNum) / 256;
             for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                 XSSFRow currentRow;
                 //当前行未被使用过
                 if (sheet.getRow(rowNum) == null) {
                     currentRow = sheet.createRow(rowNum);
                 } else {
                     currentRow = sheet.getRow(rowNum);
                 }
                 if (currentRow.getCell(columnNum) != null) {
                     XSSFCell currentCell = currentRow.getCell(columnNum);
                     if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                         int length = currentCell.getStringCellValue().getBytes().length;
                         if (columnWidth < length) columnWidth = length;
                     }
                 }
             }
             columnWidth = columnWidth * 256 ;
             sheet.setColumnWidth(columnNum, columnWidth >= 65280 ? 6000 : columnWidth);
         }
     }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3draF9fXw_size_16_color_FFFFFF_t_70]: /images/20220319/581336c0428a4bd385b05ecf0d6fd568.png