本文主要讲了如何使用 EasyExcel 插件，在导出 Excel 时，设置行高，列宽，表头格式，内容字体大小等工具类。

1、代码使用的依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.2.1</version>
</dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.20</version>
</dependency>

2、行高列宽设置工具类

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.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 org.springframework.util.CollectionUtils;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** 表头宽度根据数据内容自适应*/
public class CustomWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();/*** 设置列宽*/@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());int columnWidth = this.dataLength(cellDataList, cell, isHead);if (columnWidth >= 0) {if (columnWidth > 255) {columnWidth = 255;}Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());if (maxColumnWidth == null || columnWidth > maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);}}}}/*** 数据长度*/private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {// 头直接返回原始长度if (isHead) {return cell.getStringCellValue().getBytes().length;} else {// 不是头的话 看是什么类型 用数字加就可以了WriteCellData cellData = cellDataList.get(0);CellDataTypeEnum type = cellData.getType();if (type == null) {return -1;} else {switch (type) {case STRING:return cellData.getStringValue().getBytes().length + 3;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length + 1;case NUMBER:return cellData.getNumberValue().toString().getBytes().length + 3;default:return -1;}}}}/*** 单元格样式策略*/public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 设置边框contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);contentWriteCellStyle.setBorderRight(BorderStyle.THIN);contentWriteCellStyle.setBorderTop(BorderStyle.NONE);// 配置字体WriteFont contentWriteFont = new WriteFont();// 字体contentWriteFont.setFontName("宋体");// 字体大小contentWriteFont.setFontHeightInPoints(fontHeightInPoints);// 设置加粗contentWriteFont.setBold(false);contentWriteCellStyle.setWriteFont(contentWriteFont);// 【水平居中需要使用以下两行】// 设置文字左右居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.JUSTIFY);// 设置文字上下居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置 自动换行contentWriteCellStyle.setWrapped(true);// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// contentWriteCellStyle.setFillForegroundColor(IndexedColors.PINK.getIndex());// 样式策略return new HorizontalCellStyleStrategy(null, contentWriteCellStyle);}
}

3、代码如何使用工具类

public void exportInfo(Request request, HttpServletResponse response) {try {response.setContentType("multipart/form-data");response.setCharacterEncoding("utf-8");List<UserDto> items = new ArrayList<>();for(int i = 0; i < 10; i++) {UserDto user = new UserDto();user.setName("长江" + i + "号");user.setType(i);items.add(user);}String fileName = "用户信息";response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(fileName, "UTF-8") + ExportFileType.EXCEL_XLSX.getDefaultExtName());EasyExcel.write(response.getOutputStream(), OnlineInfoResultExcelDto.class).autoCloseStream(true).charset(/*fileType.equals(ExportFileType.EXCEL_XLSX.ordinal()) ? StandardCharsets.UTF_8 : */Charset.forName("GBK")).sheet("Sheet0").registerWriteHandler(getHorizontalCellStyleStrategy()).registerWriteHandler(new CustomWidthStyleStrategy()).doWrite(data);} catch (IOException e) {throw new OnlineMonitorException(500, "export error");}
}

4、导出类

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;@Data
public class User {@ExeclProperty("姓名")private String name;@ExeclProperty("类型")private Integet type;}