java 實現excel導入excel包含數據與圖片 并將導入過程中不符合要求的數據封裝到excel

最近在開發中需要一個excel上傳的功能，其中，excel就包含了數據和圖片，經過查詢與了解，代碼實現如下，圖片與數據需要單獨獲取。

1.利用下面兩個方法獲取圖片
/**

• 獲取圖片和位置 (xls)

  • @param sheet
  • @return

  • @throws IOException
    */
    public Map<String, PictureData> getPictures1(HSSFSheet sheet) throws IOException {
    Map<String, PictureData> map = new HashMap<String, PictureData>();
    List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
    for (HSSFShape shape : list) {
    if (shape instanceof HSSFPicture) {
    HSSFPicture picture = (HSSFPicture) shape;
    HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
    PictureData pdata = picture.getPictureData();
    String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行號-列號
    map.put(key, pdata);
    logger.info("圖片類型" + picture.getPictureData().suggestFileExtension());
    }
    }
    return map;
    }
    xls格式的獲取圖片，如果excel中不存在圖片的話會報空指針異常，需判斷excel中有無圖片再調用此方法，List<HSSFPictureData> pictures = (List<HSSFPictureData>) wookbook.getAllPictures();通過判斷list是否為空，判斷excel中有無圖片

    /**

  • 獲取圖片和位置 (xlsx)
  • @param sheet
  • @return
  • @throws IOException
    */
    public Map<String, PictureData> getPictures2(XSSFSheet sheet) throws IOException {
    Map<String, PictureData> map = new HashMap<String, PictureData>();
    List<POIXMLDocumentPart> list = sheet.getRelations();
    for (POIXMLDocumentPart part : list) {
    if (part instanceof XSSFDrawing) {
    XSSFDrawing drawing = (XSSFDrawing) part;
    List<XSSFShape> shapes = drawing.getShapes();
    for (XSSFShape shape : shapes) {
    XSSFPicture picture = (XSSFPicture) shape;
    XSSFClientAnchor anchor = picture.getPreferredSize();
    CTMarker marker = anchor.getFrom();
    String key = marker.getRow() + "-" + marker.getCol();
    map.put(key, picture.getPictureData());
    logger.info("圖片類型" + picture.getPictureData().suggestFileExtension());
    }
    }
    }
    return map;
    }

2,獲取數據和圖片返回

/**
 * 獲取excel數據 包括圖片
 *
 * @param file
 * @return
 * @throws IOException
 */
public Map<String, Object> getDataFromExcel(MultipartFile file) throws IOException {
    //文件名
    String fileName = null;
    //文件類型
    String fileType = null;
    //文件輸入流
    InputStream fis = null;
    Workbook wookbook = null;
    Sheet sheet = null;

    Map<String, Object> result = null;
    //圖片map
    List<Map<String, byte[]>> imgList = null;
    try {
        //獲取一個絕對地址的流
        fis = file.getInputStream();
    } catch (Exception e) {
        e.printStackTrace();
    }

    try {
        //2003版本的excel，用.xls結尾
        wookbook = new HSSFWorkbook(fis);//得到工作簿

    } catch (Exception ex) {
        //ex.printStackTrace();
        try {
            //2007版本的excel，用.xlsx結尾
            fis = file.getInputStream();
            wookbook = new XSSFWorkbook(fis);//得到工作簿
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    Map<String, PictureData> maplist = null;
    fileName = file.getOriginalFilename();
    fileType = fileName.substring(fileName.lastIndexOf("."));
    sheet = wookbook.getSheetAt(0);
    // 判斷用07還是03的方法獲取圖片
    List<HSSFPictureData> pictures = (List<HSSFPictureData>) wookbook.getAllPictures();
    //如果有圖片就獲取圖片
            if (pictures != null && pictures.size() > 0) {
        if (fileType.equals(MessageConstant.EXCEL_2003L)) {
            maplist = getPictures1((HSSFSheet) sheet);
        } else if (fileType.equals(MessageConstant.EXCEL_2007U)) {
            maplist = getPictures2((XSSFSheet) sheet);
        }
    }
    //得到一個工作表

    //獲得表頭
    Row rowHead = sheet.getRow(0);
    logger.info("" + rowHead.getFirstCellNum() + "" + rowHead.getLastCellNum());

   //判斷表頭是否正確
        //        if (rowHead.getPhysicalNumberOfCells() != 5) {
        //            System.out.println("表頭的數量不對!");
        //        }

    //獲得數據的總行數
    int totalRowNum = sheet.getLastRowNum();
    logger.info("excel總行數：" + totalRowNum);

    //要獲得屬性
    Cell cell = null;
    List<List<Object>> list = null;
    //獲得所有數據
    try {
        result = new HashMap<>();
        list = new ArrayList<>();
        for (int i = 1; i <= totalRowNum; i++) {
            //獲得第i行對象
            Row row = sheet.getRow(i);
            if(row == null){
                continue;
            }

            List<Object> li = new ArrayList<Object>();
                            //避免前幾個字段為空 空數據沒被封裝的情況  
            if (row == null || row.getFirstCellNum() == i) {
                li.add("");
            }
            //遍歷所有的列
            for (int y = rowHead.getFirstCellNum(); y < rowHead.getLastCellNum() - 2; y++) {
                cell = row.getCell(y);
                li.add(this.getCellValue(cell));
            }
            logger.info("liSize:" + li.size());
            //跟模板設置有關
            while (li.size() < 6) {
                li.add("");
            }
                            //java獲取excel會出現一個全部數據為空，還統計進來的
            if(!"".equals(String.valueOf(li.get(0))) || !"".equals(String.valueOf(li.get(1))) && !"".equals(String.valueOf(li.get(2)))
                    || !"".equals(String.valueOf(li.get(3))) || !"".equals(String.valueOf(li.get(4))) || !"".equals(String.valueOf(li.get(5)))) {
                list.add(li);
            }
            result.put("list", list);
        }
        result.put("piclist", maplist);
        logger.info("" + result);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return result;
}

    /**
 * 時間轉換
 *
 * @param cell
 * @return
 */
public Object getCellValue(Cell cell) {
    Object value = null;
    DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化
    DecimalFormat df2 = new DecimalFormat("0.00");  //格式化數字

    if (cell == null) {
        return "";
    }
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            value = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                value = df.format(cell.getNumericCellValue());
            } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                value = sdf.format(cell.getDateCellValue());
            } else {
                value = df2.format(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            value = "";
            break;
        default:
            break;
    }
    return value;
}

上面的方法就已經將圖片和數據分開獲取到了，分別為Map類型maplist的圖片數據 和 List類型的非圖片數據
下面的方法實現獲取數據的處理已經數據圖片驗證并將不合要求的數據封裝起來。

@Override
@Transactional
     public HashMap<String, Object> excelCreateLeaseBroker(MultipartFile file, UserVo userVo) {
    Map<String, Object> data = null;
    List<List<Object>> brokerList = null;
    Map<String, List<Map<String, String>>> picMaps = null;
    Map<String, PictureData> sheetList = null;
    List<LeasingBrokerStaffVo> leasingBrokerStaffs = null;
    HashMap<String, Object> picResult = null;
    try {
        //獲取導入的數據
        data = importExcelPictureUtil.getDataFromExcel(file);
        if (data == null) {
            return CommonUtil.ToResultHashMap(MessageConstant.ERROR_CODE, "導入數據為空", null);
        }
        //獲取非圖片數據
        brokerList = (List) data.get("list");
        if (brokerList == null || brokerList.size() <= 0) {
            return CommonUtil.ToResultHashMap(MessageConstant.ERROR_CODE, "導入數據為空", null);
        }
        logger.info("brokerListSize:" + brokerList.size());
        //獲取圖片數據
        sheetList = (Map) data.get("piclist");
        if (sheetList == null) {
            return CommonUtil.ToResultHashMap(MessageConstant.ERROR_CODE, "個人照片為空/不合規", null);
        }
        logger.info("picList:" + sheetList);

        leasingBrokerStaffs = new ArrayList<>();
        //遍歷每一行數據
        for (int i = 0; i < brokerList.size(); i++) {
            //封裝每一行數據
            LeasingBrokerStaffVo leasingBrokerStaff = this.addLeasingBrokerStaff(brokerList.get(i), sheetList, i);

            //驗證每一行數據
            HashMap<String, Object> result = this.vaildExcelParam(leasingBrokerStaff);
            logger.info(result.toString());

            //封裝問題數據
            if ("0".equals(String.valueOf(result.get("status")))) {
                leasingBrokerStaff.setMessage(String.valueOf(result.get("message")));
                leasingBrokerStaffs.add(leasingBrokerStaff);
            }
            //執行添加沒問題數據
            else {

            }
        }

        String path = "";
        if (leasingBrokerStaffs != null && leasingBrokerStaffs.size() > 0) {
            path = this.importExcel(leasingBrokerStaffs);
        }

    } catch (Exception e) {
        e.printStackTrace();
        logger.info("批量導入異常" + e.getMessage());
        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        return CommonUtil.ToResultHashMap(MessageConstant.ERROR_CODE, "批量導入失敗", null);
    }
    return CommonUtil.ToResultHashMap(MessageConstant.SUCCESS_CODE, "成功" + (brokerList.size() - leasingBrokerStaffs.size()) + "條，失敗" + leasingBrokerStaffs.size() + "條", null);
}

    /**
 * 封裝excel數據
 *
 * @param brokerList
 * @return
 */
public LeasingBrokerStaffVo addLeasingBrokerStaff(List<Object> brokerList, Map<String, PictureData> sheetList, int count) throws IOException {
    LeasingBrokerStaffVo leasingBrokerStaff = new LeasingBrokerStaffVo();
    for (int i = 0; i < brokerList.size(); i++) {
        //獲取每一行數據
        leasingBrokerStaff.setPeopleName(String.valueOf(brokerList.get(0)));
        if ("男".equals(String.valueOf(brokerList.get(1)))) {
            leasingBrokerStaff.setSex(1);
        } else if ("女".equals(String.valueOf(brokerList.get(1)))) {
            leasingBrokerStaff.setSex(2);
        } else {
            leasingBrokerStaff.setSex(0);
        }
        leasingBrokerStaff.setIdNo(String.valueOf(brokerList.get(2)).toUpperCase());
        leasingBrokerStaff.setDegree(String.valueOf(brokerList.get(3)));
        leasingBrokerStaff.setMobile(String.valueOf(brokerList.get(4)));
        leasingBrokerStaff.setWorkOn(String.valueOf(brokerList.get(5)) == null ? null : DateUtil.convert2Date(String.valueOf(brokerList.get(5)), "yyyy-MM-dd"));
        leasingBrokerStaff.setWorkStatus(1);
        leasingBrokerStaff.setStatus(1);
        Object key[] = sheetList.keySet().toArray();
                    //封裝圖片 這里是 6 7 8 列的為圖片其中file idNoPicP idNoPicC為PictureData類型
        for (int j = 0; j < sheetList.size(); j++) {
            String picName = key[j].toString();
            String[] keyName = picName.split("-");
            logger.info("行數：" + (j + 1) + "圖片行數：" + keyName[0]);
            if (String.valueOf(count + 1).equals(keyName[0])) {
                if ("6".equals(keyName[1])) {
                    leasingBrokerStaff.setFile(sheetList.get(key[j]));
                } else if ("7".equals(keyName[1])) {
                    leasingBrokerStaff.setIdNoPicP(sheetList.get(key[j]));
                } else if ("8".equals(keyName[1])) {
                    leasingBrokerStaff.setIdNoPicC(sheetList.get(key[j]));
                }
            }
        }

    }
    return leasingBrokerStaff;
}

       /**
 * 批量導入失敗數據上傳
 *
 * @param leasingBrokerStaffs
 * @return
 * @throws Exception
 */
public String importExcel(List<LeasingBrokerStaffVo> leasingBrokerStaffs) throws Exception {
    String exportDataPath = "";
    String[] rowsName = {"姓名", "性別", "×××號", "學歷", "手機號", "從業日期", "個人照片", "×××正面", "×××反面", " "};
    List<Object[]> dataList = new ArrayList<Object[]>();
    Object[] objs = null;
    OutputStream out = null;
    for (int i = 0; i < leasingBrokerStaffs.size(); i++) {
        objs = new Object[rowsName.length];
        objs[0] = leasingBrokerStaffs.get(i).getPeopleName();
        objs[1] = leasingBrokerStaffs.get(i).getSex() == 1 ? "男" : "女";
        objs[2] = leasingBrokerStaffs.get(i).getIdNo();
        objs[3] = leasingBrokerStaffs.get(i).getDegree();
        objs[4] = leasingBrokerStaffs.get(i).getMobile();
        objs[5] = DateUtil.convert2String(leasingBrokerStaffs.get(i).getWorkOn(), "yyyy-MM-dd");
        objs[6] = leasingBrokerStaffs.get(i).getFile();
        objs[7] = leasingBrokerStaffs.get(i).getIdNoPicP();
        objs[8] = leasingBrokerStaffs.get(i).getIdNoPicC();
        objs[9] = leasingBrokerStaffs.get(i).getMessage();
        dataList.add(objs);
    }
    exportDataPath = exportExcelUtil.export6(out, "操作記錄" + DateUtil.convert2String(new Date(), "yyyyMMddHHmmss"), rowsName, dataList);
    return exportDataPath;
}

public String export6(OutputStream out, String title, String[] rowName, List<Object[]> dataList) throws Exception {
    String exportDataPath = "";
    File file = null;
    try {
        int rowaccess = 1000;//內存中緩存記錄行數
        /*keep 100 rowsin memory,exceeding rows will be flushed to disk*/
        SXSSFWorkbook workbook = new SXSSFWorkbook(rowaccess);
        workbook.setCompressTempFiles(true);
//            XSSFWorkbook workbook = new XSSFWorkbook(); // 創建工作簿對象
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(title); // 創建工作表

        // 產生表格標題行
//            HSSFRow rowm = sheet.createRow(0);
//            HSSFCell cellTiltle = rowm.createCell(0);

        // sheet樣式定義【getColumnTopStyle()/getStyle()均為自定義方法 - 在下面 - 可擴展】
        CellStyle columnTopStyle = this.getColumnTopStyle(workbook);// 獲取列頭樣式對象
        CellStyle style = this.getStyle(workbook); // 單元格樣式對象
        /*
         * sheet.addMergedRegion(new
         * CellRangeAddress(0,dataList.get(0).length-1 , 0,
         * (rowName.length-1)));
         */// 合并單元格
//            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0,
//                    dataList.get(0).length - 1));// 列行
//            cellTiltle.setCellStyle(style);
//            cellTiltle.setCellValue(title);

        // 定義所需列數
        int columnNum = rowName.length;
        SXSSFRow rowRowName = (SXSSFRow) sheet.createRow(0); // 在索引2的位置創建行(最頂端的行開始的第二行)

        // 將列頭設置到sheet的單元格中
        for (int n = 0; n < columnNum; n++) {
            SXSSFCell cellRowName = (SXSSFCell) rowRowName.createCell(n); // 創建列頭對應個數的單元格
            cellRowName.setCellType(XSSFCell.CELL_TYPE_STRING); // 設置列頭單元格的數據類型
            XSSFRichTextString text = new XSSFRichTextString(rowName[n]);
            cellRowName.setCellValue(text); // 設置列頭單元格的值
            cellRowName.setCellStyle(columnTopStyle); // 設置列頭單元格樣式
        }

        // 將查詢出的數據設置到sheet對應的單元格中
        for (int i = 0; i < dataList.size(); i++) {
            Object[] obj = dataList.get(i);// 遍歷每個對象
            SXSSFRow row = (SXSSFRow) sheet.createRow(i + 1);// 創建所需的行數（從第三行開始寫數據）
            row.setHeight((short)( 5 * 256));
            for (int j = 0; j <obj.length; j++) {
                SXSSFCell cell = null; // 設置單元格的數據類型
                cell = (SXSSFCell) row.createCell(j, SXSSFCell.CELL_TYPE_STRING);
                if (obj[j] != null) {
                                            //非圖片數據封裝
                    if(j<=5 || j == 9)  {
                        cell.setCellValue(obj[j].toString());
                    }
                    //圖片數據封裝
                else{
                        XSSFDrawing patriarch = (XSSFDrawing) sheet.createDrawingPatriarch();
                                                    //i為第幾行 j為第幾列
                        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,
                                (short) j, i+1, (short) (j+1), i+2);

                        anchor.setAnchorType(3);
                        PictureData pic = (PictureData)obj[j];
                        byte[] data = pic.getData();
                        //插入圖片
                        patriarch.createPicture(anchor, workbook.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                }
                cell.setCellStyle(style); // 設置單元格樣式
            }

        }
        // 讓列寬隨著導出的列長自動適應
      for (int colNum = 0; colNum < dataList.get(0).length; colNum++) {
//                int columnWidth = sheet.getColumnWidth(colNum) / 256;
//                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
//                    SXSSFRow currentRow;
//                    // 當前行未被使用過
//                    if (sheet.getRow(rowNum) == null) {
//                        currentRow = (SXSSFRow) sheet.createRow(rowNum);
//                    } else {
//                        currentRow = (SXSSFRow) sheet.getRow(rowNum);
//                    }
//                    /*
//                     * if (currentRow.getCell(colNum) != null) { HSSFCell
//                     * currentCell = currentRow.getCell(colNum); if
//                     * (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING)
//                     * { int length =
//                     * currentCell.getStringCellValue().getBytes().length; if
//                     * (columnWidth < length) { columnWidth = length; } } }
//                     */
//                    if (currentRow.getCell(colNum) != null) {
//                        SXSSFCell currentCell = (SXSSFCell) currentRow.getCell(colNum);
//                        if (currentCell.getCellType() == SXSSFCell.CELL_TYPE_STRING) {
//                            int length = 0;
//                            try {
//                                length = currentCell.getStringCellValue()
//                                        .getBytes().length;
//                            } catch (Exception e) {
//                                e.printStackTrace();
//                            }
//                            if (columnWidth < length) {
//                                columnWidth = length;
//                            }
//                        }
//                    }
//
//                }
            if (colNum == 0) {
                sheet.setColumnWidth(colNum, 20 * 256);
//                    sheet.setColumnHidden(colNum,40 * 256);
            } else {
                sheet.setColumnWidth(colNum, 20 * 256);
//                    sheet.setCo
            }

//                if (colNum == 0) {
//                    columnWidth=columnWidth+2;
//
//                } else {
//                    columnWidth=columnWidth+4;
//                }
//
//                if(columnWidth<40){
//                    sheet.setColumnWidth(colNum, columnWidth*256);
//                }else{
//                    sheet.setColumnWidth(colNum,40*256 );
//                }
        }
        if (workbook != null) {
            try {
                String fileName = title + ".xlsx";
                fileName = URLEncoder.encode(fileName,"UTF-8");
                file = new File(System.getProperty("java.io.tmpdir") + System.getProperty("file.separator") +  fileName );
                out = new FileOutputStream(file.getPath());
                workbook.write(out);

                exportDataPath = uploadUtil.uploadExcelUtf8(file,MessageConstant.UPLOAD_EXCEL_SOURCE + "/export");
                logger.info("exportDataPath : " +exportDataPath);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if(file != null && file.exists()){
            file.delete();
        }
    }

    return exportDataPath;
}