EasyExcel自适应列宽、行高、自动换行

一、自定义模版

二、工具类

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.alibaba.nacos.common.utils.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CustomCellWeightWeightConfig extends AbstractColumnWidthStyleStrategy {
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 254) {
                    columnWidth = 254;
                }

                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 * 200);
                }

                //设置单元格类型
                cell.setCellType(CellType.STRING);
                // 数据总长度
                int length = cell.getStringCellValue().length();
                // 换行数
                int rows = cell.getStringCellValue().split("\n").length;
                // 默认一行高为20
                cell.getRow().setHeightInPoints(rows * 20);
            }
        }
    }

    /**
     * 计算长度
     *
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private Integer dataLength(List<CellData> 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:
                        // 换行符（数据需要提前解析好）
                        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;
                    default:
                        return -1;
                }
            }
        }
    }
}

 三、测试示例 

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.Data;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    public static void main(String[] args) {
        // 模板文件路径
        String tempFilePath = "/Users/sunny/templates/exportExcelTemplate.xlsx";
        // 导出目标文件
        String excelFileName = "/Users/sunny/templates/测试导出Excel.xlsx";
        // 根据模板文件生成目标文件
        ExcelWriter excelWriter = EasyExcel
                .write(excelFileName)
                .withTemplate(tempFilePath)
                // 自定义列宽、行高
                .registerWriteHandler(new CustomCellWeightWeightConfig())
                .build();

        // 内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置自动换行，前提内容中需要加「\n」才有效
        contentWriteCellStyle.setWrapped(true);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(null, contentWriteCellStyle);
        WriteSheet writeSheet = EasyExcel
                .writerSheet().head(UserTest.class)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .build();

        // 测试数据
        List<UserTest> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            UserTest userTest = new UserTest();
            userTest.setName("测试测试测试测试测试测试测试测试\n测试测试测试测试测试测试测试测试" + i);
            userTest.setAge(25 + i);
            userTest.setAddress("北京北京北京北京北京北京北京北京\n北京北京北京北京北京北京北京北京北京" + i);
            list.add(userTest);
        }

        // 第一种
        Map<String, List<?>> map = new HashMap<>();
        map.put("data", list);
        map.forEach((k, v) -> excelWriter.fill(new FillWrapper(k, v), writeSheet));

        // 第二种
//        excelWriter.fill(new FillWrapper("data", list), writeSheet);

        excelWriter.finish();
    }
}

@Data
class UserTest {
    private String name;
    private Integer age;
    private String address;
}

四、导出结果

参考 https://blog.youkuaiyun.com/liuao107329/article/details/126306205