通过实现Callable接口,使用多线程分页查询导出数据，使用SXSSFWorkbook写数据

说明

导出大量数据到excel文件，文件格式为.xlsx，版本07年及以上，使用多线程查询数据库数据，将数据通过SXSSFWorkbook将数据写入excel文件；避免堆溢出

实现Callable接口的ThredQuery类

package com.oims.thread;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.oims.util.SpringContextUtil;
import org.springframework.context.ApplicationContext;

import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.concurrent.ConcurrentHashMap;

/**
 * 实现Callable接口类
 */
public class ThredQuery implements Callable<List> {

	/**
	* 通过ApplicationContext获取bean 
	*/
    private ApplicationContext ac = SpringContextUtil.getApplicationContext();

    /**
     * 业务接口
     */
    private ServiceImpl serviceImp = null;
    /**
     * 业务参数
     */
    private Map<String, Object> map = new ConcurrentHashMap<>();
    /**
     * 业务接口名称
     */
    private String InterfaceName;
    /**
     * 业务方法名称
     */
    private String methodName;

    /**
     * 当前页
     */
    private int currentPage;

    /**
     * 每页显示数量
     */
    private int num;

    /**
     * @param currentPage   当前页
     * @param num           每页数量
     * @param map           业务参数
     * @param InterfaceName 业务接口
     * @param methodName    业务方法
     */
    public ThredQuery(int currentPage, int num, Map map, String InterfaceName, String methodName) {
        this.map = map;
        this.InterfaceName = InterfaceName;
        this.methodName = methodName;
        this.currentPage= currentPage;
        this.num = num;
    }

    @Override
    public List call() throws Exception {
        //返回数据给Future
        map.put("startRow", (currentPage- 1) * num);
        map.put("limit", num);
        //获取业务接口bean
        serviceImp = (ServiceImpl) ac.getBean(InterfaceName);
        //需要调用的业务方法（方法名称，参数类型）
        Method idMethod = serviceImp.getClass().getMethod(methodName, Map.class);
        Map<String, Object> mapCoty = new HashMap<>();
        mapCoty.putAll(map);
        //调用业务方法查询数据，返回集合
        return  (List) idMethod.invoke(serviceImp, mapCoty);
    }
}

在call方法中，将在构造方法中传入的参数map对象重新创建新的对象并赋值，避免多线程情况下调用业务方法传对象值只使用最后一个线程所传值的原因（每个线程从构造方法传的参数不一样，如果在call方法使用传进来的基本数据类型参数不会出现问题，但是使用传进来非基本数据类型参数时，需要重新创建一个新的对象，将原对象的map 值重新赋值给新的对象mapCoty给业务接口调用才不会出现问题）。

controller中的方法

/**
     * 导出
     *
     * @param response
     * @param request
     */
    @RequestMapping("/exportResponse")
    public void exportResponse(HttpServletResponse response, HttpServletRequest request) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        long startTime = System.currentTimeMillis();    //开始时间
        System.out.println("strat execute time:===== " + startTime);
        String url = FileSource.getUrl();//系统根路径
        String srcPath = url + File.separator + "upload/test.xlsx";
        //通过读取文件的形式写入内容，主要是因为表头多行，及存在合并单元格
        XSSFWorkbook workbook2 = new XSSFWorkbook(srcPath);
        SXSSFWorkbook wb = new SXSSFWorkbook(workbook2, -1);//在内存中保持100行，超过100行将被刷新到磁盘
        SXSSFSheet sh = wb.getSheetAt(0); // 获取原有的sheet对象
        PeEntRegistration peEntRegistration = new PeEntRegistration();
        QueryWrapper queryWrapper = new QueryWrapper();
        peEntRegistration.setYesManage("是");
        queryWrapper.setEntity(peEntRegistration);
        int count = peEntRegistrationService.count(queryWrapper);//查询数据库需要导出的总数量
        //====
        Map<String, Object> map = new HashMap<>();
        map.put("yesManage", "是");
        List<BeRegInfoExportResult> peEntRegistrationList = new ArrayList<>(count);//接收返回结果
        int num = 5000;//一次查询多少条
        int times = count % num > 0 ? count / num + 1 : count / num; //需要查询的次数总页数
        int bindex = 1;//开始页数
        //Callable用于产生结果
        List<Callable<List>> tasks = new ArrayList<>();
        for (int i = 0; i < times; i++) {
            Callable<List> qfe = new ThredQuery(bindex, num, map, "peEntRegistrationService", "queryListBy");
            tasks.add(qfe);
            bindex++;
        }
        //定义固定长度的线程池  防止线程过多
        ExecutorService executorService = Executors.newFixedThreadPool(15);
        //Future用于获取结果
        List<Future<List>> futures = executorService.invokeAll(tasks);
        //处理线程返回结果
        if (futures != null && futures.size() > 0) {
            for (Future<List> future : futures) {
                peEntRegistrationList.addAll(future.get());
            }
        }
        executorService.shutdown();//关闭线程池
        long end = System.currentTimeMillis();
        System.out.println("线程查询数据用时:" + (end - startTime) / 1000 + "ms");
        System.out.println("finished execute  time:======== " + (System.currentTimeMillis() - startTime) / 1000 + "m");
        SXSSFRow row = null;
        SXSSFCell cell = null;
        for (int i = 0; i < peEntRegistrationList.size(); i++) {
            row = sh.createRow(i + 2);//从第三行开始写入数据
            cell = row.createCell(0);
            cell.setCellValue(peEntRegistrationList.get(i).getEnterpriseName());
            cell = row.createCell(1);
            cell.setCellValue(peEntRegistrationList.get(i).getRegistTime() != null ? sdf.format(peEntRegistrationList.get(i).getRegistTime()) : "");
            cell = row.createCell(2);
            cell.setCellValue(peEntRegistrationList.get(i).getRegistType());
            cell = row.createCell(3);
            cell.setCellValue(peEntRegistrationList.get(i).getForeignSources());
            cell = row.createCell(4);
            cell.setCellValue(peEntRegistrationList.get(i).getRegistCapital() + "");
            cell = row.createCell(5);
            cell.setCellValue(peEntRegistrationList.get(i).getIndustry());
            cell = row.createCell(6);
            cell.setCellValue(peEntRegistrationList.get(i).getBusinessSize());   
        }
        peEntRegistrationList.clear(); 
        FileOutputStream fileOut = new FileOutputStream(url + File.separator + "upload/test1.xlsx");
        wb.write(fileOut);
        fileOut.close();
        wb.dispose();
        long finishedTime = System.currentTimeMillis();    //处理完成时间
        System.out.println("finished execute  time:======== " + (finishedTime - startTime) / 1000 + "m");
        //下载文件
        FileSource.download("/upload/test1.xlsx", request, response);
    }

FileSource下载工具类

package com.oims.util;

import org.springframework.util.ResourceUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.net.URLEncoder;
import java.util.Base64;

public class FileSource {
    
    /**
     * 下载
     *
     * @param url      文件夹及到文件的路径，不是全路径（例如：/upload/test.doc）
     * @param request
     * @param response
     * @throws Exception
     */
    public static void download(String url, HttpServletRequest request, HttpServletResponse response) throws Exception {
        String path = getUrl();
        url = path + url;
        String fileName = url.substring(url.lastIndexOf(File.separator), url.length());
        download(url, fileName, request, response);
    }

    /**
     * 下载
     *
     * @param url      全文件路径（例如：D:/upload/test.doc）
     * @param name     新文件名
     * @param request
     * @param response
     * @throws Exception
     */
    public static void download(String url, String name, HttpServletRequest request, HttpServletResponse response) throws Exception {
        String encodedFileName = name.trim().replaceAll(" ", "");
        String agent = request.getHeader("User-Agent");
        boolean isMSIE = ((agent != null) && (agent.toUpperCase().indexOf("MSIE") != -1));
        if (isMSIE) {
            encodedFileName = URLEncoder.encode(encodedFileName, "UTF-8");
        } else if (agent.contains("Firefox")) {
            // 火狐浏览器，此处使用java8
            encodedFileName = "=?utf-8?B?" + Base64.getEncoder().encodeToString(encodedFileName.getBytes("utf-8")) + "?=";
        } else {
            encodedFileName = new String(encodedFileName.getBytes("UTF-8"), "ISO8859-1");
        }
        response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedFileName + "\"");
        File file = new File(url);
        FileInputStream is = new FileInputStream(file);
        ServletOutputStream os = response.getOutputStream();
        byte[] b = new byte[1024];
        int len = 0;
        try {
            while ((len = is.read(b, 0, 1024)) != -1) {
                os.write(b, 0, len);
            }
        } finally {
            is.close();
            os.flush();
        }
    }
	
	/**
	 * 获取项目的根路径，springboot项目打成jar或者war之后运行，文件只能存到项目外的地方
	 */
    public static String getUrl() {
        try {
            String url = new File(ResourceUtils.getURL("classpath:").getPath()).getParentFile().getParentFile().getParent();
            return url.replace("file:", "") + File.separator;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }