Trong bài viết trước, tôi đã hướng dẫn đọc và ghi file excel trong Java sử dụng thư viện Apache POI. Với lượng dữ liệu ít khoảng vài nghìn dòng trở lại, khi xuất excel .xls và .xlsx chúng ta có thể sử dụng các lớp có tiếp đầu ngữ HSSF, XSSF để xuất dữ liệu ra file excel mà không ảnh hưởng nhiều đến hiệu suất của chương trình. Tuy nhiên, với dữ liệu rất lớn khoảng vài chục nghìn dòng trở lên thì thời gian xử lý sẽ tương đối chậm, tốn nhiều bộ nhớ. May mắn là thư viện Apache POI còn thêm một class khác là SXSSF giúp chúng ta giải quyết vấn đề này.
SXSSF (Streaming version of XSSFWorkbook) là một phần mở rộng API của XSSF, được sử dụng khi xuất các file excel lớn và có bộ nhớ heap sapce hạn chế. Do SXSSF mở rộng từ XSSF nên chỉ hỗ trợ xuất file có phần mở rộng là .xlsx (Microsoft Excel 2007 trở về sau).
Trong phần tiếp theo của bài này, tôi sẽ hướng dẫn các bạn đọc và ghi file excel sử dụng lớp có tiếp đầu ngữ là SXSSF. Nếu bạn chưa biết cách xuất dữ liệu ra file excel sử dụng thư viện Apache POI thì hãy xem bài viết Hướng dẫn đọc và ghi file excel trong Java sử dụng thư viện Apache POI.
Để tiện theo dõi, tôi sẽ sử dụng lại ví dụ của bài viết trước, chỉ thay đổi các lớp có tiếp đầu ngữ HSSF, XSSF bằng SXSSF.
Nội dung
Khởi tạo SXSSF
Trước khi đi vào phần ví dụ, chúng ta hãy tìm hiểu cách khởi tạo SXSSF:
// Khởi tạo với giá trị rowAccessWindowSize mặc định 100 SXSSFWorkbook workbook = new SXSSFWorkbook(); // Khởi tạo với giá trị rowAccessWindowSize tùy chỉnh: 50 SXSSFWorkbook workbook = new SXSSFWorkbook(50); // Khởi tạo với giá trị rowAccessWindowSize không giới hạn SXSSFWorkbook workbook = new SXSSFWorkbook(-1);
Lưu ý:
- rowAccessWindowSize : xác định số lượng hàng (row) có thể được truy cập nhiều nhất thông qua SXSSFSheet.getRow. Khi một hàng (row) mới được tạo ra thông qua SXSSFSheet.createRow và nếu tổng số các bản ghi vượt quá giá trị được chỉ định (rowAccessWindowSize), khi đó hàng (row) với giá trị chỉ mục thấp nhất sẽ được làm mới (flushed) và không thể được truy cập thông qua SXSSFSheet.getRow nữa.
- Các thao tác trên SXSSF như: createRow, getRow, autoSizeColumn, … chỉ ảnh hưởng đến các record trong phạm vi rowAccessWindowSize được chỉ định.
- Phương thức autoSizeColumn: chỉ tự động điều chỉnh cỡ trong phạm vi rowAccessWindowSize được chỉ định. Để có thể autoSizeColumn đúng trên tất cả các record, cần đánh dấu theo dõi các cột trong bảng để tự động điều chỉnh định cỡ. Việc xác định độ rộng phù hợp nhất cho một ô rất đắt, điều này có thể ảnh hưởng đến hiệu suất chương trình.
// Đánh dấu theo dõi thay đổi độ rộng của một cột sheet.trackColumnForAutoSizing(columnIndex); // Đánh dấu theo dõi độ rộng của tất cả các cột sheet.trackAllColumnsForAutoSizing();
Ví dụ sử dụng SXSSF
Book.java
package com.gpcoder.apachepoi;
public class Book {
private Integer id;
private String title;
private Integer quantity;
private Double price;
private Double totalMoney;
public Book() {
super();
}
public Book(Integer id, String title, Integer quantity, double price) {
super();
this.id = id;
this.title = title;
this.quantity = quantity;
this.price = price;
}
@Override
public String toString() {
return "Book [id=" + id + ", title=" + title + ", quantity=" + quantity + ", price=" + price + ", totalMoney="
+ totalMoney + "]";
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Integer getQuantity() {
return quantity;
}
public void setQuantity(Integer quantity) {
this.quantity = quantity;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public Double getTotalMoney() {
return totalMoney;
}
public void setTotalMoney(Double totalMoney) {
this.totalMoney = totalMoney;
}
}
WriteExcelUsingSXSSF.java
package com.gpcoder.apachepoi;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
public class WriteExcelUsingSXSSF {
public static final int COLUMN_INDEX_ID = 0;
public static final int COLUMN_INDEX_TITLE = 1;
public static final int COLUMN_INDEX_PRICE = 2;
public static final int COLUMN_INDEX_QUANTITY = 3;
public static final int COLUMN_INDEX_TOTAL = 4;
private static CellStyle cellStyleFormatNumber = null;
public static void main(String[] args) throws IOException {
final List<Book> books = getBooks();
final String excelFilePath = "C:/demo/books_large.xlsx";
writeExcel(books, excelFilePath);
}
public static void writeExcel(List<Book> books, String excelFilePath) throws IOException {
// Create Workbook
SXSSFWorkbook workbook = new SXSSFWorkbook();
// Create sheet
SXSSFSheet sheet = workbook.createSheet("Books"); // Create sheet with sheet name
// register the columns you wish to track and compute the column width
sheet.trackAllColumnsForAutoSizing();
int rowIndex = 0;
// Write header
writeHeader(sheet, rowIndex);
// Write data
rowIndex++;
for (Book book : books) {
// Create row
SXSSFRow row = sheet.createRow(rowIndex);
// Write data on row
writeBook(book, row);
rowIndex++;
}
// Write footer
writeFooter(sheet, rowIndex);
// Auto resize column witdth
int numberOfColumn = 5; // sheet.getRow(0).getPhysicalNumberOfCells();
autosizeColumn(sheet, numberOfColumn);
// Create file excel
createOutputFile(workbook, excelFilePath);
System.out.println("Done!!!");
}
// Create dummy data
private static List<Book> getBooks() {
List<Book> listBook = new ArrayList<>();
Book book;
for (int i = 1; i <= 5; i++) {
book = new Book(i, "Book " + i, i * 2, i * 1000);
listBook.add(book);
}
return listBook;
}
// Write header with format
private static void writeHeader(SXSSFSheet sheet, int rowIndex) {
// create CellStyle
CellStyle cellStyle = createStyleForHeader(sheet);
// Create row
SXSSFRow row = sheet.createRow(rowIndex);
// Create cells
SXSSFCell cell = row.createCell(COLUMN_INDEX_ID);
cell.setCellStyle(cellStyle);
cell.setCellValue("Id");
cell = row.createCell(COLUMN_INDEX_TITLE);
cell.setCellStyle(cellStyle);
cell.setCellValue("Title");
cell = row.createCell(COLUMN_INDEX_PRICE);
cell.setCellStyle(cellStyle);
cell.setCellValue("Price");
cell = row.createCell(COLUMN_INDEX_QUANTITY);
cell.setCellStyle(cellStyle);
cell.setCellValue("Quantity");
cell = row.createCell(COLUMN_INDEX_TOTAL);
cell.setCellStyle(cellStyle);
cell.setCellValue("Total money");
}
// Write data
private static void writeBook(Book book, SXSSFRow row) {
if (cellStyleFormatNumber == null) {
// Format number
short format = (short) BuiltinFormats.getBuiltinFormat("#,##0");
// DataFormat df = workbook.createDataFormat();
// short format = df.getFormat("#,##0");
// Create CellStyle
SXSSFWorkbook workbook = row.getSheet().getWorkbook();
cellStyleFormatNumber = workbook.createCellStyle();
cellStyleFormatNumber.setDataFormat(format);
}
SXSSFCell cell = row.createCell(COLUMN_INDEX_ID);
cell.setCellValue(book.getId());
cell = row.createCell(COLUMN_INDEX_TITLE);
cell.setCellValue(book.getTitle());
cell = row.createCell(COLUMN_INDEX_PRICE);
cell.setCellValue(book.getPrice());
cell.setCellStyle(cellStyleFormatNumber);
cell = row.createCell(COLUMN_INDEX_QUANTITY);
cell.setCellValue(book.getQuantity());
// Create cell formula
// totalMoney = price * quantity
cell = row.createCell(COLUMN_INDEX_TOTAL, CellType.FORMULA);
cell.setCellStyle(cellStyleFormatNumber);
int currentRow = row.getRowNum() + 1;
String columnPrice = CellReference.convertNumToColString(COLUMN_INDEX_PRICE);
String columnQuantity = CellReference.convertNumToColString(COLUMN_INDEX_QUANTITY);
cell.setCellFormula(columnPrice + currentRow + "*" + columnQuantity + currentRow);
}
// Create CellStyle for header
private static CellStyle createStyleForHeader(Sheet sheet) {
// Create font
Font font = sheet.getWorkbook().createFont();
font.setFontName("Times New Roman");
font.setBold(true);
font.setFontHeightInPoints((short) 14); // font size
font.setColor(IndexedColors.WHITE.getIndex()); // text color
// Create CellStyle
CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(font);
cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderBottom(BorderStyle.THIN);
return cellStyle;
}
// Write footer
private static void writeFooter(SXSSFSheet sheet, int rowIndex) {
// Create row
SXSSFRow row = sheet.createRow(rowIndex);
SXSSFCell cell = row.createCell(COLUMN_INDEX_TOTAL, CellType.FORMULA);
cell.setCellFormula("SUM(E2:E6)");
}
// Auto resize column width
private static void autosizeColumn(SXSSFSheet sheet, int lastColumn) {
for (int columnIndex = 0; columnIndex < lastColumn; columnIndex++) {
sheet.autoSizeColumn(columnIndex);
}
}
// Create output file
private static void createOutputFile(SXSSFWorkbook workbook, String excelFilePath) throws IOException {
try (OutputStream os = new FileOutputStream(excelFilePath)) {
workbook.write(os);
}
}
}
Thực thi chương trình trên, một file books_large.xlsx được tạo ra trong thư mục C:/demo như sau:
Như các bạn thấy, không có nhiều sự khác biết về cách sử dụng SXSSF so với XSSF, HSSF.
So sánh hiệu xuất chương trình khi sử dụng SXSSF và XSSF
Để so sánh hiệu suất của chương trình khi sử dụng SXSSF và XSSf, tôi sử dụng lại ví dụ WriteExcelUsingSXSSF ở trên, và ví dụ WriteExcelExample ở bài viết trước.
Trong ví dụ bên dưới, tôi sử dụng lớp StopWatch của thư viện Apache Common Lang để đo thời gian thực thi của chương trình
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.7</version>
</dependency>
Bây giờ, hãy tăng số lượng dữ liệu cần xuất ra khoảng 100.000 dòng (thay đổi trong phương thức getBooks). Xem kết quả thực thi của 2 chương trình như sau:
Sử dụng SXSSF
Thời gian thực thi:
Sử dụng XSSF
Thời gian thực thi:
Như bạn thấy, thời gian thực thi của chương trình khi sử dụng XSSF nhanh hơn so với SXSSF. Nguyên nhân, khi sử dụng autoresize column, SXSSF cũng phải tính toán chiều rộng của tất cả record nên hiệu suất khi sử dụng stream không còn hiệu quả.
Bây giờ hãy thử xóa bỏ các đoạn code autoresize column ở 2 chương trình, vẫn kiểm tra 100.000 record.
WriteExcelUsingSXSSF:
// sheet.trackAllColumnsForAutoSizing(); // int numberOfColumn = sheet.getRow(rowIndex-1).getPhysicalNumberOfCells(); // autosizeColumn(sheet, numberOfColumn);
Kết quả thực thi chương trình trên:
WriteExcelExample:
// int numberOfColumn = sheet.getRow(rowIndex-1).getPhysicalNumberOfCells(); // autosizeColumn(sheet, numberOfColumn);
Kết quả thực thi chương trình trên:
Như bạn thấy thời gian thực thi của SXSSF nhanh hơn 2-3 lần so với XSSF khi không có autoresize column.
Kết luận
Khi cần xuất dữ liệu lớn ra file .xlsx và không có yêu cầu về autoresize column thì nên sử dụng SXSSF để đạt được hiệu suất tốt hơn.
Cám ơn các bạn đã quan tâm và theo dõi bài viết, hẹn gặp lại ở các bài viết tiếp theo.