百万级数据导出工具类、java导出万级数据记录、java导出万级xls文件

导出xls是客户经常需要的功能但数据量一大就不好用了,涉及SQL性能CPU读写等瓶颈哪怕有easyexcel和poi库类导出几万几十万不优化也很难导出来本文就是在引用库后的优化工具pom坐标dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version2.2.11/version /dependency !-- POI导入导出 -- dependency groupIdorg.apache.poi/groupId artifactIdpoi/artifactId version3.17/version /dependency dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version3.17/version /dependency dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml-schemas/artifactId version3.17/version /dependency工具类代码import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import javax.servlet.http.HttpServletResponse; import java.util.*; import java.util.concurrent.*; import java.util.function.BiFunction; import java.util.function.Function; import java.util.stream.Collectors; public class ExcelExportUtil { // 队列大小最多缓存多少批 private static final int DEFAULT_QUEUE_SIZE 10; // 转换线程数建议CPU核心数 private static final int DEFAULT_THREAD_COUNT Runtime.getRuntime().availableProcessors(); // 结束标志 private static final List? END_FLAG Collections.emptyList(); /** * 通用Map游标导出 */ public static Q, D, E void exportByCursor( Q queryParams, // 查询函数返回一页数据 BiFunctionQ, MapString,Object, ListD queryFunction, // DTO转换 FunctionD, E convertFunction, // 游标生成函数 FunctionListD, MapString,Object keyExtractor, ClassE excelClass, String sheetName, HttpServletResponse response ) { ExecutorService executor Executors.newFixedThreadPool(DEFAULT_THREAD_COUNT); ExecutorService writerExecutor Executors.newSingleThreadExecutor(); BlockingQueueListE queue new LinkedBlockingQueue(DEFAULT_QUEUE_SIZE); try { // 设置响应头 setResponseHeader(response, sheetName); ExcelWriter writer EasyExcel.write( response.getOutputStream(), excelClass ).build(); WriteSheet sheet EasyExcel.writerSheet(sheetName).build(); // 写线程 Future? writerFuture writerExecutor.submit(() - { while (true) { ListE data; try { data queue.take(); } catch (InterruptedException e) { Thread.currentThread().interrupt(); throw new RuntimeException( 写线程被中断, e ); } if (data END_FLAG) { break; } writer.write(data, sheet); } }); MapString,Object lastKey null; while (true) { // 查询一页 ListD records queryFunction.apply( queryParams, lastKey ); if (records null || records.isEmpty()) { break; } // 多线程转换 ListE exportList executor.submit(() - records.stream() .map(convertFunction) .collect(Collectors.toList()) ).get(); // 放入队列 queue.put(exportList); // 生成下一页游标 lastKey keyExtractor.apply(records); if (lastKey null) { break; } // 释放内存 records.clear(); } // 结束信号 queue.put((ListE) END_FLAG); writerFuture.get(); writer.finish(); } catch (Exception e) { throw new RuntimeException( 导出失败, e ); } finally { executor.shutdown(); writerExecutor.shutdown(); } } /** * 设置下载Header */ private static void setResponseHeader( HttpServletResponse response, String fileName ) { try { response.setContentType( application/vnd.openxmlformats-officedocument.spreadsheetml.sheet ); String encodedName java.net.URLEncoder.encode( fileName - new java.text.SimpleDateFormat(yyyyMMdd) .format(new Date()), UTF-8 ); response.setHeader( Content-Disposition, attachment; filename*UTF-8 encodedName .xlsx ); } catch (Exception e) { throw new RuntimeException( 设置响应头失败, e ); } } }调用Override public void export(PutOnQueryDTO queryParams, HttpServletResponse response) { ExcelExportUtil.exportByCursor( queryParams, (params, lastKey) - { Date lastStoreInDate null; String lastTerminalCode null; if (lastKey ! null) { lastStoreInDate (Date) lastKey.get(storeInDate); lastTerminalCode (String) lastKey.get(terminalCode); } return findPutOnExport( params, lastStoreInDate, lastTerminalCode, 1000 ); }, this::toPutOnExportSingle, records - { if (records.isEmpty()) { return null; } PutOnDTO last records.get(records.size() - 1); MapString, Object key new HashMap(); key.put(storeInDate, last.getStoreInDate()); key.put(terminalCode, last.getTerminalCode()); return key; }, PutOnExport.class, 上架记录, response ); } private PutOnExport toPutOnExportSingle(PutOnDTO dto){ PutOnExport exportnew PutOnExport(); BeanUtils.copyProperties(dto,export); return export; }如果文章对你有帮助记得点赞哦