在最近的项目开发中,遇到这样一个需求,用户导入带图片的excel,excel批量导入功能已做过很多了,带图片的是第一次尝试,大概的要求有以下几点:
所有excel中的图片不能超出单元格,即必须在单元格内
所有图片单个大小必须在1M以内
其中一列的单元格放入的图片不能多于5张
其中一列的单元格为无限数量的图片与不多于2000的字符
/** * 获取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,实际处理中应该是很慢的,笔者供职外包,无法查看线上实际情况。
版权说明 : 本文为转载文章, 版权归原作者所有 版权申明
原文链接 : https://blog.csdn.net/MadLifeBin/article/details/118998500
内容来源于网络,如有侵权,请联系作者删除!