java poi中excel自适应行宽

自适应行宽实现方式

package com.olivia.sdk.utils.model;


import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.*;

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

/**
 * @Description: 自适应行宽
 */
public class CustomCellWriteHeightConfig extends AbstractColumnWidthStyleStrategy {

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

  public static HorizontalCellStyleStrategy setCellStyle() {


    // 表头策略
    WriteCellStyle style = new WriteCellStyle();
    // 背景设置为白色
    style.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
    style.setWriteFont(new WriteFont());
    WriteFont headerFont = style.getWriteFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBold(true);
    headerFont.setColor(IndexedColors.BLACK.getIndex());
    style.setWrapped(false);

    // 内容的策略
    WriteCellStyle contextStyle = new WriteCellStyle();
    contextStyle.setWriteFont(new WriteFont());
    contextStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    contextStyle.setBorderRight(BorderStyle.THIN);
    contextStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    contextStyle.setBorderLeft(BorderStyle.THIN);
    contextStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    contextStyle.setBorderTop(BorderStyle.THIN);
    contextStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    contextStyle.setBorderBottom(BorderStyle.THIN);
    contextStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    contextStyle.setWrapped(false);
    WriteFont dataFont = contextStyle.getWriteFont();
    dataFont.setFontName("Arial");
    dataFont.setFontHeightInPoints((short) 10);

    return new HorizontalCellStyleStrategy(style, contextStyle);
  }

  @Override
  protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
    boolean needSetWidth = isHead || CollUtil.isNotEmpty(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 > 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 * 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 {
      WriteCellData<?> cellData = cellDataList.getFirst();
      CellDataTypeEnum type = cellData.getType();
      if (type == null) {
        return -1;
      } else {
        return switch (type) {
          case STRING -> {
            int index = cellData.getStringValue().indexOf("\n");
            yield index != -1 ?
                cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
            // 换行符（数据需要提前解析好）
          }
          case BOOLEAN -> cellData.getBooleanValue().toString().getBytes().length;
          case NUMBER -> cellData.getNumberValue().toString().getBytes().length;
          default -> -1;
        };
      }
    }
  }


}

使用方式

 public static <T> void export(Class<T> clazz, List<T> data, String sheetName) {
    HttpServletResponse response = ReqResUtils.getResponse();
    try (ServletOutputStream outputStream = response.getOutputStream();) {
      response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(sheetName + ".XLSX", "UTF-8"));
      response.setContentType("application/vnd.ms-excel;charset=UTF-8");
      EasyExcel.write(outputStream, clazz)
          .registerWriteHandler(CustomCellWriteHeightConfig.setCellStyle())
          .registerWriteHandler(new CustomCellWriteHeightConfig()).sheet(sheetName).doWrite(data);

    } catch (Exception e) {
      log.error("export error :{}", e.getMessage(), e);
    }
  }