从Excel电子表格生成外部表

   假定：您从经理那里收到一封附带 Excel 电子表格的电子邮件。您的任务是什么？将电子表格中的数据载入到公司的 9i/10g 数据库中。您将会如何进行？你要编写一个自定义的应用程序来装载数据吗？你要使用 SQL*Loader 实用程序吗？同时还要考虑到 — 该电子表格包含几个数据工作表。首先您必须以利用分隔符来保存每个工作表。事情比乍看上去时愈加复杂了……

   DBA 和应用程序开发人员经常要将数据从 Excel 电子表格装载到 数据库中。随着 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 使用纯 语言实现 OLE2 复合文档格式，而 HSSF API 允许使用 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），以便实际地创建目录和外部表。

 

[1]   

【责编:Peng】

--------------------next---------------------