[Java] Đọc dữ liệu từ file Excel và lưu vào Database sử dụng Apache POI

Tóm Tắt

1. Giới thiệu

Excel là định dạng file rất phổ biến được tạo ra bởi Microsoft. Các ứng dụng Java có thể đọc và ghi tập tin Excel bằng cách sử dụng Apache POI – the Java API for Microsoft Documents.

Bài viết này hướng dẫn cách để đọc các tập tin Excel sử dụng API Apache POI.

(Cách ghi dữ liệu ra file Excel sẽ được viết ở một bài khác 😉 )

2. Chuẩn bị

Apache POI’s API có thể đọc và ghi các file Excel trong cả hai định dạng XLS (từ phiên bản Excel 2003 trở về trước) và XLSX (từ phiên bản Excel 2007 trở về sau này). Để sử dụng được Apache POI trong project thì bạn phải chuẩn bị một số thư viện cần thiết:

Đối với các project không tạo bằng Maven:

Tải về các thư viện với phiên bản mới nhất tại đây: Apache POI – Download Release Artifacts

Giải nén file .zip và thêm các tập tin JAR vào dự án của bạn

Nếu bạn chỉ đọc và ghi với định dạng XLS (từ Excel 2003 trở về trước) thì chỉ cần thư viện poi-VERSION.jar là đủ 😉

Nếu bạn chỉ đọc và ghi với định dạng XLSX (từ Excel 2007 trở về sau) thì cần 3 thư viện sau:

poi-ooxml-VERSION.jar
poi-ooxml-schemas-VERSION.jar
xmlbeans-VERSION.jar

Còn nếu bạn dùng cả 2 định dạng Excel trên thì bạn cần khai báo cả 4 thư viện cho dự án 😉

Đối với Maven projects:

Thêm các phụ thuộc sau vào pom.xml

– Nếu bạn chỉ đọc và ghi với định dạng XLS (từ Excel 2003 trở về trước)


<!-- Excel 2003 and earlier -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>VERSION</version>
</dependency>

– Nếu bạn chỉ đọc và ghi với định dạng XLSX (từ Excel 2007 trở về sau)


<!-- Excel 2007 and later -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>VERSION</version>
</dependency>

Còn nếu bạn dùng cả 2 định dạng Excel trên thì bạn cần khai báo cả 2 phụ thuộc trên vào file pom.xml

Lưu ý:

VERSION là tên phiên bản của thư viện. Tại thời điểm viết bài này phiên bản mới nhất là 3.15

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

Có hai tiền tố chính mà bạn sẽ gặp phải khi làm việc với Apache POI:

HSSF: biểu thị các API để làm việc với Excel 2003 và trở về trước đó.
XSSF: biểu thị các API để làm việc với Excel 2007 và trở về sau này.

Để bắt đầu API Apache POI, bạn cần hiểu và sử dụng 4 interface sau:

Workbook: đại diện cho một file Excel. Nó được triển khai dưới hai class là: HSSFWorkbook và XSSFWorkbook.
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

4. Ví dụ

Có 2 file Excel tương ứng với 2 loại định dạng có nội dung chứa thông tin về sách (tên sách, tác giả, giá sách) như sau:

Định dạng XLS

read-data-from-excel-1

Định dạng XLSX

read-data-from-excel-2

Một số class tham gia vào ví dụ này:

MySQLConnectionUtils.java: Class tiện ích kết nối với Database
Book.java: Class chứa các thuộc tính của Book và getter, setter
BookDAO.java: Thực hiện insert dữ liệu đọc từ file Excel vào Database
ReadFromExcelFile.java: Class tiện ích đọc dữ liệu từ file excel, trả về một List để lớp BookDAO insert vào DB
MainPro.java: Để test thử
Sử dụng CSDL MySQL

MySQLConnectionUtils.java


package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Stack;

public class MySQLConnectionUtils {
	
	private static String classForName;
	private static String url;
	private static String username;
	private static String password;
	private static Stack<Connection> connPools;
	
	static {
		connPools = new Stack<Connection>();
		
		classForName = "com.mysql.jdbc.Driver";
		url = "jdbc:mysql://127.0.0.1:3306/icancodeit?useUnicode=yes&characterEncoding=UTF-8";
		username = "root";
		password = "654321";
		
		try {
			Class.forName(classForName).newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Connection connect() throws SQLException {
		Connection conn = null;
		if (connPools.empty()) {
			conn = DriverManager.getConnection(url, username, password);
		} else {
			conn = connPools.pop();
		}
		return conn;
	}
	
	public static void disconnect(Connection conn) {
		if (conn != null) {
			connPools.push(conn);
		}
	}
	
	public static void rollbackQuietly(Connection conn) {
		try {
			conn.rollback();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}

Book.java


package model;

public class Book {
	
	private int id;
	private String name;
	private String author;
	private double price;
	
	public Book() {
		
	}
	
	public Book(String name, String author, double price) {
		this.name = name;
		this.author = author;
		this.price = price;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getAuthor() {
		return author;
	}

	public void setAuthor(String author) {
		this.author = author;
	}

	public double getPrice() {
		return price;
	}

	public void setPrice(double price) {
		this.price = price;
	}
	
}

BookDAO.java


package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import model.Book;
import util.MySQLConnectionUtils;

public class BookDAO {
	
	private Connection conn;
	private PreparedStatement ps;
	
	public void insertListBooks(List<Book> listBooks) {
		try {
			conn = MySQLConnectionUtils.connect();
			// Sét tự động commit false, để chủ động điều khiển
			conn.setAutoCommit(false);
			
			String sql = "INSERT INTO book(name, author, price) VALUES (?, ?, ?)";
			ps = conn.prepareStatement(sql);
			
			for (Book book : listBooks) {
				ps.setString(1, book.getName());
				ps.setString(2, book.getAuthor());
				ps.setDouble(3, book.getPrice());
				ps.addBatch();
			}
			
			ps.executeBatch();
			
			// Gọi commit() để commit giao dịch với DB
			conn.commit();
			
			System.out.println("Record is inserted into BOOK table!");
			
		} catch (Exception e) {
			
			e.printStackTrace();
			MySQLConnectionUtils.rollbackQuietly(conn);
			
		} finally {
			
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			MySQLConnectionUtils.disconnect(conn);
		}
	}
	
}

ReadFromExcelFile.java


package util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import model.Book;

public class ReadFromExcelFile {
	
	public List<Book> readBooksFromExcelFile(String excelFilePath) throws IOException {
		List<Book> listBooks = new ArrayList<Book>();
		FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
		
		Workbook workBook = getWorkbook(inputStream, excelFilePath);
		Sheet firstSheet = workBook.getSheetAt(0);
		Iterator<Row> rows = firstSheet.iterator();
		
		while (rows.hasNext()) {
			Row row = rows.next();
			Iterator<Cell> cells = row.cellIterator();
			Book book = new Book();
			
			while (cells.hasNext()) {
				Cell cell = cells.next();
				int columnIndex = cell.getColumnIndex();
				
				switch (columnIndex) {
					case 0:
						book.setName((String) getCellValue(cell));
						break;
					case 1:
						book.setAuthor((String) getCellValue(cell));
						break;
					case 2:
						book.setPrice((Double) getCellValue(cell));
						break;
				}
			}
			listBooks.add(book);
		}
		
		workBook.close();
		inputStream.close();
		
		return listBooks;
	}
	
	private Object getCellValue(Cell cell) {
	    switch (cell.getCellType()) {
	    	case Cell.CELL_TYPE_STRING:
	    		return cell.getStringCellValue();
	 
	    	case Cell.CELL_TYPE_BOOLEAN:
	    		return cell.getBooleanCellValue();
	 
	    	case Cell.CELL_TYPE_NUMERIC:
	    		return cell.getNumericCellValue();
	    }
	 
	    return null;
	}
	
	private Workbook getWorkbook(FileInputStream 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;
	}
	
}

MainPro.java


import java.util.List;

import dao.BookDAO;
import model.Book;
import util.ReadFromExcelFile;

public class MainPro {
	
	public static void main(String[] args) {
		try {
			
			//String excelFilePath = "D:/JAVA/DataSamples/books_1.xls";
			String excelFilePath = "D:/JAVA/DataSamples/books_2.xlsx";
			
			List<Book> listBooks = new ReadFromExcelFile().readBooksFromExcelFile(excelFilePath);
			BookDAO bookDAO = new BookDAO();
			bookDAO.insertListBooks(listBooks);
			
		} catch (Exception e) {
			System.out.println("Error: " + e.getMessage());
		}
	}
	
}

Và kết quả trong Database:

read-data-from-excel-3

Tham khảo: codejava.net

Hết 😉

1. Giới thiệu

2. Chuẩn bị

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

4. Ví dụ

mtoplist