1. The difference between HSSF Workbook and SXSSF Workbook
HSSF Workbook: A version of Excel 2003 (including 2003) with an extension of. xls. A table supports up to 65536 rows of data, 256 columns, that is, a sheet page, which can export up to 6 weeks of data.
XSSF Workbook: Operates Excel 2007-2010 version with the extension. xlsx uses different tool classes for different versions of EXCEL documents, if used incorrectly.
The following error message will be prompted.
org.apache.poi.openxml4j.exceptions.InvalidOperationException
org.apache.poi.poifs.filesystem.OfficeXmlFileException
Its one table supports 1048576 rows and 16384 columns. The introduction of both is very important for exporting millions of data below. Don't use them incorrectly!
2. Export millions of data using SXSSFWorkbook object
SXSSF Workbook is similar to HSSF Workbook. If you used HSSF Workbook as I did before and want to modify it now, you just need to change HSSF Workbook to SXSSF Workbook. Here is my introduction. The specific use can also be referred to. API.
Because of the project business, it is necessary to export millions of data to excel. After studying various schemes, the SXSSF Workbook using POI is finally determined.
SXSSF Workbook is a new addition to POI 3.8. After excel 2007, each sheet supports 104,000 rows of data.
3. How to divide millions of data into sheet pages and export them to excel
Exporting millions of data to excel is very simple. It only needs to modify the original HSSF Workbook to SXSSF Workbook or use SXSSF Workbook object directly. It is used to export large data directly. Official Documents It's introduced, but if you have 300w data, import it into a sheet page of excel. Think about how long it will take to open excel. Slow down may cause the program to fail to load or terminate the process directly.
4. Look at the effect of the derivation first.
Because millions of data are too long, only the tail effect is intercepted here.
5. Here's the Java code section
/** * Using SXSSFWorkbook Object to Implement excel Export * (Generally, excel is used to export millions of data. */ public void exportBigDataExcel() { long startTime = System.currentTimeMillis(); //start time System.out.println("start execute time: " + startTime); // 1.Create A Workbook // Threshold, the maximum number of objects in memory, beyond which a temporary file is generated and stored on the hard disk SXSSFWorkbook wb = new SXSSFWorkbook(1000); //2.stay Workbook Add a sheet,Corresponding Excel In the document sheet Sheet sheet = wb.createSheet(); //3.Setting Styles and Font Styles CellStyle titleCellStyle = createTitleCellStyle(wb); CellStyle headCellStyle = createHeadCellStyle(wb); CellStyle cellStyle = createCellStyle(wb); //4.Create titles, headers, contents, and merge cells int rowNum = 0;// Line number // Create the first row, index from 0 Row row0 = sheet.createRow(rowNum++); row0.setHeight((short) 800);// Setting line height String title = "Here is the title title title."; Cell c00 = row0.createCell(0); c00.setCellValue(title); c00.setCellStyle(titleCellStyle); // Merge cells, parameters are start row, end row, start column, end column (index 0 starts) sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));//Title merge cell operation, 6 is total column number // Second line Row row1 = sheet.createRow(rowNum++); row1.setHeight((short) 500); String[] row_first = {"Form filling units:", "", "", "", "", " xxxx Year end x quarter ", ""}; for (int i = 0; i < row_first.length; i++) { Cell tempCell = row1.createCell(i); tempCell.setCellStyle(headCellStyle); if (i == 0) { tempCell.setCellValue(row_first[i] + "Testing unit"); } else if (i == 5) { tempCell.setCellStyle(headCellStyle); tempCell.setCellValue(row_first[i]); } else { tempCell.setCellValue(row_first[i]); } } // merge sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 6)); //The third line Row row2 = sheet.createRow(rowNum++); row2.setHeight((short) 700); String[] row_second = {"Name", "Acquisition", "", "", "Registration", "", "Remarks"}; for (int i = 0; i < row_second.length; i++) { Cell tempCell = row2.createCell(i); tempCell.setCellValue(row_second[i]); tempCell.setCellStyle(headCellStyle); } // merge sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));//Name sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 3));//Number of persons sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 5));//Registration sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, 6));//Remarks //The third line Row row3 = sheet.createRow(rowNum++); row3.setHeight((short) 700); String[] row_third = {"", "Number of registrations(people)", "Total number of certificates(people)", "Certification rate(%)", "Number of registered households(household)", "Registration time", ""}; for (int i = 0; i < row_third.length; i++) { Cell tempCell = row3.createCell(i); tempCell.setCellValue(row_third[i]); tempCell.setCellStyle(headCellStyle); } // data processing(Create 1 million test data) List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>(); for (int i = 0; i < 999999; i++) { Map<String,Object> map = new HashMap<String,Object>(); map.put("name", "Test name" + i); map.put("r1", "111"); map.put("r2", "222"); map.put("r3", "333"); map.put("r4", "444"); map.put("addTime", new DateTime()); map.put("r6", "Here is the remark."+i); dataList.add(map); } for (Map<String, Object> excelData : dataList) { Row tempRow = sheet.createRow(rowNum++); tempRow.setHeight((short) 500); // Loop Cells Fill in Data for (int j = 0; j < 7; j++) { Cell tempCell = tempRow.createCell(j); tempCell.setCellStyle(cellStyle); String tempValue; if (j == 0) { // Name of Township and Street tempValue = excelData.get("name").toString(); } else if (j == 1) { // Number of registrations (persons) tempValue = excelData.get("r1").toString(); } else if (j == 2) { // Number of Certificates (Persons) tempValue = excelData.get("r2").toString(); } else if (j == 3) { // Certification rate(%) tempValue = excelData.get("r3").toString(); } else if (j == 4) { // Number of registered households (households) tempValue = excelData.get("r4").toString(); } else if (j == 5) { // Date of registration tempValue = excelData.get("addTime").toString(); } else { // Remarks tempValue = excelData.get("r6").toString(); } tempCell.setCellValue(tempValue); // sheet.autoSizeColumn(j);// Automatically adjust column widths according to content. } } //Setting column widths must follow cell values sheet.setColumnWidth(0, 4000);//Name sheet.setColumnWidth(1, 3000);//Number of registrations(people) sheet.setColumnWidth(2, 3000);//Total number of certificates(people) sheet.setColumnWidth(3, 3000);//Certification rate(%) sheet.setColumnWidth(4, 3000);//Number of registered households(household) sheet.setColumnWidth(5, 6000);//Registration time sheet.setColumnWidth(6, 4000);//Remarks // Commentary line Row remark = sheet.createRow(rowNum++); remark.setHeight((short) 500); String[] row_remark = {"Note: The "Certification Rate" in the table=Total number of certificates÷Number of registrations×100%"", "", "", "", "", "", ""}; for (int i = 0; i < row_remark.length; i++) { Cell tempCell = remark.createCell(i); if (i == 0) { tempCell.setCellStyle(headCellStyle); } else { tempCell.setCellStyle(cellStyle); } tempCell.setCellValue(row_remark[i]); } int remarkRowNum = dataList.size() + 4; sheet.addMergedRegion(new CellRangeAddress(remarkRowNum, remarkRowNum, 0, 6));//Comment line merge cell // Biko Row foot = sheet.createRow(rowNum++); foot.setHeight((short) 500); String[] row_foot = {"Auditor:", "", "Filler:", "", "Fill in time:", "", ""}; for (int i = 0; i < row_foot.length; i++) { Cell tempCell = foot.createCell(i); tempCell.setCellStyle(cellStyle); if (i == 0) { tempCell.setCellValue(row_foot[i] + "Zhang San"); } else if (i == 2) { tempCell.setCellValue(row_foot[i] + "Li Si"); } else if (i == 4) { tempCell.setCellValue(row_foot[i] + "xxxx"); } else { tempCell.setCellValue(row_foot[i]); } } int footRowNum = dataList.size() + 5; // notes sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 0, 1)); sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 4, 6)); long finishedTime = System.currentTimeMillis(); //Processing completion time System.out.println("finished execute time: " + (finishedTime - startTime)/1000 + "m"); //export HttpServletResponse response = this.getResponse(); String fileName = "Report file name.xlsx"; try { fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1"); response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\""); OutputStream stream = response.getOutputStream(); if (null != wb && null != stream) { wb.write(stream);// Write the data out wb.close(); stream.close(); long stopTime = System.currentTimeMillis(); //Writing time System.out.println("write xlsx file time: " + (stopTime - startTime)/1000 + "m"); } } catch (Exception e) { e.printStackTrace(); } }
CellStyle Title, Header, Content Style Code:
private static CellStyle createTitleCellStyle(Workbook workbook) { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.index); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.index); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.index); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.index); cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); return cellStyle; }
private static CellStyle createHeadCellStyle(Workbook workbook) { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.index); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.index); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.index); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.index); cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); return cellStyle; }
private static CellStyle createCellStyle(Workbook workbook) { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.index); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.index); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.index); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.index); return cellStyle; }
Complete! good luck!