EasyExcel使用总结

宽度自适应

public class AutoResizeColumnWithStyleStrategy extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 80;
    private final Map<Integer, Map<Integer, Integer>> cache = MapUtils.newHashMapWithExpectedSize(8);
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell,
                                  Head head,
                                  Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = CollectionUtils.isNotEmpty(cellDataList);
        if (!needSetWidth) {
            return;
        }
        Map<Integer, Integer> maxColumnWidthMap = cache.computeIfAbsent(writeSheetHolder.getSheetNo(), key -> new HashMap<>());
        Integer columnWidth = dataLength(cellDataList, cell, isHead);
        if (columnWidth < 0) {
            return;
        }
        if (columnWidth > MAX_COLUMN_WIDTH) {
            columnWidth = MAX_COLUMN_WIDTH;
        }
        Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
        if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
            maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), (int) (columnWidth * 256 * 0.8));
        }
    }
    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return dataLengthForString(cell.getStringCellValue());
        }
        WriteCellData<?> cellData = cellDataList.get(0);
        CellDataTypeEnum type = cellData.getType();
        if (type == null) {
            return -1;
        }
        switch (type) {
            case STRING:
                return dataLengthForString(cellData.getStringValue());
            case BOOLEAN:
                return cellData.getBooleanValue().toString().getBytes().length;
            case NUMBER:
                return cellData.getNumberValue().toString().getBytes().length;
            default:
                return -1;
        }
    }
    private Integer dataLengthForString(String cellValue) {
        List<String> lines = CharSequenceUtil.split(cellValue, StrPool.LF);
        return Streams.of(lines)
                .mapToInt(line -> line.getBytes().length)
                .max()
                .orElse(-1);
    }
}

高度自适应

public class AutoResizeRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {
    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
        //do nothing
    }
    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.iterator();
        int numberOfLines = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getCellType() != CellType.STRING) {
                continue;
            }
            List<String> lines = CharSequenceUtil.split(cell.getStringCellValue(), StrPool.LF, true, false);
            if (lines.size() > numberOfLines) {
                numberOfLines = lines.size();
            }
        }
        if (numberOfLines > 1) {
            row.setHeight((short) (numberOfLines * 254));
        }
    }
}

单元格合并

public class CellMergeStrategy implements SheetWriteHandler {
    private final List<CellMergeRangeConfig> mergeRangeConfigList;
    public CellMergeStrategy(List<CellMergeRangeConfig> mergeRangeConfigList) {
        Assert.notEmpty(mergeRangeConfigList, "参数不能为空");
        for (CellMergeRangeConfig config : mergeRangeConfigList) {
            Assert.isTrue(config.getFirstRowIndex() >= 0 && config.getLastRowIndex() >= 0
                            && config.getFirstColumnIndex() >= 0 && config.getLastColumnIndex() >= 0,
                    "All parameters must be less than 0");
            Assert.isTrue(config.getFirstRowIndex() <=  config.getLastRowIndex(),
                    "All parameters firstRowIndex must be greater than lastRowIndex");
            Assert.isTrue(config.getFirstColumnIndex() <= config.getLastColumnIndex(),
                    "All parameters firstColumnIndex must be less than lastColumnIndex");
        }
        this.mergeRangeConfigList = mergeRangeConfigList;
    }
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        for (CellMergeRangeConfig mergeRangeConfig : mergeRangeConfigList) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(mergeRangeConfig.getFirstRowIndex(), mergeRangeConfig.getLastRowIndex(),
                    mergeRangeConfig.getFirstColumnIndex(), mergeRangeConfig.getLastColumnIndex());
            writeSheetHolder.getSheet().addMergedRegionUnsafe(cellRangeAddress);
        }
    }
}

行列冻结

public class FreezeRowColumnStrategy implements SheetWriteHandler {
    private final int colSplit;
    private final int rowSplit;
    private final int leftmostColumn;
    private final int topRow;
    public FreezeRowColumnStrategy(int colSplit, int rowSplit, int leftmostColumn, int topRow) {
        if (colSplit < 0 || rowSplit < 0 || leftmostColumn < 0 || topRow < 0) {
            throw new IllegalArgumentException("All parameters must be greater than 0");
        }
        this.colSplit = colSplit;
        this.rowSplit = rowSplit;
        this.leftmostColumn = leftmostColumn;
        this.topRow = topRow;
    }
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        writeSheetHolder.getSheet().createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
    }
}