java export excel exceeds 65533 lines

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;
    }

Posted on Thu, 07 Nov 2019 13:44:12 -0500 by kiwibrit