springBoot中使用easyExcel导出

pom依赖

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
        </dependency>
1
2
3
4
5

Controller层

// 导出时方法返回值为void
@PostMapping("/exportQAData")
    public void exportQAData(String startDate, String endDate, HttpServletResponse response) {
        qADataManageService.exportQAData(response, startDate,endDate);
    }
1
2
3
4
5

Service层

@Component
@Slf4j
public class QADataManageService implements FileExportService<QADataManageVo> {
    /**
     * 声明并获取dao层实例
     */
    @Autowired
    private QADataManageDao qADataManageDao;
    @Autowired
    private ExcelExportUtil excelExportUtil;

    public Boolean exportQAData(HttpServletResponse response, String startDate,String endDate) {
        Map<String, Object> maps = new HashMap<>();
        maps.put(QADataManageConstant.CUSTOMER_TIME, startDate);
        maps.put(QADataManageConstant.FORECAST_TIME, endDate);
        Integer qaDataTotal = qADataManageDao.getQADataTotal(maps);
        if (qaDataTotal > 0) {
            excelExportUtil.multiThreadExportExcel(response, qaDataTotal, QADataManageVo.class, this, maps, null);
            return true;
        }
        return false;
    }

    @Override
    public List<QADataManageVo> queryPageExcel(Map<String, Object> map) {
        Integer pageNum = (Integer) map.get("page");
        Integer pageSize = (Integer) map.get("pageSize");
        List<QADataManagePo> qaDataManagePos = qADataManageDao.findAllList(pageNum, pageSize, map);
        return buildVoItem(qaDataManagePos);
    }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

策略工厂

1. 接口层面

public interface FileExportService<R> {

    /**
     * Description 通用分页接口
     * @date 2023/11/2 11:12
     * @param: map 包含页面大小和当前页数据
     */
    List<R> queryPageExcel(Map<String,Object> map);
}
1
2
3
4
5
6
7
8
9

2. 工厂

public class FileExportFactory<R> {

    private FileExportService<R> fileExportService;

    public FileExportFactory(FileExportService<R> fileExportService){
        this.fileExportService = fileExportService;
    }

    public List<R> queryPageExcel(Map<String,Object> map){
        return fileExportService.queryPageExcel(map);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12

工具类

@Component
public class ExcelExportUtil {

    @Autowired
    @Qualifier("excelThreadPool")
    private ThreadPoolTaskExecutor threadPoolTaskExecutor;

    private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);

    /**
     * @param response   响应
     * @param totalCount 总记录条数
     * @param clazz      导出的Excel对象
     * @throws Exception
     */
    public <R> void exportExcel(HttpServletResponse response, int totalCount, Class<?> clazz, FileExportService<R> service, String fileName) throws Exception {
        //文件名
        fileName = StrUtil.isNotEmpty(fileName) ? fileName : String.valueOf(System.currentTimeMillis());
        FileExportFactory<R> context = new FileExportFactory<>(service);

        OutputStream outputStream = null;
        try {
            //每一个Sheet存放5000条数据
            int sheetDataRows = 5000;
            //每次写入的数据量3000,每页查询3000
            Integer writeDataRows = 3000;
            //计算需要的Sheet数量
            int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            int oneSheetWriteCount = sheetDataRows / writeDataRows;
            //计算最后一个sheet需要写入的次数
            int lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));
            // 获取输出流
            outputStream = response.getOutputStream();
            //必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            // 缓存写入的数据
            List<R> dataList = new ArrayList<>();

            Map<String, Object> queryMap = new ConcurrentHashMap<>();

            //开始分批查询分次写入 sheetNum
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetName("Sheet" + i);
                sheet.setSheetNo(i);
                //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    // 每次数据写入后清空集合
                    dataList.clear();
                    // 从数据库获取数据分页获取数据

                    queryMap.put("page", i * sheetDataRows + j * writeDataRows);
                    queryMap.put("pageSize", writeDataRows);
                    dataList = context.queryPageExcel(queryMap);

                    // 分sheet保存数据
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(clazz)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();

                    excelWriter.write(dataList, writeSheet);
                }
            }
            // 下载EXCEL，返回给前端stream流
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
            outputStream.flush();
        } catch (Exception e) {
            logger.info("excel导出异常", e);
            throw new ServiceException("excel导出异常");
        } finally {
            if (outputStream != null) {
                outputStream.close();
            }
        }
    }


    /**
     * @param totalCount 总记录条数
     * @param clazz      导出的Excel对象
     * @param service    具体实现查询数据的服务类
     * @param map        查询参数
     */
    public <R> void multiThreadExportExcel(HttpServletResponse response, int totalCount, Class<?> clazz, FileExportService<R> service, Map<String, Object> map, String fileName) {
        try{
            FileExportFactory<R> context = new FileExportFactory<>(service);
            //文件名
            fileName = StrUtil.isNotEmpty(fileName) ? fileName : String.valueOf(System.currentTimeMillis());

            OutputStream outputStream = null;
            try {
                //每一个Sheet存放1w条数据
                int sheetDataRows = 100;
                //每次写入的数据量5000,每页查询5000
                int writeDataRows = 50;
                //计算需要的Sheet数量
                int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
                //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
                int oneSheetWriteCount = sheetDataRows / writeDataRows;
                //计算最后一个sheet需要写入的次数
                int lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : totalCount % sheetDataRows % writeDataRows == 0 ? totalCount % sheetDataRows / writeDataRows : (totalCount % sheetDataRows / writeDataRows) + 1;

                outputStream = response.getOutputStream();

                //必须放到循环外，否则会刷新流
                ExcelWriter excelWriter = EasyExcel.write(outputStream).build();

                Map<Integer, List<R>> pageMap = new ConcurrentHashMap<>(Math.toIntExact(sheetNum));

                CountDownLatch countDownLatch = new CountDownLatch(Math.toIntExact(sheetNum));
                // 多线程查询参数Map
                Map<Integer, Map<String, Object>> queryMap = new ConcurrentHashMap<>();

                //开始分批查询分次写入 sheetNum
                for (int i = 0; i < sheetNum; i++) {
                    //创建Sheet
                    WriteSheet sheet = new WriteSheet();
                    sheet.setSheetName("Sheet" + i);
                    sheet.setSheetNo(i);
                    int finalNum = i;
                    threadPoolTaskExecutor.submit(() -> {
                        ConcurrentHashMap<String, List<List<String>>> dataListMap = new ConcurrentHashMap<>();
                        //循环写入次数, j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                        for (int j = 0; j < (finalNum != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                            int finalJ = j;
                            queryMap.put(finalNum, new HashMap<String, Object>() {
                                {
                                    put("page", finalNum * sheetDataRows + finalJ * writeDataRows);
                                    put("pageSize", writeDataRows);
                                    if (CollectionUtil.isNotEmpty(map)) {
                                        // 传递其他查询参数
                                        putAll(map);
                                    }
                                }
                            });
                            // 策略模式调用查询
                            List<R> dataList = pageMap.get(finalNum);
                            if (CollectionUtil.isEmpty(dataList)){
                                dataList = new ArrayList<>();
                            }
                            dataList.addAll(context.queryPageExcel(queryMap.get(finalNum)));
                            // 将分页数据进行存储
                            pageMap.put(finalNum, dataList);
                        }
                        countDownLatch.countDown();
                    });
                }
                try {
                    countDownLatch.await();
                } catch (Exception e) {
                    logger.info("多线程启动异常");
                    throw new ServiceException("多线程启动异常");
                }

                pageMap.forEach((k, v) -> {
                    // 分sheet保存数据
                    WriteSheet writeSheet = EasyExcel.writerSheet(k, "Sheet" + (k + 1)).head(clazz)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                    excelWriter.write(v, writeSheet);
                    pageMap.remove(k);
                });
                // 下载EXCEL，返回给前端stream流
                response.setContentType("application/octet-stream");
                response.setCharacterEncoding("utf-8");
                response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
                excelWriter.finish();
                outputStream.flush();
            } catch (Exception e) {
                logger.info("数据导出异常",e);
            } finally {
                if (outputStream != null) {
                    outputStream.close();
                }
            }
        } catch (Exception e) {
            logger.info("多线程导出数据异常");
            throw new ServiceException("多线程导出数据异常");
        }

    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186