百万数据导出

pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.xiaoteng</groupId>
    <artifactId>excelexportdemo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- 集成mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <!-- 集成mysql连接 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!--分页插件-->
        <!-- pagehelper 插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.13</version>
        </dependency>
        <!--导出-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
    </dependencies>
</project>

配置application.yml

 

 

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/shiyan?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false
    username: root
    password: root
    driver-class-name: com.mysql.jdbc.Driver
server:
  port: 9000
#  mybaits
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.xiaoteng.demo.entity

启动类

package com.xiaoteng.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.xiaoteng.demo.mapper")
public class AppRun {
    public static void main(String[] args) {
        SpringApplication.run(AppRun.class,args);
    }
}

对应导出方法

@RestController
@RequestMapping("/test")
public class EexcelPortCtroller {

    @Resource
    private ExcelPortService excelPortService;
    @Resource
    private ExcelPortMapper excelPortMapper;


    @GetMapping("/hello2")
    public void test2(HttpServletResponse response) throws Exception {
        // 导出开始时间戳
        long start = System.currentTimeMillis();
        // 文件名称
        String fileName = URLEncoder.encode(String.format("%s-(%s).xlsx", "百万数据导出", UUID.randomUUID().toString()),
                StandardCharsets.UTF_8.toString());

        ExcelWriter writer = new ExcelWriterBuilder()
                .autoCloseStream(true)
                .excelType(ExcelTypeEnum.XLSX)
                .file(response.getOutputStream())
                .head(Product.class)
                .build();
        // xlsx文件上上限是104W行左右,这里如果超过104W需要分Sheet
        int lastBatchMaxId = 0;      //相当于页数
        int writeDataRows = 100000; //每次查10W条
        Integer sheetDataRows = 1000000; //每个sheet写100W条数据
        Integer totalCount = excelPortMapper.count();
        // 一个sheet得循环次数
        int oneSheetWriteCount = totalCount / writeDataRows;
        //计算最后一个sheet需要写入的次数
        Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

        for (int i = 0; i < 2; i++) {
            WriteSheet writeSheet = new WriteSheet();
            writeSheet.setSheetName("测试Sheet" + i);
//            Sheet sheet = new Sheet(i, 0);
//            sheet.setSheetName("测试Sheet1" + i);
            for (int j = 0; j <(i!=2-1?oneSheetWriteCount:lastSheetWriteCount) ; j++) {
               // 分页插件，没有就用limit分页
                PageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);
//                List<Product> list = excelPortService.findAllById(lastBatchMaxId, writeDataRows);
                List<Product> list = excelPortService.findAll();
                writer.write(list, writeSheet);
            }
        }
        response.setContentType("application/force-download");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setCharacterEncoding("utf-8");
        writer.finish();


//        log.info("导出数据耗时:{} ms,start:{},end:{}", System.currentTimeMillis() - start);
        long time = System.currentTimeMillis() - start;
        System.out.println("导出数据耗时:" + time/1000);

    }

  
}