使用 Apache 的 POI 和 HSSF 将 Excel 电子表格数据加载到 DB2

2004 年 3 月 01 日

在本文中，您将学习如何使用 Apache 的 POI 和 HSSF 构建能将 Microsoft Excel 电子表格数据有效地传送给 DB2 UDB 数据库的应用程序。

简介

在 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，可以构建满足您企业需求的应用程序。