Java多人导出Excel干崩服务器？排队导出功能实现方案

一、问题分析

多人同时导出Excel时，服务器资源（如CPU、内存、I/O）会被大量消耗，导致性能下降甚至崩溃。主要问题包括：

资源消耗大：每次导出都需要从数据库中查询数据并进行格式化、计算等操作，这会大量消耗服务器资源。
数据库压力大：大量并发请求会导致数据库查询变慢，甚至耗尽连接池。
网络瓶颈：多个用户同时下载大文件会占用大量带宽。

二、解决方案

（一）实现排队导出功能

通过维护一个固定大小的导出队列，限制并发导出任务的数量，确保服务器资源的合理利用。

1. ExportQueue队列

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import java.util.LinkedList;@Component
@Slf4j
public class ExportQueue {private final int MAX_CAPACITY = 10; // 队列最大容量private LinkedList<ExportUser> queue = new LinkedList<>();public synchronized LinkedList<ExportUser> add(ExportUser sysUser) {while (queue.size() >= MAX_CAPACITY) {try {log.info("当前排队人已满，请等待");wait();} catch (InterruptedException e) {e.printStackTrace();}}queue.add(sysUser);log.info("目前导出队列排队人数：" + queue.size());notifyAll();return queue;}public synchronized ExportUser getNextSysUser() {while (queue.isEmpty()) {try {wait();} catch (InterruptedException e) {e.printStackTrace();}}ExportUser sysUser = queue.remove();notifyAll();return sysUser;}
}

2. AbstractExport导出类

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.PageUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.system.api.domain.ExportUser;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;@Slf4j
public abstract class AbstractExport<T, K> {public abstract void export(ExportUser sysUser) throws InterruptedException;public void export(HttpServletResponse response, int pageSize, T t, Class<K> k, String fileName) throws Exception {ExcelWriter writer = null;try {writer = getExcelWriter(response, fileName);int total = this.countExport(t);int loopCount = PageUtil.totalPage(total, pageSize);BeanUtil.setProperty(t, "pageSize", pageSize);for (int i = 0; i < loopCount; i++) {BeanUtil.setProperty(t, "pageNum", PageUtil.getStart(i + 1, pageSize));List<K> kList = this.getExportDetail(t);WriteSheet writeSheet = EasyExcel.writerSheet(fileName).head(k).build();writer.write(kList, writeSheet);}} catch (Exception e) {String msg = "导出" + fileName + "异常";log.error(msg, e);throw new Exception(msg + e);} finally {if (writer != null) {writer.finish();}}}public ExcelWriter getExcelWriter(HttpServletResponse response, String fileName) throws IOException {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");String fileNameUtf = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameUtf + ".xlsx");return EasyExcel.write(response.getOutputStream()).build();}public abstract void complexFillWithTable(T t, String fileName, HttpServletResponse response);public abstract int countExport(T t);public abstract List<K> getExportDetail(T t);
}

3. ExportImpl导出实现方法

import com.alibaba.excel.ExcelWriter;
import com.example.system.api.domain.ExportUser;
import com.example.system.config.AbstractExport;
import com.example.system.config.ExportQueue;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.LinkedList;
import java.util.List;@Service
@Slf4j
public class ExportImpl extends AbstractExport {@Autowiredprivate ExportQueue exportQueue;@Overridepublic void export(ExportUser sysUser) throws InterruptedException {log.info("导出文件方法执行～～～～～～～～～");LinkedList<ExportUser> queue = exportQueue.add(sysUser);log.info("导出队列：" + queue);Thread.sleep(20000); // 模拟导出处理时间ExportUser nextSysUser = exportQueue.getNextSysUser();log.info("移除后获取下一个排队的用户: " + nextSysUser.getUserName());}@Overridepublic void export(HttpServletResponse response, int pageSize, Object o, Class k, String fileName) throws Exception {super.export(response, pageSize, o, k, fileName);}@Overridepublic ExcelWriter getExcelWriter(HttpServletResponse response, String fileName) throws IOException {return super.getExcelWriter(response, fileName);}@Overridepublic void complexFillWithTable(Object o, String fileName, HttpServletResponse response) {}@Overridepublic int countExport(Object o) {return 0;}@Overridepublic List getExportDetail(Object o) {return null;}
}

4. 测试controller

import com.example.system.api.domain.ExportUser;
import com.example.system.service.impl.ExportImpl;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;@RestController
@RequestMapping("/export")
@Slf4j
public class ExportController {@Autowiredprivate ExportImpl export;@PostMapping("/exportFile")public void exportFile() {new Thread(new Runnable() {@SneakyThrows@Overridepublic void run() {Thread thread1 = Thread.currentThread();ExportUser sysUser = new ExportUser();sysUser.setUserName(thread1.getName());export.export(sysUser);}}).start();}
}