使用 Apache Jakarta POI 从 Excel 电子表格生成外部表
作者 Casimir Saternos
利用开放源代码技术和外部表，将数据从 Excel 电子表格装载到 Oracle 中。
本文相关下载
假定：您从经理那里收到一封附带 Excel 电子表格的电子邮件。您的任务是什么？将电子表格中的数据载入到公司的 Oracle 9i/10g 数据库中。您将会如何进行？你要编写一个自定义的应用程序来装载数据吗？你要使用 SQL*Loader 实用程序吗？同时还要考虑到 — 该电子表格包含几个数据工作表。首先您必须以利用分隔符来保存每个工作表。事情比乍看上去时愈加复杂了……
DBA 和应用程序开发人员经常要将数据从 Excel 电子表格装载到 Oracle 数据库中。随着 Oracle 10g 引入了一些类似电子表格的功能，更多当前在电子表格中存储和查看的数据很可能会装载到 Oracle 数据库中进行操作和处理。
Oracle 提供了多种将数据从电子表格装载到数据库中的方法。大多数 Oracle DBA 和开发人员熟悉 SQL*Loader 的功能。Oracle HTML DB 也可用于以批处理方式装载 Excel 数据。本文提供另一种从 Excel 电子表格装载数据的方法，它利用了开放源代码技术和外部表。
在本文中，您将了解如何使用 Apache Jakarta POI 开放源代码项目来创建外部表，这些外部表可以引用带多个数据工作表的 Excel 电子表格中的数据。在这一过程中，您将要创建一个名为 ExternalTableGenerator 的自定义实用程序，以达到该目的。
如果给定一个包含一个或多个数据工作表的 Excel 电子表格，则 ExternalTableGenerator 实用程序可以生成纯文本数据文件和一个 DDL 脚本，用户可以运行它，可以在外部表中查看这些数据。此项目还有助于向那些刚接触该特性的人阐明外部表的概念。
本文包含 ExternalTableGenerator 的源代码（可以用作独立的应用程序或者作为更复杂和更强健的解决方案的基础），还包含一个用于测试该过程的示例电子表格。
Jakarta POI
Jakarta POI 文件系统 API 使用纯 Java 语言实现 OLE2 复合文档格式，而 HSSF API 允许使用 Java POI 进行 Excel 文件的读写操作，这些操作包含示例代码文件中，并在您运行 ExternalTableGenerator 时在 Java CLASSPATH 中对它们进行引用。
ExternalTableGenerator ExternalTableGenerator 使用了三个类，这些类利用 POI API 来处理电子表格。
ExternalTable.java — 该类表示外部表。ExternalTable 对象拥有名称，引用文件系统中的目录和文件，并拥有很多相应类型的列。该类还拥有属性，这些属性描述了错误文件名、日志文件名、目录位置以及用于各种文件名扩展的常量。该类由这些属性值填充，然后用于生成外部表使用的 DDL，该外部表对应于电子表格中给定工作表的结构。
构造器使用一个参数以获取表名称（名称中的所有空格都被替换为下划线）。ExternalTableGenerator 类使用特定工作表的名称来设置该类中的 name 属性。该 name 用于表名以及错误文件和日志文件。当 ExternalTable 类的所有列和其他属性都完成填充后（基于电子表格中的前两行），可以调用 getDdl()，返回用于创建外部表的 DDL。 DDL 的结构在本文的“DDL 脚本”一节中予以说明。
ExternalTableColumn.java — 该类表示外部表的一列。其属性包括列名及其类型。就当前的应用程序来看，该类型被限制为 VARCHAR2 或 NUMBER，而且使用 POI 为这些类型所提供的常量。VARCHAR2 长度由工作表中第二行的值所确定。在由 ExternalTableGenerator 类调用时，列名由工作表中的第一行所确定。该类将列中的任何空格替换为下划线，以确保 DDL 有效。
ExternalTableGenerator.java — 该类执行实际操作。它将读取以参数形式传递给它的电子表格。对于电子表格中的每个工作表，它在当前目录中生成一个以逗号对值进行分隔的文件（扩展名为 .csv）。它还创建出用于创建目录和外部表的 DDL。
处理过程必要的 POI 专用调用有以下：
在 execute() 方法中，以下的两行代码用于从文件系统中访问电子表格，并创建一个新的工作单对象，该对象允许您操作电子表格。
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(spreadsheet));
HSSFWorkbook wb = new HSSFWorkbook(fs);
访问 HSSFWorkbook 对象时，您可以通过迭代所有的工作表、行和列来处理该对象。processWorkbook() 方法迭代工作单中的每个工作表，使用工作表名来创建 ExternalTable 对象，处理每个工作表，并提取相关数据来填充 ExternalTable 对象。
private void processWorkbook(HSSFWorkbook wb) {
for (int i = 0; i < wb.getNumberOfSheets(); i++)
{
HSSFSheet sheet = wb.getSheetAt(i);
ExternalTable table = new ExternalTable(wb.getSheetName(i));
processSheet(sheet, table);
System.out.println("...Table "+ table.getName()+ " processed." );
}
}
processSheet() 方法从工作表中获取该表信息，写入一个 .csv 文件（它将是 ExternalTable 引用的实际数据），并连续追加到含有 DDL 内容的字符串中。
getColumns() 方法包含对 POI API 的相关调用，用于检索特定单元的数据。根据被访问单元的类型，需要不同的方法调用。请注意，在处理我们示例中的数据时，必须考虑那些没有数据的单元 (SSFCell.CELL_TYPE_BLANK)。
要将数据写入某个工作表，writeCsv() 需要迭代相关的行和列，并创建一个字符串，其中包含逗号分隔的数据。它不写出列名或者含有表示 VARCHAR2 大小的数据的行。write() 方法包含了将数据写入文件系统的文件中的代码。
您必须通过 SQL*Plus 来单独运行 DDL 脚本（名为 ExternalTables.sql），以便实际地创建目录和外部表。
外部表
从 Oracle 9i 开始，外部表已经成为 SQL*Loader 的一种替换方法。与传统的数据库表不同，外部表是引用那些存储在数据库之外的数据的只读表。这些表能够以标准 Oracle 表的相同方式进行查询，并且经常作为较大型 ETL（提取/转换/装载）过程的早期阶段。外部表非常有助于引用数据库外的数据，但是创建外部表需要相当多的 DDL 代码，以创建引用数据文件的表、表的列定义和其他文件（错误文件和日志文件）。
创建外部表需要了解数据文件的文件格式和记录格式，还要了解 SQL。ExternalTableGenerator 自动执行该过程，并提供对于创建外部表（它们访问带分隔符的文件）的语法的一些见解。
DDL 脚本
以下是对 ExternalTableGenerator 所生成的 DDL 脚本的简要描述，将把示例电子表格作为一个参数来运行此脚本：
CREATE OR REPLACE DIRECTORY load_dir AS 'C:\workspaces\test\XL2ExternalTables'
;
CREATE OR REPLACE DIRECTORY 语句创建一个目录对象，该对象允许 Oracle 访问操作系统目录。该目录包含外部表所引用的数据文件以及错误文件和日志文件。ExternalTableGenerator 使用当前工作目录作为所引用的操作系统目录。
CREATE TABLE PA_Zip_Code_Locations
(
ZIPVARCHAR2(10),
AREANAME VARCHAR2(30),
LATITUDE NUMBER,
LONGITUDE NUMBER,
POPULATION NUMBER
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY load_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
badfile load_dir:'PA_Zip_Code_Locations.bad'
logfile load_dir:'PA_Zip_Code_Locations.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
ZIP,
AREANAME,
LATITUDE,
LONGITUDE,
POPULATION
))
LOCATION ('PA_Zip_Code_Locations.csv')
)REJECT LIMIT UNLIMITED;
CREATE TABLE 语法和列定义看上去类似创建常规 Oracle 表的典型 DDL 语句。ORGANIZATION EXTERNAL 将此表确定为外部表。TYPE 子句用于指定驱动程序类型。随后的子句描述了文件的结构以及日志文件、错误文件和数据文件的位置。有关 EXTERNAL TABLE 语法的更多信息，请参见 Oracle 文档。
ExternalTableGenerator 根据以下规则生成 DDL 的以下片断：
表的名称基于给定工作表的名称。工作表的名称还用于确定错误文件、日志文件以及外部表所引用的数据文件的名称。
列的名称基于工作表第一行中的值。这些名称还用于外部表的 FIELDS 定义部分。
VARCHAR2 的长度由电子表格中的第二行所指定。
列类型被定义为 VARCHAR2 或 NUMBER，这取决于电子表格随后一行中的单元。
因为每个 .xls 工作表都是导出以逗号对值进行分隔 (.csv) 文件，所以其中包含了 RECORDS DELIMITED BY NEWLINE 和 FIELDS TERMINATED BY ',' 信息。
new_departments 和 new_employees 表具有与以上所示的 pa_zip_code_locations 表相同的基本结构。
Excel 电子表格
需要装载的数据经常具有由逗号、线段或制表符分隔数值的简单格式，而装载这些数据是相当简单直接的任务。但是，数据可以来自可能包含多个工作表的 Excel 电子表格。电子表格以二进制格式存储，不能直接利用 SQL*Loader 或外部表进行访问。Excel 电子表格优于传统纯文本文件的一个好处是可以对数据分类（分为数值型、字符串型等等）。如上所述，您可以在 ExternalTableGenerator 中利用这一情况来确定列类型。
对于 ExternalTableGenerator 所处理的电子表格有一些要求：
它们可以包含一个或多个电子表格。
电子表格的名称用于表名定义。外部表名、数据文件名、日志文件名和错误文件名由电子表格的名称指定。
电子表格中的每个工作表用于生成外部表 DDL 的一个单元块和一个 .csv 数据文件。
每个电子表格中的第一行是列名定义行。列的名称用于 EXTERNAL TABLE 单元块的列名和字段名部分。该行不包括在 .csv 数据文件中。
每个电子表格中的第二行用于确定 VARCHAR2 字段的长度。此行也不包括在 .csv 数据文件中。
由于您使用电子表格的各部分来确定表名和列名，请谨慎选择那些能够生成有效 SQL 的名称。换言之，避免由 ExternalTableGenerator 处理电子表格中的非法字符、SQL 关键字、现有的表名以及类似内容。
运行该项目不需要 Excel，但是查看 .xls 文件需要它。示例数据的屏幕快照如下：
示例中的数据表示需要添加到数据库中的两个新部门、其员工以及相关的邮政编码列表。员工和部门数据基于 scott/tiger 模式表，如果您向 scott 授予所有必需的权限，则可以导入这些数据以供实验。邮政编码信息表明 ExternalTableGenerator 能够处理更多的数据。
运行该项目
该项目在 Java 运行时环境 (JRE) 1.4.2_03 中完成了测试，但是它应该可以在任何能够运行 POI 的 JRE 中运行。您可以通过执行 runSample.bat 来运行 ExternalTableGenerator。这个批处理文件使用了在参数中所指定的 Excel 工作表，将相关的 jar 包含在类路径中 (XL2ExternalTables.jar;jakarta-poi-1.5.1-final-20020615.jar;jakarta-poi-contrib-1.5.1-final-20020615.jar) 并运行适当的 Java 类 com.saternos.database.utilities.ExternalTableGenerator。
示例成功运行后所得到的输出应该类似于以下内容（其路径基于您的工作目录）。
C:\Documents and Settings\Administrator\Desktop\XL2ETB>runExample
C:\Documents and Settings\Administrator\Desktop\XL2ETB>
java -cp XL2ExternalTables.jar;jakarta-poi-1.5.1-final-20020615.jar;
jakarta-poi-contrib-1.5.1-final-20020
615.jar com.saternos.database.utilities.ExternalTableGenerator new_department_data.xls
Begin processing.
Using working directory C:\Documents and Settings\Administrator\Desktop\XL2ETB
...File PA_Zip_Code_Locations.csv created.
...Table PA_Zip_Code_Locations processed.
...File New_Departments.csv created.
...Table New_Departments processed.
...File New_Employees.csv created.
...Table New_Employees processed.
...File ExternalTables.sql created.
Processing complete.
这些代码创建了三个数据文件（指定使用 .csv 扩展名）并生成一个的 SQL 脚本，该脚本包含用于创建外部表的 DDL。
以下是运行该 DDL 脚本并测试所得结果的一个示例。通过 SQL*Plus 从创建 ExternalTables.sql 脚本的目录连接到数据库，开始进行工作。
C:\XL2ETB>SQL*Plus testuser/mypassword@orcladm
SQL*Plus:Release 10.1.0.2.0 - Production on Tue Dec 21 09:36:25 2004
Copyright (c) 1982, 2004, Oracle.All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from tab;
no rows selected
虽然本示例是一个空的模式，但是如果用户拥有适当的权限，并且没有名称冲突，则该脚本可以在任何模式中运行。
SQL> @ExternalTables
Directory created.
Table created.
Table created.
Table created.
该脚本被执行，并且创建了目录对象和三个外部表。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
NEW_DEPARTMENTS TABLE
NEW_EMPLOYEES TABLE
PA_ZIP_CODE_LOCATIONS TABLE
SQL> select count(*) from new_departments;
COUNT(*)
----------
2
SQL> select count(*) from new_employees;
COUNT(*)
----------
14
SQL> select count(*) from pa_zip_code_locations;
COUNT(*)
----------
1458
SQL> select * from new_employees;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ---------- ------------ -------- ---------- ----------
9499 ALDEN SALESMAN 9698 33289.0 3200 1300 50
9521 WORD SALESMAN 9698 33291.0 2500 1000 50
9654 MALTIN SALESMAN 9698 33509.0 2500 4400 50
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ---------- ------------- -------- ---------- ----------
9844 TURKER SALESMAN 9698 33489.0 3000 0 50
SQL>
结论
本文介绍了 POI，但只是浅显地讨论了通过该 API 可以使用的功能。有多种方法可以增强本文中所描述的 ExternalTableGenerator 实用程序的功能：
在该实用程序中可以建立一个 JDBC 连接，使外部表 DDL 能够在该实用程序内部执行。
可以添加基于电子表格类型的其他列类型和精度。
该实用程序可以添加功能以便操作外部表的其他属性（PARALLEL 等）。
可以设计出将电子表格类型转换为 Oracle 数据类型的其他方法。
本文有可能使您更希望探索通过协同使用 POI 和 Oracle 来促进在 Excel 与 Oracle 之间进行数据操作的可能性。
Casimir Saternos 是 Oracle 认证 DBA、IBM 认证企业开发人员和 Sun 认证 Java 程序员，住在宾夕法尼亚州 Allentown。
