Giới thiệu thư viện Java POI
Java POI là một API Java mã nguồn mở để xử lý tài liệu Microsoft Office, bao gồm các thành phần HSSF, XSSF và HWPF tương ứng với bảng tính Excel, tài liệu Word và bài trình chiếu PowerPoint. Thư viện này cung cấp nhiều chức năng mạnh mẽ để đọc, ghi và sửa đổi các định dạng tệp này, đặc biệt hữu ích trong các ứng dụng doanh nghiệp cần xử lý lượng lớn dữ liệu nhập/xuất.
POI hoạt động đa nền tảng, cho phép chạy trên Windows, Linux và Mac mà không gặp sự khác biệt. Thư viện tích hợp liền mạch với các công nghệ Java hiện tại và hỗ trợ các môi trường phát triển phổ biến như Maven và Gradle.
Các lớp cốt lõi cần nắm khi làm việc với POI bao gồm Workbook, Sheet và Cell. Trong bài viết này, chúng ta sẽ khám phá cách sử dụng POI để nhập dữ liệu từ Excel vào cơ sở dữ liệu MySQL và ngược lại, đồng thời xem xét các phương pháp xử lý ngoại lệ, xác thực dữ liệu và tối ưu hóa hiệu năng.
Quy trình nhập dữ liệu từ Excel vào cơ sở dữ liệu
Chuẩn bị môi trường
Cấu hình thư viện Java POI
Để sử dụng Java POI, cần thêm thư viện vào dự án. Với Maven, thêm các dependency sau vào file pom.xml:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
Hiểu cấu trúc tệp Excel
Excel có hai định dạng chính: .xls (Excel 97-2003) và .xlsx (Excel 2007 trở đi). POI cung cấp API tương ứng: HSSFWorkbook cho .xls và XSSFWorkbook cho .xlsx.
Đọc dữ liệu từ tệp Excel
Mở và truy cập bảng tính
InputStream input = new FileInputStream(new File("du_lieu.xlsx"));
Workbook workbook = WorkbookFactory.create(input);
Sheet trangTinh = workbook.getSheetAt(0);
Đọc và phân tích dữ liệu ô
for (Row dong : trangTinh) {
for (Cell o : dong) {
switch (o.getCellType()) {
case STRING:
System.out.print(o.getStringCellValue() + "\t");
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(o)) {
System.out.print(o.getDateCellValue() + "\t");
} else {
System.out.print(o.getNumericCellValue() + "\t");
}
break;
case BOOLEAN:
System.out.print(o.getBooleanCellValue() + "\t");
break;
default:
System.out.print(" " + "\t");
}
}
System.out.println();
}
Xử lý và xác thực dữ liệu
Làm sạch dữ liệu
for (Row dong : trangTinh) {
for (Cell o : dong) {
if (o.getCellType() == CellType.BLANK) {
o.setCellValue("Không có dữ liệu");
}
}
}
Xác thực định dạng dữ liệu
for (Row dong : trangTinh) {
Cell cellNgay = dong.getCell(0);
if (cellNgay.getCellType() == CellType.STRING) {
try {
SimpleDateFormat dinhDang = new SimpleDateFormat("yyyy-MM-dd");
Date ngay = dinhDang.parse(cellNgay.getStringCellValue());
// Dữ liệu hợp lệ
} catch (ParseException e) {
// Xử lý lỗi dữ liệu không hợp lệ
}
}
}
Chèn dữ liệu vào cơ sở dữ liệu
Xây dựng câu lệnh SQL
String sql = "INSERT INTO bang_du_lieu (cot1, cot2) VALUES (?, ?)";
PreparedStatement pstm = ketNoi.prepareStatement(sql);
pstm.setString(1, "gia_tri1");
pstm.setInt(2, 123);
pstm.executeUpdate();
Quản lý giao dịch
try {
ketNoi.setAutoCommit(false); // Bắt đầu giao dịch
// Thực hiện các thao tác
ketNoi.commit(); // Commit giao dịch
} catch (Exception e) {
ketNoi.rollback(); // Rollback khi có lỗi
e.printStackTrace();
}
Quy trình xuất dữ liệu từ cơ sở dữ liệu ra Excel
Chuẩn bị truy vấn cơ sở dữ liệu
Thiết kế logic truy vấn
Xác định rõ mục đích xuất dữ liệu để thiết kế truy vấn phù hợp. Cần cân nhắc yếu tố thời gian thực, phạm vi dữ liệu và điều kiện lọc.
Tối ưu hóa câu lệnh SQL
- Sử dụng chỉ mục (index) cho các trường thường xuyên truy vấn
- Giảm thiểu các phép join không cần thiết
- Chỉ chọn các trường cần thiết thay vì SELECT *
- Với dữ liệu lớn, sử dụng truy vấn theo từng phần
Xử lý và định dạng dữ liệu
Chuyển đổi định dạng tương thích Excel
Sử dụng các lớp của POI để chuyển đổi ngày giờ, số liệu thành định dạng Excel phù hợp.
Sắp xếp và nhóm dữ liệu
Sắp xếp dữ liệu theo các cột cụ thể (ngày, số tiền...) trước khi xuất ra Excel để tăng tính dễ đọc.
Tạo và cấu hình tệp Excel
Xây dựng cấu trúc tệp Excel
Tạo các bảng tính với tên phù hợp và thiết lập các thuộc tính cơ bản như chiều cao hàng, chiều rộng cột.
Định dạng ô và đường viền
Sử dụng CellStyle để thiết lập định dạng ô, bao gồm font chữ, màu nền, căn chỉnh và đường viền.
Ghi dữ liệu vào tệp Excel
Đọc kết quả truy vấn
try (ResultSet ketQua = truyVan.executeQuery("SELECT * FROM bang_cua_ban")) {
int soDong = 0;
while (ketQua.next()) {
Row dong = trangTinh.createRow(soDong++);
// Xử lý từng cột dữ liệu
dong.createCell(0).setCellValue(ketQua.getString("ten_cot"));
// ... Xử lý các cột khác tương tự
}
}
Định dạng dữ liệu khi ghi vào Excel
Khi ghi dữ liệu vào Excel, cần chuyển đổi định dạng đảm bảo dữ liệu hiển thị chính xác, đặc biệt với các kiểu dữ liệu ngày giờ và số liệu.
Xử lý ngoại lệ và xác thực dữ liệu
Cơ chế xử lý ngoại lệ Java
Loại ngoại lệ phổ biến khi thao tác Excel
Khi sử dụng POI, có thể gặp các ngoại lệ sau:
- EmptyCellException: Khi cố gắng đọc ô trống
- IllegalStateException: Khi thao tác không hợp lệ với workbook được bảo vệ
- ParseException: Khi phân tích dữ liệu ô thành ngày hoặc định dạng khác thất bại
- IOException: Lỗi khi đọc/ghi tệp
Xây dựng ngoại lệ tùy chỉnh
public class LoiGiaTriCell extends Exception {
public LoiGiaTriCell(String thongBao) {
super(thongBao);
}
}
Tầm quan trọng của xác thực dữ liệu
Phương pháp kiểm tra dữ liệu dựa trên quy tắc
Sử dụng biểu thức chính quy và quy tắc nghiệp vụ để kiểm tra tính hợp lệ của dữ liệu trước khi xử lý.
Tương tác với người dùng qua phản hồi xác thực
Cung cấp phản hồi ngay lập tức khi người dùng nhập dữ liệu không hợp lệ:
public boolean kiemTraDuLieu(Cell o) {
// Giả sử kiểm tra số điện thoại
Pattern phonePattern = Pattern.compile("^0[0-9]{9}$");
Matcher matcher = phonePattern.matcher(o.getStringCellValue());
return matcher.matches();
}
// Sử dụng ngoại lệ tùy chỉnh để phản hồi
if (!kiemTraDuLieu(o)) {
throw new LoiGiaTriCell("Số điện thoại không đúng định dạng, vui lòng nhập lại.");
}
Tối ưu hóa hiệu năng và thao tác với Excel phức tạp
Đề xuất tối ưu hóa hiệu năng
Tối ưu hóa ở cấp độ mã
- Tránh sử dụng vòng lặp để đọc/ghi từng ô, sử dụng thao tác hàng loạt
- Với dữ liệu lớn, sử dụng SXSSF (Streaming Usermodel API) để giảm tiêu thụ bộ nhớ
SXSSFWorkbook workbook = new SXSSFWorkbook();
SXSSFSheet sheet = workbook.createSheet("DuLieu");
for (int r = 0; r < 100000; r++) {
SXSSFRow row = sheet.createRow(r);
for (int c = 0; c < 10; c++) {
SXSSFCell cell = row.createCell(c);
cell.setCellValue("DuLieu " + r + "," + c);
}
}
FileOutputStream output = new FileOutputStream("file_lon.xlsx");
workbook.write(output);
output.close();
workbook.dispose();
Tối ưu hóa ở cấp hệ thống
Điều chỉnh tham số JVM để phân bổ bộ nhớ hợp lý, tránh lỗi tràn bộ nhớ khi xử lý tệp Excel lớn:
java -Xms256m -Xmx1024m -jar ung_dung_cua_ban.jar
Thao tác với Excel định dạng phức tạp
Xử lý ô hợp nhất và định dạng tùy chỉnh
HSSFCell cellHopNhat = sheet.getRow(0).getCell(0);
cellHopNhat.setCellValue("Ô đã hợp nhất");
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
HSSFCellStyle kieuTuChinh = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
kieuTuChinh.setFont(font);
kieuTuChinh.setDataFormat(workbook.createDataFormat().getFormat("@"));
cellHopNhat.setCellStyle(kieuTuChinh);
Kỹ thuật nhập/xuất đồ thị và biểu đồ
Sử dụng HSSFPatriarch và SXSSFPatriarch để tạo và thao tác với đồ thị trong Excel. Khi xử lý đồ thị, cần chú ý vị trí, kích thước và mối quan hệ với các ô để đảm bảo tính nhất quán.
Phân tích tình huống: Ứng dụng trong các kịch bản điển hình
Xử lý tệp Excel có dung lượng lớn
Khi xử lý tệp Excel lớn, việc sử dụng SXSSF là giải pháp hiệu quả. Cần lưu ý đặc biệt khi xử lý ô hợp nhất và thời điểm đóng tệp.
Quy trình xử lý đồng nhất cho nhiều bảng tính
Đối với tệp có nhiều bảng tính, hãy xây dựng logic xử lý thống nhất cho từng bảng. Sử dụng mẫu chiến lược (strategy pattern) để trừu tượng hóa các logic xử lý khác nhau, giảm thiểu sự trùng lặp mã và tăng tính bảo trì.