EasyExcel 自定义宽高

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106

package com.cloud.module.management.common.handler;   import cn.hutool.core.util.ObjectUtil; import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.metadata.data.CellData; import com.alibaba.excel.metadata.data.WriteCellData; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy; import org.apache.commons.collections.CollectionUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Sheet;   import javax.imageio.ImageIO; import java.awt.image.BufferedImage; import java.io.ByteArrayInputStream; import java.io.IOException; import java.util.HashMap; import java.util.List; import java.util.Map;     public class CustomCellWriteWidthHandle extends AbstractColumnWidthStyleStrategy {       private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();     @Override     protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {         boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);         if (needSetWidth) {             Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());               Integer columnWidth = null;             try {                 columnWidth = this.dataLength(cellDataList, cell, isHead);             } catch (IOException e) {                 throw new RuntimeException(e);             }             // 单元格文本长度大于60换行             if (columnWidth >= 0) {                 if (columnWidth > 60) {                     columnWidth = 60;                 }                 Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());                 if (maxColumnWidth == null || columnWidth > maxColumnWidth) {                     maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);                     Sheet sheet = writeSheetHolder.getSheet();                     sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);                 }             }         }     }     /**      * 计算长度      * @param cellDataList      * @param cell      * @param isHead      * @return      */     private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) throws IOException {         if (isHead) {             return cell.getStringCellValue().getBytes().length;         } else {             CellData<?> cellData = cellDataList.get(0);             CellDataTypeEnum type = cellData.getType();             if (type == null) {                 return -1;             } else {                 switch (type) {                     case STRING:                         // 换行符（数据需要提前解析好）                         int index = cellData.getStringValue().indexOf("\n");                         return index != -1 ?                                 cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;                     case BOOLEAN:                         return cellData.getBooleanValue().toString().getBytes().length;                     case NUMBER:                         return cellData.getNumberValue().toString().getBytes().length;                     // 如果是图片返回图片的宽度，并设置row的高度为图片的高度                     case EMPTY:                         if (ObjectUtil.isNotEmpty(cellDataList)  && ObjectUtil.isNotEmpty(cellDataList.get(0))) {                             BufferedImage bufferedImage = ImageIO.read(new ByteArrayInputStream(cellDataList.get(0).getImageDataList().get(0).getImage()));                             int originalWidth = bufferedImage.getWidth();                             int originalHeight = bufferedImage.getHeight();                               // 计算与210的宽度的比例                             double ratio = (double) 210 / originalWidth;                             int newWidth = 210; // 设置新宽度为210                             int newHeight = (int) (originalHeight * ratio);                               // 将像素转换为磅以设置Excel行高                             double heightInPoints = newHeight * 0.75;                             cell.getRow().setHeightInPoints((float) heightInPoints);                               // 将像素转换为Excel中的宽度单位                             double charWidth = 7.0; // 默认的Excel字体中标准字符的大致宽度                             double estimatedChars = newWidth / charWidth;                             return (int) estimatedChars; // 以Excel单位返回调整后的宽度                         }                         return -1;                     default:                         return -1;                 }             }         }     } }

　　

       // 导出 Excel 文件
            EasyExcel.write(response.getOutputStream(), this.entityClass)
                    .sheet(dto.getSheetName())
                    .includeColumnFiledNames(dto.getColumnList())
                    .registerWriteHandler(new CustomCellWriteWidthHandle()) /*自适应列宽*/
                    .registerWriteHandler(seedDemandCellWriteHandler)
                    .doWrite(writeList);