多线程实现导出海量数据到excel

海量数据导入excel

实现数据库查询100万条数据，导入excel
1.分批从数据库查询数据到List集合中，每次查询10000条
2.将List集合中数据读取SXSSFWorkbook
3.创建 FileOutPutStream 类，将SXSSFWorkbook写到 FileOutPutStream 中
4.FileOutPutStream 类flush到对应文件中

代码实现

    public JSONObject save(@RequestBody JSONObject body,HttpSession session, HttpServletRequest request){
        User user= (User) session.getAttribute("user");
        Map<String, Object> roleLimit = new HashMap<>();
        roleLimit.put("roleId", user.getRoleId());
        Date date=new Date();
        body.put("user", user);
        body.put("createUser",user.getName());
        body.put("createTime", sdf.format(date));
        body.put("updateTime", sdf.format(date));
        body.put("status", "1");
        String fileName=UUID.randomUUID().toString()+".xlsx";
        body.put("fileName", fileName);
        body.put("session", session);
        //保存导出日志
        exportService.save(body);
        String filePath=request.getSession().getServletContext().getRealPath("/")+"upload";
         new Thread(new Runnable() {
            @Override
            public void run() {
                Map<String, Object> roleLimit = new HashMap<>();
                roleLimit.put("roleId", user.getRoleId());
                List<String> modelNameList = userRoleService.selectUserAuthModelName(user.getRoleId());
                body.put("modelNameList", modelNameList);
                String fileName = body.get("fileName").toString();
                String key="export:"+fileName.replaceAll(".xlsx", "");
                File file=new File(filePath);
                if(!file.exists()){
                    file.mkdirs();
                }
                List<String> fieldList = Arrays.asList(new String[]{"mac_address","serialNo","manufacturer",
                        "model_name","last_contact","groupName","current_software","online"}) ;

                FileOutputStream outputStream = null;
                int total=exportDao.queryTerminalsCount(body);

                try {
                    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(5000);
                    Sheet sheet = sxssfWorkbook.createSheet("Terminal");\
                    //将excel标题写入
                    Row row = sheet.createRow(0);
                    for (int i = 0; i < fieldList.size(); i++) {
                        row.createCell(i).setCellValue(fieldList.get(i));
                    }
					//设置每次从数据的查询List集合条数
                    int limit =10000,offset=0,num=1;
                    body.put("limit",limit);
                    body.put("offset",offset);
                    List<Map<String,Object>> terminalList=exportDao.queryTerminals(body);
                    int rowNum = 1;
                    while (terminalList.size()>0){
                        int fieldLength = fieldList.size();
                        for(Map<String,Object> tempMap : terminalList){
                            Row tempRow = sheet.createRow(rowNum);
                            int j=0;
                            for(; j<fieldLength; j++){
                                Object  object =  tempMap.get(fieldList.get(j));
                                tempRow.createCell(j).setCellValue(object!=null?object.toString():"");
                            }
                            rowNum++;
                        }
                        offset = limit*num;
                        body.put("offset", offset);
                        terminalList = exportDao.queryTerminals(body);
                        num++;
                        //计算导出进度条百分比
                        double sum=total;
                        double count=offset;
                        double d=count/sum;
                        int i= (int) (d*100);
                        if(i>100){
                            i=100;
                        }
                        long l=60*60;
                        redisTemplateUtil.set(key, i,l);
                    }
                    outputStream=new FileOutputStream(filePath+"/"+fileName);
                    sxssfWorkbook.write(outputStream);
                    outputStream.flush();
                    body.put("url", "upload/"+fileName);
                    body.put("updateTime", sdf.format(new Date()));
                    body.put("status", "0");
                    exportDao.update(body);
                    logger.info("Terminal导出完成...");
                } catch (Exception e) {
                    logger.info("Terminal导出失败...");
                    e.printStackTrace();
                    body.put("updateTime", sdf.format(new Date()));
                    body.put("status", "2");
                    exportDao.update(body);

                } finally {
                    if (outputStream != null) {
                        try {
                            outputStream.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }

            }
        }).start();
        JSONObject result=new JSONObject();
        result.put("status", true);
        result.put("message", "success");
        return result;
    }