SXSSFWorkbook导出百万级数据性能和行数问题解决

先说我的背景：我的情况是从iotdb中查询数据，然后解析成需要的格式 通过SXSSFWorkbook导出到excel,实验数据频次n*5000/s,n表示我本次实验选择的通道数量，可想而知我几分钟的实验就会有几百万的数据，并且要对数据进行解析然后画表导出。

再说遇到的问题：

1.大数据量势必出现导出性能问题

我尽量优化了每一步的代码，但是一次次测试随着实验时间的增加，性能始终是个瓶颈，前端不可能无限制增加超时时间，SXSSFWorkbook也不支持多线程和异步操作。

解决办法：最后我想了个办法边做实验边画表，当实验结束的时候直接生成excel,那么前端点击下载的时候我只是把这个已经生成的文件直接给他下载就ok了，下面是具体的代码：

1.1创建一个Schedule定时任务

@Slf4j
public class EventBasedScheduler {
    private final ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);

    private final ScheduledFuture<?> scheduledTask;

    private SessionPool sessionPool;
    private volatile boolean running = false;

    private String peName="";

    private Integer rowNum= 0;

    private SXSSFWorkbook workbook = new SXSSFWorkbook();

   // private FZProjectExperimentEntity experimentEntity;

    private long startTimestamp;

    private List<String> channels;


    public EventBasedScheduler(long initialDelay, long period, TimeUnit unit) {
        scheduledTask = scheduler.scheduleAtFixedRate(() -> {
            if (running) {
                // 这里执行定时任务
               log.info("定时任务执行中。。。");
                 workbook =processExportData(workbook,startTimestamp, channels);
            }
        }, initialDelay, period, unit);
    }

/**
实验开始调用此方法去触发定时任务启动，开始创建SXSSFWorkbook绘制表格数据
*/
    public void start(int rowNum,SXSSFWorkbook workbook,SessionPool sessionPool,long startTimestamp,String peName,List<String> channels) {
        running = true;
        log.info("开启实验："+peName);
        this.rowNum= rowNum;
        this.sessionPool = sessionPool;
        this.startTimestamp = startTimestamp;
        this.peName = peName;
        this.channels=channels;
        this.workbook = workbook;
    }


/**
暂停实验 停止定时任务
*/
    public void pause(){
        running = false;
        log.info("暂停实验："+peName);
    }

/**
 恢复实验，重启定时任务
*/
    public void restore(SessionPool sessionPool,long startTimestamp,String peName,List<String> channels) {
        running = true;
        log.info("恢复实验："+peName);
        this.sessionPool = sessionPool;
        this.startTimestamp = startTimestamp;
        this.peName = peName;
        this.channels=channels;
    }

/**
结束实验，输出SXSSFWorkbook到excel文件
*/
    public String stop() {
        running = false;
        log.info("结束实验："+peName);
       // scheduledTask.cancel(false);
        MultipartFile file = workbookToCommonsMultipartFile(workbook,peName+".xlsx");
        String path = "";
        if(file!=null){
            try {
                path = FileUploadUtils.upload(file);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
       // scheduler.shutdown();
        log.info("定时任务结束");
         return path;
    }


     /**
    * 按照业务需求绘制你的workbook
    */
    SXSSFWorkbook processExportData(SXSSFWorkbook workbook,long startTimestamp, List<String> channels) {
        log.info("开始处理导出数据");

        //....此处可以按照具体业务组装你的workbook....
        //*****
        log.info("结束处理导出数据");
        // 具体处理逻辑
        //每次组装完更新开始时间，因为每次去iotdb查询上次到这次时间段内的数据，我是每隔2s执行一次这个定时任务
     this.startTimestamp=endTimestamp;
        return workbook;
    }



    public  MultipartFile workbookToCommonsMultipartFile(SXSSFWorkbook workbook, String fileName) {
        FileItemFactory factory = new DiskFileItemFactory(16, null);
        FileItem fileItem = factory.createItem("textField", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", true, fileName);
        try {
            OutputStream os = fileItem.getOutputStream();
            if(workbook!=null) {
                workbook.write(os);
                workbook.dispose();
            }
            os.close();

            //FileItem转MultipartFile
            MultipartFile multipartFile = new CommonsMultipartFile(fileItem);
            return multipartFile;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;

    }


}

1.2.初始化定时器

public class GlobalScheduler {
    private static GlobalScheduler instance;
    private EventBasedScheduler scheduler;
    private GlobalScheduler() {
        scheduler = new EventBasedScheduler(0, 2, TimeUnit.SECONDS);
    }
    public static GlobalScheduler getInstance() {
        if (instance == null) {
            instance = new GlobalScheduler();
        }
        return instance;
    }
    public EventBasedScheduler getScheduler() {
        return scheduler;
    }
}

1.3调用定时器：

   public class testController {
  @Autowired
    private SessionPool sessionPool;

GlobalScheduler globalScheduler = GlobalScheduler.getInstance();
    EventBasedScheduler scheduler = globalScheduler.getScheduler();
    //开始实验
     @GetMapping("/startExperiment")
    @ApiOperation("开始实验")
    public R startExperiment(@RequestParam Integer id) {
 //此处省略其他业务代码。。。
    long startTimestamp = DateUtil.parse(entity.getStartTime()).getTime();
      SXSSFWorkbook workbook = new SXSSFWorkbook();
scheduler.start(1,workbook,sessionPool,startTimestamp,entity.getExperimentName(),channels);
       //此处省略其他业务代码。。。
}

//***暂停恢复结束实验以此类推 调用不同的scheduler方法即可***
    
}

2.SXSSFWorkbook行数上限问题

SXSSFWorkbook每个sheet行数上限104w左右，显然在我这个项目中是完全不够用的，所以画表的数据需要进行一下行数判断，当行数过多的时候，重新创建一个sheet,在上面定时器processExportData构建SXSSFWorkbook的时候加入如下代码就可以实现当行数达到100w就新建sheet的操作

 if(workbook.getSheet(eName+"_"+type+"_"+rowNum)!=null){
            int num = workbook.getSheet(eName+"_"+type+"_"+rowNum).getLastRowNum();
            log.info("LastRowNum:"+num);
            if((num/(1000000*rowNum))>=1){
                ++rowNum;
                log.info("newRowNum:"+rowNum);
                sheet = workbook.createSheet(eName+"_"+type+"_"+rowNum);
            }else{
                log.info("rNum:"+rowNum);
                sheet = workbook.getSheet(eName+"_"+type+"_"+rowNum);
            }

        }else{
            sheet = workbook.createSheet(eName+"_"+type+"_"+rowNum);
        }

当然我上面的前提是我的实验一旦结束 不允许修改，以上是我自己遇到的大数据导出问题及解决办法，文章是我对自己做的东西的记录，也希望能帮助到遇到同样问题的朋友，如有问题，欢迎指正。