Đọc Ghi file excel trong Java sử dụng Apache POI

Apache POI là gì?
Tổng quan về Apache POI
Tổng quan Apache POI Excel
Thư viện Apache POI
Tạo và ghi file Excel
Đọc file xsl và xslx
Cập nhập file Excel có sẵn
Công thức và đánh giá
Áp dụng kiểu dáng (Style)

1. Apache POI là gì?

Apache POI là một thư viện mã nguồn mở Java, được cung cấp bởi Apache, nó là một thư viện đầy sức mạnh giúp bạn làm việc với các tài liệu của Microsoft như Word, Excel, Power point, Visio,...

POI là viết tắt của "Poor Obfuscation Implementation". Các định dạng file của Microsoft được giấu kín. Những kỹ sư của Apache phải cố gắng để tìm hiểu nó, và họ thấy rằng Microsoft đã tạo ra các định dạng phức tạp một cách không cần thiết. Và cái tên thư viện bắt nguồn từ sự hài ước.

Poor Obfuscation Implementation: Sự thực hiện cái nghèo nàn ngu muội. (Tạm dịch là vậy).

Trong tài liệu này tôi hướng dẫn các bạn sử dụng Apache POI để làm việc với Excel.

2. Tổng quan về Apache POI

Apache POI hỗ trợ bạn làm việc với các định dạng của Microsoft, các class của nó thường có tiếp đầu ngữ HSSF, XSSF, HPSF, ... Nhìn vào tiếp đầu ngữ của một class bạn có thể biết được class đó hỗ trợ loại định dạng nào.

Chẳng hạn để làm việc với các định dạng Excel (XLS) bạn cần các class:

HSSFWorkbook
HSSFSheet
HSSFCellStyle
HSSFDataFormat
HSSFFont
...

	Tiếp đầu ngữ	Mô tả
1	HSSF (Horrible SpreadSheet Format)	Đọc và ghi file định dạng Microsoft Excel (XLS).
2	XSSF (XML SpreadSheet Format)	Đọc và ghi định dạng file Open Office XML (XLSX).
3	HPSF (Horrible Property Set Format)	Đọc thông tin tóm tắt về tài liệu từ các file Microsoft Office.
4	HWPF (Horrible Word Processor Format)	Mục đích đọc và ghi các file định dạng Microsoft Word 97 (DOC).
5	HSLF (Horrible Slide Layout Format)	Một thực hiện thuần Java cho các file Microsoft PowerPoint.
6	HDGF (Horrible DiaGram Format)	Các thực hiện (implementation) thuần Java khởi đầu cho các file nhị phân Microsoft Visio.
7	HPBF (Horrible PuBlisher Format)	Một thực hiện thuần Java cho các file Microsoft Publisher.
8	HSMF (Horrible Stupid Mail Format)	Một thực hiện thuần Java cho các file Microsoft Outlook MSG
9	DDF (Dreadful Drawing Format)	Một package cho giải mã các định dạng Microsoft Office Drawing.

3. Tổng quan Apache POI Excel

Hình ảnh dưới đây minh họa cấu trúc của một tài liệu excel.

Apache POI cung cấp cho bạn các interface Workbook, Sheet, Row, Cell,... và các class thể hiện (implementation) tương ứng là HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell,...

4. Thư viện Apache POI

Nếu project của bạn sử dụng Maven, bạn chỉ cần khai báo thư viện một cách đơn giản trong pom.xml:

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

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

Nếu bạn không sử dụng Maven bạn có thể download thư viện Apache POI tại:

http://poi.apache.org/download.html

Download về và giải nén, để làm việc với Excel bạn cần ít nhất 3 file jar:

poi-**.jar
lib/commons-codec-**.jar
lib/commons-collections4-**.jar

Trong tài liệu này, tôi tạo một Project Maven đơn giản có tên ApachePOIExcel

Group ID: org.o7planning
Artifact ID: ApachePOIExcel

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
                        http://maven.apache.org/xsd/maven-4.0.0.xsd">
                      
    <modelVersion>4.0.0</modelVersion>
    <groupId>org.o7planning</groupId>
    <artifactId>ApachePOIExcel</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <dependencies>
  
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

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


</project>

5. Tạo và ghi file Excel

Microsoft Office các phiên bản trước đây (97-2003) các file excel có định dạng XLS và các phiên bản mới thường sử dụng định dạng XSLX. Để thao tác với các file XSL bạn cần sử dụng các class có tiếp đầu ngữ HSSF. Còn đối với các file định dạng XSLX cần sử dụng các class có tiếp đầu ngữ XSSF.

Dưới đây là một ví dụ đơn giản sử dụng POI để tạo ra một file excel. Bạn có thể kết hợp với việc sử dụng kiểu dáng (Style) trên các ô (Cell) để tạo ra tài liệu Excel đẹp hơn. POI Style được đề cập chi tiết hơn ở phía cuối của tài liệu.

CreateExcelDemo.java

package org.o7planning.apachepoiexcel.demo;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.o7planning.apachepoiexcel.model.Employee;
import org.o7planning.apachepoiexcel.model.EmployeeDAO;

public class CreateExcelDemo {

    private static HSSFCellStyle createStyleForTitle(HSSFWorkbook workbook) {
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);
        return style;
    }

    public static void main(String[] args) throws IOException {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Employees sheet");

        List<Employee> list = EmployeeDAO.listEmployees();

        int rownum = 0;
        Cell cell;
        Row row;
        //
        HSSFCellStyle style = createStyleForTitle(workbook);

        row = sheet.createRow(rownum);

        // EmpNo
        cell = row.createCell(0, CellType.STRING);
        cell.setCellValue("EmpNo");
        cell.setCellStyle(style);
        // EmpName
        cell = row.createCell(1, CellType.STRING);
        cell.setCellValue("EmpNo");
        cell.setCellStyle(style);
        // Salary
        cell = row.createCell(2, CellType.STRING);
        cell.setCellValue("Salary");
        cell.setCellStyle(style);
        // Grade
        cell = row.createCell(3, CellType.STRING);
        cell.setCellValue("Grade");
        cell.setCellStyle(style);
        // Bonus
        cell = row.createCell(4, CellType.STRING);
        cell.setCellValue("Bonus");
        cell.setCellStyle(style);

        // Data
        for (Employee emp : list) {
            rownum++;
            row = sheet.createRow(rownum);

            // EmpNo (A)
            cell = row.createCell(0, CellType.STRING);
            cell.setCellValue(emp.getEmpNo());
            // EmpName (B)
            cell = row.createCell(1, CellType.STRING);
            cell.setCellValue(emp.getEmpName());
            // Salary (C)
            cell = row.createCell(2, CellType.NUMERIC);
            cell.setCellValue(emp.getSalary());
            // Grade (D)
            cell = row.createCell(3, CellType.NUMERIC);
            cell.setCellValue(emp.getGrade());
            // Bonus (E)
            String formula = "0.1*C" + (rownum + 1) + "*D" + (rownum + 1);
            cell = row.createCell(4, CellType.FORMULA);
            cell.setCellFormula(formula);
        }
        File file = new File("C:/demo/employee.xls");
        file.getParentFile().mkdirs();

        FileOutputStream outFile = new FileOutputStream(file);
        workbook.write(outFile);
        System.out.println("Created file: " + file.getAbsolutePath());

    }

}

Employee.java

package org.o7planning.apachepoiexcel.model;

public class Employee {

    private String empNo;
    private String empName;

    private Double salary;
    private int grade;
    private Double bonus;

    public Employee(String empNo, String empName,//
            Double salary, int grade, Double bonus) {
        this.empNo = empNo;
        this.empName = empName;
        this.salary = salary;
        this.grade = grade;
        this.bonus = bonus;
    }

    public String getEmpNo() {
        return empNo;
    }

    public void setEmpNo(String empNo) {
        this.empNo = empNo;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public int getGrade() {
        return grade;
    }

    public void setGrade(int grade) {
        this.grade = grade;
    }

    public Double getBonus() {
        return bonus;
    }

    public void setBonus(Double bonus) {
        this.bonus = bonus;
    }

}

EmployeeDAO.java

package org.o7planning.apachepoiexcel.model;

import java.util.ArrayList;
import java.util.List;

public class EmployeeDAO {

    public static List<Employee> listEmployees() {
        List<Employee> list = new ArrayList<Employee>();

        Employee e1 = new Employee("E01", "Tom", 200.0, 1, null);
        Employee e2 = new Employee("E02", "Jerry", 100.2, 2, null);
        Employee e3 = new Employee("E03", "Donald", 150.0, 2, null);
        list.add(e1);
        list.add(e2);
        list.add(e3);
        return list;
    }

}

Chạy ví dụ:

6. Đọc file xsl và xslx

Ví dụ dưới đây đọc một file excel đơn giản và ghi thông tin ra màn hình Console. File excel sử dụng để đọc là file excel đã được tạo ra ở ví dụ trên.

Chú ý: Trong tài liệu này tôi đang sử dụng Apache POI 3.15, API có nhiều thay đổi so với phiên bản cũ hơn. Có nhiều phương thức sẽ bị loại bỏ trong phiên bản tương lai (Apache POI 4.x). POI đang hướng tới sử dụng Enum thay thế cho các hằng số trong API của nó.

ReadExcelDemo.java

package org.o7planning.apachepoiexcel.demo;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;

public class ReadExcelDemo {

   public static void main(String[] args) throws IOException {
 
       // Đọc một file XSL.
       FileInputStream inputStream = new FileInputStream(new File("C:/demo/employee.xls"));
 
       // Đối tượng workbook cho file XSL.
       HSSFWorkbook workbook = new HSSFWorkbook(inputStream);

 
       // Lấy ra sheet đầu tiên từ workbook
       HSSFSheet sheet = workbook.getSheetAt(0);

 
       // Lấy ra Iterator cho tất cả các dòng của sheet hiện tại.
       Iterator<Row> rowIterator = sheet.iterator();

       while (rowIterator.hasNext()) {
           Row row = rowIterator.next();
    
           // Lấy Iterator cho tất cả các cell của dòng hiện tại.
           Iterator<Cell> cellIterator = row.cellIterator();

           while (cellIterator.hasNext()) {
               Cell cell = cellIterator.next();
 
               // Đổi thành getCellType() nếu sử dụng POI 4.x
               CellType cellType = cell.getCellTypeEnum();

               switch (cellType) {
               case _NONE:
                   System.out.print("");
                   System.out.print("\t");
                   break;
               case BOOLEAN:
                   System.out.print(cell.getBooleanCellValue());
                   System.out.print("\t");
                   break;
               case BLANK:
                   System.out.print("");
                   System.out.print("\t");
                   break;
               case FORMULA:
      
                   // Công thức
                   System.out.print(cell.getCellFormula());
                   System.out.print("\t");
                   
                   FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        
                   // In ra giá trị từ công thức
                   System.out.print(evaluator.evaluate(cell).getNumberValue());
                   break;
               case NUMERIC:
                   System.out.print(cell.getNumericCellValue());
                   System.out.print("\t");
                   break;
               case STRING:
                   System.out.print(cell.getStringCellValue());
                   System.out.print("\t");
                   break;
               case ERROR:
                   System.out.print("!");
                   System.out.print("\t");
                   break;
               }

           }
           System.out.println("");
       }
   }

}

Chạy ví dụ:

7. Cập nhập file Excel có sẵn

Trong ví dụ này, tôi đọc file excel employee.xls và cập nhập các giá trị cho cột Salary tăng lên 2 lần.

UpdateExcelDemo.java

package org.o7planning.apachepoiexcel.demo;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class UpdateExcelDemo {

   public static void main(String[] args) throws IOException {

       File file = new File("C:/demo/employee.xls");
 
       // Đọc một file XSL.
       FileInputStream inputStream = new FileInputStream(file);
 
       // Đối tượng workbook cho file XSL.
       HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
 
       // Lấy ra sheet đầu tiên từ workbook
       HSSFSheet sheet = workbook.getSheetAt(0);

       HSSFCell cell = sheet.getRow(1).getCell(2);
       cell.setCellValue(cell.getNumericCellValue() * 2);
     
       cell = sheet.getRow(2).getCell(2);
       cell.setCellValue(cell.getNumericCellValue() * 2);
     
       cell = sheet.getRow(3).getCell(2);
       cell.setCellValue(cell.getNumericCellValue() * 2);

       inputStream.close();

       // Ghi file
       FileOutputStream out = new FileOutputStream(file);
       workbook.write(out);
       out.close();

   }

}

Kết quả sau khi cập nhập:

8. Công thức và đánh giá

Nếu bạn có kiến thức về Excel, sẽ không khó để bạn thiết lập một công thức. Với Apache POI bạn có thể tạo một Cell có kiểu CellType.FORMULA, giá trị của nó được tính dựa trên một công thức.

SUM

Ví dụ: Tính tổng các ô trên cùng cột "C" từ dòng thứ 2 tới dòng thứ 4:

// Tạo cell có kiểu FORMULA
cell = row.createCell(rowIndex, CellType.FORMULA);

// Sét công thức.
cell.setCellFormula("SUM(C2:C4)");

Công thức từ các ô riêng lẻ:

cell = row.createCell(rowIndex, CellType.FORMULA);
cell.setCellFormula("0.1*C2*D3");

Với một cell có kiểu FORMULA bạn có thể in ra công thức của nó và sử dụng FormulaEvaluator để tính toán giá trị của ô cho bởi công thức đó.

// Formula
String formula = cell.getCellFormula();

FormulaEvaluator evaluator
      = workbook.getCreationHelper().createFormulaEvaluator();

// CellValue
CellValue cellValue = evaluator.evaluate(cell);

double value = cellValue.getNumberValue();
String value = cellValue.getStringValue();
boolean value = cellValue.getBooleanValue();
// ...

9. Áp dụng kiểu dáng (Style)

Ví dụ:

StyleDemo.java

package org.o7planning.apachepoiexcel.demo;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.IndexedColors;

public class StyleDemo {

    private static HSSFCellStyle getSampleStyle(HSSFWorkbook workbook) {
        // Font
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setItalic(true);

        // Font Height
        font.setFontHeightInPoints((short) 18);

        // Font Color
        font.setColor(IndexedColors.RED.index);

        // Style
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);

        return style;
    }

    public static void main(String[] args) throws IOException {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Style Demo");

        HSSFRow row = sheet.createRow(0);

        //
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("String with Style");

        HSSFCellStyle style = getSampleStyle(workbook);
        cell.setCellStyle(style);

        File file = new File("C:/demo/style.xls");
        file.getParentFile().mkdirs();

        FileOutputStream outFile = new FileOutputStream(file);
        workbook.write(outFile);
        System.out.println("Created file: " + file.getAbsolutePath());

    }

}