Gần đây mình phải cài đặt chức năng tự động tạo báo cáo dạng Microsoft Excel file. Việc tạo báo cáo dưới dạng file Excel này là một chức năng rất hay được yêu cầu khi phát triển phần mềm. Bên cạnh đó, nhiều phần mềm cũng thường xuyên phải nhận input data từ file Excel.

1. Tổng quan

1.1. Apache POI là gì?

Apache POI là một API nổi tiếng cho phép ta làm việc với các MS Office files bằng ngôn ngữ Java. Nó là thư viện mã nguồn mở được phát triển bởi Apache Software Foundation.
POI là viết tắt của "Poor Obfuscation Implementation" với ám chỉ hài hước cho việc Microsoft đã tạo ra các định dạng phức tạp một cách không cần thiết. Các subcomponent của POI cũng được viết tắt từ những cái tên tương tự thế. Tuy nhiên, những giải thích viết tắt này đã được gỡ bỏ khỏi trang chủ của Apache POI.

1.2. Các Component của Apache POI

Apache POI chứa các class và method để làm việc với định dạng OLE2 của Microsoft. Sau đây là 1 số component của Apache POI mà ta hay sử dụng

  • POIFS for OLE 2 Documents dùng để đọc và viết dưới định dạng OLE2 của Microsoft. Là khốí cơ bản cho các POI component khác. POIFS là viết tắt của "Poor Obfuscation Implementation File System".
  • HSSF and XSSF for Excel Documents
    • HSSF (Horrible SpreadSheet Format) dùng để đọc và ghi file Microsoft Excel 97(-2007), với định dạng file là BIFF8, có file extension là XLS
  • XSSF (XML Spreadsheet Format) dùng cho Microsoft Excel XML (2007+) với định dạng file OOXML, có file extension là XLSX
  • Package SS cung cấp common hỗ trợ cả 2 định dạng trên
  • HWPF and XWPF for Word Documents
  • HWPF (Horrible Word Processor Format) hỗ trợ cho Microsoft Word 97 (-2003) với extention DOC
  • Còn XWPF (XML Word Processor Format) hỗ trợ cho Microsoft Word (2007+) với extention DOCX
  • HSLF and XSLF for PowerPoint Documents
  • Tương tự, HSLF (Horrible Slide Layout Format) hỗ trợ Microsoft PowerPoint 97 (-2003) với extention PPT
  • XSLF (XML Slide Layout Format) hỗ trợ Microsoft PowerPoint (2007+) với extention PPTX
  • Package SL cung cấp common hỗ trợ cả 2 định dạng trên

Ngoài ra, còn nhiều component khác với những cái tên thú vị như

  • HPSF (Horrible Property Set Format) cho OLE 2 Document Properties
  • HDGF (Horrible Diagram Format) và XDGF (XML Diagram Format) for Visio Documents
  • HPBF (Horrible Publisher Format) cho Publisher Documents
  • HSMF (Horrible Stupid Mail Format) cho Outlook Messages
  • DDF (Dreadful Drawing Format)
  • HMEF (Horrible Mail Encoding Format) cho TNEF Outlook Attachments
  • ...

2. Java Excel APIs

Trong bài viết này, mình chỉ tập trung vào phần Excel API của Apache POI.

Trước hết ta cần nắm rõ cấu trúc của 1 file excel. Nó bao gồm các khái niệm cơ bản sau với các class cài đặt tương ứng

  • Workbook là cách gọi của file excel
  • HSSFWorkbook
  • XSSFWorkbook
  • Spreadsheet (Sheet): Một workbook thì có nhiều sheet, các sheet có tên không trùng nhau
  • HSSFSheet
  • XSSFSheet
  • Row: mỗi sheet có nhiều row
  • HSSFRow
  • XSSFRow
  • Cell sẽ chứa các dữ liệu mà chúng ta cần đọc hoặc ghi
  • HSSFCell
  • XSSFCell
  • HSSFCellStyle
  • XSSFCellStyle
  • HSSFColor
  • XSSFColor
  • HSSFFont
  • XSSFFont
  • ...

Khi sử dụng Maven có thể khai báo đơn giản như sau với phiên bản mới nhất là 3.16

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.16</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.16</version>
</dependency>

2.1 Tạo 1 file excel đơn giản

Chỉ với việc sử dụng vài hàm đơn giản sau, ta có thể dễ dàng tạo 1 file excel

# Tạo workbook
XSSFWorkbook workbook = new XSSFWorkbook();

# Tạo spreadsheet
XSSFSheet spreadsheet = workbook.createSheet("Sheet Name");

# Tạo row trong spreadsheet
XSSFRow row = spreadsheet.createRow(rowId);

# Tạo cell trong row
Cell cell = row.createCell(cellId);

2.2 Trình bày file excel đẹp hơn với CellStyle

Row, Column

Ta có thể set các thuộc tính về chiều cao của row, độ rộng của column 1 cách đơn giản như sau

XSSFRow row = spreadsheet.createRow(rowId);
row.setHeight(800);

spreadsheet.setColumnWidth(columnID, 8000);
CellStyle (+ Color)
  • Ta có thể căn chỉnh alignment cho cell
# Ví dụ với Center alignment
# Ta tạo style
XSSFCellStyle centerStyle = workbook.createCellStyle();
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

# Set style cho cell
cell.setCellStyle(centerStyle);

Có thể tham khảo thêm các enum HorizontalAlignment và VerticalAlignment để biết thêm các kiểu căn chỉnh khác.

  • Hoặc setting border

Ta có thể setting border cho các đường trên, dưới, phải, trái của cell với nhiều thuộc tính khác nhau như kiểu đường, màu sắc, ...

XSSFCellStyle style = workbook.createCellStyle();

# Set bottom
style.setBorderBottom(BorderStyle.DASH_DOT);
style.setBottomBorderColor(IndexedColors.BLUE.getIndex());

# Set left
style.setBorderLeft(BorderStyle.DOUBLE);
style.setLeftBorderColor(IndexedColors.GREEN.getIndex());

# Set right
style.setBorderRight(BorderStyle.HAIR);
style.setRightBorderColor(IndexedColors.RED.getIndex());

# Set top
style.setBorderTop(BorderStyle.THICK);
style.setTopBorderColor(IndexedColors.CORAL.getIndex());

# Set style cho cell
cell.setCellStyle(style);

Tham khảo thêm các enum BorderStyle và IndexedColors để biết thêm nhiều thuộc tính khác.

  • Ta cũng có thể set background color cho cell
XSSFCellStyle style = workbook.createCellStyle();

# Set màu
style.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.getIndex());

# Set cách fill màu vào cell
style.setFillPattern(FillPatternType.LESS_DOTS);
style.setAlignment(XSSFCellStyle.ALIGN_FILL);

# Set style cho cell
cell.setCellStyle(style);
CellStyle (+ Font + Color)

Ta cũng có thể tùy chỉnh kiểu font cho các ô như sau

# Tạo font
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints(30);
font.setFontName("IMPACT");
font.setItalic(true);

# Ta cũng có thể set màu cho font
font.setColor(IndexedColors.BRIGHT_GREEN.getIndex());

# Set font cho style
XSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);

# Set style cho cell
cell.setCellStyle(style);

2.3 Công thức Excel

Một phần không thể thiếu của excel là các công thức tính toán mạnh mẽ. Apache POI cũng dễ dàng tạo các công thức cho cell. Ví dụ như sau

cell = row.createCell(cellId);

# Tạo công thức SUM
cell.setCellType(CellType.FORMULA);
cell.setCellFormula("SUM(C2:C3)" );

2.4 Ví dụ

Ta sẽ tạo 1 file excel có 2 sheet có nội dung như sau

  • Sheet1: "Thông tin nhân viên"
MSNV Họ tên Số điện thoại
1 Nguyễn Văn X 098000001
2 Nguyễn Thị Y 098000003
3 Lê Văn X 098000003
  • Sheet2: "Ngày công tháng 5"
MSNV Ngày công
1 20
2 22
3 23

Code ví dụ như sau:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

public class TestPoi {

    private static SimpleDateFormat YYYYMMDDHHMMSS = new SimpleDateFormat("yyyyMMddHHmmss");
    private static String LOCAL_EXCEL_ROOT_PATH = "C:/Users/Minh NC/2017q2";
    private static String LOCAL_EXCEL_FILE_PATH = LOCAL_EXCEL_ROOT_PATH + "/blog_test_" + YYYYMMDDHHMMSS.format(new Date()) + ".xlsx";

    public static void main(String[] args) throws IOException {
        // Sheet1 data
        Map<Integer, Object[]> sheet1Data = new TreeMap<Integer, Object[]>();
        sheet1Data.put(1, new Object[]{"MSNV", "Họ tên", "Số điện thoại"});
        sheet1Data.put(2, new Object[]{"1", "Nguyễn Văn X", "098000001"});
        sheet1Data.put(3, new Object[]{"2", "Nguyễn Thị Y", "098000002"});
        sheet1Data.put(4, new Object[]{"3", "Lê Văn X", "098000003"});

        // Sheet2 data
        Map<Integer, Object[]> sheet2Data = new TreeMap<Integer, Object[]>();
        sheet2Data.put(1, new Object[]{"MSNV", "Ngày công"});
        sheet2Data.put(2, new Object[]{"1", "20"});
        sheet2Data.put(3, new Object[]{"2", "22"});
        sheet2Data.put(4, new Object[]{"3", "23"});

        // Tạo 2 sheet, và set width cho column
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet spreadsheet1 = workbook.createSheet("Thông tin nhân viên");
        spreadsheet1.setColumnWidth(0, 6000);
        spreadsheet1.setColumnWidth(1, 20000);
        spreadsheet1.setColumnWidth(2, 12000);

        XSSFSheet spreadsheet2 = workbook.createSheet("Ngày công tháng 5");
        spreadsheet2.setColumnWidth(0, 6000);
        spreadsheet2.setColumnWidth(1, 12000);

        // Border cho tất cả các cell bình thường
        XSSFCellStyle border = workbook.createCellStyle();
        border.setBorderTop(BorderStyle.THIN);
        border.setBorderBottom(BorderStyle.THIN);
        border.setBorderLeft(BorderStyle.THIN);
        border.setBorderRight(BorderStyle.THIN);

        // Tạo style header: Alignment Center, Bold Font, Font color, background, border
        XSSFCellStyle headerStyle = workbook.createCellStyle();

        /* Multiple Styles to Excel Cell POI */
        headerStyle.cloneStyleFrom(border);

        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        XSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setColor(IndexedColors.BLUE.getIndex());
        headerStyle.setFont(font);

        headerStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.THIN_BACKWARD_DIAG);

        // Căn giữa cho column MSNV
        XSSFCellStyle center = workbook.createCellStyle();

        /* Multiple Styles to Excel Cell POI */
        center.cloneStyleFrom(border);

        center.setAlignment(HorizontalAlignment.CENTER);
        center.setVerticalAlignment(VerticalAlignment.CENTER);

        // Ghi data vào các sheet
        writeToSheet(spreadsheet1, sheet1Data, headerStyle, border, center, 1);
        writeToSheet(spreadsheet2, sheet2Data, headerStyle, border, center, 2);

        // Tạo công thức tổng ngày công với sheet 2
        XSSFRow sumRow = spreadsheet2.createRow(5);
        Cell sumLabelCell = sumRow.createCell(0);

        XSSFCellStyle right = workbook.createCellStyle();
        right.setAlignment(HorizontalAlignment.RIGHT);
        right.setVerticalAlignment(VerticalAlignment.CENTER);
        sumLabelCell.setCellStyle(right);

        sumLabelCell.setCellValue("Tổng");

        Cell sumCell = sumRow.createCell(1);
        sumCell.setCellType(CellType.FORMULA);
        sumCell.setCellFormula("SUM(B2:B4)");

        // Ghi ra local file
        File rootDir = new File(LOCAL_EXCEL_ROOT_PATH);
        rootDir.mkdirs();
        FileOutputStream out = new FileOutputStream(new File(LOCAL_EXCEL_FILE_PATH));
        workbook.write(out);
        out.close();
    }

    private static void writeToSheet(XSSFSheet spreadsheet, Map<Integer, Object[]> sheetData,
                                     XSSFCellStyle headerStyle, XSSFCellStyle border, XSSFCellStyle center, Integer type) {

        Set<Integer> keyIds = sheetData.keySet();
        XSSFRow row;
        int rowId = 0;

        for (Integer key : keyIds) {
            // Tạo row
            row = spreadsheet.createRow(rowId++);

            Object[] objectArr = sheetData.get(key);
            int cellId = 0;

            for (Object obj : objectArr) {
                // Tạo cell
                Cell cell = row.createCell(cellId++);

                // Set border cho tất cả các cell
                cell.setCellStyle(border);

                if (rowId == 1) {
                    // Với header, set value dạng String
                    cell.setCellValue((String) obj);
                    // Set style cho header
                    cell.setCellStyle(headerStyle);
                } else {
                    // Các row khác
                    if (cellId == 1) {
                        // Với cột MSNV, set value dạng number
                        cell.setCellValue(Integer.valueOf((String) obj));
                        // Set center style
                        cell.setCellStyle(center);
                    } else {
                        // Các cột khác
                        if (type == 2 && cellId == 2) {
                            // Trường hợp Ngày công thì dạng number
                            cell.setCellValue(Integer.valueOf((String) obj));
                        } else {
                            // Bình thường thì dạng String
                            cell.setCellValue((String) obj);
                        }
                    }
                }
            }
        }
    }
}

Kết quả

Sheet 1: Thông tin nhân viên

*Sheet 1: Thông tin nhân viên*

Ngày công tháng 5

*Sheet 2: Ngày công tháng 5*

Ở ví dụ này thì có 2 điểm đáng chú ý là

  • Apache POI không hỗ trợ set nhiều style cho 1 cell. Khi ta set nhiều style khác nhau cho 1 cell thì cái được set cuối cùng sẽ ghi đè lên tất cả style trước đó.
  • Giải pháp cho việc muốn tạo set nhiều style cho 1 cell là ta có thể tạo 1 style base, rồi các style khác sẽ clone từ style base đó bằng cách sử dụng hàm cloneStyleFrom(CellStyle source) như ở ví dụ trên
  • Việc set background cho cell thì các pattern hiện tại chưa được đẹp lắm. Mình phải chọn màu và cách fill màu, và các cách fill màu hiện tại còn ít và đơn giản.

3. Kết luận

Apache POI là 1 thư viện mạnh mẽ giúp ta làm việc hiệu quả với MS Office.

Trên đây chỉ là 1 ứng dụng nhỏ của Apache POI, nhằm giúp cho việc tạo các bản báo cáo Excel đã trở nên dễ dàng và nhanh chóng.

Tham khảo