java实现百万级别数据导入excel在浏览器直接下来。使用SXSSFWorkbook和多线程

## 一：可能需要的maven依赖。我项目的依赖有点多。要是不够或者不对自己在找找吧

	<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>5.2.2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.2.2</version>
			<exclusions>
				<exclusion>
					<groupId>org.apache.logging.log4j</groupId>
					<artifactId>log4j-api</artifactId>
				</exclusion>
			</exclusions>
		</dependency>


		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-api</artifactId>
			<version>2.17.1</version>
		</dependency>


		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>4.0.0</version>
		</dependency>

二： 功能代码

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.Executor;
import java.util.concurrent.atomic.LongAdder;
import java.util.stream.Collectors;
//这些依赖可能不够用。自己在引入吧

//把线程池注入到spring
@Resource(name = "taskExector")
 private Executor executor;


 /**
     * 导出excel
     *
     * @return
     */
    @GetMapping("/leading")
    public Response leading(HttpServletResponse response, @RequestParam("uuIds") String uuIds) throws Exception {
//按照指定条数进行集合的分组
 Integer chunkSize = 100000;
//查询出需要导出的数据的唯一标识集合
List<Integer> ids = simulationMapper.selectIdsByUuId(uuId);
        List<List<Integer>> chunkedList = new LinkedList<>();
        for (int i = 0; i < ids.size(); i += chunkSize) {
            chunkedList.add(ids.subList(i, Math.min(i + chunkSize, ids.size())));
        }
        //内存中只创建100个对象，写临时文件，当超过100条，就将内存中不用的对象释放。
        //如果不用这个会撑爆内存
        Workbook wb = new SXSSFWorkbook(100);

        LongAdder count = new LongAdder();

        List<CompletableFuture<Void>> id = chunkedList.stream().map(x -> CompletableFuture.runAsync(() -> {

			//分组后查询出全部的数据。我是查出全部的集合后按照每100000条分出新集合。之后多线程查询每个小集合的全部数据
            List<Simulation> simulations = simulationMapper.selectList(new QueryWrapper<Simulation>().in("id", x));
            int pageRowNo = 0;  //页行号
            Sheet sheet = null;
            Row nRow = null;
            Cell nCell = null;

            synchronized (Exception.class) {
                long sum = count.sum();
                count.increment();
                wb.createSheet("第" + sum + "个工作簿");//建立新的sheet对象
                sheet = wb.getSheetAt((int) sum);//动态指定当前的工作表
            }
            for (Simulation dataInterface : simulations) {
                nRow = sheet.createRow(pageRowNo++);//创建指定的行
                nCell = nRow.createCell(0);//创建指定的单元格
                nCell.setCellValue("写入数据");//像指定的单元格写入数据


				nCell = nRow.createCell(1);//创建指定的单元格
                nCell.setCellValue("写入数据");//像指定的单元格写入数据
                
              
       
            }
            //executor 这个是自定义的线程池。在下面会提供代码
        }, executor)).collect(Collectors.toList());
        for (CompletableFuture<Void> voidCompletableFuture : id) {
            voidCompletableFuture.join();
        }
        OutputStream output;
        output = response.getOutputStream();
        //清空缓存
        response.reset();
        //定义浏览器响应表头，顺带定义下载名，比如students(中文名需要转义)
        LocalDateTime now = LocalDateTime.now();
        String s = now + "";
        response.setHeader("Content-disposition", "attachment;filename=" + new String(s.getBytes(), "iso-8859-1") + ".xls");
        //定义下载的类型，标明是excel文件
        response.setContentType("application/vnd.ms-xls");
        //这时候把创建好的excel写入到输出流
        wb.write(output);
        //养成好习惯，出门记得随手关门
        output.close();
        wb.close();
        }

三： 线程池代码

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import java.util.concurrent.ThreadPoolExecutor;

@Configuration //声明该类为配置类
public class Executor {





    @Bean("taskExector")
    public java.util.concurrent.Executor taskExector() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        int i = Runtime.getRuntime().availableProcessors();//获取到服务器的cpu内核
        executor.setCorePoolSize(i);//核心池大小
        executor.setMaxPoolSize(i+1);//最大线程数
        executor.setQueueCapacity(256);//队列程度
        executor.setKeepAliveSeconds(20);//线程空闲时间
        executor.setThreadNamePrefix("tsak-asyn");//线程前缀名称
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());//配置拒绝策略
        return executor;
    }


}