项目在直接使用poi进行excel数据的操作过于繁琐，于是使用了封装过的easypoi但是在使用的过程中遇到了一些不满足需求以及存在的小问题，于是写博客记录一下。
问题：
1、在对验证失败的数据进行导出时，想到将数据的集合在此导出到一个新的workbook中，然后在导出到出版中，但是这个过程中测试出，图片始终不能导出到模板文件中。
2、在导出到模板过程中对于excel样式的操作过于繁琐，于是放弃创建的的workbook，采用再原来的workbook中进行操作。
3、在原来的workbook中进行操作时又遇到了一个新问题，在easypoi通过工具类过去excel中的图片后，excel中的图片会被初始化到刚插入到excel中时的大小。
4、在失败的workbook中校验成功的数据的图片是没有被删除的
解决方案：
1、图片大小发生变化
问题原因：
通过debug调试后发现图片大小发生变化话是因为PoiPublicUtil.getSheetPictrues07中在获取图片所在的行号-列号时，调用了XSSFClientAnchor anchor = pic.getPreferredSize();方法来过去XSSFClientAnchor 对象，getPreferredSize()此方法中会计算图片的首选比例（Calculate the preferred size for this picture.），所以不通过 pic.getPreferredSize()此方法获取XSSFClientAnchor 此对象就可以保证图像不会被缩放。
解决方案：
通过查看poi的API发现XSSFClientAnchor 对象可以通过XSSFPicture.getClientAnchor来过去，所以重写后的方法如下。
/** * 获取Excel2007图片 * * @param sheet 当前sheet对象 * @param workbook 工作簿对象 * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData */ public static Map<String, PictureData> getSheetPictrues07(XSSFSheet sheet, XSSFWorkbook workbook) { Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(); for (POIXMLDocumentPart dr : sheet.getRelations()) { if (dr instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing) dr; List<XSSFShape> shapes = drawing.getShapes(); for (XSSFShape shape : shapes) { if (shape instanceof XSSFPicture) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor clientAnchor = pic.getClientAnchor(); String picIndex = clientAnchor.getRow1() + "_" + clientAnchor.getCol1(); //与原来行号，列号的方法不同但是大同小异 sheetIndexPicMap.put(picIndex, pic.getPictureData()); } } } } return sheetIndexPicMap; }
2、失败的workbook中还存在校验成功的图片
在easypoi导入数据时可以自定义verifyHandler对数据进行校验
ImportParams params = new ImportParams(); params.setTitleRows(0); params.setVerifyHandler((IExcelVerifyHandler<VehicleInfoImportVo>) obj -> { try { // 无效数据检查 if (checkInvalid(obj)) { obj.setInvalid(true); return new ExcelVerifyHandlerResult(true); } // 数据格式检验 String msg = checkVehicleInfoImportVo(obj); if (StringUtils.isBlank(msg)) { return new ExcelVerifyHandlerResult(true); } else { return new ExcelVerifyHandlerResult(false, msg); } } catch (Exception e) { log.error("校验错误", e); return new ExcelVerifyHandlerResult(false); } }); // 导入的数据格式 //objectExcelImportResult 此对象中保存失败和成功的数据集合以及workbook ExcelImportResult<VehicleInfoImportVo> objectExcelImportResult = ExcelImportUtil.importExcelMore( file.getInputStream(), VehicleInfoImportVo.class, params);
问题的原因（针对于07以上的excel）：
在easypoi中，失败的workbook是通过在原来workbook的基础上，将正确的数据行删除，但是没有对图片进行删除所以会导致此问题。
（当时想的比较简单你没有删除我给你删除就行了，但是在后面的测试中删除图片不紧紧是对XSSFPicture进行操作，还需要删除其中的关系，具体可以看一下POI中插入图片的代码）
private Workbook removeSuperfluousRows(Workbook book, List<Row> rowList, ImportParams params) { for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex() + params.getSheetNum(); i++) { for (int j = rowList.size() - 1; j >= 0; j--) { if (rowList.get(j).getRowNum() < rowList.get(j).getSheet().getLastRowNum()) { book.getSheetAt(i).shiftRows(rowList.get(j).getRowNum() + 1, rowList.get(j).getSheet().getLastRowNum(), -1); } else if (rowList.get(j).getRowNum() == rowList.get(j).getSheet().getLastRowNum()) { book.getSheetAt(i).createRow(rowList.get(j).getRowNum() + 1); book.getSheetAt(i).shiftRows(rowList.get(j).getRowNum() + 1, rowList.get(j).getSheet().getLastRowNum() + 1, -1); } } } return book; }
解决方案：
在删除正确的数据行的时候，将对应的照片也删除
Map<String, PictureData> sheetPictrues07 = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(0), (XSSFWorkbook) book); if (sheetPictrues07 != null && sheetPictrues07.size() > 0) { for (Row row : successRow) { int rowNum = row.getRowNum(); short lastCellNum = row.getLastCellNum(); for (int i = 0; i < lastCellNum; i++) { PictureData pictureData = sheetPictrues07.get(rowNum + "_" + i); if (pictureData != null) { RemovePicWorkbook.removeExcelImageByPicture(book, pictureData); } } } }
RemovePicWorkbook.java
public class RemovePicWorkbook { public static void removeExcelImageByPicture(Workbook workbook, PictureData pictureData) { Sheet sheet = workbook.getSheetAt(0); Drawing drawing = sheet.getDrawingPatriarch(); XSSFPicture xssfPictureToDelete = null; if (drawing instanceof XSSFDrawing) { for (XSSFShape shape : ((XSSFDrawing) drawing).getShapes()) { if (shape instanceof XSSFPicture) { XSSFPicture xssfPicture = (XSSFPicture) shape; String shapename = xssfPicture.getShapeName(); int row = xssfPicture.getClientAnchor().getRow1(); int col = xssfPicture.getClientAnchor().getCol1(); if (pictureData instanceof XSSFPictureData) { XSSFPictureData inPictureData = (XSSFPictureData) pictureData; XSSFPictureData curpictureData1 = xssfPicture.getPictureData(); PackagePartName inPartName = curpictureData1.getPackagePart().getPartName(); PackagePartName curPartName = inPictureData.getPackagePart().getPartName(); if (curPartName.equals(inPartName)) xssfPictureToDelete = xssfPicture; } } } } if (xssfPictureToDelete != null) ExcelDeleteImage.deleteEmbeddedXSSFPicture(xssfPictureToDelete); if (xssfPictureToDelete != null) ExcelDeleteImage.deleteCTAnchor(xssfPictureToDelete); } }
ExcelDeleteImage.java
/** * I have now been trying for too long to remove an image from my XSSFSheet. I cannot find any information about this, but I would think that it has to be possible.. * Is there any way to remove an image from my XSSFSheet? Even the official (?) apache poi website does not mention anything besides creating and reading images * I am now not far away from giving up and just copying everything except said image into a new sheet. Which is obviously not how this should be done. I don‘t think I would be able to sleep well for a week if I did that. * My last unsuccessful attempt was to use my code which moves images (I shared that code in this post) but instead of setting valid row numbers I would set null, but that‘s not possible since the parameter for setRow() is int (primitive type). * Then I tried setting a negative value for the anchor rows. While this technically removes the images, the excel file has to be repaired when it is opened the next time. The images are not being displayed. * I believe I would have to remove the relation from the XSSFDrawing too to completely remove the image (I think this after finding this custom implementation of XSSFDrawing) but I have no idea what is going on there... * I would be grateful for any kind of help here! * For XSSF this is not as simple as it sounds. There is HSSFPatriarch.removeShape but there is not something comparable in XSSFDrawing. * We must delete the picture itself inclusive the relations. And we must delete the shape‘s anchor from the drawing. * Example which goes trough all pictures in a sheet and deletes a picture if it‘s shape name is "Image 2": */ public class ExcelDeleteImage { public static void deleteCTAnchor(XSSFPicture xssfPicture) { XSSFDrawing drawing = xssfPicture.getDrawing(); XmlCursor cursor = xssfPicture.getCTPicture().newCursor(); cursor.toParent(); if (cursor.getObject() instanceof org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTTwoCellAnchor) { for (int i = 0; i < drawing.getCTDrawing().getTwoCellAnchorList().size(); i++) { if (cursor.getObject().equals(drawing.getCTDrawing().getTwoCellAnchorArray(i))) { drawing.getCTDrawing().removeTwoCellAnchor(i); System.out.println("TwoCellAnchor for picture " + xssfPicture + " was deleted."); } } } else if (cursor.getObject() instanceof org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTOneCellAnchor) { for (int i = 0; i < drawing.getCTDrawing().getOneCellAnchorList().size(); i++) { if (cursor.getObject().equals(drawing.getCTDrawing().getOneCellAnchorArray(i))) { drawing.getCTDrawing().removeOneCellAnchor(i); System.out.println("OneCellAnchor for picture " + xssfPicture + " was deleted."); } } } else if (cursor.getObject() instanceof org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTAbsoluteAnchor) { for (int i = 0; i < drawing.getCTDrawing().getAbsoluteAnchorList().size(); i++) { if (cursor.getObject().equals(drawing.getCTDrawing().getAbsoluteAnchorArray(i))) { drawing.getCTDrawing().removeAbsoluteAnchor(i); System.out.println("AbsoluteAnchor for picture " + xssfPicture + " was deleted."); } } } } public static void deleteEmbeddedXSSFPicture(XSSFPicture xssfPicture) { if (xssfPicture.getCTPicture().getBlipFill() != null) { if (xssfPicture.getCTPicture().getBlipFill().getBlip() != null) { if (xssfPicture.getCTPicture().getBlipFill().getBlip().getEmbed() != null) { String rId = xssfPicture.getCTPicture().getBlipFill().getBlip().getEmbed(); XSSFDrawing drawing = xssfPicture.getDrawing(); drawing.getPackagePart().removeRelationship(rId); drawing.getPackagePart().getPackage().deletePartRecursive(drawing.getRelationById(rId).getPackagePart().getPartName()); System.out.println("Picture " + xssfPicture + " was deleted."); } } } } public static void deleteHSSFShape(HSSFShape shape) { HSSFPatriarch drawing = shape.getPatriarch(); drawing.removeShape(shape); System.out.println("Shape " + shape + " was deleted."); } public static void main(String[] args) throws Exception { String filename = "C:\\Users\\yuxia\\Desktop\\ce.xlsx"; String outfilename = "C:\\Users\\yuxia\\Desktop\\ce1.xlsx"; InputStream inp = new FileInputStream(filename); Workbook workbook = WorkbookFactory.create(inp); Sheet sheet = workbook.getSheetAt(0); Drawing drawing = sheet.getDrawingPatriarch(); XSSFPicture xssfPictureToDelete = null; if (drawing instanceof XSSFDrawing) { for (XSSFShape shape : ((XSSFDrawing) drawing).getShapes()) { if (shape instanceof XSSFPicture) { XSSFPicture xssfPicture = (XSSFPicture) shape; String shapename = xssfPicture.getShapeName(); int row = xssfPicture.getClientAnchor().getRow1(); int col = xssfPicture.getClientAnchor().getCol1(); System.out.println("Picture " + "" + " with Shapename: " + shapename + " is located row: " + row + ", col: " + col); if ("图片 3".equals(shapename)) xssfPictureToDelete = xssfPicture; } } } if (xssfPictureToDelete != null) deleteEmbeddedXSSFPicture(xssfPictureToDelete); if (xssfPictureToDelete != null) deleteCTAnchor(xssfPictureToDelete); // HSSFPicture hssfPictureToDelete = null; // // if (drawing instanceof HSSFPatriarch) { // // for (HSSFShape shape : ((HSSFPatriarch) drawing).getChildren()) { // // if (shape instanceof HSSFPicture) { // // HSSFPicture hssfPicture = (HSSFPicture) shape; // // int picIndex = hssfPicture.getPictureIndex(); // // String shapename = hssfPicture.getShapeName().trim(); // // int row = hssfPicture.getClientAnchor().getRow1(); // // int col = hssfPicture.getClientAnchor().getCol1(); // // System.out.println("Picture " + picIndex + " with Shapename: " + shapename + " is located row: " + row + ", col: " + col); // // if ("Image 2".equals(shapename)) hssfPictureToDelete = hssfPicture; // // } // // } // // } // // if (hssfPictureToDelete != null) deleteHSSFShape(hssfPictureToDelete); FileOutputStream out = new FileOutputStream(outfilename); workbook.write(out); out.close(); workbook.close(); } }
ExcelDeleteImage中的代码来源于外网
*************************************************************************************************************************
此外在导入excel时easypoi中只支持将图片保存到一个目录下，此问题可以通过重写ExcelImportService方法来解决
private void saveImage(Object object, String picId, Map<String, ExcelImportEntity> excelParams, String titleString, Map<String, PictureData> pictures, ImportParams params) throws Exception { if (pictures == null) { return; } PictureData image = pictures.get(picId); if (image == null) { return; } byte[] data = image.getData(); String fileName = IdUtils.fastUUID(); fileName += "." + PoiPublicUtil.getFileExtendName(data); if (excelParams.get(titleString).getSaveType() == 1) { String path = getSaveUrl(); // 此方法获取保存的路径扩展一下就好了 File savefile = new File(path); if (!savefile.exists()) { savefile.mkdirs(); } savefile = new File(path + "/" + fileName); FileOutputStream fos = new FileOutputStream(savefile); try { fos.write(data); } finally { IOUtils.closeQuietly(fos); } setValues(excelParams.get(titleString), object, getAbsoluteSaveUrl(path) + "/" + fileName); pictures.remove(picId); } else { setValues(excelParams.get(titleString), object, data); } }