• 使用easyexcel将csv转为excel

    一.背景

            供应商系统下载的csv文件不支持域控（主要是第三方wps服务不能对csv文件加密，但是可以对office系列产品进行权限访问的加密控制）。因此思路就改为现将csv文件转为excel文件，然后对excel文件进行加域控制。本文主要介绍如何将csv文件转为excel文件。

    二.要求

    1.         Csv文件可能比较大，达到40-60M，需要控制内存使用率;
      1.         考虑接口的并发，需要进行接口的限流

    2. 三.方案

      1.         采用alibaba的easyexcel，降低内存占用率，根据压测结果，设置合理的接口限流参数（限流
      2. 本文不再介绍，可以使用java注解+redis+lua, 或者nginx限流等）

      4. 四.代码

      5. CsvController

      1. package com.xxx.xxx.controller;
      2.  
      3. import java.io.IOException;
      4. import java.io.InputStream;
      5. import java.io.OutputStream;
      6. import java.util.concurrent.ExecutionException;
      7. import java.util.concurrent.Future;
      8.  
      9. import javax.annotation.Resource;
      10. import javax.servlet.http.HttpServletResponse;
      11.  
      12. import com.xxx.xxx.common.utils.EasyExcelUtil;
      13. import com.xxx.xxx.common.utils.ObjectUtil;
      14. import com.xxx.xxx.service.ExcelAnalysisService;
      15.  
      16. import lombok.extern.slf4j.Slf4j;
      17. import org.springframework.web.bind.annotation.PostMapping;
      18. import org.springframework.web.bind.annotation.RequestMapping;
      19. import org.springframework.web.bind.annotation.RequestParam;
      20. import org.springframework.web.bind.annotation.RestController;
      21. import org.springframework.web.multipart.MultipartFile;
      22.  
      23. /**
      24.  * description:
      25.  *
      26.  * @author: lgq
      27.  * @create: 2024-04-16 11:06
      28.  */
      29. @Slf4j
      30. @RestController
      31. @RequestMapping("/csv")
      32. public class CsvController {
      33.     @Resource
      34.     private ExcelAnalysisService excelAnalysisService;
      35.  
      36.     /**
      37.      * 读取传入的csv  文本的内容可以存入数据库
      38.      *
      39.      * @param file
      40.      * @return
      41.      */
      42.     @PostMapping("/uploadCsvAndImportExcel")
      43.     public void uploadCsvAndImportExcel(@RequestParam("file") MultipartFile file, HttpServletResponse response) {
      44.         String[] splitName = file.getOriginalFilename().split(".csv");
      45.         if (ObjectUtil.isEmpty(splitName) || ObjectUtil.isEmpty(splitName[0])) {
      46.             return;
      47.         }
      48.         EasyExcelUtil.setResponseParam(response, splitName[0]);
      49.         long startTime = System.currentTimeMillis();
      50.         log.info("导出开始时间:{}", startTime);
      51.  
      52.         try {
      53.             // 输出流可以为本地文件
      54. //          OutputStream outputStream = new FileOutputStream("D:\\templateExcel\\filename.xlsx");
      55.             OutputStream outputStream = response.getOutputStream();
      56.             InputStream inputStream = file.getInputStream();
      57.             Future future = excelAnalysisService.csv2Excel(inputStream, outputStream);
      58.             future.get();
      59.         } catch (IOException ioException) {
      60.             log.error("csv转为excel出错！", ioException.getMessage());
      61.             ioException.printStackTrace();
      62.         } catch (InterruptedException interruptedException) {
      63.             log.error("csv转为excel出错！", interruptedException.getMessage());
      64.             interruptedException.printStackTrace();
      65.         } catch (ExecutionException executionException) {
      66.             log.error("csv转为excel出错！", executionException.getMessage());
      67.             executionException.printStackTrace();
      68.         }
      69.         // 导出时间结束
      70.         long endTime = System.currentTimeMillis();
      71.         log.info("导出结束时间:{}", endTime + "ms");
      72.         log.info("导出所用时间:{}", (endTime - startTime) / 1000 + "秒");
      73.     }
      74.  
      75. }

      EasyExcelGeneralCsvListener 

      1. package com.xxx.xxx.listener;
      2.  
      3. import java.util.ArrayList;
      4. import java.util.Collections;
      5. import java.util.List;
      6. import java.util.Map;
      7.  
      8. import com.alibaba.excel.ExcelWriter;
      9. import com.alibaba.excel.context.AnalysisContext;
      10. import com.alibaba.excel.event.AnalysisEventListener;
      11. import com.alibaba.excel.write.metadata.WriteSheet;
      12. import com.xxx.xxx.constants.ExcelConstants;
      13.  
      14. /**
      15.  * description:
      16.  *
      17.  * @author: lgq
      18.  * @create: 2024-04-16 11:25
      19.  */
      20. public class EasyExcelGeneralCsvListener extends AnalysisEventListener>  {
      21.     /**
      22.      * 用于存储读取的数据
      23.      */
      24.     private List> dataList = new ArrayList<>();
      25.  
      26.     private ExcelWriter excelWriter;
      27.  
      28.     private WriteSheet writeSheet;
      29.  
      30.     public EasyExcelGeneralCsvListener() {
      31.     }
      32.  
      33.     public EasyExcelGeneralCsvListener(ExcelWriter excelWriter, WriteSheet writeSheet) {
      34.         this.excelWriter = excelWriter;
      35.         this.writeSheet = writeSheet;
      36.     }
      37.  
      38.     @Override
      39.     public void invoke(Map data, AnalysisContext context) {
      40.         // 数据add进入集合
      41.         dataList.add(data);
      42.         // size是否为2000条:这里其实就是分批.当数据等于2k的时候执行一次写入excel
      43.         if (dataList.size() >= ExcelConstants.PER_WRITE_EXCEL_ROW_COUNT) {
      44.             save2Excel();
      45.             // 清理集合便于GC回收
      46.             dataList.clear();
      47.         }
      48.     }
      49.  
      50.     @Override
      51.     public void invokeHeadMap(Map headers, AnalysisContext context) {
      52.         List> titles = new ArrayList<>();
      53.         for (int i = 0; i < headers.size(); i++) {
      54.             titles.add(Collections.singletonList(headers.get(i)));
      55.         }
      56.         this.writeSheet.setHead(titles);
      57.     }
      58.  
      59.     /**
      60.      * 保存数据到 excel
      61.      */
      62.     private void save2Excel() {
      63.         if (dataList.size() > 0) {
      64.             List> consumerDataList = new ArrayList<>();
      65.             dataList.stream().forEach( e ->
      66.                     {
      67.                         List objects = new ArrayList<>();
      68.                         for (int i = 0; i < e.size(); i++) {
      69.                             objects.add(e.get(i));
      70.                         }
      71.                         consumerDataList.add(objects);
      72.                     }
      73.  
      74.             );
      75.             this.excelWriter.write(consumerDataList, writeSheet);
      76.         }
      77.     }
      78.  
      79.     /**
      80.      * Excel 中所有数据解析完毕会调用此方法
      81.      */
      82.     @Override
      83.     public void doAfterAllAnalysed(AnalysisContext context) {
      84.         save2Excel();
      85.         dataList.clear();
      86.     }
      87.  
      88. }

      VisiableThreadPoolTaskExecutor

      1. package com.xxx.xxx.task;
      2.  
      3. import java.util.concurrent.Callable;
      4. import java.util.concurrent.Future;
      5. import java.util.concurrent.ThreadPoolExecutor;
      6.  
      7. import lombok.extern.slf4j.Slf4j;
      8. import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
      9. import org.springframework.util.concurrent.ListenableFuture;
      10.  
      11. /**
      12.  * description:VisiableThreadPoolTaskExecutor
      13.  *
      14.  * @author: lgq
      15.  * @create: 2024-04-17 10:52
      16.  */
      17. @Slf4j
      18. public class VisiableThreadPoolTaskExecutor extends ThreadPoolTaskExecutor {
      19.     private void showThreadPoolInfo(String prefix){
      20.         ThreadPoolExecutor threadPoolExecutor = getThreadPoolExecutor();
      21.  
      22.         if(null==threadPoolExecutor){
      23.             return;
      24.         }
      25.  
      26.         log.info("{}, {},taskCount [{}], completedTaskCount [{}], activeCount [{}], queueSize [{}]",
      27.                 this.getThreadNamePrefix(),
      28.                 prefix,
      29.                 threadPoolExecutor.getTaskCount(),
      30.                 threadPoolExecutor.getCompletedTaskCount(),
      31.                 threadPoolExecutor.getActiveCount(),
      32.                 threadPoolExecutor.getQueue().size());
      33.     }
      34.  
      35.     @Override
      36.     public void execute(Runnable task) {
      37.         showThreadPoolInfo("1. do execute");
      38.         super.execute(task);
      39.     }
      40.  
      41.     @Override
      42.     public void execute(Runnable task, long startTimeout) {
      43.         showThreadPoolInfo("2. do execute");
      44.         super.execute(task, startTimeout);
      45.     }
      46.  
      47.     @Override
      48.     public Future submit(Runnable task) {
      49.         showThreadPoolInfo("1. do submit");
      50.         return super.submit(task);
      51.     }
      52.  
      53.     @Override
      54.     public  Future submit(Callable task) {
      55.         showThreadPoolInfo("2. do submit");
      56.         return super.submit(task);
      57.     }
      58.  
      59.     @Override
      60.     public ListenableFuture submitListenable(Runnable task) {
      61.         showThreadPoolInfo("1. do submitListenable");
      62.         return super.submitListenable(task);
      63.     }
      64.  
      65.     @Override
      66.     public  ListenableFuture submitListenable(Callable task) {
      67.         showThreadPoolInfo("2. do submitListenable");
      68.         return super.submitListenable(task);
      69.     }
      70. }
      71.  
      ExcelAnalysisService
      1. package com.xxx.xxx.service;
      2.  
      3. import java.io.OutputStream;
      4. import java.io.InputStream;
      5. import java.util.concurrent.Future;
      6.  
      7. /**
      8.  * description:excel文档分析处理类
      9.  *
      10.  * @author: lgq
      11.  * @create: 2024-04-17 11:42
      12.  */
      13. public interface ExcelAnalysisService {
      14.     /**
      15.      * csv文档转为excel文档
      16.      */
      17.     Future csv2Excel(InputStream inputStream, OutputStream outputStream);
      18. }

      ExcelAnalysisServiceImpl

      1. package com.xxx.xxx.service.impl;
      2.  
      3. import java.io.OutputStream;
      4. import java.nio.charset.Charset;
      5.  
      6. import com.alibaba.excel.EasyExcel;
      7. import com.alibaba.excel.ExcelWriter;
      8. import com.alibaba.excel.support.ExcelTypeEnum;
      9. import com.alibaba.excel.write.metadata.WriteSheet;
      10. import com.xxx.xxx.listener.EasyExcelGeneralCsvListener;
      11. import com.xxx.xxx.service.ExcelAnalysisService;
      12.  
      13. import lombok.extern.slf4j.Slf4j;
      14. import java.io.InputStream;
      15. import java.util.concurrent.Future;
      16.  
      17. import org.springframework.scheduling.annotation.Async;
      18. import org.springframework.scheduling.annotation.AsyncResult;
      19. import org.springframework.stereotype.Service;
      20.  
      21. /**
      22.  * description:ExcelAnalysisService实现类
      23.  *
      24.  * @author: lgq
      25.  * @create: 2024-04-17 14:53
      26.  */
      27. @Service
      28. @Slf4j
      29. public class ExcelAnalysisServiceImpl implements ExcelAnalysisService {
      30.  
      31.     @Async("asyncExcelAnalysisServiceExecutor")
      32.     @Override
      33.     public Future csv2Excel(InputStream inputStream, OutputStream outputStream) {
      34.         try {
      35.             ExcelWriter writer = EasyExcel.write(outputStream).excelType(ExcelTypeEnum.XLSX).build();
      36.  
      37.             EasyExcel.read(inputStream, new EasyExcelGeneralCsvListener(writer, new WriteSheet()))
      38.                     .excelType(ExcelTypeEnum.CSV)
      39.                     .charset(Charset.forName("UTF-8"))
      40.                     .sheet()
      41.                     .doRead();
      42.  
      43.             writer.finish();
      44.             outputStream.flush();
      45.         } catch (Exception e) {
      46.             log.error("csv转为excel出错！", e.getMessage());
      47.             e.printStackTrace();
      48.         } finally {
      49.             if (outputStream != null) {
      50.                 try {
      51.                     outputStream.close();
      52.                 } catch (Exception e) {
      53.                     log.error("outputStream.close() -> csv转为excel出错！", e.getMessage());
      54.                     e.printStackTrace();
      55.                 }
      56.             }
      57.             if (inputStream != null) {
      58.                 try {
      59.                     inputStream.close();
      60.                 } catch (Exception e) {
      61.                     log.error("inputStream.close() -> csv转为excel出错！", e.getMessage());
      62.                     e.printStackTrace();
      63.                 }
      64.             }
      65.         }
      66.         return new AsyncResult<>("task complete!");
      67.     }
      68. }

      ExecutorConfig

      1. package com.xxx.xxx.config;
      2.  
      3. import java.util.concurrent.Executor;
      4. import java.util.concurrent.ThreadPoolExecutor;
      5.  
      6.  
      7. import com.xxx.xxx.task.VisiableThreadPoolTaskExecutor;
      8.  
      9. import lombok.extern.slf4j.Slf4j;
      10. import org.springframework.context.annotation.Bean;
      11. import org.springframework.context.annotation.Configuration;
      12. import org.springframework.scheduling.annotation.EnableAsync;
      13. import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
      14.  
      15. /**
      16.  * description:线程池配置类
      17.  *
      18.  * @author: lgq
      19.  * @create: 2024-04-17 10:28
      20.  */
      21. @Configuration
      22. @Slf4j
      23. @EnableAsync
      24. public class ExecutorConfig {
      25.     private static int corePoolSize = Runtime.getRuntime().availableProcessors() + 1;
      26.     private static int maxPoolSize = Runtime.getRuntime().availableProcessors() + 1;
      27.     private static int queueCapacity = 100;
      28.     private static final String namePrefix = "ExcelAnalysis";
      29.  
      30.     @Bean(name = "asyncExcelAnalysisServiceExecutor")
      31.     public Executor asyncExcelServiceExecutor() {
      32.         log.info("start asyncExcelAnalysisServiceExecutor----------------");
      33.         //ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
      34.         //使用可视化运行状态的线程池
      35.         ThreadPoolTaskExecutor executor = new VisiableThreadPoolTaskExecutor();
      36.         //配置核心线程数
      37.         executor.setCorePoolSize(corePoolSize);
      38.         //配置最大线程数
      39.         executor.setMaxPoolSize(maxPoolSize);
      40.         //配置队列大小
      41.         executor.setQueueCapacity(queueCapacity);
      42.         //配置线程池中的线程的名称前缀
      43.         executor.setThreadNamePrefix(namePrefix);
      44.  
      45.         // rejection-policy：当pool已经达到max size的时候，如何处理新任务
      46.         // CALLER_RUNS：不在新线程中执行任务，而是有调用者所在的线程来执行
      47.         executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
      48.  
      49.         //执行初始化
      50.         executor.initialize();
      51.         log.info("end asyncExcelAnalysisServiceExecutor------------");
      52.         return executor;
      53.     }
      54.  
      55. }

      ExcelConstants

      1. package com.xxx.xxx.constants;
      2.  
      3. /**
      4.  * description:线程池配置类
      5.  *
      6.  * @author: lgq
      7.  * @create: 2024-04-17 10:28
      8.  */
      9. public class ExcelConstants {
      10.     public static final Integer PER_SHEET_ROW_COUNT = 100*10000;
      11.     public static final Integer PER_WRITE_ROW_COUNT = 20*10000;
      12.     public static final Integer PER_WRITE_EXCEL_ROW_COUNT = 2 * 1000;
      13.     public static final Integer GENERAL_ONCE_SAVE_TO_DB_ROWS_JDBC = 10*10000;
      14.     public static final Integer GENERAL_ONCE_SAVE_TO_DB_ROWS_MYBATIS = 5*10000;
      15. }

      配置文件

      1. spring:
      2.   servlet:
      3.     multipart:
      4.       enabled: true
      5.       max-file-size: 100MB # 单个文件的最大值
      6.       max-request-size: 100MB # 上传文件总的最大值

      pom依赖

      1.         
      2.             com.alibaba
      3.             easyexcel
      4.             3.3.2
      5.

    12. 五.压测

    13. jvm参数（本地电脑，性能较差）
    14. -Xms2g -Xmx2g
    15. 导出日志

    性能监控

    压测结果

  • 相关阅读:
    大数据之路阿里巴巴实践
    mybatis
    Jupyter安装启动、登录密码问题解决
    微信小游戏5月畅销榜，新老产品更替显著，亿级爆款频出
    ES6(1) let 和 const
    外贸爬虫系统
    【NestJS系列】核心概念：Providers提供者
    Nanoprobes丨Nanogold 印迹和免疫化学技术
    关于VO、BO、DTO、PO、Entity、DO的说法
    echart 雷达图
  • 原文地址：https://blog.csdn.net/lgq2016/article/details/137932007