大数据(百万)导出到excel

最新推荐文章于 2024-07-28 16:45:11 发布

纯爱战神等待沈幼楚

最新推荐文章于 2024-07-28 16:45:11 发布

阅读量1k

点赞数 2

文章标签：大数据 excel java

本文链接：https://blog.csdn.net/wenjiehk/article/details/130246733

版权

一.分析

1.大数据量，最好使用多线程导出提升效率。
2.多线程导出，需要考虑线程安全。
3.使用分页查询，防止发生内存溢出。
4.需要查询数据总量进行分页。

二.代码实现

1.springboot + swagger2 + easyexcel

使用到了spring的多线程，所以在启动类添加注解@EnableAsync，在需要多线程执行的方法上添加注解@Async

2.pom文件

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.5.0</version>
        </dependency>

        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>1.9.0</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
            <scope>runtime</scope>
        </dependency>

        <!-- 阿里开源EXCEL -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.1</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.14</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.4</version>
        </dependency>

        <!-- spring-boot-devtools -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional> <!-- 表示依赖不会传递 -->
            <version>2.1.1.RELEASE</version>
        </dependency>

3.代码
配置类

@Configuration
public class ThreadPoolConfig {

    //配置线程池
    @Bean("threadPool")
    public ThreadPoolTaskExecutor threadPoolExecutor() {
        ThreadPoolTaskExecutor threadPoolExecutor = new ThreadPoolTaskExecutor();
        //核心线程数  Runtime.getRuntime().availableProcessors()获取CPU核数
        int coreSize = Runtime.getRuntime().availableProcessors();
        threadPoolExecutor.setCorePoolSize(coreSize);
        //最大线程数
        threadPoolExecutor.setMaxPoolSize(coreSize);
        //队列容量 4倍线程数
        threadPoolExecutor.setQueueCapacity(coreSize * 4);
        //线程池对拒绝任务(无线程可用)的处理策略
        threadPoolExecutor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        return threadPoolExecutor;
    }
}

@Configuration
@EnableSwagger2
public class SwaggerConfig {


    /**
     * 创建API
     */
    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .pathMapping("/")
                // 用来创建该API的基本信息，展示在文档的页面中（自定义展示的信息）
                .apiInfo(apiInfo())
                // 设置哪些接口暴露给Swagger展示
                .select()
                // 扫描所有有注解的api，用这种方式更灵活
                .apis(RequestHandlerSelectors.withMethodAnnotation(ApiOperation.class))
                // 扫描指定包中的swagger注解
                //.apis(RequestHandlerSelectors.basePackage("com.enn.security.project.tool.swagger"))
                // 扫描所有 .apis(RequestHandlerSelectors.any())
                .paths(PathSelectors.any())
                .build();
    }

    /**
     * 添加摘要信息
     */
    private ApiInfo apiInfo() {
        // 用ApiInfoBuilder进行定制
        return new ApiInfoBuilder()
                // 设置标题
                .title("标题：导出测试")
                // 描述
                .description("描述：导出测试.")
                // 作者信息
                .contact(new Contact("export", null, null))
                // 版本
                .version("版本号:" + "1.0.1")
                .build();
    }

    @Bean
    public Docket exportApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.any())
                .paths(PathSelectors.ant("/export/**"))
                .build()
                .groupName("01-导出")
                .pathMapping("/");
    }
}

工具类
导出工具类

@Getter
public class ExportExcelUtil {

    private static final Logger log = LoggerFactory.getLogger(ExportExcelUtil.class);

    /**
     * 数据总量
     */
    private final Integer dataCount;

    /**
     * 每次从数据库查询数据,防止内存溢出
     */
    private final int limit;

    /**
     * 每个sheet总数据
     */
    private final int sheetCount;

    // Queue是java自己的队列，是同步安全的
    public Queue<Map<String, Integer>> queue;

    // 计数器所以任务都处理完才返回下载地址
    private CountDownLatch countDownLatch;

    public ExportExcelUtil(int dataCount, int limit, int sheetCount) {
        this.sheetCount = sheetCount;
        this.dataCount = dataCount;
        this.limit = limit;
        initQueue();
    }

    /**
     * 初始化队列
     */
    public void initQueue() {
        queue = new ConcurrentLinkedQueue<>();
        // 计算出多少页，即循环次数
        int count = dataCount / sheetCount + (dataCount % sheetCount > 0 ? 1 : 0);
        for (int i = 0; i < count; i++) {
            Map<String, Integer> map = new HashMap<>();
            //第几页
            map.put("page", i);
            //分页长度
            map.put("limit", limit);
            //每个sheet数据总量
            map.put("sheetCount", sheetCount);
            //添加元素
            queue.offer(map);
        }
        countDownLatch = new CountDownLatch(queue.size());
    }
}

压缩工具类

public class ZipFileUtil {

    /**
     * 文件压缩
     *
     * @param filePath 压缩文件路径
     * @param zipPath  zip文件路径
     */
    public static void zip(String filePath, String zipPath) throws IOException {
        // 判断文件是否存在，如文件不存在创建一个新文件
        File zipFile = new File(zipPath);
        if (!zipFile.exists()) {
            zipFile.createNewFile();
        }
        File file = new File(filePath);
        if (!file.exists()) {
            return;
        }
        // 创建一个zip文件输出流
        BufferedInputStream bufferedInput = null;
        try (ZipOutputStream zipOutput = new ZipOutputStream(Files.newOutputStream(zipFile.toPath()))) {
            /*
              创建一个缓冲读取流，提高读取效率
              也可以直接创建一个 FileInputStream 对象，BufferedInputStream内部维护了一个8KB的缓冲区，BufferedInputStream本身不具备读取能力
              BufferedInputStream 可以手动指定缓冲区大小 单位为字节例如：new BufferedInputStream(new FileInputStream(file), 10240)
             */
            // 设置压缩条目名称
            zipOutput.putNextEntry(new ZipEntry(file.getName()));
            File[] files;
            int len;
            // 我的CPU1级缓存为256Kb
            byte[] bytes = new byte[256 * 1024];
            if (file.isFile()) {
                files = new File[]{file};
            } else {
                files = file.listFiles();
            }
            // 读取file内的字节流，写入到zipOutput内
            if (files != null) {
                for (File file1 : files) {
                    bufferedInput = new BufferedInputStream(Files.newInputStream(file1.toPath()));
                    while ((len = bufferedInput.read(bytes)) != -1) {
                        zipOutput.write(bytes, 0, len);
                    }
                    // 写入完毕后关闭条目
                    //zipOutput.closeEntry();
                }
            }
        } finally {
            if (bufferedInput != null) {
                bufferedInput.close();
            }
        }
    }
}

实体类

@Data
public class SecurityMapDetail {
    @ExcelProperty("id")
    private Long id;
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty("统计日期")
    private Date calDay;
    @ExcelProperty("省公司id")
    private Long xaReig;
    @ExcelProperty("省公司名称")
    private String xaReigTxt;
    @ExcelProperty("管理实体id")
    private Long cpmoCop;
    @ExcelProperty("管理实体名称")
    private String cpmoCopTxt;
    @ExcelProperty("指标名称")
    private String targetName;
    @NumberFormat("#.##%")
    @ExcelProperty("指标计算分子")
    private Double targetNumerator;
    @ExcelProperty("指标计算分母")
    @NumberFormat("#.##%")
    private Double targetDenominator;
    @ExcelProperty("指标值")
    @NumberFormat("#.##%")
    private Double targetValue;
    @ExcelProperty("排名")
    private Integer sort;
    @ExcelProperty("得分")
    @NumberFormat("#.##%")
    private Double score;
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty("记录生成时间")
    private Date createTime;
}

Controller

@RestController
@RequestMapping("/export")
public class Controller {

    private static final Logger log = LoggerFactory.getLogger(Controller.class);

    @Autowired
    private ISecurityMapDetailService securityMapDetailService;

    @GetMapping("/excelAliExcel")
    public String excelAliExcel(SecurityMapDetail securityMapDetail, HttpServletResponse response) {
        long start = System.currentTimeMillis();
        //返回数据总量
        int dataCount = securityMapDetailService.getDataCount(securityMapDetail);
        //初始化导出工具类
        ExportExcelUtil excelUtil = new ExportExcelUtil(dataCount, 200000, 200000);
        String filePath = "E:\\path\\excel\\" + UUID.randomUUID() + "\\";
        String fileZipPath = "E:\\path\\zip\\excel\\" + UUID.randomUUID() + ".zip";
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                file.mkdirs();
            }
            //使用 CountDownLatch保证最后全部导出压缩文件，返回下载地址
            CountDownLatch countDownLatch = excelUtil.getCountDownLatch();
            Queue<Map<String, Integer>> queue = excelUtil.getQueue();
            Map<String, Integer> poll;
            //循环获取任务，发生给线程池执行
            while ((poll = queue.poll()) != null) {
                securityMapDetailService.excelAliExcel(securityMapDetail, poll, countDownLatch, filePath);
            }
            countDownLatch.await();
            //压缩

            long end = System.currentTimeMillis();
            System.out.println("任务执行完毕共消耗：  " + (end - start) + "ms");
            // 下载EXCEL
            ZipFileUtil.zip(filePath, fileZipPath);
        } catch (InterruptedException e) {
            log.error("导出异常:{}", e.getMessage());
        } catch (IOException e) {
            log.error("压缩异常:{}", e.getMessage());
        }
        return fileZipPath;
    }
}

Service

public interface ISecurityMapDetailService {

    int getDataCount(SecurityMapDetail securityMapDetail);

    void excelAliExcel(SecurityMapDetail securityMapDetail, Map<String, Integer> poll, CountDownLatch countDownLatch, String filePath);
}

@Service
public class SecurityMapDetailServiceImpl implements ISecurityMapDetailService {

    private static final Logger log = LoggerFactory.getLogger(SecurityMapDetailServiceImpl.class);

    @Autowired
    private SecurityMapDetailMapper mapDetailMapper;

    @Override
    public int getDataCount(SecurityMapDetail securityMapDetail) {
        return mapDetailMapper.selectSecurityMapDetailCount(securityMapDetail);
    }

    @Override
    @Async("threadPool")
    public void excelAliExcel(SecurityMapDetail securityMapDetail, Map<String, Integer> poll, CountDownLatch countDownLatch, String filePath) {
        Integer page = poll.get("page");
        Integer limit = poll.get("limit");
        Integer sheetCount = poll.get("sheetCount");
        int count = sheetCount / limit + page;
        List<SecurityMapDetail> data;
        String fileName = page + ".xlsx";
        try (ExcelWriter excelWriter = EasyExcel.write(filePath + fileName, SecurityMapDetail.class).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet(page, "模板" + page).build();
            for (; page < count; page++) {
                // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                data = mapDetailMapper.selectSecurityMapDetailList(securityMapDetail, page * limit, limit);
                excelWriter.write(data, writeSheet);
            }
        } finally {
            countDownLatch.countDown();
        }
    }
}

启动类

@EnableAsync
@SpringBootApplication
public class SpringExexportExcelApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringExexportExcelApplication.class, args);
    }
}