最近項目需要讀取Excel，將數據導入數據庫保存，最終可以用報表讀取數據庫並顯示，比較簡單。查了一下兩者的資料，覺的也無所謂，我需要的功能比較簡單，只要能讀取Excel數據就行，最後決定使用POI，比較信任apache
使用果然很簡單，上網找了個源碼，Copy過來稍微改改就能用了。
下面付的代碼是一個Demo，功能點有兩個：一就是POI讀取Excel，二是DB2數據庫的連接和SQL執行。
現在的問題就是這樣做的話代碼太死，針對一張Excel表就要編寫一個專門的轉換類，太麻煩了，要考慮怎麼實現一個通用轉換類。
import Java.io.FileInputStream;
import Java.io.IOException;
import Java.sql.Connection;
import Java.sql.DriverManager;
import Java.sql.SQLException;
import Java.sql.Statement;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class POITest {
private static Connection conn = null;
private static Statement stmt = null;
private static boolean connectDB2() {
String url = "";
String username = "username";
String password = "passWord";
//加載驅動程序以連接數據庫
try {
//添加類庫驅動包db2jcc.jar和db2jcc_license_cu.jar
Class.forName("com.ibm.db2.jcc.DB2Driver");
url = "jdbc:db2://192.168.0.1:50000/dbname";
//添加類庫驅動包db2Java.jar
//Class.forName("com.ibm.db2.jdbc.app.DB2Driver").newInstance();
//url = "jdbc:db2:njtcdata";
conn = DriverManager.getConnection(url, username, passWord);
stmt = conn.createStatement();
}
//捕獲加載驅動程序異常
catch (ClassNotFoundException cnfex) {
System.err.println("裝載JDBC驅動程序失敗。");
cnfex.printStackTrace();
return false;
}
//捕獲連接數據庫異常
catch (SQLException sqlex) {
System.err.println("無法連接數據庫");
sqlex.printStackTrace();
//System.exit(1); // terminate program
return false;
}
return true;
}
private static boolean readExcelToDB2() {
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
try {
fs = new POIFSFileSystem(new FileInputStream("c:\test.xls"));
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
return false;
}
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = null;
HSSFCell cell = null;
String name = "";
int id = 0;
int rowNum, cellNum;
int i;
rowNum = sheet.getLastRowNum();
for (i = 0; i <= rowNum; i++) {
row = sheet.getRow(i);
//cellNum = row.getLastCellNum();
cell = row.getCell((short) 0);
name = cell.getStringCellValue();
cell = row.getCell((short) 1);
id = (int) cell.getNumericCellValue();
String sql = "insert into TEST(ID, NAME) values(" + id + ",'" + name + "')";
try {
stmt.executeUpdate(sql);
} catch (SQLException e1) {
e1.printStackTrace();
return false;
}
}
return true;
}
public static void main(String[] args) {
if (connectDB2()==true){
if (readExcelToDB2()==true)
System.out.println("數據導入成功");
else
System.out.println("數據導入失敗");
}
else{
System.out.println("數據庫連接失敗");
}
}
}
