前言:
客户要求Excel自适应列宽,本想百度cv代码,但都有些细节问题,只好自己动手丰衣足食。
为了广大沦落人避免无效百度,特此分享。 转载请标明出处, 谢谢。
正文:
自定义处理器:
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.nacos.shaded.com.google.common.collect.Maps;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.nio.charset.StandardCharsets;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
/**
* EasyExcel自适应列宽专用
*/
public class AutoCellWriteWidthHandle implements RowWriteHandler {
private final Map<Integer, Map<Integer, Integer>> MAX_COLUMN_WIDTH_CACHE = new HashMap<>(8);
private final int MIN_WIDTH = 800;
private final DataFormatter dataFormatter = new DataFormatter();
//补丁: 用于判断是否表头数据
private Integer startHeadRow;
private Integer endHeadRow;
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
//因为使用填充模板的方式表头无法进来,为了兼容统一手动处理表头
if (isHead) {
return;
}
//补丁:EasyExcel判断表头错误会误入: isHead = false, 但实际数据是表头
isHead = ObjectUtil.isEmpty(startHeadRow) || ObjectUtil.isEmpty(endHeadRow) || (row.getRowNum() >= startHeadRow && row.getRowNum() <= endHeadRow);
Integer sheetNo = writeSheetHolder.getSheetNo();
if (CollUtil.isEmpty(MAX_COLUMN_WIDTH_CACHE.get(sheetNo)) && isHead) {
//踩坑标注; 不能通过writeSheetHolder获取sheet对象, 这会拿不到已填充的数据 因为EasyExcel是最后才把sheet同步到writeSheetHolder
Sheet sheet = row.getSheet();
HashMap<Integer, Integer> headWidthMap = Maps.newHashMap();
MAX_COLUMN_WIDTH_CACHE.put(sheetNo, headWidthMap);
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
//表头可能存在多行
for (int headRowIndex = startHeadRow = sheet.getFirstRowNum(); headRowIndex < (endHeadRow = row.getRowNum()); headRowIndex++) {
Row headRow = sheet.getRow(headRowIndex);
if (ObjectUtil.isEmpty(headRow)) {
continue;
}
for (int cellIndex = headRow.getFirstCellNum(); cellIndex < headRow.getLastCellNum(); cellIndex++) {
Cell headCell = headRow.getCell(cellIndex);
if (ObjectUtil.isEmpty(headCell) || this.isMeagerCell(mergedRegions, headCell)) {
continue;
}
//获取字符长度
int newHeadCellWidth = this.getDataLength(headCell);
Integer oldHeadMaxWidth = headWidthMap.get(headCell.getColumnIndex());
int currentMaxWidth = ObjectUtil.isEmpty(oldHeadMaxWidth) ? newHeadCellWidth : Math.max(oldHeadMaxWidth, newHeadCellWidth);
headWidthMap.put(cellIndex, currentMaxWidth);
}
}
}
for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
//获取字符长度
Cell cell = row.getCell(cellIndex);
if (ObjectUtil.isEmpty(cell)) {
continue;
}
int newHeadCellWidth = this.getDataLength(cell);
//比较列宽度,取最大值
Map<Integer, Integer> cellMaxWidthMap = Optional.ofNullable(MAX_COLUMN_WIDTH_CACHE.get(writeSheetHolder.getSheetNo())).orElse(Maps.newHashMap());
Integer mapMaxWidth = Optional.ofNullable(cellMaxWidthMap.get(cell.getColumnIndex())).orElse(MIN_WIDTH);
int currentMaxWidth = Math.max(mapMaxWidth, newHeadCellWidth);
//设置列宽
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), currentMaxWidth);
cellMaxWidthMap.put(cell.getColumnIndex(), currentMaxWidth);
MAX_COLUMN_WIDTH_CACHE.put(writeSheetHolder.getSheetNo(), cellMaxWidthMap);
}
}
private int getDataLength(Cell cell) {
String cellValue = dataFormatter.formatCellValue(cell);
return cellValue.getBytes(StandardCharsets.UTF_8).length * 256 + MIN_WIDTH;
}
/**
* 判断是否合并单元格
* @param mergedRegions
* @param cell
* @return
*/
private boolean isMeagerCell(List<CellRangeAddress> mergedRegions, Cell cell) {
Optional<CellRangeAddress> mergedRegionOptional = mergedRegions.stream().filter(mergedRegion -> mergedRegion.isInRange(cell)).findFirst();
//纵向合并忽略, 横向合并才处理
return mergedRegionOptional.isPresent() && mergedRegionOptional.get().getLastColumn() - mergedRegionOptional.get().getFirstColumn() > 0;
}
}
使用:通过ExcelWriterBuilder注入