【Java代碼】——Java實現匯出Excel中的圖片和資料

前言

最近公司需要做匯出Excel中的圖片和資料，雖然不是什麼新的技術，但是我的業務需要圖片和資料對應，而每一行肯定會有資料，但是不一定有圖片，這時候就需要考慮如何保證圖片和資料是一一對應，圖片和資料走的是不同的方式，所以這時候就需要考慮讓擷取每一行圖片的row，在迴圈row的過程中，判斷row相等則取出圖片，否則就是當前該資料沒有圖片。 內容

Control層代碼

/**     *      * @param key     * @param file     * @param request     * @param response     *            void     * @description 將excel資料匯入到資料庫中     * @date 2018年4月10日     * @author 21     */    @RequestMapping("/importExcel")    @ResponseBody    public Map<String, Object> importExcel(HttpSession session, @RequestParam("lefile") MultipartFile file,            HttpServletRequest request, HttpServletResponse response) {        Map<String, Object> map = new HashMap<>();        int code = Constants.SUCCESS_CODE;        String message = Constants.SUCCESS_MGS;        boolean isExcel2003 = true;        String basePath = session.getServletContext().getRealPath("/zjk/zjzp/");        try {            String filename = file.getOriginalFilename();            if (isExcel2007(filename)) {                isExcel2003 = false;            }            int count = dataListService.importExcelData(file.getInputStream(), isExcel2003, basePath);            if (count <= 0) {                code = Constants.FAILTURE_CODE;                message = Constants.FAILTURE_MGS;            }        } catch (IOException e) {            code = Constants.FAILTURE_CODE;            message = Constants.FAILTURE_MGS;            e.printStackTrace();        }        map.put("code", code);        map.put("message", message);        return map;    }public static boolean isExcel2003(String filepath) {        return filepath.matches("^.+\\.(?i)(xls)$");    }    public static boolean isExcel2007(String filepath)            {        return filepath.matches("^.+\\.(?i)(xlsx)$");    }

Service層代碼

@Transactional    public int importExcelData(InputStream is, boolean isExcel2003, String basePath) {        int count = 0;        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();        try {            Workbook workbook = null;            Sheet sheet = null;            if (isExcel2003) {                workbook = new HSSFWorkbook(is);                sheet = workbook.getSheetAt(0);                sheetIndexPicMap = ExcelImageUtil.getSheetPictures03(0, (HSSFSheet) sheet, workbook);            } else {                workbook = new XSSFWorkbook(is); // 2007版本之上的                sheet = workbook.getSheetAt(0);                sheetIndexPicMap = ExcelImageUtil.getSheetPictures07(0, (XSSFSheet) sheet, workbook);            }            List<ZJModel> list = readExcelValue(workbook, sheet, basePath, sheetIndexPicMap);            // 儲存到資料庫中            count = dataListDao.insertZJList(list);        } catch (Exception e) {            e.printStackTrace();        }        return count;    }public List<ZJModel> readExcelValue(Workbook wb, Sheet sheet, String basePath,            Map<String, PictureData> sheetIndexPicMap) {        // 得到第一個sheet        List<ZJModel> zjModels = new ArrayList<ZJModel>();        try {            Integer totalCells = 0;            Integer totalRows = sheet.getPhysicalNumberOfRows();            if (totalRows > 1 && sheet.getRow(0) != null) {                totalCells = sheet.getRow(0).getPhysicalNumberOfCells();            }            for (int i = 1; i < totalRows; i++) {                Row row = sheet.getRow(i);                if (row == null) {                    continue;                }                ZJModel zjModel = new ZJModel();                String uuid = UUID.randomUUID().toString().replaceAll("-", "");                zjModel.setId(uuid);                if (sheetIndexPicMap.get("" + i + "") != null) {// 圖片資訊                    String picUuid = UUID.randomUUID().toString().replaceAll("-", "");                    PictureData pictureData = sheetIndexPicMap.get("" + i + "");                    byte[] buffer = pictureData.getData();                    String ext = pictureData.suggestFileExtension();// 擷取檔案拓展名；                    FileOutputStream outputStream = new FileOutputStream(basePath + picUuid + "." + ext);                    outputStream.write(buffer);                    outputStream.close();                    zjModel.setZjzp(picUuid + "." + ext);                }else{                    zjModel.setZjzp(Constants.DEFAULT_FILE_NAME);//預設圖片                }                for (int j = 0; j < totalCells; j++) {                    Cell cell = row.getCell(j);                    if (cell != null) {                        switch (j) {                        case 1:// 專家姓名                            zjModel.setName(cell.getStringCellValue());                            zjModel.setYwm(cell.getStringCellValue());                            break;                        case 2: //中文譯名                            zjModel.setZwym(cell.getStringCellValue());                            break;                        case 3:// 從事領域 //照片                            break;                        case 4: //單位                            zjModel.setDw(cell.getStringCellValue());                            break;                        case 5:// 專業 //職務/職稱                            zjModel.setZw(cell.getStringCellValue());                            break;                        case 6: //連絡方式                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {                                BigDecimal bigDecimal = new BigDecimal(cell.getNumericCellValue());                                String phone = bigDecimal.toString();                                zjModel.setPhone(phone);                            }                            break;                        case 7:  //固定電話                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {                                BigDecimal bigDecimal = new BigDecimal(cell.getNumericCellValue());                                String tel = bigDecimal.toString();                                zjModel.setTel(tel);                            }                            break;                        case 8:  //傳真                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {                                BigDecimal bigDecimal = new BigDecimal(cell.getNumericCellValue());                                String fax = bigDecimal.toString();                                zjModel.setFax(fax);                            }                            break;                        case 9: //郵箱                            zjModel.setEmail(cell.getStringCellValue());                            break;                        case 10: //社會安全號碼                            zjModel.setIdnumber(cell.getStringCellValue());                            break;                        case 11: //護照號/及有效證件                            zjModel.setHz(cell.getStringCellValue());                            break;                        case 12: //從事領域                            zjModel.setCsly(cell.getStringCellValue());                            break;                        case 13:  //學曆                            zjModel.setXl(cell.getStringCellValue());                            break;                        case 14:  //專業                            zjModel.setZy(cell.getStringCellValue());                            break;                        case 15:  //國籍                            zjModel.setGj(cell.getStringCellValue());                            break;                        case 16:   //性別                            zjModel.setSex(cell.getStringCellValue() == "男" ? 0 : 1);                            break;                        case 17:   //銀行卡號                            zjModel.setYhkh(cell.getStringCellValue());                            break;                        case 18:  //開戶行                            zjModel.setKhh(cell.getStringCellValue());                            break;                        case 19: //銀行代碼                            zjModel.setYhdm(cell.getStringCellValue());                            break;                        case 20:  //住址                            zjModel.setAddress(cell.getStringCellValue());                            break;                        case 21:                            zjModel.setBz(cell.getStringCellValue());                            break;                        }                    }                }                zjModels.add(zjModel);            }        } catch (Exception e) {            e.printStackTrace();        }        return zjModels;    }

由於在Excel2003和Excel2007版本擷取圖片的方法不同，所以抽出來兩種方法

public class ExcelImageUtil { public static Map<String, PictureData> getSheetPictures03(int sheetNum,HSSFSheet sheet,Workbook workbook){      Map<String,PictureData> sheetIndexPicMap=new HashMap<String, PictureData>();      @SuppressWarnings("unchecked")    List<HSSFPictureData> pictures=(List<HSSFPictureData>) workbook.getAllPictures();      if(pictures.size()>0){          for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {              HSSFClientAnchor anchor=(HSSFClientAnchor) shape.getAnchor();              if(shape instanceof HSSFPicture){                  HSSFPicture pic=(HSSFPicture) shape;                  int pictureIndex=pic.getPictureIndex()-1;                  HSSFPictureData picData=pictures.get(pictureIndex);                  String picIndex=String.valueOf(anchor.getRow1());                  sheetIndexPicMap.put(picIndex, picData);              }        }      }      return sheetIndexPicMap;  }  public static Map<String, PictureData> getSheetPictures07(int sheetNum,XSSFSheet sheet,Workbook workbook){      Map<String,PictureData> sheetIndexPicMap=new HashMap<String, PictureData>();      for(POIXMLDocumentPart dr:sheet.getRelations()){          if(dr instanceof XSSFDrawing){             XSSFDrawing drawing=(XSSFDrawing) dr;             List<XSSFShape> shapes=drawing.getShapes();             for (XSSFShape shape : shapes) {                XSSFPicture pic=(XSSFPicture) shape;                XSSFClientAnchor anchor=pic.getPreferredSize();               CTMarker ctMarker=anchor.getFrom();                String picIndex=String.valueOf(ctMarker.getRow());                sheetIndexPicMap.put(picIndex, pic.getPictureData());            }          }      }      return sheetIndexPicMap;   }  }

總結

這也是一次對代碼的積累，我相信每一次對代碼的總結都會對自己的能力有提升，感謝您的瀏覽，對於代碼有可以改進的進步，請不吝賜教。