前言:在做Excel导出的时候自动设置行高失效,找了各种方法都没法解决
最后发现是同一行cell中HeightInPoints被覆盖了的问题。。。
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 org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.util.CollectionUtils;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class AutoExcelStyleStrategy extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 50;
private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>(8);
@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 > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
int width = columnWidth * 400;
if (maxColumnWidth == null || width > maxColumnWidth) {
Sheet sheet = writeSheetHolder.getSheet();
// 自适应宽度
if (width > 65280) {
width = 65280;
}
sheet.setColumnWidth(cell.getColumnIndex(), width);
maxColumnWidthMap.put(cell.getColumnIndex(), width);
}
// 设置单元格类型
cell.setCellType(CellType.STRING);
// 数据总长度
// 换行数
int rows = cell.getStringCellValue().split("\n").length;
// 默认一行高为20
if (cell.getRow().getHeightInPoints() < rows * 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;
}
}
}
}
}
输出的时候注册一下就可以了
EasyExcel.write(response.getOutputStream())
.registerWriteHandler(new AutoExcelStyleStrategy())
.build();
设置行宽的方法