POI java 处理excel上传图片

x33g5p2x  于10个月前 转载在 Java  
字(3.7k)|赞(0)|评价(0)|浏览(102)

在最近的项目开发中,遇到这样一个需求,用户导入带图片的excel,excel批量导入功能已做过很多了,带图片的是第一次尝试,大概的要求有以下几点:
所有excel中的图片不能超出单元格,即必须在单元格内
所有图片单个大小必须在1M以内
其中一列的单元格放入的图片不能多于5张
其中一列的单元格为无限数量的图片与不多于2000的字符

核心思路:获取所有的图片放入Map

/** * 获取Excel2003图片 * * @param sheet 当前sheet对象 * @param workbook 工作簿对象 * @return Map key:图片左上角-右下角单元格索引+uuid(leftRow,leftCol_rightRow,rightCol_uuid)String,value:图片流PictureData */
    private Map<String, PictureData> getSheetPictrues03(HSSFSheet sheet, HSSFWorkbook workbook) {
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
        // 获取excel上所有的图片
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        if (pictures.size() != 0) {
            HSSFPatriarch hp = sheet.getDrawingPatriarch();
            List<HSSFShape> shapeList = hp.getChildren();
            for (HSSFShape shape : shapeList) {
                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()) + "," + String.valueOf(anchor.getCol1()) + "_"
                                      + anchor.getRow2() + "," + String.valueOf(anchor.getCol2()) + "_"
                                      + UUID.randomUUID();
                    // Map key:图片左上角-右下角单元格索引+uuid(1,2_3,4_uuid),左上右下定位一张图片
                    // leftRow,leftCol_rightRow,rightCol_uuid ,放入返回的Map时,拼接uuid标示唯一的图片
                    sheetIndexPicMap.put(picIndex, picData);
                }
            }
        }
        return sheetIndexPicMap;
    }

验证关键代码

// 声明的存放同一单元的图片,方便后面判断同一单元格图片是否超过5张
   Map<String, String> numTempMap = new HashMap<String, String>();
            Object key[] = sheetIndexPicMap.keySet().toArray();
            for (int i = 0; i < sheetIndexPicMap.size(); i++) {
                String keyTemp = key[i].toString();

                // 图片左上角所在的行列
                String leftTop = keyTemp.substring(0, keyTemp.indexOf("_"));
                // 图片右下角所在的行列
                String rightBottom = keyTemp.substring(keyTemp.indexOf("_") + 1, keyTemp.lastIndexOf("_"));

                int leftRow = Integer.parseInt(leftTop.substring(0, leftTop.indexOf(",")));
                int leftCol = Integer.parseInt(leftTop.substring(leftTop.indexOf(",") + 1));

                int rightRow = Integer.parseInt(rightBottom.substring(0, rightBottom.indexOf(",")));
                int rightCol = Integer.parseInt(rightBottom.substring(rightBottom.indexOf(",") + 1));

                int currentCol = leftCol;
                int currentRow = leftRow;

                // 左上角行与右下角行不相同 或 左上角列与右下角列不相同,即为图片超出单元格
                if (leftRow != rightRow || leftCol != rightCol) {
                    throw bizExceptions.create(CatalogError.ERROR_10001610, "图片超出单元格!");
                }

                // 为使逻辑更清晰,分开两种写超出单元格的判断,图片不在4、5列的也为超出单元格
                // 这里我的实际需求是,只有第4、5列才能上传图片
                if (!(leftCol == 4 || leftCol == 5)) {
                    throw bizExceptions.create(CatalogError.ERROR_10001610, "图片超出单元格!");
                }

                PictureData pic = sheetIndexPicMap.get(key[i]);
                byte[] data = pic.getData();
                if (data.length / 1024.0 / 1024.0 > 3) {
                    throw bizExceptions.create(CatalogError.ERROR_10001608, "推荐描述图片大小超过3M!");
                }

             // 这里循环所有的图片,同一单元的图片放入同一key的Map中,后续根据判断单元格的数量
                    if (!numTempMap.containsKey(currentRow + "")) {
                        for (int k = 0; k < sheetIndexPicMap.size(); k++) {
                            String keyTempk = key[k].toString();
                            String leftTopk = keyTempk.substring(0, keyTempk.indexOf("_"));
                            int currentRowk = Integer.parseInt(leftTopk.substring(0, leftTopk.indexOf(",")));
                            int currentColk = Integer.parseInt(leftTopk.substring(leftTopk.indexOf(",") + 1));

                            if (currentRow == currentRowk && currentColk == startCellNum) {
                                String productImgNumStr = numTempMap.get(currentRow + "");
                                int productImgNum = 0;
                                if (null != productImgNumStr) {
                                    productImgNum = Integer.parseInt(productImgNumStr) + 1;
                                }
                                numTempMap.put(currentRow + "", productImgNum + "");
                            }
                        }
                    }
                }
            }
            Object keyNum[] = numTempMap.keySet().toArray();
            for (int i = 0; i < numTempMap.size(); i++) {
                String productImgs = numTempMap.get(keyNum[i]);
                if (null != productImgs && Integer.parseInt(productImgs) >= 5) {
                    throw bizExceptions.create(CatalogError.ERROR_10001609, "产品图片数量超过5张!");
                }
            }

这种方式,个人觉得还是有点太消耗性能,本来就是批量导入,一次性将所有图片放入Map中,然后循环key,实际处理中应该是很慢的,笔者供职外包,无法查看线上实际情况。

相关文章

热门文章

更多