genexcel_java.java

本文展示如何將由資料庫中提取的數據以Excel檔的形式編排存檔

import java.sql.*;
import java.io.*;
import org.apache.poi.hssf.usermodel.*;
import java.util.Calendar;
//import com.mysql.jdbc.Driver;

public class test {
	/**
	 * @param args
	 */
	 //Connects  host to JDBC DB(Rudolph in this context); Note that this is an re-implemented version of DriverManager.getConnection that includes DB profile for convenience
	public static Connection getConnection() throws SQLException {
		final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
		final String DB_URL = "db_url";
		final String DB_USER = "db_user";
		final String DB_PASSWORD = "123";		
		
		try 
		{
			Class.forName(JDBC_DRIVER);
		} 
		catch (ClassNotFoundException e) 
		{
			e.printStackTrace();
		}		
		return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
	} 
	
	public static void main(String[] args) {		
		//conn: for connection; 
		//stmt: for  the ResultSet containing SQL query results
		Connection conn= null;
		Statement stmt = null;
		try{
			conn=getConnection();
			stmt=conn.createStatement();
			
			//SQL statement
			String sql;			 
			sql="SELECT task.taskID,pick_up_time, update_time,dispatch_result,address,sequence FROM dispatch_result INNER JOIN (task INNER JOIN (task_location INNER JOIN location ON task_location.locationID=location.locationID) ON task.taskID=task_location.taskID) ON task.taskID=dispatch_result.taskID ";
			//Use  WHERE DATEDIFF('1997-12-31 23:59:59','1997-12-30') 's format in cases that WHERE is needed;
			sql+="ORDER BY task.taskID,update_time,sequence";
			
			//query and obtain meta-data
			ResultSet rSet = stmt.executeQuery(sql); 
			ResultSetMetaData mData=rSet.getMetaData();
			int nCol=mData.getColumnCount();
			
			

			//New WorkBook
			HSSFWorkbook wb = new HSSFWorkbook(); 
			//New sheet(can be 1..n)
			HSSFSheet sheet = wb.createSheet("new sheet");
			final String FILE_NAME="Excel_Java";
			
			//Insert the contents of SQL query results to Excel File. Note rows and cells are to be generated one-by-one
			int rNum=0;
			while(rSet.next()){
				//Note: Excel's row & col starts from 0, yet resultSet's start from 1 (thus +1)?
				HSSFRow row = sheet.createRow((short)rNum);
				if(rNum==0){
					HSSFCell cell = row.createCell((int)(1));
					cell.setCellValue("诫Ω