How to divide millions of data into multiple sheets?

How to divide millions of data into multiple sheet pages and export them to excel

Exporting millions of data to excel is very simple. You only need to modify the original HSSFWorkbook to SXSSFWorkbook. That's okay, but if there are 3 million pieces of data, import it into an excel sheet page at once. Think about it, it will take a while to open excel. If it is slow, it may cause the program to fail to load, or the process will directly end, or it will open. There is so much data that it gives me a headache to look at it. I once saw a piece of news where I deeply admire the perseverance of foreigners.

Part of the code is given here for reference and research. Paging has been implemented:

@SuppressWarnings({ "deprecation", "unchecked" })
    @RequestMapping("export-TrainHistoryRecord")
    @ResponseBodyprotected void buildExcelDocument(EmployeeTrainHistoryQuery query,ModelMap model,
            SXSSFWorkbook workbook, HttpServletRequest request,
            HttpServletResponse response) throws Exception {try {
            response.reset();// 获得国际化语言RequestContext requestContext = new RequestContext(request);
            String CourseCompany = requestContext
                    .getMessage("manage-student-trainRecods");
            response.setContentType("APPLICATION/vnd.ms-excel;charset=UTF-8");// 注意，如果去掉下面一行代码中的attachment; 那么也会使IE自动打开文件。            response.setHeader("Content-Disposition","attachment; filename="
                            + java.net.URLEncoder.encode(
                                    DateUtil.getExportDate() + ".xlsx", "UTF-8"));//Excel 扩展名指定为xlsx  SXSSFWorkbook对象只支持xlsx格式OutputStream os = response.getOutputStream();
            CellStyle style = workbook.createCellStyle();// 设置样式style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);//设置单元格着色style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //设置单元格填充样式style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//设置下边框style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//设置左边框style.setBorderRight(HSSFCellStyle.BORDER_THIN);//设置右边框style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中//获取国际化文件String employeeCode = requestContext.getMessage("employeeCode");
            String employeeName = requestContext.getMessage("employeeName");
            String orgName = requestContext.getMessage("orgName");
            String startDate = requestContext.getMessage("start.date");
            String endDate = requestContext.getMessage("end.date");
            String courseCode = requestContext.getMessage("courseCode");
            String courseName = requestContext.getMessage("courseName");
            String sessionName = requestContext.getMessage("sessionName");

            List<EmployeeTrainHistoryModel> list = null;try {//查询数据库中共有多少条数据                            query.setTotalItem(employeeTrainHistoryService.fetchCountEmployeeTrainHistoryByQuery(query));                int page_size = 100000;// 定义每页数据数量int list_count =query.getTotalItem();//总数量除以每页显示条数等于页数int export_times = list_count % page_size > 0 ? list_count / page_size+ 1 : list_count / page_size;                     //循环获取产生每页数据for (int m = 0; m < export_times; m++) {
                        query.setNeedQueryAll(false);
                        query.setPageSize(100000);//每页显示多少条数据query.setCurrentPage(m+1);//设置第几页 list=employeeTrainHistoryService.getEmployeeTrainHistoryByQuery(query);//新建sheet Sheet sheet = null;
                            sheet = workbook.createSheet(System.currentTimeMillis()+ CourseCompany+m);// 创建属于上面Sheet的Row，参数0可以是0～65535之间的任何一个，Row header = sheet.createRow(0); // 第0行// 产生标题列，每个sheet页产生一个标题                             Cell cell;
                            String[] headerArr = new String[] { employeeCode, employeeName,
                                    orgName, startDate, endDate, courseCode, courseName, sessionName,
                                    hoursNunber };for (int j = 0; j < headerArr.length; j++) {
                                cell = header.createCell((short) j);
                                cell.setCellStyle(style);
                                cell.setCellValue(headerArr[j]);
                            }// 迭代数据 if (list != null && list.size() > 0) {                                 int rowNum = 1;                                 for (int i = 0; i < list.size(); i++) {
                                     EmployeeTrainHistoryModel history=list.get(i);
                                         sheet.setDefaultColumnWidth((short) 17);
                                     Row row = sheet.createRow(rowNum++);
                                     row.createCell((short) 0).setCellValue(
                                             history.getEmployeeCode());
                                     row.createCell((short) 1).setCellValue(
                                             history.getEmployeeName());
                                     row.createCell((short) 2)
                                             .setCellValue(history.getOrgName());                                     if (history.getTrainBeginTime() != null) {
                                         row.createCell((short) 3).setCellValue(
                                                 DateUtil.toString(history.getTrainBeginTime()));
                                     } else {
                                         row.createCell((short) 3).setCellValue("");
                                     }                                     if (history.getTrainEndTime() != null) {
                                         row.createCell((short) 4).setCellValue(
                                                 DateUtil.toString(history.getTrainEndTime()));
                                     } else {
                                         row.createCell((short) 4).setCellValue("");
                                     }
                                     row.createCell((short) 5).setCellValue(
                                             history.getCourseCode());
                                     row.createCell((short) 6).setCellValue(
                                             history.getCourseName());
                                     row.createCell((short) 7).setCellValue(
                                             history.getSessionName());                                     if (history.getHoursNumber() != null)
                                         row.createCell((short) 8).setCellValue(
                                                 history.getHoursNumber().toString());
                                 }
                             }
                             
                             list.clear();
                         }                            
            } catch (Exception e) {
                e.printStackTrace();
            }try {
                workbook.write(os);
                os.close();
            } catch (Exception e) {// TODO Auto-generated catch block                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

4. How to export data efficiently

Paging has been implemented. When exporting data based on data volume, efficiency must be considered. How can we squeeze out time? Since Apache POI provides a method to export excel, it must have also taken into account the issue of efficiency. Check the official documentation. Sure enough, looking at the documentation, it probably means that SXSSF is used when large spreadsheets must be generated, and the heap space is limited Officially provides 2 types Method:

1. SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk

2.SXSSFWorkbook wb = new SXSSFWorkbook(-1 ); // turn off auto-flushing and accumulate all rows in memory

Value 100 Keep 100 rows in memory, excess rows will be flushed to disk

A value of -1 indicates unlimited access. In this case all records that have not been flushed by calling flush() are available and is used for random access.

##The article says at the end that when the temporary file is too large, you can use the setCompressTempFiles method to compress it,

I am greedy. I used two here. One is used to set up temporary files and the other is used to input data. The test data is 30w data. The results are as shown in the figure. However, I still feel that it takes too much time. I don’t know why. It’s not that there’s something wrong with my program writing. Friends who know, please leave a message!

The above is the detailed content of How to divide millions of data into multiple sheets?. For more information, please follow other related articles on the PHP Chinese website!