java實現將oracle表中的資料匯出到excel表裡

標籤：

1、資料庫連接類：Dbutil

package sql2excel;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

public class Dbutil {

/*

* 功能：編寫一個靜態方法用於與資料庫建立串連 輸入參數：無 傳回值：資料庫連接對象

*/

public static Connection getConnection() {

// 定義一個連線物件

Connection conn = null;

// 建立先行編譯語句對象，一般都是用這個而不用Statement

PreparedStatement pre = null;

// 建立一個結果集對象

ResultSet result = null;

// 定義串連資料庫的URL資源

String url = "jdbc:oracle:thin:@10.20.56.52:1521:orcl";

// 定義串連資料庫的使用者名稱稱與密碼

String username = "crm";

String password = "crm";

// 載入資料庫連接驅動

String className = "oracle.jdbc.driver.OracleDriver";

try {

Class.forName(className);

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

// 擷取資料庫的連線物件

try {

conn = DriverManager.getConnection(url, username, password);

System.out.println("資料庫連接建立成功...");

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

// 返回連線物件

return conn;

}

public static void close(Connection c) {

if (c != null) {

try {

c.close();

} catch (Throwable e) {

e.printStackTrace();

}

}

}

public static void close(PreparedStatement c) {

if (c != null) {

try {

c.close();

} catch (Throwable e) {

e.printStackTrace();

}

}

}

}

2、主程式sql2excel

package sql2excel;

import java.io.File;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.List;

import jxl.Workbook;

import jxl.format.Colour;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

public class sql2excel {

public void createXLS() {

Connection conn = null;

PreparedStatement pstmt = null;

try {

conn = Dbutil.getConnection();

Date now = new Date(); 

SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");

String nowdate = df.format(now);

// 開啟檔案

WritableWorkbook book = Workbook.createWorkbook(new File(nowdate+".xls"));

// 產生名為"第一頁"的工作表，參數0表示這是第一

WritableSheet sheet = book.createSheet("第一頁", 0);

// 設定字型為宋體,16號字,加粗,顏色為黑色

WritableFont font1 = new WritableFont(

WritableFont.createFont("宋體"), 10, WritableFont.BOLD);

font1.setColour(Colour.BLACK);

WritableCellFormat format1 = new WritableCellFormat(font1);

format1.setAlignment(jxl.format.Alignment.CENTRE);

format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

// Label labelA = new Label(0, 0, "CALL_GUID", format1);

// Label labelB = new Label(1, 0, "RELATIONID", format1);

// Label labelC = new Label(2, 0, "ANI", format1);

// Label labelD = new Label(3, 0, "DNIS", format1);

// Label labelE = new Label(4, 0, "STAFF_ID", format1);

// Label labelF = new Label(5, 0, "CALLSTARTTIME", format1);

// Label labelG = new Label(6, 0, "CALLENDTIME", format1);

// Label labelH = new Label(7, 0, "CALLRESULT", format1);

// Label labelI = new Label(8, 0, "CALLRESULTREASON_ID", format1);

// Label labelJ = new Label(9, 0, "CALLREMARK", format1);

// Label labelK = new Label(10, 0, "EVENT_GUID", format1);

Label labelA = new Label(0, 0, "NAME", format1);

Label labelB = new Label(1, 0, "ADDRE", format1);

Label labelC = new Label(2, 0, "AGE", format1);

// 將定義好的儲存格添加到工作表中

// sheet.addCell(labelA);

// sheet.addCell(labelB);

// sheet.addCell(labelC);

// sheet.addCell(labelD);

// sheet.addCell(labelE);

// sheet.addCell(labelF);

// sheet.addCell(labelG);

// sheet.addCell(labelH);

// sheet.addCell(labelI);

// sheet.addCell(labelJ);

// sheet.addCell(labelK);

sheet.addCell(labelA);

sheet.addCell(labelB);

sheet.addCell(labelC);

// 建立先行編譯語句對象，一般都是用這個而不用Statement

PreparedStatement pre = null;

// 建立一個結果集對象

ResultSet result = null;

String sql = "select * from student";// 先行編譯語句

pre = conn.prepareStatement(sql);// 執行個體化先行編譯語句

result = pre.executeQuery();// 執行查詢，注意括弧中不需要再加參數

            //查詢資料庫中所有的資料

            List<StuEntity> list= StuService.getAllByDb();

while (result.next()) {

for (int i = 0; i < list.size(); i++) {

Label labelAi = new Label(0, i + 1, list.get(i).getName());

Label labelBi = new Label(1, i + 1, list.get(i).getAddre());

Label labelCi = new Label(2, i + 1, list.get(i).getAge());

sheet.addCell(labelAi);

sheet.addCell(labelBi);

sheet.addCell(labelCi);

}

}

// 寫入資料並關閉檔案

book.write();

book.close();

System.out.println("建立檔案成功!");

} catch (Exception e) {

System.out.println(e);

}

}

public static void main(String[] args) {

new sql2excel().createXLS();

}

}

3、StuEntity

package sql2excel;

public class StuEntity {

/**

* @author liuhaijun

* 

*/

private String name;

private String age;

private String addre;

public StuEntity(String name, String addre, String age) {

this.age = age;

this.name = name;

this.addre = addre;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getAddre() {

return addre;

}

public void setAddre(String Addre) {

this.addre = addre;

}

public String getAge() {

return age;

}

public void setAge(String age) {

this.age = age;

}

}

4、StuService

package sql2excel;

import java.io.File;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import jxl.Sheet;

import jxl.Workbook;

/**

 * @author liuhaijun

 * 

 */

public class StuService {

/**

* @return

*/

public static List<StuEntity> getAllByDb() {

List<StuEntity> list = new ArrayList<StuEntity>();

try {

Connection conn = null;

conn = Dbutil.getConnection();

// 建立先行編譯語句對象，一般都是用這個而不用Statement

PreparedStatement pre = null;

// 建立一個結果集對象

ResultSet result = null;

String sql = "select * from student";

pre = conn.prepareStatement(sql);// 執行個體化先行編譯語句

result = pre.executeQuery();// 執行查詢，注意括弧中不需要再加參數

while (result.next()) {

String name = result.getString("name");

String addre = result.getString("addre");

String age = result.getString("age");

list.add(new StuEntity(name, addre, age));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

}

本文出自 “資料庫攻城獅” 部落格，請務必保留此出處http://liu16.blog.51cto.com/6494747/1720706

java實現將oracle表中的資料匯出到excel表裡