EasyExcel 导出 xlsx 自动列宽

参考 https://blog.csdn.net/weixin_44811578/article/details/107101248

 /**
     * 表头样式
     */
    private static class XHorizontalCellStyleStrategy extends HorizontalCellStyleStrategy {

        // 表头的策略
        private static final WriteCellStyle headWriteCellStyle = new WriteCellStyle();

        // 内容的策略
        private static final WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

        static {
            // 背景设置为灰色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short) 12);
            // 字体样式
            headWriteFont.setFontName("Frozen");
            headWriteCellStyle.setWriteFont(headWriteFont);
            // 自动换行
            headWriteCellStyle.setWrapped(false);
            // 水平对齐方式
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            // 垂直对齐方式
            headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
            // contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
            // 背景白色
            contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short) 12);
            // 字体样式
            contentWriteFont.setFontName("Calibri");
            contentWriteCellStyle.setWriteFont(contentWriteFont);
        }

        public XHorizontalCellStyleStrategy() {
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            super(headWriteCellStyle, contentWriteCellStyle);
        }

    }

    /**
     * 宽度自适应
     */
    private static class AutoColumnWidthWriteHandler extends AbstractColumnWidthStyleStrategy {

        private static final int MAX_COLUMN_WIDTH = 255;

        // 因为在自动列宽的过程中，有些设置地方让列宽显得紧凑，所以做出了个判断
        private static final int COLUMN_WIDTH = 20;

        private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

        @Override
        protected void setColumnWidth(@NonNull WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, @NonNull Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
            if (needSetWidth) {
                Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());

                Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
                if (columnWidth >= 0) {
                    if (columnWidth > MAX_COLUMN_WIDTH) {
                        columnWidth = MAX_COLUMN_WIDTH;
                    } else {
                        if (columnWidth < COLUMN_WIDTH) {
                            columnWidth = columnWidth * 2;
                        }
                    }
                    Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                        maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                    }
                }
            }
        }

        private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, @NonNull Boolean isHead) {
            if (isHead) {
                return cell.getStringCellValue().getBytes().length;
            } else {
                WriteCellData<?> cellData = cellDataList.get(0);
                CellDataTypeEnum type = cellData.getType();
                if (type == null) {
                    return -1;
                } else {
                    switch (type) {
                        case STRING:
                            return cellData.getStringValue().getBytes().length;
                        case BOOLEAN:
                            return cellData.getBooleanValue().toString().getBytes().length;
                        case NUMBER:
                            return cellData.getNumberValue().toString().getBytes().length;
                        default:
                            return -1;
                    }
                }
            }
        }

    }

---

使用方法:

public byte[] test() {
        // 使用 EasyExcel 生成 xlsx 文件
        ByteArrayOutputStream ous = new ByteArrayOutputStream();
        EasyExcel.write(ous)
                .head(tableHead)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("sheet1")
                .registerWriteHandler(new AutoColumnWidthWriteHandler())
                .registerWriteHandler(new XHorizontalCellStyleStrategy())
                .doWrite(tableData);
        return ous.toByteArray();
    }