当前位置：首页 > news >正文

springboot 之使用easyexcel导出数据到多个sheet，动态表头，自动计算列宽

news 来源：原创 2024/9/20 2:47:55

软件版本

springboot 2.7.17
easyexcel 3.0.5

代码

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.poi.ss.usermodel.Cell;
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 ExcelCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();@Overrideprotected 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 * 2 * 256);}}}}/*** 计算长度*/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;}}}}
}

//动态表头
private List<List<String>> getDynamicHeadList() {List<List<String>> headList = new ArrayList<>();List<String> head1 = new ArrayList<>();head1.add("Group Name");headList.add(head1);List<String> head2 = new ArrayList<>();head2.add("Model Name");headList.add(head2);return headList;
}
//动态数据
private List<List<String>> getDynamicBodyList() {List<List<String>> bodysList = new ArrayList<>();for(int i = 0; i < 5; i++){List<String> bodyList = new ArrayList<>();bodyList.add("Group " + i);bodyList.add("Model " + i);bodysList.add(bodyList);}return bodysList;
}

//写多sheet
final ExcelWriter excelWriter = EasyExcel.write(excel)//这里LocalDateTimeConverter类参考上一篇内容.registerConverter(new LocalDateTimeConverter()).registerWriteHandler(new ExcelCellWriteWidthConfig()).build();
WriteSheet writeSheet;
int index = 1；
for (List<List<String>> dataList : ListUtil.split(bodysList, 2)) {// 构建sheet对象writeSheet = EasyExcel.writerSheet(index,"Sheet"+index++).head(getDynamicHeadList()).build();excelWriter.write(getDynamicBodyList(), writeSheet);
}
excelWriter.finish();