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 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.
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:
SXSSFWorkbook workbook = new SXSSFWorkbook(); SXSSFWorkbook workbook = new SXSSFWorkbook(50); 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.
sheet.trackColumnForAutoSizing(columnIndex); 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 String excelFilePath = "C:/demo/books_large.xlsx"; writeExcel(books, excelFilePath); } SXSSFWorkbook workbook = new SXSSFWorkbook(); SXSSFSheet sheet = workbook.createSheet("Books"); sheet.trackAllColumnsForAutoSizing(); int rowIndex = 0; writeHeader(sheet, rowIndex); rowIndex++; for (Book book : books) { SXSSFRow row = sheet.createRow(rowIndex); writeBook(book, row); rowIndex++; } writeFooter(sheet, rowIndex); int numberOfColumn = 5; autosizeColumn(sheet, numberOfColumn); createOutputFile(workbook, excelFilePath); System.out.println("Done!!!"); } Book book; for (int i = 1; i <= 5; i++) { book = new Book(i, "Book " + i, i * 2, i * 1000); listBook.add(book); } return listBook; } private static void writeHeader(SXSSFSheet sheet, int rowIndex) { CellStyle cellStyle = createStyleForHeader(sheet); SXSSFRow row = sheet.createRow(rowIndex); 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"); } private static void writeBook(Book book, SXSSFRow row) { if (cellStyleFormatNumber == null) { short format = (short) BuiltinFormats.getBuiltinFormat("#,##0"); 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()); 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); } private static CellStyle createStyleForHeader(Sheet sheet) { Font font = sheet.getWorkbook().createFont(); font.setFontName("Times New Roman"); font.setBold(true); font.setFontHeightInPoints((short) 14); font.setColor(IndexedColors.WHITE.getIndex()); CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setFont(font); cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setBorderBottom(BorderStyle.THIN); return cellStyle; } private static void writeFooter(SXSSFSheet sheet, int rowIndex) { SXSSFRow row = sheet.createRow(rowIndex); SXSSFCell cell = row.createCell(COLUMN_INDEX_TOTAL, CellType.FORMULA); cell.setCellFormula("SUM(E2:E6)"); } private static void autosizeColumn(SXSSFSheet sheet, int lastColumn) { for (int columnIndex = 0; columnIndex < lastColumn; columnIndex++) { sheet.autoSizeColumn(columnIndex); } } 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:
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
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:
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:
Kết quả thực thi chương trình trên:
Kết quả thực thi chương trình trê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.