How to use the Streaming Excel Reader framework to export a large Excel file in the Java library
How to use the Streaming Excel Reader framework to export a large Excel file in the Java library
Overview:
In actual applications, it is a common demand to export data to Excel files when large data sets are required.However, if the amount of data is large, the traditional export method may cause memory overflow and performance problems.To solve this problem, the Streaming Excel Reader framework can be used. It provides an effective and scalable way based on the Apache Poi library to export large Excel files.The next article will introduce how to use the Streaming Excel Reader framework to generate a large Excel file in the Java library.
Step 1: Add dependencies
First, add Streaming Excel Reader dependencies to the project construction file.Suppose you are using Maven to build a project, you can add the following dependencies to the pom.xml file:
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.1.0</version>
</dependency>
Step 2: Create an excel file and worksheet
In the Java code, you must first create a new Excel file and a worksheet.The following is a simple example:
try {
Workbook workbook = StreamingReader.builder()
.rowCacheSize(100)
.bufferSize(4096)
.open(new File("output.xlsx"));
Sheet sheet = workbook.createSheet("Sheet1");
// Add the header
Row headerRow = sheet.createRow(0);
Cell headerCell1 = headerRow.createCell(0);
headerCell1.setCellValue("列1");
Cell headerCell2 = headerRow.createCell(1);
headerCell2.setCellValue("列2");
// Add data line
Row dataRow = sheet.createRow(1);
Cell dataCell1 = dataRow.createCell(0);
Datacell1.SetcellValue ("Data 1");
Cell dataCell2 = dataRow.createCell(1);
Datacell2.SetcellValue ("Data 2");
// Write the worksheet into the file
FileOutputStream fileOut = new FileOutputStream("output.xlsx");
workbook.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
Step 3: Write data one by one
The main feature of the Streaming Excel Reader framework is that it divides the worksheet into a series of blocks, and then it can be used to write the data as iterates.This method greatly reduces memory occupation and is particularly suitable for the export of large data sets.
try {
Workbook workbook = StreamingReader.builder()
.rowCacheSize(100)
.bufferSize(4096)
.open(new File("output.xlsx"));
Sheet sheet = workbook.getSheetAt(0);
// Start writing data from the second line
int rowNum = 1;
// Simten to generate a large amount of data
for (int i = 0; i < 1000000; i++) {
Row row = sheet.createRow(rowNum++);
Cell cell1 = row.createCell(0);
cell1.setcellValue ("data" + i);
Cell cell2 = row.createCell(1);
cell2.setcellValue ("data" + i);
}
// Write the worksheet into the file
FileOutputStream fileOut = new FileOutputStream("output.xlsx");
workbook.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
Step 4: Set style and format
The Streaming Excel Reader framework also supports setting styles and formats to beautify the generated Excel file.You can use the style and format object in the POI library to achieve this.The following is an example of a setting style:
try {
Workbook workbook = StreamingReader.builder()
.rowCacheSize(100)
.bufferSize(4096)
.open(new File("output.xlsx"));
Sheet sheet = workbook.getSheetAt(0);
// Set the header style
CellStyle headerStyle = workbook.createCellStyle();
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerStyle.setFont(headerFont);
Row headerRow = sheet.getRow(0);
Cell headerCell1 = headerRow.createCell(0);
headerCell1.setCellValue("列1");
headerCell1.setCellStyle(headerStyle);
Cell headerCell2 = headerRow.createCell(1);
headerCell2.setCellValue("列2");
headerCell2.setCellStyle(headerStyle);
// Set the data format
CellStyle dataStyle = workbook.createCellStyle();
dataStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd"));
Row dataRow = sheet.getRow(1);
Cell dataCell1 = dataRow.createCell(0);
dataCell1.setCellValue(new Date());
dataCell1.setCellStyle(dataStyle);
// Write the worksheet into the file
FileOutputStream fileOut = new FileOutputStream("output.xlsx");
workbook.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
in conclusion:
By using the Streaming Excel Reader framework, we can effectively generate large Excel files in the Java library.It provides a way to write data according to the line to avoid memory overflow and performance.We can also set styles and formats to beautify the generated Excel files.I hope this article will help you understand how to use the Streaming Excel Reader framework to export a large Excel file.
