Topic 56:  Đọc và ghi file excel trong Java sử dụng Apache POI

Chào các bạn đã đến với chủ đề tiếp theo của mình. Hôm nay, mình sẽ tiếp tục tìm hiểu về cách đọc và ghi file excel trong Java bằng cách sử dụng Apache POI. Ở bài này, mình sẽ đi qua những nội dung như sau:  

1. Giới thiệu về Apache POI?

Apache POI là một thư viện mã nguồn mở Java, được cung cấp bởi Apache. Thư viện này cung cấp các API (phương thức) làm việc với các tài liệu của Microsoft như Word, Excel, Power point, Visio,…

Các class của Apache POI 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.

No.	Tiếp đầu ngữ	Mô tả
1	HSSF (Horrible SpreadSheet Format)	Đọc và ghi file định dạng Microsoft Excel (XLS – định dạng hỗ trợ của Excel 2003).
2	XSSF (XML SpreadSheet Format)	Đọc và ghi định dạng file Open Office XML (XLSX – định dạng hỗ trợ của Excel 2007 trở lên).
3	SXSSF (Streaming version of XSSFWorkbook)	SXSSF 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ế.
4	HPSF (Horrible Property Set Format)	Đọc thông tin tóm tắt về tài liệu từ các file Microsoft Office.
5	HWPF (Horrible Word Processor Format)	Mục đích đọc và ghi các file định dạng Microsoft Word 97 (DOC).
6	HSLF (Horrible Slide Layout Format)	Một thực hiện thuần Java cho các file Microsoft PowerPoint.
7	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.
8	HPBF (Horrible PuBlisher Format)	Một thực hiện thuần Java cho các file Microsoft Publisher.
9	HSMF (Horrible Stupid Mail Format)	Một thực hiện thuần Java cho các file Microsoft Outlook MSG
10	DDF (Dreadful Drawing Format)	Một package cho giải mã các định dạng Microsoft Office Drawing.

2. Tổng quan Apache POI Excel

Microsoft Excel hiện tại có 2 phần mở rộng:

• .xls: tương ứng với phiên bản Microsoft Excel 2003 trở về trước. Định dạng này được Apache POI hỗ trợ bởi các lớp java với tiếp đầu ngữ là HSSF.
• .xlsx: tương ứng với phiên bản Microsoft Excel 2007 trở về sau. Định dạng này được Apache POI hỗ trợ bởi các lớp java với tiếp đầu ngữ là XSSF, SXSSF.

Một số khái niệm cơ bản của Apache API:

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:

• Workbook: đại diện cho một file Excel. Nó được triển khai dưới hai class là: HSSFWorkbook và XSSFWorkbook tương ứng cho định dạng .xls và .xlsx .
• Sheet: đại diện cho một bảng tính Excel (một file Excel có thể có nhiều Sheet). Nó có 2 class là HSSFSheet và XSSFSheet.
• Row: đại diện cho một hàng trong một bảng tính (Sheet). Nó có 2 class là HSSFRow và XSSFRow.
• Cell: đại diện cho một ô trong một hàng (Row). Tương tự nó cũng có 2 class là HSSFCell and XSSFCell.

3. Khai báo 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. Các bạn lên trên maven repository để get latest version về nhé.

<!-- Excel 2003 (.xls) -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
 
<!-- Excel 2007 (.xlsx) -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<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 và import vào project của bạn. Link download: http://poi.apache.org/download.html

4. Một số thao tác cơ bản

4.1. Workbook

Tạo Workbook

Workbook wb2003 = new HSSFWorkbook();
Workbook wb2007 = new XSSFWorkbook();

Lấy đối tượng Workbook từ file excel có sẵn

String fileExcel2003 = "path/books.xls";
Workbook wb2003 = new HSSFWorkbook(fileExcel2003);
 
String fileExcel2007 = "path/books.xlsx";
Workbook wb2007 = new XSSFWorkbook(fileExcel2007);

4.2. Sheet

Tạo Sheet mới

// Tạo sheet với tên mặc định Sheet0
Sheet sheet = wb2003.createSheet(); 
Sheet sheet = wb2007.createSheet(); 
 
// Tạo sheet với tên MySheetName
String safeName = WorkbookUtil.createSafeSheetName("MySheetName");
Sheet sheet = wb2003.createSheet(safeName);
Sheet sheet = wb2007.createSheet(safeName);

Lớp WorkbookUtil.createSafeSheetName() được sử dụng để loại bỏ ký tự không hợp lệ trong tên sheet. Phương thức tiện ích này sẽ thay thế các ký tự không hợp lệ bằng ký tự khoảng trắng (space).

Lấy đối tượng Sheet từ file sẵn có 

// Lấy sheet theo index
// Lấy sheet thứ nhất (index = 0)
Sheet sheet = wb2003.getSheetAt(0);
Sheet sheet = wb2007.getSheetAt(0);
 
// Lấy sheet theo tên Sheet
Sheet sheet = wb2003.getSheet("MySheetName");
Sheet sheet = wb2007.getSheet("MySheetName");

Lưu ý:

• Phương thức getSheetAt() sẽ throw IllegalArgumentException nếu index < 0 hoặc index >= totalSheet.
• Phương thức getSheet() sẽ trả về NULL nếu không tồn tại tên sheet được chỉ định.

4.3. Row

Tạo Row

Row row = sheet.createRow(rowIndex);

Lấy đối tượng Row từ Sheet sẵn có

Row row = sheet.getRow(rowIndex);

Lưu ý:

• Phương thức getRow() sẽ trả về giá trị NULL nếu Row tại rowIndex chưa được tạo.

4.4. Cell

Tạo Cell

Cell cell = row.createCell(columnIndex);

Lấy đối tượng Cell từ Row sẵn có 

Cell cell = row.getCell(columnIndex);

Tạo Cell công thức

cell = row.createCell(columnIndex, CellType.FORMULA);

Lưu ý:

• Phương thức getCell() sẽ trả về giá trị NULL nếu Column tại columnIndex chưa được tạo.

Gán giá trị cho Cell

cell.setCellValue(stringValue);
cell.setCellValue(numberValue);
 
// Gán giá trị là một công thức Excel (lưu ý: không có dấu = trong công thức)
cell.setCellFormula("SUM(E2:E6)");
 
// ...

Lấy giá trị của Cell

boolean value = cell.getBooleanCellValue();
double value = cell.getNumericCellValue();
String value = cell.getStringCellValue();
 
// Get Formula value
Workbook workbook = cell.getSheet().getWorkbook();
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
double value = evaluator.evaluate(cell).getNumberValue();
 
// ...

Tự động điều chỉnh độ rộng của cột vừa đủ để hiển thị đầy đủ nội dung

for (int columnIndex = 0; columnIndex < lastColumn; columnIndex++) {
    sheet.autoSizeColumn(columnIndex);
}

Lưu ý:

• Phương thức autoSizeColumn() xử lý khá chậm, do đó chỉ sử dụng sau khi đã hoàn thành xử lý đỗ dữ liệu hoặc chỉ sử dụng khi cần thiết.

4.5. CellStyle

Lớp CellStyle được sử dụng để format các dữ liệu xuất ra của file excel chẳng hạn: font chữ, màu sắc, border, format chữ số thập phân, …

// Tạo định dạng: font Times New Roman, in đậm, font-size 14, chữ màu trắng
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
 
// Tạo cell style áp dụng font ở trên
// Sử dụng màu nền xanh (Blue), định dạng border dưới 
CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(font);
cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderBottom(BorderStyle.THIN);
 
// Áp dụng định dạng CellStyle cho một Cell
cell.setCellStyle(cellStyle);

Ví dụ định dạng format hiển thị số:

// Tạo format số
short format = (short)BuiltinFormats.getBuiltinFormat("#,##0");
 
// hoặc 
DataFormat df = workbook.createDataFormat();
short format = df.getFormat("#,##0");
 
// Tạo CellStyle với format số
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(format);
 
// Áp dụng định dạng CellStyle cho một Cell
cell.setCellStyle(cellStyle);

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 hơn (2007 trở về sau) thường sử dụng định dạng .xlsx. Để thao tác với các file .xls 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 .xlsx 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 bài viết.

CreateExcelDemo.java

package ExcelFile;

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;

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:/Data File/employee.xls");
        file.getParentFile().mkdirs();

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

Employee.java

package ExcelFile;

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 ExcelFile;

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;
    }
}

Sau khi run xong, bạn mở file excel và sẽ thấy kết quả như sau:

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.

package ExcelFile;

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:/Data File/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("");
        }
    }
}

Kết quả:

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

Trong ví dụ này, mình sẽ đọ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.

package ExcelFile;

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:/Data File/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ả:

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 ExcelFile;

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:/Data File/style.xls");
        file.getParentFile().mkdirs();

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

10. Kết

Như vậy chúng ta đã tìm hiểu cách xử đọc và ghi file excel trong Java. Cảm ơn các bạn đã theo dõi bài viết của mình.  Chúc các bạn thành công. Hẹn gặp lại các bạn ở những chủ đề tiếp theo. Bái bai.

Nguồn:

• https://gpcoder.com/3144-huong-dan-doc-va-ghi-file-excel-trong-java-su-dung-thu-vien-apache-poi/
• https://openplanning.net/11259/doc-ghi-file-excel-trong-java-su-dung-apache-poi#a5132380