100秒内导出百万数据

利用easyexcel百秒内导出百万数据

数据量如下

导出时间83s

EasyExcel依赖

<!-- EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.1</version>
</dependency>

controller示例代码如下

 @GetMapping("/export/stream")
    public void exportStream(HttpServletResponse response) {
        long start = System.currentTimeMillis();

        // 记录总数
        Integer totalCount = orderInfoDao.selectCount();

        // 每一个Sheet存放10w条数据
        Integer sheetDataRows = 100000;

        // 计算需要的Sheet数量
        int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);

        try {
            String filename = URLEncoder.encode(System.currentTimeMillis() + ".xlsx", "UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-disposition", "attachment; filename=" + filename);//设定输出文件头
            response.setContentType("application/x-xls");// 定义输出类型
            OutputStream out = response.getOutputStream();
            
            ExcelWriter excelWriter = EasyExcel.write(out, OrderInfo.class).build();

            Map<Integer, WriteSheet> sheetMap = new HashMap<>();
            for (int i = 1; i <= sheetNum; i++) {
                // 创建Sheet
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "订单信息" + i).head(OrderInfo.class)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                sheetMap.put(i, writeSheet);
            }
            List<OrderInfo> list = new ArrayList<>();
            AtomicInteger atomicInteger = new AtomicInteger(0);

            // 流式查询
            orderInfoDao.listStream(context -> {
                OrderInfo orderInfo = context.getResultObject();
                list.add(orderInfo);
                int atomic = atomicInteger.addAndGet(1);
                if (list.size() >= 100000) {
                    // 导出
                    write(list, excelWriter, atomic, sheetMap);
                    list.clear();
                }
            });

            if (CollUtil.isNotEmpty(list)) {
                write(list, excelWriter, totalCount, sheetMap);
            }
            excelWriter.finish();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        long end = System.currentTimeMillis();
        log.info("导出order_info表共计耗时：{}", end - start);
    }

dao层代码

@Mapper
public interface OrderInfoDao {

    Integer selectCount();

    void listStream(ResultHandler<OrderInfo> handler);

}

xml代码

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.he.mapper.OrderInfoDao">

 <resultMap id="baseResultMap" type="com.he.entity.OrderInfo">
        <id property="orderId" column="order_id"/>
        <result column="order_date" property="orderDate"/>
        <result column="order_code" property="orderCode"/>
        <result column="pid" property="pid"/>
    </resultMap>

    <select id="selectCount" resultType="integer">
        select count(*) from order_info;
    </select>


    <select id="listStream" fetchSize="-2147483648" resultSetType="FORWARD_ONLY" resultMap="baseResultMap">
        select * from order_info;
    </select>
</mapper>