Software engineers: Discover how to efficiently generate large Excel files in Java using Apache POI. This guide covers techniques to optimize memory use and prevent out-of-memory errors. Elevate your development skills with expert strategies for high-performance Excel generation.
How to generate large Excel files in Java using Apache POI.
The client wants to export Excel files from the database in real-time for the project. Still, the database has a million records. In that case, it will cause an error “java.lang.OutOfMemoryError: Java heap space”(Java Excel out of memory error) when generating the Excel file process.
For a developer who faces this error when generating large Excel files.
The solution uses Apache POI 3.16 or later and Java version 1.7 or later.
This solution will reduce logical memory on the JVM. The developer can download JAR files or dependencies from the Maven repository website.
VisualVM is used to monitor memory management with Apache POI
VisualVM is a powerful tool for monitoring and troubleshooting Java applications. It provides real-time insights into memory usage, CPU performance, and thread activity and allows for profiling and heap dumps.
Example: Generating Large Excel Files
- Add Maven dependency.
Apache POI Maven Repository:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version> <!-- Use the latest version available -->
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version> <!-- Use the latest version available -->
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version> <!-- Use the latest version available -->
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>5.1.1</version> <!-- Use the latest version available -->
</dependency>2. Create a Java class, an example of a large Java Excel file.
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();
}//lombok
@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;
}3. Result: large Excel file.
For example, the method “setRandomAccessWindowSize” reduces logical memory on the JVM(JVM optimization) and prevents an error “out of memory”(Apache POI memory management).
The Developer can adjust the size for suitable individual projects.
This SXSSFWorkbook for writing rows and cells only can’t edit or replace a cell’s value; if the developer wants to edit a cell value after creating an Excel file, change to XSSFWorkbook instead.
Choose the Apache POI version, which must be compatible with your Java version. Check your compatible version.
