Hướng dẫn đọc và ghi file excel trong Java sử dụng thư viện Apache POI

Excel là định dạng file rất phổ biến được tạo ra bởi Microsoft. Thông thường, các ứng dụng Java sử dụng thư viện Apache POI để đọc và ghi tập tin Excel. Trong bài này, tôi sẽ hướng dẫn cách để đọc và ghi các tập tin Excel sử dụng API của thư viện Apache POI.

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.

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.

Khai báo thư viện Apache POI

Tạo Maven project và khai báo thư viện trong file pom.xml của project như sau:

<!-- 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>

Lưu ý:

Các phiên bản cũ của Apache POI chỉ hỗ trợ các định dạng file binary như doc, xls, ppt, … .Từ phiên bản 3.5 trở đi, POI hỗ trợ các định dạng file OOXML của MS-Office như docx, xlsx, pptx, …

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

Quy ước chung cần nhớ khi làm việc với Apache POI: Các index của Workbook, Sheet, Row đều bằng đầu bằng 0.

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

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.

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.

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.

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

Ví dụ đọc 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.

Ví dụ ghi file excel (.xls, .xlsx)

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

}

WriteExcelExample.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.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcelExample {
	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.xlsx";
		writeExcel(books, excelFilePath);
	}

	public static void writeExcel(List<Book> books, String excelFilePath) throws IOException {
		// Create Workbook
		Workbook workbook = getWorkbook(excelFilePath);

		// Create sheet
		Sheet sheet = workbook.createSheet("Books"); // Create sheet with sheet name

		int rowIndex = 0;
		
		// Write header
		writeHeader(sheet, rowIndex);

		// Write data
		rowIndex++;
		for (Book book : books) {
			// Create row
			Row row = sheet.createRow(rowIndex);
			// Write data on row
			writeBook(book, row);
			rowIndex++;
		}
		
		// Write footer
		writeFooter(sheet, rowIndex);

		// Auto resize column witdth
		int numberOfColumn = 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;
	}

	// Create workbook
	private static Workbook getWorkbook(String excelFilePath) throws IOException {
		Workbook workbook = null;

		if (excelFilePath.endsWith("xlsx")) {
			workbook = new XSSFWorkbook();
		} else if (excelFilePath.endsWith("xls")) {
			workbook = new HSSFWorkbook();
		} else {
			throw new IllegalArgumentException("The specified file is not Excel file");
		}

		return workbook;
	}

	// Write header with format
	private static void writeHeader(Sheet sheet, int rowIndex) {
		// create CellStyle
		CellStyle cellStyle = createStyleForHeader(sheet);
		
		// Create row
		Row row = sheet.createRow(rowIndex);
		
		// Create cells
		Cell 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, Row row) {
		if (cellStyleFormatNumber == null) {
			// Format number
			short format = (short)BuiltinFormats.getBuiltinFormat("#,##0");
			// DataFormat df = workbook.createDataFormat();
			// short format = df.getFormat("#,##0");
			
			//Create CellStyle
			Workbook workbook = row.getSheet().getWorkbook();
			cellStyleFormatNumber = workbook.createCellStyle();
			cellStyleFormatNumber.setDataFormat(format);
		}
		
		Cell 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(Sheet sheet, int rowIndex) {
		// Create row
		Row row = sheet.createRow(rowIndex);
		Cell cell = row.createCell(COLUMN_INDEX_TOTAL, CellType.FORMULA);
		cell.setCellFormula("SUM(E2:E6)");
	}
	
	// Auto resize column width
	private static void autosizeColumn(Sheet sheet, int lastColumn) {
		for (int columnIndex = 0; columnIndex < lastColumn; columnIndex++) {
			sheet.autoSizeColumn(columnIndex);
		}
	}
	
	// Create output file
	private static void createOutputFile(Workbook 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.xlsx được tạo ra trong thư mục C:/demo như sau:

Chọn các ô của cột Total money để xem các công thức được tạo ra

Ví dụ đọc file excel (.xls, .xlsx)

package com.gpcoder.apachepoi;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

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;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelExample {
	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;

	public static void main(String[] args) throws IOException {
		final String excelFilePath = "C:/demo/books.xlsx";
		final List<Book> books = readExcel(excelFilePath);
		for (Book book : books) {
			System.out.println(book);
		}
	}

	public static List<Book> readExcel(String excelFilePath) throws IOException {
		List<Book> listBooks = new ArrayList<>();

		// Get file
		InputStream inputStream = new FileInputStream(new File(excelFilePath));

		// Get workbook
		Workbook workbook = getWorkbook(inputStream, excelFilePath);

		// Get sheet
		Sheet sheet = workbook.getSheetAt(0);

		// Get all rows
		Iterator<Row> iterator = sheet.iterator();
		while (iterator.hasNext()) {
			Row nextRow = iterator.next();
			if (nextRow.getRowNum() == 0) {
				// Ignore header
				continue;
			}

			// Get all cells
			Iterator<Cell> cellIterator = nextRow.cellIterator();

			// Read cells and set value for book object
			Book book = new Book();
			while (cellIterator.hasNext()) {
				//Read cell
				Cell cell = cellIterator.next();
				Object cellValue = getCellValue(cell);
				if (cellValue == null || cellValue.toString().isEmpty()) {
					continue;
				}
				// Set value for book object
				int columnIndex = cell.getColumnIndex();
				switch (columnIndex) {
				case COLUMN_INDEX_ID:
					book.setId(new BigDecimal((double) cellValue).intValue());
					break;
				case COLUMN_INDEX_TITLE:
					book.setTitle((String) getCellValue(cell));
					break;
				case COLUMN_INDEX_QUANTITY:
					book.setQuantity(new BigDecimal((double) cellValue).intValue());
					break;
				case COLUMN_INDEX_PRICE:
					book.setPrice((Double) getCellValue(cell));
					break;
				case COLUMN_INDEX_TOTAL:
					book.setTotalMoney((Double) getCellValue(cell));
					break;
				default:
					break;
				}

			}
			listBooks.add(book);
		}

		workbook.close();
		inputStream.close();

		return listBooks;
	}

	// Get Workbook
	private static Workbook getWorkbook(InputStream inputStream, String excelFilePath) throws IOException {
		Workbook workbook = null;
		if (excelFilePath.endsWith("xlsx")) {
			workbook = new XSSFWorkbook(inputStream);
		} else if (excelFilePath.endsWith("xls")) {
			workbook = new HSSFWorkbook(inputStream);
		} else {
			throw new IllegalArgumentException("The specified file is not Excel file");
		}

		return workbook;
	}

	// Get cell value
	private static Object getCellValue(Cell cell) {
		CellType cellType = cell.getCellTypeEnum();
		Object cellValue = null;
		switch (cellType) {
		case BOOLEAN:
			cellValue = cell.getBooleanCellValue();
			break;
		case FORMULA:
			Workbook workbook = cell.getSheet().getWorkbook();
			FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
			cellValue = evaluator.evaluate(cell).getNumberValue();
			break;
		case NUMERIC:
			cellValue = cell.getNumericCellValue();
			break;
		case STRING:
			cellValue = cell.getStringCellValue();
			break;
		case _NONE:
		case BLANK:
		case ERROR:
			break;
		default:
			break;
		}

		return cellValue;
	}
}

Kết quả thực thi chương trình trên:

Book [id=1, title=Book 1, quantity=2, price=1000.0, totalMoney=2000.0]
Book [id=2, title=Book 2, quantity=4, price=2000.0, totalMoney=8000.0]
Book [id=3, title=Book 3, quantity=6, price=3000.0, totalMoney=18000.0]
Book [id=4, title=Book 4, quantity=8, price=4000.0, totalMoney=32000.0]
Book [id=5, title=Book 5, quantity=10, price=5000.0, totalMoney=50000.0]
Book [id=null, title=null, quantity=null, price=null, totalMoney=110000.0]

Lưu ý:

Các kiểu dữ liệu số khi đọc từ file excel sẽ có giá trị là kiểu double.

Tài liệu tham khảo:

• https://poi.apache.org/spreadsheet/index.html
• https://www.tutorialspoint.com/apache_poi/index.htm

Trên đây là ví dụ cơ bản về đọc và ghi file excel. Ngoài ra, còn rất nhiều tính năng khác của Apache POI, các bạn có thể tìm hiểu thêm trên trang document của Apache POI. Cảm ơn các bạn đã quan tâm và theo dõi bài viết. Hẹn gặp lại ở bài viết tiếp theo.

Chuyên mục: Java I/O, Report Được gắn thẻ: Apache Poi, excel, Report

Bình luận

bình luận