java 實(shí)現(xiàn)excel導(dǎo)入excel包含數(shù)據(jù)與圖片 并將導(dǎo)入過(guò)程中不符合要求的數(shù)據(jù)封裝到excel

最近在開(kāi)發(fā)中需要一個(gè)excel上傳的功能，其中，excel就包含了數(shù)據(jù)和圖片，經(jīng)過(guò)查詢與了解，代碼實(shí)現(xiàn)如下，圖片與數(shù)據(jù)需要單獨(dú)獲取。

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

• 獲取圖片和位置 (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(); // 行號(hào)-列號(hào)
    map.put(key, pdata);
    logger.info("圖片類型" + picture.getPictureData().suggestFileExtension());
    }
    }
    return map;
    }
    xls格式的獲取圖片，如果excel中不存在圖片的話會(huì)報(bào)空指針異常，需判斷excel中有無(wú)圖片再調(diào)用此方法，List<HSSFPictureData> pictures = (List<HSSFPictureData>) wookbook.getAllPictures();通過(guò)判斷l(xiāng)ist是否為空，判斷excel中有無(wú)圖片

    /**

  • 獲取圖片和位置 (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,獲取數(shù)據(jù)和圖片返回

/**
 * 獲取excel數(shù)據(jù) 包括圖片
 *
 * @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 {
        //獲取一個(gè)絕對(duì)地址的流
        fis = file.getInputStream();
    } catch (Exception e) {
        e.printStackTrace();
    }

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

    } catch (Exception ex) {
        //ex.printStackTrace();
        try {
            //2007版本的excel，用.xlsx結(jié)尾
            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);
        }
    }
    //得到一個(gè)工作表

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

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

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

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

            List<Object> li = new ArrayList<Object>();
                            //避免前幾個(gè)字段為空 空數(shù)據(jù)沒(méi)被封裝的情況  
            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());
            //跟模板設(shè)置有關(guān)
            while (li.size() < 6) {
                li.add("");
            }
                            //java獲取excel會(huì)出現(xiàn)一個(gè)全部數(shù)據(jù)為空，還統(tǒng)計(jì)進(jìn)來(lái)的
            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;
}

    /**
 * 時(shí)間轉(zhuǎn)換
 *
 * @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");  //格式化數(shù)字

    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;
}

上面的方法就已經(jīng)將圖片和數(shù)據(jù)分開(kāi)獲取到了，分別為Map類型maplist的圖片數(shù)據(jù) 和 List類型的非圖片數(shù)據(jù)
下面的方法實(shí)現(xiàn)獲取數(shù)據(jù)的處理已經(jīng)數(shù)據(jù)圖片驗(yàn)證并將不合要求的數(shù)據(jù)封裝起來(lái)。

@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 {
        //獲取導(dǎo)入的數(shù)據(jù)
        data = importExcelPictureUtil.getDataFromExcel(file);
        if (data == null) {
            return CommonUtil.ToResultHashMap(MessageConstant.ERROR_CODE, "導(dǎo)入數(shù)據(jù)為空", null);
        }
        //獲取非圖片數(shù)據(jù)
        brokerList = (List) data.get("list");
        if (brokerList == null || brokerList.size() <= 0) {
            return CommonUtil.ToResultHashMap(MessageConstant.ERROR_CODE, "導(dǎo)入數(shù)據(jù)為空", null);
        }
        logger.info("brokerListSize:" + brokerList.size());
        //獲取圖片數(shù)據(jù)
        sheetList = (Map) data.get("piclist");
        if (sheetList == null) {
            return CommonUtil.ToResultHashMap(MessageConstant.ERROR_CODE, "個(gè)人照片為空/不合規(guī)", null);
        }
        logger.info("picList:" + sheetList);

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

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

            //封裝問(wèn)題數(shù)據(jù)
            if ("0".equals(String.valueOf(result.get("status")))) {
                leasingBrokerStaff.setMessage(String.valueOf(result.get("message")));
                leasingBrokerStaffs.add(leasingBrokerStaff);
            }
            //執(zhí)行添加沒(méi)問(wèn)題數(shù)據(jù)
            else {

            }
        }

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

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

    /**
 * 封裝excel數(shù)據(jù)
 *
 * @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++) {
        //獲取每一行數(shù)據(jù)
        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("行數(shù)：" + (j + 1) + "圖片行數(shù)：" + 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;
}

       /**
 * 批量導(dǎo)入失敗數(shù)據(jù)上傳
 *
 * @param leasingBrokerStaffs
 * @return
 * @throws Exception
 */
public String importExcel(List<LeasingBrokerStaffVo> leasingBrokerStaffs) throws Exception {
    String exportDataPath = "";
    String[] rowsName = {"姓名", "性別", "×××號(hào)", "學(xué)歷", "手機(jī)號(hào)", "從業(yè)日期", "個(gè)人照片", "×××正面", "×××反面", " "};
    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;//內(nèi)存中緩存記錄行數(shù)
        /*keep 100 rowsin memory,exceeding rows will be flushed to disk*/
        SXSSFWorkbook workbook = new SXSSFWorkbook(rowaccess);
        workbook.setCompressTempFiles(true);
//            XSSFWorkbook workbook = new XSSFWorkbook(); // 創(chuàng)建工作簿對(duì)象
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(title); // 創(chuàng)建工作表

        // 產(chǎn)生表格標(biāo)題行
//            HSSFRow rowm = sheet.createRow(0);
//            HSSFCell cellTiltle = rowm.createCell(0);

        // sheet樣式定義【getColumnTopStyle()/getStyle()均為自定義方法 - 在下面 - 可擴(kuò)展】
        CellStyle columnTopStyle = this.getColumnTopStyle(workbook);// 獲取列頭樣式對(duì)象
        CellStyle style = this.getStyle(workbook); // 單元格樣式對(duì)象
        /*
         * 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);

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

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

        // 將查詢出的數(shù)據(jù)設(shè)置到sheet對(duì)應(yīng)的單元格中
        for (int i = 0; i < dataList.size(); i++) {
            Object[] obj = dataList.get(i);// 遍歷每個(gè)對(duì)象
            SXSSFRow row = (SXSSFRow) sheet.createRow(i + 1);// 創(chuàng)建所需的行數(shù)（從第三行開(kāi)始寫數(shù)據(jù)）
            row.setHeight((short)( 5 * 256));
            for (int j = 0; j <obj.length; j++) {
                SXSSFCell cell = null; // 設(shè)置單元格的數(shù)據(jù)類型
                cell = (SXSSFCell) row.createCell(j, SXSSFCell.CELL_TYPE_STRING);
                if (obj[j] != null) {
                                            //非圖片數(shù)據(jù)封裝
                    if(j<=5 || j == 9)  {
                        cell.setCellValue(obj[j].toString());
                    }
                    //圖片數(shù)據(jù)封裝
                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); // 設(shè)置單元格樣式
            }

        }
        // 讓列寬隨著導(dǎo)出的列長(zhǎng)自動(dòng)適應(yīng)
      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;
//                    // 當(dāng)前行未被使用過(guò)
//                    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;
}