Export dữ liệu ra Excel dùng POI

Một vấn đề mà tôi nhận được nhiều request nữa đó là làm sao để kết xuất dữ liệu ra Excel nhằm các mục đích báo cáo, tương tác cơ bản người dùng,…

Nhận thấy đó cũng là điều rất cần thiết nên tôi viết bài này nhằm hỗ trợ các bạn gặp khúc mắc vê vấn đề này.

Có rất nhiều kỹ thuật để làm được việc này. Tuy nhiên ở đây tôi chọn POI – một dự án open-source của Apache để sử dụng. Bạn cần phải download thư viện này tại poi.apache.org. Sau khi download về, giải nén và bạn có thư viện sẵn sàng cho việc export. Phiên bản tôi sử dụng ở đây là bản poi-3.8-beta4-20110826. Bạn có thể download các bản khác xài cũng được. Kích thước gói jar vào khoảng 1.8MB ( chỉ cần 1 file jar poi-3.8-beta4-20110826.jar là đủ nhé bạn).

Bạn có thể dùng Eclipse hay IDE nào đó cũng được miễn là bạn reference đến thư viện này. Ở đây tôi dùng Eclipse.

Trong ví dụ này tôi kết nối đến cơ sở dữ liệu Access(bạn có thể khác) để lấy dữ liệu sau đó kết xuất ra excel. Cơ sở dữ liệu này có cấu trúc như sau:

Cấu trúc chương trình như sau:

Trong đó lớp ConnectDB dùng để kết nối đến cơ sở dữ liệu (ở đây là ms access), lớp Export2Office chứa các phương thức liên quan đến kết xuất cơ sở dữ liệu ra excel. Cuối cùng là lớp Export2Office_Driver cho mục đích thử các phương thức vừa tạo.

Code của chương trình như sau:
Lớp ConnectDB.java

package vovanhai.wordpress.com;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ConnectDB {
	//connection kết nối cơ sở dữ liệu
	private Connection con;

	public ConnectDB(String accessDBName) throws Exception{
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String url="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+accessDBName;
		con=DriverManager.getConnection(url);
	}
	/**
	 * Lấy 1 bảng cơ sở dữ liệu
	 * @param tableName là bảng cơ sở dữ liệu cần lấy
	 * @return
	 * @throws Exception
	 */
	public ResultSet getTable(String tableName)throws Exception{
		String sql="select * from "+tableName;
		PreparedStatement pstmt=con.prepareStatement(sql);
		return pstmt.executeQuery();
	}

	/**
	 * Lấy 1 resultset từ  câu truy vấn
	 * @param sql là câu truy vấn
	 * @return
	 * @throws Exception
	 */
	public ResultSet getTableFromSQL(String sql)throws Exception{
		PreparedStatement pstmt=con.prepareStatement(sql);
		return pstmt.executeQuery();
	}
	/**
	 * Lấy 1 connection
	 * @return
	 */
	public Connection getConnection(){
		return con;
	}
	/**
	 * Đóng kết nối
	 * @throws Exception
	 */
	public void CloseConnection()throws Exception{
		if(con!=null)
			con.close();
	}
}

Lớp Export2Office .java

package vovanhai.wordpress.com;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
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;

public class Export2Office {
	//workbook ta làm việc ~ excel file
	private Workbook wb;
	public Export2Office(Workbook wb) {
		this.wb=wb;
	}
	/**
	 * Export 1 result set ra file excel
	 * @param rs là result set cần xuất
	 * @param sheetName là tên của sheet trong workbook
	 * @throws Exception
	 */
	public void Export(ResultSet rs, String sheetName)throws Exception{
		int nos=wb.getNumberOfSheets();
		for (int i = 0; i < nos; i++) {
			String sn=wb.getSheetName(i);
			if(sn.equalsIgnoreCase(sheetName)){
				sheetName+="$1";
			}
		}
		//tạo worksheet
		Sheet sheet = wb.createSheet(sheetName);
		//Lấy metadata làm column header
		ResultSetMetaData rsmd=rs.getMetaData();
		//tạo dòng tiêu đề
		Row row = sheet.createRow((short)0);
		int colNum=rsmd.getColumnCount();//số fields có trong bảng
		//duyệt qua các fields lấy làm column header
		for (int i = 0; i < colNum; i++) {
			String col=rsmd.getColumnName(i+1);
			Cell cell=row.createCell(i);
			cell.setCellValue(col);
		}
		//điền giá trị vào các hàng
		while(rs.next()){
			//tạo 1 hàng mới
			row=sheet.createRow(rs.getRow());
			for (int i = 0; i < colNum; i++) {
				String val=rs.getString(i+1);
				//tạo 1 ô mới
				Cell cell=row.createCell(i);
				cell.setCellValue(val);
			}
		}

	}
	/**
	 * Lấy tất cả các bảng của cơ sở dữ liệu sau đó export ra thành các sheet trong 1 file excel
	 * @param AccessDBFilePath là đường dẫn tuyệt đối đến file access
	 * @param excelFilePath là đường dẫn đến file excel kết xuất
	 * @throws Exception
	 */
	public void ExportAllDBFiles(String AccessDBFilePath,String excelFilePath)throws Exception{
		ConnectDB condb=new ConnectDB(AccessDBFilePath);
		Connection con=condb.getConnection();

		DatabaseMetaData dbm=con.getMetaData();
		String []types={"TABLE"};
		ResultSet rsm= dbm.getTables(null, null, null, types);
		while(rsm.next()){
			String tableName=rsm.getString("TABLE_NAME");
			ResultSet rs=condb.getTable(tableName);
			Export(rs,tableName);
		}
		//Ghi dữ liệu xuống file
		FileOutputStream fos=new FileOutputStream(excelFilePath,true);
		wb.write(fos);
		fos.close();
		condb.CloseConnection();
	}
	/**
	 * Export 1 table
	 * @param AccessDBFilePath la file access cần export
	 * @param tableName là bảng cần export
	 * @param excelFilePath đường dẫn đến file excel cần tạo
	 * @throws Exception
	 */
	public void ExportTable(String AccessDBFilePath,String tableName,String excelFilePath)throws Exception{
		ConnectDB condb=new ConnectDB(AccessDBFilePath);
		ResultSet rs=condb.getTable(tableName);
		Export(rs,tableName);
		//Ghi dữ liệu xuống file
		String fileName=excelFilePath;
		FileOutputStream fos=new FileOutputStream(fileName,true);
		wb.write(fos);
		fos.close();
		condb.CloseConnection();
	}
}

Lớp Export2Office_Driver.java

package vovanhai.wordpress.com;

import java.io.FileOutputStream;
import java.sql.ResultSet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;

public class Export2Office_Driver {

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

		System.out.println("waiting for export...");
		long before=System.currentTimeMillis();

		//demo 01-export toàn bộ cơ sở dữ liệu thành 1 file excel với mỗi bảng là 1 sheet
		Workbook wb=new HSSFWorkbook();
		Export2Office ep=new Export2Office(wb);
		ep.ExportAllDBFiles("school_DB.mdb", "ex1.xls");

		//demo 02- export 1 bảng ra file excel
		Workbook wb2=new HSSFWorkbook();
		Export2Office ep2=new Export2Office(wb2);
		ep2.ExportTable("school_DB.mdb","tblClass", "ex2.xls");

		//demo 03-export kết quả của 1 câu seleect sql bất kỳ ra excel
		Workbook wb3=new HSSFWorkbook();
		ConnectDB condb=new ConnectDB("school_DB.mdb");
		Export2Office ep3=new Export2Office(wb3);
		String sql="select * from tblClass c inner join tblStudents s on s.classID=c.classID "+
		"where s.classID='cdth4a'";
		ResultSet rs=condb.getTableFromSQL(sql);
		ep3.Export(rs, "Join");
		FileOutputStream fos=new FileOutputStream("ex3.xls",true);
		wb3.write(fos);
		fos.close();
		condb.CloseConnection();

		//finish
		long after=System.currentTimeMillis();
		System.out.println("finished after "+(after-before)+" milliseconds");
	}
}

Chạy thử ứng dụng, sau đó chọn project, nhấn F5 để refresh lại project, ta có kết quả

Mở thử các file excel để thưởng thức nhỉ!

File access demo bạn có thể download tại đây.