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