Business background:
List export, data export more than 65533 rows
Solution:
1. Over 65533 lines, direct system hint: The maximum number of bars supported by this system is 65533 lines
2. Change the export template to.xlsx, use XSSFWorkbook for POI export, take all data into memory, you can export more than 65533 lines, but when you go online, you will find that there will be memory overflow
3. Change the export template to.xlsx, use SXSSFWorkbook for POI export, put some data in memory each time. After import, refresh and put data from disk to memory again will produce temporary files. After export, delete temporary files. This will make CPU rise briefly, but it will decrease quickly.
private static ByteArrayInputStream WriteToExcelTemplate(DataModel dataModel, HttpServletRequest request) { String template = getRealPathModelExcel(dataModel.getTemplate(), request); FileInputStream fileInputStream = new FileInputStream(template); XSSFWorkbook workBook = new XSSFWorkbook(fileInputStream); int rowaccess = 100; SXSSFWorkbook wb = new SXSSFWorkbook(workBook,rowaccess); ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); Sheet sh = wb.getSheetAt(0); List<Object[]> data = dataModel.getData(); int startRow = dataModel.getStartRow(); for (int i = 0; i < data.size(); ++i) { Object[] objArray = (Object[]) data.get(i); Row row = sh.createRow(startRow); Cell[] cell = new SXSSFCell[objArray.length]; for (int j = 0; j < objArray.length; ++j) { cell[j] = row.createCell(j); cell[j].setCellStyle(dataModel.getCenterStyle()); if (objArray[j] == null) { cell[j].setCellValue(""); } else { setCellValue(cell[j], objArray[j]); } } ++startRow; if(i%rowaccess==0){ ((SXSSFSheet)sh).flushRows(); } } wb.write(byteArrayOutputStream); byte[] byteArray = byteArrayOutputStream.toByteArray(); ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArray); byteArrayOutputStream.close(); deleteSXSSFTempFiles(wb);//Delete Temporary Files return byteArrayInputStream; } /** * Delete Temporary Files * @param workbook * @throws NoSuchFieldException * @throws IllegalAccessException */ public static void deleteSXSSFTempFiles(SXSSFWorkbook workbook) throws NoSuchFieldException, IllegalAccessException { int numberOfSheets = workbook.getNumberOfSheets(); // iterate through all sheets (each sheet as a temp file) for (int i = 0; i < numberOfSheets; i++) { Sheet sheetAt = workbook.getSheetAt(i); // delete only if the sheet is written by stream if (sheetAt instanceof SXSSFSheet) { SheetDataWriter sdw = (SheetDataWriter) getPrivateAttribute(sheetAt, "_writer"); File f = (File) getPrivateAttribute(sdw, "_fd"); try { f.delete(); } catch (Exception ex) { // could not delete the file } } } } public static Object getPrivateAttribute( Object containingClass, String fieldToGet) throws NoSuchFieldException, IllegalAccessException{ // get the field of the containingClass instance Field declaredField = containingClass.getClass().getDeclaredField(fieldToGet); declaredField.setAccessible(true); // access it Object get = declaredField.get(containingClass); // return it! return get; }