1.pom文件dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.0.5/version /dependency2.自定义字典注解import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * 数据字段类型识别注解 * 需要传入数据字典的type */ Target(ElementType.FIELD) Retention(RetentionPolicy.RUNTIME) public interface DictTypeProperty { // 传入的type接收 String value() default ; }3.数据转化import com.alibaba.excel.converters.Converter; import com.alibaba.excel.metadata.GlobalConfiguration; import com.alibaba.excel.metadata.data.WriteCellData; import com.alibaba.excel.metadata.property.ExcelContentProperty; import java.lang.reflect.Field; public class DictTypeConvert implements ConverterString { Override public Class? supportJavaTypeKey() { return String.class; } Override public WriteCellData? convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { //获取当前字段 Field field contentProperty.getField(); //获取字段上的注解 DictTypeProperty declaredAnnotation field.getDeclaredAnnotation(DictTypeProperty.class); if (BlankUtil.isBlank(declaredAnnotation)) { throw new Exception(没有这个注解); } //获取DictTypeProperty的value String dictValue declaredAnnotation.value(); String label DictUtils.getDictLabel(dictValue, value);//若依dict工具 //将结果覆盖到字段上 return new WriteCellData(label); } }4.实体类import com.alibaba.excel.annotation.ExcelIgnoreUnannotated; import com.alibaba.excel.annotation.ExcelProperty; /** * author author * since 2024-11-12 */ ExcelIgnoreUnannotated public class TestPO extends BaseEntity { private static final long serialVersionUID 1L; ExcelProperty(value 状态,converter DictTypeConvert.class) DictTypeProperty(value status_type) private String status;//Integer类型转义后为String类型 }5.工具类import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; Service public class ExportUtilT { private static final Logger log LoggerFactory.getLogger(ExportUtil.class); //一个sheet装100w数据 public static final Integer PER_SHEET_ROW_COUNT 1000000; //每次查询20w数据每次写入20w数据 public static final Integer PER_WRITE_ROW_COUNT 200000; /** * 对list数据源将其里面的数据导入到excel表单 * * param response 返回数据 * param list 导出数据集合 * param totalCount 集合总条数 * param sheetName 工作表的名称 * param clazz 导出实体类 * return 结果 */ public void exportExcel(HttpServletResponse response, ListT list,int totalCount, String sheetName,ClassT clazz) throws Exception{ String fileName String.valueOf(System.currentTimeMillis()); OutputStream outputStream null; try { //必须放到循环外否则会刷新流 outputStream response.getOutputStream(); ExcelWriter excelWriter EasyExcel.write(outputStream).build(); ListListT returnList exportSurfList(list,totalCount); int i 0; for (ListT items : returnList) { i; //不做设置全部导出 WriteSheet writeSheet EasyExcel.writerSheet(i, sheetName i).head(clazz) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build(); excelWriter.write(items, writeSheet); } // 下载EXCEL返回给前端stream流 response.setContentType(application/octet-stream); response.setCharacterEncoding(utf-8); response.setHeader(Content-disposition, attachment;filename*utf-8 fileName .xlsx); excelWriter.finish(); outputStream.flush(); outputStream.close(); log.info(*********列表导出结束**************); } catch (Exception e) { e.printStackTrace(); } finally { if (outputStream ! null) { outputStream.close(); } } } public ListListT exportSurfList(ListT list,int totalCount) throws Exception{ ListListT returnList new ArrayList(); //记录总数:实际中需要根据查询条件进行统计即可:一共多少条 //每一个Sheet存放100w条数据 Integer sheetDataRows PER_SHEET_ROW_COUNT; //每次写入的数据量20w,每页查询20W Integer writeDataRows PER_WRITE_ROW_COUNT; //计算需要的Sheet数量 Integer sheetNum totalCount % sheetDataRows 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows 1); //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据) Integer oneSheetWriteCount sheetDataRows / writeDataRows; //计算最后一个sheet需要写入的次数 Integer lastSheetWriteCount totalCount % sheetDataRows 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount % sheetDataRows / writeDataRows 1)); //开始分批查询分次写入 //注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数 for (int i 0; i sheetNum; i) { //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount for (int j 0; j (i ! sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j) { //数据分页writeDataRows //开始分批查询分次写入 ListT newList listSplit(list,j 1 oneSheetWriteCount * i, writeDataRows); returnList.add(newList); } } return returnList; } public ListT listSplit(ListT list, int pageNum, int pageSize) { ListT newListnull; int totallist.size(); newListlist.subList(pageSize*(pageNum-1), ((pageSize*pageNum)total?total:(pageSize*pageNum))); return newList; } }6.使用示例public void export(HttpServletResponse response, TestDTO testDTO) throws Exception { ListTestPO list mapper.getList(testDTO); exportUtil.exportExcel(response,list,list.size(),导出结果,TestPO.class); }7.问题实体类中使用的字典个数多可能会报错8.参考链接使用EasyExcel实现excel导出支持百万大数据量导出-----超简单_easyexcel导出大量数据-CSDN博客Java中easyExcel框架字段转换具体实现根据数据字典转换对注解ExcelProperty中converter的具体使用_java_weixin_54566947-开放原子开发者工作坊java中把查询出来的结果进行分页_java 对于查询两次的结果进行合并分页-CSDN博客