EasyExcel导出自动计算列宽

Maven

<dependencies>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.22</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.47</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
    </dependency>
</dependencies>

excel导出处理模块 

@Override
    public ResultMsg export(SatisfactionTemplateExportDTO search, HttpServletRequest request, HttpServletResponse response) {
        try {
        PageInfo<SatisfactionOrderPageResqVO> userList=hosSatisfactionOrderService.findPage(search);
        if (userList.getList().size()==0){
            return new ResultMsg(false,"没有数据导出");
        }
        HosSatisfactionQuestionExample example=new HosSatisfactionQuestionExample();
        example.createCriteria().andSatisfactionIdEqualTo(search.getId());
        List<HosSatisfactionQuestion> questionList=hosSatisfactionQuestionService.selectByExample(example);
        logger.info("导出问题列表：{}",JSON.toJSONString(questionList));
        //表头
        List<List<String>> head = executeHead(questionList);
        //导出数据容器
        ArrayList<HashMap<Integer, String>> dataList = new ArrayList<>();
        userList.getList().forEach(user->{
            HashMap<Integer, String> detailShuled = new HashMap<>();
            if(!StringUtils.isEmpty(user.getAccountPhone())){
                try {
                    user.setAccountPhone(AesUtils.decrypt(user.getAccountPhone(), dbAesConfig.getAesKey()));
                } catch (Exception e) {
                    logger.error("解密电话号码失败：{}",user.getAccountPhone());
                }
            }
            // 创建一个SimpleDateFormat对象，指定日期/时间格式
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            detailShuled.put(0, user.getAccountPhone());
            detailShuled.put(1, formatter.format(user.getCreaterTime()));
            for (int i = 0; i < questionList.size(); i++){
                if (questionList.get(i).getQuestionType()== NumberConstant.THREE.getNumber()){
                    SatisfactionCommitAnswerExportResqVO  completionQuestionAnswer= hosSatisfactionCommitRecondService.getCompletionQuestionAnswer(user.getId(),questionList.get(i).getId());
                    detailShuled.put(i+2, completionQuestionAnswer!=null?completionQuestionAnswer.getContextValue():"");
                }else {
                    StringBuilder contextValueBuilder = new StringBuilder();
                    List<SatisfactionCommitAnswerExportResqVO>  choiceQuestionAnswer= hosSatisfactionCommitRecondService.getChoiceQuestionAnswer(user.getId(),questionList.get(i).getId());
                    choiceQuestionAnswer.forEach(item->{
                        contextValueBuilder.append(item.getContextValue()).append(';');
                    });
                    detailShuled.put(i+2, contextValueBuilder.length() > 0
                            ? contextValueBuilder.substring(0, contextValueBuilder.length() - 1)
                            : "");
                }
            }
            dataList.add(detailShuled);
            logger.info("导出表头列表：{}",JSON.toJSONString(detailShuled));
        });
        logger.info("导出表头列表：{}",JSON.toJSONString(head));
        logger.info("导出问题列表：{}",JSON.toJSONString(dataList));
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("满意度调查.xlsx", "UTF-8"));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        EasyExcel.write(response.getOutputStream())
                //自适应宽度
                .registerWriteHandler(new ExcelCellWriteWidthConfig())
                // 这里放入动态头
                .head(head).sheet("Sheet1").doWrite(dataList);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("导出问卷数据异常：{}", e.getMessage(), e);
        }
        return new ResultMsg(true,"数据导出成功！");
    }

计算列宽 

package com.qhjk.gyzx.admin.service.utils;

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.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

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

/**
 * @ClassName CustomCellWriteWidthConfig
 * @Description TODD
 * @Author luwei
 * @Date 2024/4/19 17:07
 **/
public class ExcelCellWriteWidthConfig 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 = this.dataLength(cellDataList, cell, isHead);
            // 单元格文本长度大于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) {
        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;
                }
            }
        }
    }
}

示例