一、背景
使用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; } @Override public 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) ; } }