EasyExcel自定义列宽列高、导出单元格指定格式

EasyExcel介绍

EasyExcel是一款开源的Java库，用于读取、写入和操作Excel文件。它是阿里巴巴集团开发的一款高效、功能丰富且易于使用的Excel操作工具。

EasyExcel提供了简洁的API，使得读写Excel文件变得非常方便，尤其适用于大量数据的导入导出操作。以下是EasyExcel的一些主要特性：

读取和写入Excel文件：EasyExcel提供了简单易用的API用于读取和写入Excel文件。你可以通过指定模型类来读取Excel文件的数据，并将数据写入到Excel文件中。

高性能：EasyExcel采用了基于事件驱动的模式，在读取和写入时能够更高效地处理大量数据。它能够以极快的速度读取和写入Excel文件，提高了数据处理的效率。

支持大数据量：EasyExcel使用内存友好的方式进行数据读取和写入，可以处理大量数据而不会导致内存溢出的问题。

导入导出灵活：EasyExcel支持导入和导出多种格式的Excel文件，包括xls和xlsx格式。你可以导入Excel文件的数据到Java对象中，并将Java对象的数据导出为Excel文件。

数据转换：EasyExcel提供了丰富的数据转换功能，可以方便地进行数据格式转换、数据映射、日期格式化等操作。

监听器支持：EasyExcel提供了监听器功能，你可以自定义监听器来处理Excel读取和写入的过程，实现一些自定义的业务逻辑。

多线程支持：EasyExcel支持多线程并发读写Excel，提高数据处理的速度。

自定义列宽

public class CustomCellWriteWeightConfig extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 255;
    private static final int COLUMN_WIDTH_BASE = 255;
    private final Map<Integer, Map<Integer, Double>> cache = new HashMap<>(8);

    private Integer relativeRowIndex = -1;

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

                double columnWidth = this.dataLength(cellDataList, cell, isHead);
                if (columnWidth >= 0) {
                    if (columnWidth > MAX_COLUMN_WIDTH) {
                        columnWidth = MAX_COLUMN_WIDTH;
                    }
                    Double maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                        maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), (int)(maxColumnWidthMap.get(cell.getColumnIndex())*COLUMN_WIDTH_BASE));
                    }
                }
            }
        }
    }


    private double dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        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:
                        return getExcelWidth(cellData.getStringValue());
                    case BOOLEAN:
                        return getExcelWidth(cellData.getBooleanValue().toString());
                    case NUMBER:
                        return getExcelWidth(cellData.getNumberValue().toString());
                    default:
                        return -1;
                }
            }
        }
    }

    /**
     * 调整单元格字符字节宽度，easyExcel默认直接用的UTF-8的byte长度，导致一旦三字节的字符过多就会变得很宽，一字节的字符过多就会不够宽
     */
    private double getExcelWidth(String str){
        double length = 0.0;
        char[] chars = str.toCharArray();
        for(char c : chars){
            byte[] bytes = this.getUtf8Bytes(c);
            if(bytes.length == 1){
                length += 1.05;
            }
            if(bytes.length == 2){
                length += 1.5;
            }
            if(bytes.length == 3){
                length += 1.85;
            }
            if(bytes.length == 4){
                length += 2.2;
            }
        }
        return length;
    }

    private byte[] getUtf8Bytes(char c) {
        char[] chars = {c};
        CharBuffer charBuffer = CharBuffer.allocate(chars.length);
        charBuffer.put(chars);
        charBuffer.flip();
        ByteBuffer byteBuffer = StandardCharsets.UTF_8.encode(charBuffer);
        return byteBuffer.array();
    }
}

自定义列高

public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 300;

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return;
        }

        // 默认为 1行高度
        int maxHeight = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getCellType() == CellType.STRING) {
                if (cell.getStringCellValue().contains("\n")) {
                    int length = cell.getStringCellValue().split("\n").length;
                    maxHeight = Math.max(maxHeight, length);
                }
            }
        }

        row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return;
        }

        // 默认为 1行高度
        int maxHeight = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getCellType() == CellType.STRING) {
                if (cell.getStringCellValue().contains("\n")) {
                    int length = cell.getStringCellValue().split("\n").length;
                    maxHeight = Math.max(maxHeight, length);
                }
            }
        }
        row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
    }

代码引用

    public static void styleDownload(HttpServletResponse response, List<?> list, String fileName) {
        try {
            Class<?> clazz = list.get(0).getClass();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), clazz)
                    .head(clazz)
                    .registerWriteHandler(new DefaultStyle())
                    .registerWriteHandler(new CustomCellWriteWeightConfig())
                    .registerWriteHandler(new CustomCellWriteHeightConfig())
                    .sheet("sheet1")
                    .doWrite(list);
        } catch (Exception e) {
            log.error("EasyExcelUtil -> download error", e);
            throw new ServiceException("excel下载失败");
        }
    }

导出单元格指定格式

这里已导出String转数字类型为例

public class EasyExcelStringToNumberConvert implements Converter<String> {
    @Override
    public Class<String> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(ReadCellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return cellData.getStringValue();
    }

    @Override
    public WriteCellData<CellDataTypeEnum> convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 将字符串转换为数字类型
        BigDecimal numberValue;
        try {
            numberValue = new BigDecimal(value);
        } catch (NumberFormatException e) {
            numberValue = new BigDecimal("0");
        }
        return new WriteCellData<>(numberValue);
    }

代码引用

    /**
     * 得分
     */
    @ApiModelProperty(value = "得分")
    @ExcelProperty(value = "得分",converter = EasyExcelStringToNumberConvert.class)
    private String score;