EasyExcel导出列表

通过easyexcel导出列表数据

根据列表内容自适应宽高。

文件名冲突，修改文件名递增设置。

依赖

            <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>${easyexcel.version}</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel-core</artifactId><version>${easyexcel.version}</version><scope>compile</scope></dependency>

  <easyexcel.version>3.3.2</easyexcel.version>

 具体实现

 public void export() {//获取列表集合，根据自己业务逻辑获取List<Api> list = this.list();String fileName = "服务发布.xlsx";String filePath = getUniqueFilePath(fileName);EasyExcel.write(filePath, ApiExcel.class)
.registerWriteHandler(new CustomCellWriteWidthConfig())
//.registerWriteHandler(new CustomCellWriteHeightConfig())
.sheet("服务发布列表").doWrite(list);}

 判断文件是否存在。

 private static String getUniqueFilePath(String baseFileName) {File file = new File(baseFileName);int counter = 1;while (file.exists()) {String newFileName = String.format("%s(%d).xlsx", baseFileName.replace(".xlsx", ""), counter);file = new File(newFileName);counter++;}return file.getAbsolutePath();}

自适应列宽类

package com.cait.fm.console.common;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 java.util.HashMap;
import java.util.List;
import java.util.Map;/*** @author: jg* @date: 2024-12-06* @description:*/
public class CustomCellWriteWidthConfig 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 || !cellDataList.isEmpty();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 > 10 && columnWidth <= 20) {columnWidth = 20;} else if (columnWidth <= 10) {columnWidth = 10;}Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());if (maxColumnWidth == null || columnWidth > maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);Sheet sheet = writeSheetHolder.getSheet();sheet.setColumnWidth(cell.getColumnIndex(), 256 * columnWidth + 184);}}}}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:// 换行符（数据需要提前解析好）String stringValue = cellData.getStringValue();if (stringValue.contains("\n")) {int index = stringValue.indexOf("\n");return index != -1 ? stringValue.substring(0, index).getBytes().length + 1 : stringValue.getBytes().length + 1;} else if (stringValue.contains("(")) {return stringValue.getBytes().length;} else {return stringValue.getBytes().length;}case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length;default:return -1;}}}}}

 自适应行高

public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {/*** 默认高度*/private static final Integer DEFAULT_HEIGHT = 300;@Overrideprotected void setHeadColumnHeight(Row row, int relativeRowIndex) {}@Overrideprotected void setContentColumnHeight(Row row, int relativeRowIndex) {Iterator<Cell> cellIterator = row.cellIterator();if (!cellIterator.hasNext()) {return;}// 默认为 1 行高度int maxHeight = 1;while (cellIterator.hasNext()) {Cell cell = cellIterator.next();if (cell.getCellType() == CellType.STRING) {String value = cell.getStringCellValue();for (int i = 0; i < value.length(); i += 10) {if (i + 10 < value.length()) {value = value.substring(0, i) + "\n" + value.substring(i, i + 10) + value.substring(i + 10);} else {value = value.substring(0, i) + "\n" + value.substring(i);}}if (value.contains("\n")) {int length = value.split("\n").length;maxHeight = Math.max(maxHeight, length);}}}row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));}@Overridepublic void afterRowDispose(RowWriteHandlerContext context) {if (context.getHead() != null) {if(ObjectUtils.isEmpty(context.getRelativeRowIndex())){return;}if (Boolean.TRUE.equals(context.getHead())) {this.setHeadColumnHeight(context.getRow(), context.getRelativeRowIndex());} else {this.setContentColumnHeight(context.getRow(), context.getRelativeRowIndex());}}}
}