一、背景
- 使用EasyExcel导出excel文件,但是需要同时导出图片信息,且图片信息不能影响行高和单元格宽度,图片本身被导出时,不能因为压缩导致图片变形
二、思路
- 使用EasyExcel最主要是要实现各种hadler,通过实现CellWriteHandler,获取单元格图片,进行图片压缩;
- 由于excel的行高和单元格宽度换算方式完全不一样,所以要通过计算得出最合适的压缩比例
- (excel宽度 / 字符宽度转换为标准字符宽度) * 估算的字符到像素的转换因子 = 宽度像素;
- (excel高度 / 点转换为英寸) * 英寸转换为像素 = 行高像素;
- 最终宽度像素或者行高像素 根据行高与像素的转换因子 ,计算出需要设置的单元格间距值,即可正常缩小图片
三、代码
java">public static class QuotationCustomCellWriteHandler implements CellWriteHandler {public static final int STANDARD_CHARACTER_WIDTH = 256;public static final float CHARACTER_2_PIXEL_FACTOR = 7.5f;public static final int PIXEL_2_INCH_FACTOR = 72;public static final int DPI = 96;public static final float ROW_HEIGHT_2_PIXEL_FACTOR = 1.3333f;private final int rowHeight;public QuotationCustomCellWriteHandler(int rowHeight) {this.rowHeight = rowHeight;}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex,Boolean isHead) {boolean noImageValue = Objects.isNull(cellData) || cellData.getImageDataList() == null || cellData.getImageDataList().isEmpty();if (Objects.equals(Boolean.TRUE, isHead) || noImageValue) {return;}Sheet sheet = cell.getSheet();ImageData imageData = cellData.getImageDataList().get(0);imageData.setRelativeLastRowIndex(0);imageData.setRelativeLastColumnIndex(0);try (ByteArrayInputStream bis = new ByteArrayInputStream(imageData.getImage())) {BufferedImage image = ImageIO.read(bis);int targetWidth = (int) ((double) sheet.getColumnWidth(cell.getColumnIndex()) / STANDARD_CHARACTER_WIDTH * CHARACTER_2_PIXEL_FACTOR);int targetHeight = (int) (rowHeight * 1.0 / PIXEL_2_INCH_FACTOR * DPI);double scaleX = (double) targetWidth / image.getWidth();double scaleY = (double) targetHeight / image.getHeight();double scale = Math.min(scaleX, scaleY);int scaledWidth = (int) (image.getWidth() * scale);int scaledHeight = (int) (image.getHeight() * scale);int topPadding = (targetHeight - scaledHeight) / 2;int bottomPadding = targetHeight - scaledHeight - topPadding;int leftPadding = (targetWidth - scaledWidth) / 2;int rightPadding = targetWidth - scaledWidth - leftPadding;imageData.setTop((int) (topPadding / ROW_HEIGHT_2_PIXEL_FACTOR));imageData.setBottom((int) (bottomPadding / ROW_HEIGHT_2_PIXEL_FACTOR));imageData.setLeft((int) (leftPadding / ROW_HEIGHT_2_PIXEL_FACTOR));imageData.setRight((int) (rightPadding / ROW_HEIGHT_2_PIXEL_FACTOR));} catch (Exception e) {log.error("QuotationCustomCellWriteHandler afterCellDataConverted err", e);}CellWriteHandler.super.afterCellDataConverted(writeSheetHolder, writeTableHolder, cellData, cell, head, relativeRowIndex, isHead);}}