使用easyExcel，多线程，导出excel

使用easyExcel，多线程，导出excel，多个sheet页，excel数据和查询时数据顺序一致

阿里easyexcel 插件
easyexcel 项目git地址为: https://github.com/alibaba/easyexcel
官网地址：https://alibaba-easyexcel.github.io
web下载demo参见：https://blog.csdn.net/weixin_43614067/article/details/116262853

pom.xml

 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>2.1.6</version>
 </dependency>

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-collections4</artifactId>
    <version>4.1</version>
</dependency>
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>2.6.0</version>
</dependency>

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

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

 <dependency>
     <groupId>org.apche.poi</groupId>
     <artifactId>poi</artifactId>
     <version>3.17</version>
 </dependency>

简单示例

    @Test
    public void simpleWrite() {
        String fileName = "C:\\Users\\Think\\Desktop\\" + "write" + System.currentTimeMillis() + ".xlsx";

        //EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
        ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
        for (int i = 0; i < 10; i++) {
            WriteSheet writeSheet = EasyExcel.writerSheet(i, "PS模板" + (i + 1)).build();
            excelWriter.write(data(), writeSheet);
        }
        excelWriter.finish();
    }

    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }
public class DemoData {
    /**@ColumnWidth 字段宽度，@ExcelProperty表头名 @ExcelIgnore不导出的字段*/
    @ColumnWidth(15)
    @ExcelProperty("字符串")
    private String string;
    @ColumnWidth(15)
    @ExcelProperty("日期")
    private Date date;
    @ColumnWidth(15)
    @ExcelProperty("数据")
    private Double doubleData;
    @ExcelIgnore
    private String name;
    //get,set方法省略
   }

easyExcel的使用可以查看官网，官网有详细demo，这里是主要代码
写excel类

public class WriteExcel {
    public static void writeExcel(ExportExcelService exportExcelservice, QueryCondition queryCondition, int exifInfoCount, String finalXlsxPath) {
        //每个sheet保存的数据量
        int num = 600;
        ExcelWriter excelWriter = null;
        int corePoolSize=10;
        int maximumPoolSize=20;
        //用线程池管理多线程
        ThreadPoolExecutor exector = (ThreadPoolExecutor) Executors.newFixedThreadPool(corePoolSize);
        exector.setCorePoolSize(corePoolSize);
        exector.setMaximumPoolSize(maximumPoolSize);
        List<ReadExifInfoThread> tasks = new ArrayList<ReadExifInfoThread>();
        excelWriter = EasyExcel.write(finalXlsxPath, ExifInfo.class).build();
        //exifInfoCount 写入excel数据总量
        //pageCount 要写入sheet页数量。同分页
        int pageCount = exifInfoCount % num == 0 ? (exifInfoCount / num) : (exifInfoCount / num + 1);
        for (int i = 0; i < pageCount; i++) {
            ReadExifInfoThread readExifInfoThread = new ReadExifInfoThread(queryCondition, exportExcelservice, i, num);
            tasks.add(readExifInfoThread);
            }
        try {
        	//用invokeAll方法提交任务，返回数据的顺序和tasks中的任务顺序一致，如果第一个线程查0-10000行数据，第二个线程查10000-10001行数据，
        	//第二个线程大概率比第一个线程先执行完，但是futures中第一位数据是0-10000的数据。
        	//[demo见：](https://blog.csdn.net/weixin_43614067/article/details/104983719)
            List<Future<List<ExifInfo>>> futures = exector.invokeAll(tasks);
            for (int i = 0; i < pageCount; i++) {
                List<ExifInfo> exifInfoList = futures.get(i).get();
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "xxxx信息表" + (i + 1)).build();
                excelWriter.write(exifInfoList, writeSheet);
            }
         } catch (Exception e) {
            Constant.bLog.error("写入excel数据失败",e);
         }
        exector.shutdown();
        excelWriter.finish();
   }
}

线程：从数据库读取数据

public class ReadExifInfoThread implements Callable<List<ExifInfo>> {
    private QueryCondition queryCondition;
    private ExportExcelService exportExcelservice;
    private  int i;
    private  int num;
    public ReadExifInfoThread(QueryCondition queryCondition, ExportExcelService exportExcelservice, int i, int num) {
        this.queryCondition = queryCondition;
        this.exportExcelservice = exportExcelservice;
        this.i = i;
        this.num = num;
    }
    @Override
    public List<ExifInfo> call(){
        queryCondition.setBeginRow(String.valueOf(i*num));
        queryCondition.setEndRow(String.valueOf((i+1)*num));
        long startTime = System.currentTimeMillis();
        List<ExifInfo> exifInfoList = null;
        try {
        	//从数据库查询要写入excle的数据
            exifInfoList = exportExcelservice.getExifInfoByPage(queryCondition);
            long endTime=System.currentTimeMillis();
            long spendTime=endTime-startTime;
            Constant.bLog.info(Thread.currentThread().getName()+"查询耗时："+spendTime);
        } catch (Exception e) {
            Constant.bLog.error("查询数据失败",e);
        }
        return exifInfoList;
    }
}

注：这里只用了多线程查询数据，写数据只是单线程。试过多线程写excel，一个线程写一个sheet页，但写入excel的数据会损坏，导致excel打开失败。