簡介
在 developerWorks DB2 上本文的 先行篇 中，我們研究了 apache 的 POI 項目的開放源代碼產品。期間，我們開始在了解 SQL 的技術專家和通常將電子表格選作工具的商務專家之間架起一座橋梁。通過展示如何將數據庫數據傳送給 Excel，商務專家最終可以使用並操作鎖定在數據庫中的數據。
這次，我們將從另一個方向來搭建橋梁，即從 Excel 到 DB2® Universal Database TM。在該過程中，我們將進一步探索 Apache POI 項目的產品。正如您將看到的，apache 為我們提供的 API 允許您以單元格為單位讀取電子表格中的數據。
商務需求
商務專家並非軟件專家，他們通常必須依賴通用的工具。因為 Microsoft 的 Office 得到了十分普遍的安裝和使用，所以其電子表格應用程序 Excel 也得到了廣泛接受以用於簡單的數據存儲和操作。Microsoft Excel 允許非技術上的熟練用戶在易於使用且功能強大的基於 GUI 的應用程序中輸入以及操作數據。
然而，偶爾也必須將 Excel 電子表格中所駐留的數據適配於其他用途，將其進行廣泛分布、頻繁更新，或由一個大型用戶群來訪問。而這些功能都是 Excel 設計中所沒有提供的。另一方面，DB2 UDB 卻可以輕而易舉地達到這些目的。
最好是能夠利用 Microsoft Excel 中已開發的商務用戶的知識和技能 以及 關系數據庫存儲模型固有的功能和分布式特性。通過利用 apache 的 POI 和 HSSF 以及 JDBC TM 來促進 Excel 和 DB2 之間的集成，我們將逐漸實現這些目標。
您將學到什麼
在本文中，您將學習如何構建能將 Microsoft Excel 電子表格數據有效地傳送給 DB2 UDB 數據庫的應用程序。而 apache POI 項目中所提供的 API 將有助於讀取 Excel 電子表格。特別地，我們將使用 Apache POI 的 HSSF 組件。本文的目的並非是要取代完成樣本應用程序功能的其他方法，而是要進一步使讀者熟悉 apache 的 POI 項目。
雖然現在使用的是 DB2，但是我們將利用 JDBC 的產品以提供一個可移植的、非特定數據庫的無成本解決方案。並且通過利用 apache 的 POI 項目和 Java 平台，我們可以構建完全免費且極具靈活性的應用程序。
為什麼選擇 POI？
一個十分令人沮喪的事實就是難以與 Microsoft 的專有文件格式進行交互操作。但 apache Software Foundation 通過稱作 POI（POI 代表 Poor Obfuscation Implementation，即不良模糊化實現）的項目解決了該問題。POI 的目標就是提供一組 Java API 來使得基於 Microsoft OLE 2 Compound Document 格式的 Microsoft Office 文件易於操作。一些 POI API 僅僅是為最常用的 Microsoft Office 文件 Word 和 Excel 而開發的；而其他的 API 則是用於通用的 OLE 2 Compound Document 和屬性文件。Apache POI 網站提供了豐富的關於 POI 項目及其 API 的附加信息，其網址為 http://jakarta.apache.org/poi/index.Html。
DB2 和 Excel 之間的關系是通過 POI 組件 HSSF 來創建的，HSSF 代表 Horrible Spreadsheet Format（可怕的電子表格格式）。API 給程序員提供了極其容易地讀寫或操作 Microsoft Excel 97-2002 文件的能力。
POI 是一個開放源代碼項目，並且得到了數百名志願者的不斷更新。可以在 http://jakarta.apache.org/builds/jakarta-poi/ 上獲得源代碼和文檔。在撰寫本文之時，POI 的版本是 1.5.1，但是本文只會使用到 API 的基本功能。因此，任何更新的版本都應該可以工作。
HSSF 給表帶來什麼
集成 DB2 UDB 和 Microsoft Excel 有多種不同的方法。過去，最常用的方法就是編寫一次性的宏或 Visual Basic 代碼來利用 DB2 的 IMPORT 和 EXPORT 實用程序導出將要重新導入 DB2 的數據。該方法存在許多不足：
不滿足需求：只能將 Excel 和 DB2 松散集成，且需要仔細同步。
維護：易受 DB 模式更改或 Excel 格式的影響。
成本：需要程序員來實現。
利用：必須為每一個實例進行改寫。
自動化：無。
POI 組件 HSSF 就是用於讀寫以及操作 Microsoft Excel 電子表格的 API。使用 HSSF 將緩解過去解決方案中所出現的問題：
滿足需求：可創建使用 HSSF 的工具來自動化同步以及將 DB2 和 Excel 緊密集成。
維護：易受 DB 模式更改的影響，但獨立於 Excel 的功能。
成本：取決於所需功能，在編程工作上可能需要投入大量時間和金錢。
利用：利用整個 Java API 和 POI 志願者。
自動化：Java API 支持自動化。
雖然 POI 顯著地改善了這一情況，但是 IBM 已經做了大量外勤工作並且完成了稱作 DB2 Office Connect 的產品。該產品擁有一個豐富的功能集，用以支持商務用戶以一種容易且直觀的方式與 Microsoft Office 數據進行交互。雖然其優點眾多而無法一一列舉，但使用該產品在以下幾方面也具有一些相對優勢：
滿足需求：將 DB2 和 Excel 高度集成，且具同步、報表編制和自動化等特性。
維護：IBM 通過數據庫模式抽象和通用方法為您代勞。
成本：商務用戶變成開發者，且無需 Java 、SQL 或 DB2 的知識。
利用：充分利用 Microsoft Office、商務用戶以及現有的應用程序。
自動化：幾乎將所有 Excel 生成和分布任務自動化。
關於 Office Connect 的更多信息，請參閱文章 ClIEnt-side Information Integration using Office Connect。
然而，我們這裡的目標是使您熟悉 POI 和 HSSF 的功能，以便當其適合於支持 Java 應用程序和 Excel 的集成時，可以使用並實現它。為此，我們將使用 HSSF 的一些基本功能。有必要注意的是，HSSF 提供了控制格式（顏色、邊界、字體等）和元數據的功能。而我們所使用的 API 將不會利用這些高級功能。
安裝和運行該應用程序
此處提供的樣本代碼將從 Excel 電子表格讀取數據並創建包含該數據的數據庫表。您將運行的該應用程序的主類位於 ExcelDB2.Java 中。大致查看一下下一節中所描述的代碼以深入了解它是如何工作的是一個好主意。
確保您的環境設置如下：
您在 Microsoft Windows® 環境中運行 DB2 8.1。DB2 的早期版本可能也可運行，但不保證一定能夠運行。
將 db2java.zip 和 POI jar 文件添加到 Java 類路徑中。 db2Java.zip 包含與您的 DB2 數據庫進行交互所需要的 JDBC API。
將 POI jar 安裝並添加到 Java 類路徑中。POI jar 文件包含您將用以讀取 Microsoft Excel 文件的 Apache POI 包（ org.apache.poi.hssf.usermodel ）。
通過使用諸如 IBM Websphere® Studio Application Developer Version 5.0 這樣的現代 IDE，可顯著地簡化應用程序的開發、安裝和運行。該應用程序是通過 Application Developer 所提供的功能來開發的。Application Developer 允許您快速地完成和運行代碼，並且支持您正確地理解它是如何工作的。
一步步執行該應用程序
如果閱讀了本文的 先行篇，您會發現該應用程序與之十分類似。否則，或許值得您去讀一下那篇文章。正如您將看到的，也是真正的面向對象的程序員應該做的，我們重用了在其姊妹應用程序中所寫的大量代碼。
在第一次激活 ExcelDB2 時，您將需要向正要寫入的數據庫驗證自己，見 圖 1。該應用程序的 GUI 組件是使用 Java Swing TM 構建的。而應用程序的登錄部分則是由 LoginDialog.java 和 LoginService.java 的代碼處理的。登錄服務調用 SQLFacade.Java 。整個應用程序都會用這個類來與 DB2 數據庫進行交互。其工作是要將程序員從直接使用 JDBC API 中抽身出來。如果用戶提供了必要的憑證和連接信息，SQLFacade 對象就會被實例化，而應用程序則繼續運行。否則，您將收到錯誤消息並且被要求重新輸入這些值。
圖 1. 登錄數據庫
在成功登錄之後，將出現一個屏幕，您將在其中提供兩個輸入：要導入的 Excel 電子表格文件和要在目標數據庫中創建的表名（默認為 New_Table），見 圖 2。
圖 2. 等待選擇電子表格
按下“Load My Spreadsheet”按鈕將彈出 FileChooser 對話框，見 圖 3，您可以從中選擇要加載的 Excel 文件。
圖 3. 選擇 Excel 電子表格
一旦選擇了 Excel 電子表格，就會向您提供將要創建的表的預覽，見 圖 4。注意，現在“Table to Create”為“Excel_TEST”。該值是通過 HSSF 從工作表的命名選項卡面板中動態提取的，其中還包括了行號。這些行號僅僅用於預覽目的，而不會被寫入數據庫。
圖 4. 表的預覽
此時，apache 的 POI 正完成該過程中最為繁瑣的工作，所以我們值得再看一看這裡的進展如何。從電子表格中取數據的過程是由 POI/HSSF 在 SpreadsheetTableModel.Java 類的 reloadSpreadsheetModel 方法中處理的。 清單 1顯示了相關部分的代碼。
清單 1.
// Use POI to read the selected Excel Spreadsheet
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(fileName));
// Extract the name of the first worksheet and use this for the tableName
tableName = wb.getSheetName(0);
// Select the first worksheet
HSSFSheet sheet = wb.getSheet(tableName);
try {
clearAll();
updateColumnModel(fileName);
// Use the HFFS functions for the number of rows &
// columns, instead of computing them ourselves
// System.out.println("Getting Spreadsheet Dimensions...");
rowCount = sheet.getPhysicalNumberOfRows();
colCount = sheet.getRow(0).getPhysicalNumberOfCells();
// Skip the first row, the column names are extracted
// from this row.
for(int i = 1; i < rowCount; i++) {
// Get row number i
HSSFRow row = sheet.getRow(i);
// Store the row in a list
ArrayList list = new ArrayList();
for(short j = 0; j <colCount; j++) {
// Add each cell to the row
list.add(row.getCell(j));
}
// Store the row in a list of lists
result.add(list);
}
// Remove one row from the rowCount, since the first row
// is assumed to be the column names
rowCount--;
fireTableStructureChanged();
// Catch all Exceptions, most likely a POI error
} catch (Exception e) {
System.out.println("A POI error has occured.");
e.printStackTrace();
}
該代碼的操作十分簡單。首先將 HSSF 用於打開選中的電子表格，然後使用嵌套的 for 循環來一行行、一個個單元格地掃描電子表格。接著 HSSF 存取器方法 getRow 和 getCell 利用一系列的 list 結構在內存中構建一個表。我們還使用 HSSF 所提供的方法來確定行和列的數目，而諸如 getSheetName 和 getSheet 的各種內務方法則用於區分要掃描的工作表。而 SpreadsheetTableModel 類的 updateColumnModel 方法將用於提取電子表格的列名及其類型。該方法的工作方式與 reloadSpreadsheetModel 方法相似，但是它只需要查看第一行以獲得列名以及查看下一非空行以獲得列的類型，該方法是直接由 HSSF 提供的，這消除了猜測。
留心的讀者還將注意到其代碼中提供了另一種與 清單 1 中代碼實現相似的備選方法。在 SpreadsheetTableModel 中，兩個方法 reloadSpreadsheetModelAlternate 和 updateColumnModelAlternate 封裝了使用 HSSF/POI 所提供的行和列迭代器的方法。這些備選方法的操作也十分簡單。嵌套的 while 循環使用迭代器來一行行、一個個單元格地掃描電子表格。在我們需要這些數據之前，還是由一系列的 list 結構來存儲它們。我們將其作為練習留給讀者來檢查代碼中的差異。
當我們將數據置於數據庫中時，Excel 電子表格中所有的格式和元數據都將丟失。因此，一旦將數據傳送給 SpreedsheetTableModel，就可以忽略格式和元數據了。如果您打算使用 POI/HSSF 來執行 DB2 到 Excel 的轉換，那麼這些特性可能就占有重要地位了。
此時，可以將 Table to Create這個可編輯的文本域更改為任何名字，但要警告您：如果該表的名字已經存在，則將產生錯誤，見 圖 5。如果需要，我們可以刪除任何現有的表，但是這樣做是有危險的，所以我們選擇保守一點並且總是強制用戶創建一個新表。如果真的需要覆蓋現有的表，您可以使用 DB2 提供的管理工具手工來完成。
圖 5. DB 表“Excel_TEST”已經存在
要向數據庫提交一個表，用戶只需選擇“Write DB Table”按鈕。此時， DBTableGenerator.java 中一個以線程運行的方法開始讀取 Excel 數據，創建新的數據庫表以及通過 JDBC 將行插入表中。類 DBTableGenerator.Java 在一個線程模型中處理該任務，是為了允許用一個進度條來提供說明一切運行正常的可視反饋。
我們讀取以前由 HSSF 從一個個單元格中收集的 Excel 數據並且不要管對類 SQLFacade.java 的 SQL 調用。JDBC 和 SQL 的使用需要我們為每一行的處理構造 INSERT 語句，這將使用已顯示的大多數方法。所有困難的數據庫工作都由類 SQLFacade.Java 通過 JDBC API 執行 DB 更新來加以處理。
您可以在 清單 2 中顯示的 DBTableGenerator.Java 類的 run 方法中找到促進數據庫表創建過程的代碼。
清單 2.
// Create the Table with the name and credentials provided
// Calculate the number sql calls we will complete to track progress
int goal = (spreadsheetModel.getRowCount() + 1) * 2;
int progressToGoal = 0;
// call constructor to create a progress bar
DBTableGenerator frame = new DBTableGenerator(goal, spreadsheetName);
frame.addWindowListener(new WindowAdapter() {
public void windowClosing(WindowEvent e) {
// Allow close of progress window
}
});
frame.pack();
frame.centerDialog();
frame.setVisible(true);
// Create the new table by calling the standard Create SQL
// Ex CREATE TABLE ADMIN.EMAIL_ADDRESSES
// ( NAME CHARACTER (100) , EMAIL CHARACTER (100) ) ;
sqlCreateQuery = "CREATE TABLE " + sqlFacade.getUser() + "." +
spreadsheetName + " ( ";
String sqlColumnNames = " ( ";
// Make sure to neglect the first column, which was used for
// placeholder row numbers
for(int col = 0; col < (spreadsheetModel.getColumnCount()-1); col++) {
if (col>0) {
sqlCreateQuery += ", ";
sqlColumnNames += ", ";
}
sqlCreateQuery += spreadsheetModel.getColumnNames().get(col);
sqlColumnNames += spreadsheetModel.getColumnNames().get(col);
switch(((Integer)(spreadsheetModel.getColumnTypes().get(col))).intValue()) {
// Convert numeric types the equivalent floats
// (guaranteed to work for all number types)
case HSSFCell.CELL_TYPE_NUMERIC: sqlCreateQuery += " FLOAT "; break;
// No need to convert strings
case HSSFCell.CELL_TYPE_STRING: sqlCreateQuery += " CHARACTER (100) "; break;
// Convert formulas to their string representations
case HSSFCell.CELL_TYPE_FORMULA: sqlCreateQuery += " CHARACTER (100) "; break;
// No need to convert blank cells
case HSSFCell.CELL_TYPE_BLANK: sqlCreateQuery += " CHARACTER (100) "; break;
// We have covered all the cell types POI/HSSF produce above,
// but just in case we will provide error handling for any others by
// throwing an exception.
default:
System.out.println("Current SQL statement: " + sqlCreateQuery);
throw new SQLException();
}
}
sqlCreateQuery += " ); ";
sqlColumnNames += " ) ";
// update the progress bar
frame.current.setValue(1);
// Run the sql query
sqlFacade.executeUpdate(sqlCreateQuery);
// update the progress bar
frame.current.setValue(2);
progressToGoal = 2;
// Populate the new DB table row-by-row from the spreadsheetModel
// data, converting each column as appropriate
for(int row = 0; row < spreadsheetModel.getRowCount(); row++) {
sqlRowInsertQuery = "INSERT INTO " + sqlFacade.getUser()
+ "." + spreadsheetName + sqlColumnNames + " VALUES (";
for(int col = 0; col < spreadsheetModel.getColumnCount()-1; col++) {
if (col > 0) {
sqlRowInsertQuery += ", ";
}
sqlRowInsertQuery += "'" +
((HSSFCell)(spreadsheetModel.getRow(row).get(col))).getStringCellValue() + "'";
}
sqlRowInsertQuery += " ); ";
// update the progress bar
progressToGoal++;
frame.current.setValue(progressToGoal);
// Run the sql query
sqlFacade.executeUpdate(sqlRowInsertQuery);
progressToGoal++;
frame.current.setValue(progressToGoal);
}
對於這個簡單的應用程序，我們沒有做很多工作來推斷數據庫的列類型，而是使用了可涵蓋多數情況的通用類型。我們還使用了一個包括電子表格數據的低效的多重單行本，用以將不同的讀取和寫入操作分離。雖然缺乏高效性，但是它允許我們更好地說明 HSSF 的使用，而這正是我們本篇文章的首要目標。
您的勞動成果
如上文提到的那樣，會向用戶顯示一個進度條（見 圖 6），讓他們知道正在生成數據庫表。
圖 6. 進度條
然而，僅僅為了證明我們已經完成了所承諾的工作，請讓我們在 DB2 的 Control Center GUI 中取出新的表，您可以使用 db2cc.exe 命令來啟動 Control Center GUI。一旦啟動之後，就可以使用導航窗格來找到您剛剛創建的表，本例中就是 EXCEL_TEST。選擇該表來查看其樣本內容或僅運行一條 SQL 查詢，然後，您就應該看到一個與在 Excel 中樣子相似的表，如下列屏幕快照 圖 7和 圖 8所示。
圖 7. 完成所有工作
圖 8. 與最初樣子相同
結束語
本文中，我們構建了從 Microsoft Excel 電子表格中讀取數據並且寫入 DB2 的應用程序。該應用程序利用了 Java、JDBC 和來自於 apache POI 項目的產品。因為您可以訪問該應用程序的源代碼，所以您也可以進行修改以便定制符合您的組織需求的應用程序。
apache 的 POI 是一個極佳的用於操作 Microsoft Office 文檔的工具，並且要歸功於開放源代碼團體。通過 API，可以構建滿足您企業需求的應用程序。
本文示例源代碼或素材下載
