Spring Boot使用Easy Excel导出百万级数据_spring boot 百万数据导入导出

首先引入导出Easy Excel所需要的包

  <!--JAVA解析Excel工具EasyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>
        <!--easyexcel  2.1.1 对应 poi  3.17 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Controller文件

@PostMapping("/export")
    public void export(HttpServletResponse response, EmploymentUsersDTO employmentUsers) throws IOException {
        employmentUsersService.downloadFailedUsingJson(response,employmentUsers);
    }
1
2
3
4

Service文件

void downloadFailedUsingJson(HttpServletResponse response, EmploymentUsersDTO employmentUsers) throws IOException;
1

Servicempl文件

@Override
    public void downloadFailedUsingJson(HttpServletResponse response, EmploymentUsersDTO employmentUsersDTO) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        long startTime = System.currentTimeMillis();
        log.info("导出开始时间:{}", startTime);
        Long aLong = excelEmploymentUsersMapper.selectCount(null);
        int count = Math.toIntExact(aLong);
        long cycles = count / Constants.MAXCOUNT+1;
        List<List<ExcelEmploymentUsers>> userList = new ArrayList<>();
        CountDownLatch countDownLatch = new CountDownLatch((int) cycles);
        ThreadPoolExecutor threadPool = new ThreadPoolExecutor(10, 20, 30, 
                TimeUnit.MINUTES, new LinkedBlockingDeque<>(20),Executors.defaultThreadFactory(),new ThreadPoolExecutor.AbortPolicy());
        //预启动所有核心线程
        threadPool.prestartAllCoreThreads();
        for (int i = 0; i < cycles; i++) {
            int offset = i * Constants.MAXCOUNT;
            EmploymentUsersDTO dto = YinPageUtils.CopyProperties(employmentUsersDTO, new EmploymentUsersDTO());
            dto.setPage(offset);
            dto.setLimit(Constants.MAXCOUNT);
            threadPool.execute(()->{
                List<ExcelEmploymentUsers> employmentUsers = employmentUsersMapper.pageList(dto);
                synchronized (userList){
                    userList.add(employmentUsers);
                }
                countDownLatch.countDown();
            });
        }
        try {
            countDownLatch.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } finally {
            threadPool.shutdown();
        }

        long loadTime = System.currentTimeMillis();
        log.info("数据结束时间:{}", loadTime/1000);
        log.info("数据所用时间:{}", (loadTime - startTime) / 1000 + "秒");
//        String fileName = String.format("D:\\%s","测试表格导出.xlsx");

        setExcelRespProp(response,"测试表格导出");
//        fileName = new String(("export-excel").getBytes(), StandardCharsets.UTF_8);
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build()) {
            for (int i = 0; i < userList.size(); i++) {
                // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).build();
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                excelWriter.write(userList.get(i), writeSheet);
            }
            excelWriter.finish();
        }

        long endTime = System.currentTimeMillis();
        log.info("导出结束时间:{}", endTime + "ms");
        log.info("导出所用时间:{}", (endTime - startTime) / 1000 + "秒");
    }
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

这里的导出是用的流，直接在浏览器下载的，加入了转换器测的是25秒开启了10个线程

每个线程查询条数
public static final int MAXCOUNT= 100000;
1
2

DTO层SQL查询的条件以及前端所传参数

@Data
public class EmploymentUsersDTO extends PageParamDTO {

    private Integer id;

    private String name;

    private String streetId;

    private String communityId;

    private String identity;

    private String employment;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Mapper文件

@Repository
public interface EmploymentUsersMapper extends BaseMapper<EmploymentUsers>  {

    List<ExcelEmploymentUsers> pageList(EmploymentUsersDTO employmentUsersDTO);

}
1
2
3
4
5
6

建议转化器转换字段少的直接用SQL

    <select id="pageList" resultType="com.ruoyi.employment.pojo.entity.ExcelEmploymentUsers">
        select id, name, CASE WHEN sex =1 THEN '女' WHEN sex = 0 THEN '男' END as sex, identity, education, tel, employment, company, street_id, community_id, unemployment_time,
            content from oe_employment_users  where
        id>=#{page} LIMIT #{limit}
    </select>
1
2
3
4
5

实体类

@Getter
@Setter
@TableName("users")
@ApiModel(value = "ExcelEmploymentUsers对象", description = "表")
public class ExcelEmploymentUsers implements Serializable {
    private static final long serialVersionUID = 1L;

//    @ExcelProperty(value = "任务序号")
    @TableId(value = "id", type = IdType.AUTO)
//    @ExcelIgnore
    private Integer id;

    @ExcelProperty(value = "姓名")
    private String name;

    @ApiModelProperty("用户性别（1男 ,2女 ）")
    @ExcelProperty(value = "性别")
//    @ExcelProperty(value = "性别",converter = SexConverter.class)
    private String sex;

    @ExcelProperty(value = "年龄")
    private Integer age;

    @ExcelProperty(value = "身份证号")
    @ApiModelProperty("身份证号")
    private String identity;

    @ExcelProperty(value = "学历",converter = EducationConverter.class)
    @ApiModelProperty("学历")
    private String education;


    @ExcelProperty(value = "电话")
    @ApiModelProperty("电话")
    private String tel;

//    @ExcelProperty(value = "就业状态")
    @ExcelProperty(value = "就业状态",converter = EmploymentConverter.class)
    @ApiModelProperty("就业状态")
    private String employment;

    @ExcelProperty(value = "所在单位")
    @ApiModelProperty("所在单位")
    private String company;

//    @ExcelProperty(value = "街道")
    @ExcelProperty(value = "街道",converter = StreetConverter.class)
    @ApiModelProperty("街道")
    private String streetId;

//    @ExcelProperty(value = "社区")
    @ExcelProperty(value = "社区",converter = CommunityConverter.class)
    @ApiModelProperty("社区")
    private String communityId;

//    @ExcelProperty(value = "创建时间")
//    @JsonFormat(pattern = "yyyy-MM-dd")
//    private Date createTime;

    @ApiModelProperty("失业时间")
    @ExcelProperty(value = "失业时间")
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date unemploymentTime;

    @ExcelProperty(value = "备注")
    @ApiModelProperty("备注")
    private String content;
}

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

拦截器，拦截器建议是优先写死后读取缓存，我用的是直接读取的redis。

public class EducationConverter extends BaseConverter implements Converter<String> {


    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(ReadConverterContext<?> context) {
        String str = "";
        if ( null  == list) {
            list = DictUtils.getallDictCache();
        }
        for(int i=0; i < list.size(); i ++){
            if("education".equals(list.get(i).getDictType()) && context.getReadCellData().getStringValue().equals(list.get(i).getDictLabel())){
                str = list.get(i).getDictValue();
            }
        }
        return  str;
    }

    @Override
    public WriteCellData<?> convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        //        获取缓存
        if ( null  == list) {
            list = DictUtils.getallDictCache();
        }
        String str = "";
        for(int i=0; i < list.size(); i ++){
            if(value.equals(list.get(i).getDictValue()) && "education".equals(list.get(i).getDictType())){
                str = list.get(i).getDictLabel();
            }
        }
        return new WriteCellData(str);
    }

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

最后所需时间是