Chapter 1: Introduction to Excel File Generation

Generating Excel files is a common requirement in many web applications. This guide will discuss how to efficiently create large Excel files using Java and Apache POI.

The challenge arises when a client needs to export real-time data from a database that may contain millions of records. Attempting to generate an Excel file with such vast data can lead to an "out of memory" error.

If you encounter this issue during the Excel file generation process, there is a solution. Utilizing Apache POI version 3.16 or newer along with Java 1.7 or above can significantly reduce the logical memory footprint on the Java Virtual Machine (JVM).

Here’s a sample implementation to illustrate how this can be done:

public static void generateExcelExample1() throws IOException {

SXSSFWorkbook sxssWb = new SXSSFWorkbook(100);

SXSSFSheet sxssSheet = (SXSSFSheet) sxssWb.createSheet("sheet1");

sxssSheet.setRandomAccessWindowSize(100);

Iterator<UserInfo> it = sampleData().stream().iterator();

int rowNumber = 0;

while (it.hasNext()) {

UserInfo userInfo = (UserInfo) it.next();

SXSSFRow row = (SXSSFRow) sxssSheet.createRow(rowNumber++);

SXSSFCell cell = (SXSSFCell) row.createCell(0);

cell.setCellValue(userInfo.getFirstName());

cell = (SXSSFCell) row.createCell(1);

cell.setCellValue(userInfo.getLastName());

}

// Support file name UTF-8.

String originalNameEncode = URLEncoder.encode("excel.xlsx", "UTF-8");

FileOutputStream outputStream = new FileOutputStream(originalNameEncode);

sxssWb.write(outputStream);

sxssWb.close();

}

@Data

public class UserInfo {

private String firstName;

private String lastName;

public UserInfo(String firstName, String lastName){

this.firstName = firstName;

this.lastName = lastName;

}

}

public List<UserInfo> sampleData(){

List<UserInfo> userInfos = new ArrayList<>();

for(int i = 0 ; i < 1000000; i++) {

userInfos.add(new UserInfo("Jane", "Doe"));

}

return userInfos;

}

Chapter 2: The Result of Excel File Creation

The Excel file has been successfully created.

The method setRandomAccessWindowSize is essential as it minimizes logical memory usage on the JVM, thus helping to avoid the "out of memory" error. Developers have the flexibility to adjust this size based on their specific project requirements.

It is important to note that the SXSSFWorkbook, which is used for writing rows and cells, does not allow for editing or replacing cell values. If you need to modify cell values after the file has been created, consider switching to XSSFWorkbook.

Thank you for your attention.