從Excel電子表格生成外部表

2024-07-21 02:39:10

字體：大中小

供稿：網(wǎng)友

假定：您從經(jīng)理那里收到一封附帶 Excel 電子表格的電子郵件。您的任務(wù)是什么？將電子表格中的數(shù)據(jù)載入到公司的 Oracle 9i/10g 數(shù)據(jù)庫中。您將會(huì)如何進(jìn)行？你要編寫一個(gè)自定義的應(yīng)用程序來裝載數(shù)據(jù)嗎？你要使用 SQL*Loader 實(shí)用程序嗎？同時(shí)還要考慮到 — 該電子表格包含幾個(gè)數(shù)據(jù)工作表。首先您必須以利用分隔符來保存每個(gè)工作表。事情比乍看上去時(shí)愈加復(fù)雜了…… DBA 和應(yīng)用程序開發(fā)人員經(jīng)常要將數(shù)據(jù)從 Excel 電子表格裝載到 Oracle 數(shù)據(jù)庫中。隨著 Oracle 10g 引入了一些類似電子表格的功能，更多當(dāng)前在電子表格中存儲和查看的數(shù)據(jù)很可能會(huì)裝載到 Oracle 數(shù)據(jù)庫中進(jìn)行操作和處理。 Oracle 提供了多種將數(shù)據(jù)從電子表格裝載到數(shù)據(jù)庫中的方法。大多數(shù) Oracle DBA 和開發(fā)人員熟悉 SQL*Loader 的功能。Oracle Html DB 也可用于以批處理方式裝載 Excel 數(shù)據(jù)。本文提供另一種從 Excel 電子表格裝載數(shù)據(jù)的方法，它利用了開放源代碼技術(shù)和外部表。在本文中，您將了解如何使用 Apache Jakarta POI 開放源代碼項(xiàng)目來創(chuàng)建外部表，這些外部表可以引用帶多個(gè)數(shù)據(jù)工作表的 Excel 電子表格中的數(shù)據(jù)。在這一過程中，您將要?jiǎng)?chuàng)建一個(gè)名為 ExternalTableGenerator 的自定義實(shí)用程序，以達(dá)到該目的。假如給定一個(gè)包含一個(gè)或多個(gè)數(shù)據(jù)工作表的 Excel 電子表格，則 ExternalTableGenerator 實(shí)用程序可以生成純文本數(shù)據(jù)文件和一個(gè) DDL 腳本，用戶可以運(yùn)行它，可以在外部表中查看這些數(shù)據(jù)。此項(xiàng)目還有助于向那些剛接觸該特性的人闡明外部表的概念。本文包含 ExternalTableGenerator 的源代碼（可以用作獨(dú)立的應(yīng)用程序或者作為更復(fù)雜和更強(qiáng)健的解決方案的基礎(chǔ)），還包含一個(gè)用于測試該過程的示例電子表格。 Jakarta POI Jakarta POI 文件系統(tǒng) API 使用純 java 語言實(shí)現(xiàn) OLE2 復(fù)合文檔格式，而 HSSF API 答應(yīng)使用 Java POI 進(jìn)行 Excel 文件的讀寫操作，這些操作包含示例代碼文件中，并在您運(yùn)行 ExternalTableGenerator 時(shí)在 Java CLASSPATH 中對它們進(jìn)行引用。 ExternalTableGenerator ExternalTableGenerator 使用了三個(gè)類，這些類利用 POI API 來處理電子表格。

ExternalTable.java — 該類表示外部表。ExternalTable 對象擁有名稱，引用文件系統(tǒng)中的目錄和文件，并擁有很多相應(yīng)類型的列。該類還擁有屬性，這些屬性描述了錯(cuò)誤文件名、日志文件名、目錄位置以及用于各種文件名擴(kuò)展的常量。該類由這些屬性值填充，然后用于生成外部表使用的 DDL，該外部表對應(yīng)于電子表格中給定工作表的結(jié)構(gòu)。構(gòu)造器使用一個(gè)參數(shù)以獲取表名稱（名稱中的所有空格都被替換為下劃線）。ExternalTableGenerator 類使用特定工作表的名稱來設(shè)置該類中的 name 屬性。該 name 用于表名以及錯(cuò)誤文件和日志文件。當(dāng) ExternalTable 類的所有列和其他屬性都完成填充后（基于電子表格中的前兩行），可以調(diào)用 getDdl()，返回用于創(chuàng)建外部表的 DDL。 DDL 的結(jié)構(gòu)在本文的“DDL 腳本”一節(jié)中予以說明。
ExternalTableColumn.java — 該類表示外部表的一列。其屬性包括列名及其類型。就當(dāng)前的應(yīng)用程序來看，該類型被限制為 VARCHAR2 或 NUMBER，而且使用 POI 為這些類型所提供的常量。VARCHAR2 長度由工作表中第二行的值所確定。在由 ExternalTableGenerator 類調(diào)用時(shí)，列名由工作表中的第一行所確定。該類將列中的任何空格替換為下劃線，以確保 DDL 有效。
ExternalTableGenerator.java — 該類執(zhí)行實(shí)際操作。它將讀取以參數(shù)形式傳遞給它的電子表格。對于電子表格中的每個(gè)工作表，它在當(dāng)前目錄中生成一個(gè)以逗號對值進(jìn)行分隔的文件（擴(kuò)展名為 .csv）。它還創(chuàng)建出用于創(chuàng)建目錄和外部表的 DDL。

處理過程必要的 POI 專用調(diào)用有以下： 在 execute() 方法中，以下的兩行代碼用于從文件系統(tǒng)中訪問電子表格，并創(chuàng)建一個(gè)新的工作單對象，該對象答應(yīng)您操作電子表格。
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(sPReadsheet));HSSFWorkbook wb = new HSSFWorkbook(fs);訪問 HSSFWorkbook 對象時(shí)，您可以通過迭代所有的工作表、行和列來處理該對象。processWorkbook() 方法迭代工作單中的每個(gè)工作表，使用工作表名來創(chuàng)建 ExternalTable 對象，處理每個(gè)工作表，并提取相關(guān)數(shù)據(jù)來填充 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() 方法從工作表中獲取該表信息，寫入一個(gè) .csv 文件（它將是 ExternalTable 引用的實(shí)際數(shù)據(jù)），并連續(xù)追加到含有 DDL 內(nèi)容的字符串中。 getColumns() 方法包含對 POI API 的相關(guān)調(diào)用，用于檢索特定單元的數(shù)據(jù)。根據(jù)被訪問單元的類型，需要不同的方法調(diào)用。請注重，在處理我們示例中的數(shù)據(jù)時(shí)，必須考慮那些沒有數(shù)據(jù)的單元 (SSFCell.CELL_TYPE_BLANK)。要將數(shù)據(jù)寫入某個(gè)工作表，writeCsv() 需要迭代相關(guān)的行和列，并創(chuàng)建一個(gè)字符串，其中包含逗號分隔的數(shù)據(jù)。它不寫出列名或者含有表示 VARCHAR2 大小的數(shù)據(jù)的行。write() 方法包含了將數(shù)據(jù)寫入文件系統(tǒng)的文件中的代碼。您必須通過 SQL*Plus 來單獨(dú)運(yùn)行 DDL 腳本（名為 ExternalTables.sql），以便實(shí)際地創(chuàng)建目錄和外部表。 外部表 從 Oracle 9i 開始，外部表已經(jīng)成為 SQL*Loader 的一種替換方法。與傳統(tǒng)的數(shù)據(jù)庫表不同，外部表是引用那些存儲在數(shù)據(jù)庫之外的數(shù)據(jù)的只讀表。這些表能夠以標(biāo)準(zhǔn) Oracle 表的相同方式進(jìn)行查詢，并且經(jīng)常作為較大型 ETL（提取/轉(zhuǎn)換/裝載）過程的早期階段。外部表非常有助于引用數(shù)據(jù)庫外的數(shù)據(jù)，但是創(chuàng)建外部表需要相當(dāng)多的 DDL 代碼，以創(chuàng)建引用數(shù)據(jù)文件的表、表的列定義和其他文件（錯(cuò)誤文件和日志文件）。創(chuàng)建外部表需要了解數(shù)據(jù)文件的文件格式和記錄格式，還要了解 SQL。ExternalTableGenerator 自動(dòng)執(zhí)行該過程，并提供對于創(chuàng)建外部表（它們訪問帶分隔符的文件）的語法的一些見解。 DDL 腳本 以下是對 ExternalTableGenerator 所生成的 DDL 腳本的簡要描述，將把示例電子表格作為一個(gè)參數(shù)來運(yùn)行此腳本： CREATE OR REPLACE DirectorY load_dir AS 'C:/workspaces/test/XL2ExternalTables';CREATE OR REPLACE DIRECTORY 語句創(chuàng)建一個(gè)目錄對象，該對象答應(yīng) Oracle 訪問操作系統(tǒng)目錄。該目錄包含外部表所引用的數(shù)據(jù)文件以及錯(cuò)誤文件和日志文件。ExternalTableGenerator 使用當(dāng)前工作目錄作為所引用的操作系統(tǒng)目錄。 CREATE TABLE PA_Zip_Code_Locations (ZIP VARCHAR2(10),AREANAME VARCHAR2(30),LATITUDE NUMBER,LONGITUDE NUMBER,POPULATION NUMBER) ORGANIZATION EXTERNAL (TYPE oracle_loaderDEFAULT DIRECTORY load_diraccess PARAMETERS (RECORDS DELIMITED BY NEWLINEbadfile 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 語法和列定義看上去類似創(chuàng)建常規(guī) Oracle 表的典型 DDL 語句。ORGANIZATION EXTERNAL 將此表確定為外部表。TYPE 子句用于指定驅(qū)動(dòng)程序類型。隨后的子句描述了文件的結(jié)構(gòu)以及日志文件、錯(cuò)誤文件和數(shù)據(jù)文件的位置。有關(guān) EXTERNAL TABLE 語法的更多信息，請參見 Oracle 文檔。
ExternalTableGenerator 根據(jù)以下規(guī)則生成 DDL 的以下片斷：

表的名稱基于給定工作表的名稱。工作表的名稱還用于確定錯(cuò)誤文件、日志文件以及外部表所引用的數(shù)據(jù)文件的名稱。
列的名稱基于工作表第一行中的值。這些名稱還用于外部表的 FIELDS 定義部分。
VARCHAR2 的長度由電子表格中的第二行所指定。
列類型被定義為 VARCHAR2 或 NUMBER，這取決于電子表格隨后一行中的單元。
因?yàn)槊總€(gè) .xls 工作表都是導(dǎo)出以逗號對值進(jìn)行分隔 (.csv) 文件，所以其中包含了 RECORDS DELIMITED BY NEWLINE 和 FIELDS TERMINATED BY ',' 信息。

new_departments 和 new_employees 表具有與以上所示的 pa_zip_code_locations 表相同的基本結(jié)構(gòu)。 Excel 電子表格 需要裝載的數(shù)據(jù)經(jīng)常具有由逗號、線段或制表符分隔數(shù)值的簡單格式，而裝載這些數(shù)據(jù)是相當(dāng)簡單直接的任務(wù)。但是，數(shù)據(jù)可以來自可能包含多個(gè)工作表的 Excel 電子表格。電子表格以二進(jìn)制格式存儲，不能直接利用 SQL*Loader 或外部表進(jìn)行訪問。Excel 電子表格優(yōu)于傳統(tǒng)純文本文件的一個(gè)好處是可以對數(shù)據(jù)分類（分為數(shù)值型、字符串型等等）。如上所述，您可以在 ExternalTableGenerator 中利用這一情況來確定列類型。對于 ExternalTableGenerator 所處理的電子表格有一些要求：

它們可以包含一個(gè)或多個(gè)電子表格。
電子表格的名稱用于表名定義。外部表名、數(shù)據(jù)文件名、日志文件名和錯(cuò)誤文件名由電子表格的名稱指定。
電子表格中的每個(gè)工作表用于生成外部表 DDL 的一個(gè)單元塊和一個(gè) .csv 數(shù)據(jù)文件。
每個(gè)電子表格中的第一行是列名定義行。列的名稱用于 EXTERNAL TABLE 單元塊的列名和字段名部分。該行不包括在 .csv 數(shù)據(jù)文件中。
每個(gè)電子表格中的第二行用于確定 VARCHAR2 字段的長度。此行也不包括在 .csv 數(shù)據(jù)文件中。

由于您使用電子表格的各部分來確定表名和列名，請謹(jǐn)慎選擇那些能夠生成有效 SQL 的名稱。換言之，避免由 ExternalTableGenerator 處理電子表格中的非法字符、SQL 要害字、現(xiàn)有的表名以及類似內(nèi)容。運(yùn)行該項(xiàng)目不需要 Excel，但是查看 .xls 文件需要它。示例數(shù)據(jù)的屏幕快照如下：從Excel電子表格生成外部表（圖一）

點(diǎn)擊查看大圖從Excel電子表格生成外部表（圖二）

點(diǎn)擊查看大圖從Excel電子表格生成外部表（圖三）

點(diǎn)擊查看大圖示例中的數(shù)據(jù)表示需要添加到數(shù)據(jù)庫中的兩個(gè)新部門、其員工以及相關(guān)的郵政編碼列表。員工和部門數(shù)據(jù)基于 scott/tiger 模式表，假如您向 scott 授予所有必需的權(quán)限，則可以導(dǎo)入這些數(shù)據(jù)以供實(shí)驗(yàn)。郵政編碼信息表明 ExternalTableGenerator 能夠處理更多的數(shù)據(jù)。 運(yùn)行該項(xiàng)目 該項(xiàng)目在 Java 運(yùn)行時(shí)環(huán)境 (JRE) 1.4.2_03 中完成了測試，但是它應(yīng)該可以在任何能夠運(yùn)行 POI 的 JRE 中運(yùn)行。您可以通過執(zhí)行 runSample.bat 來運(yùn)行 ExternalTableGenerator。這個(gè)批處理文件使用了在參數(shù)中所指定的 Excel 工作表，將相關(guān)的 jar 包含在類路徑中 (XL2ExternalTables.jar;jakarta-poi-1.5.1-final-20020615.jar;jakarta-poi-contrib-1.5.1-final-20020615.jar) 并運(yùn)行適當(dāng)?shù)?Java 類 com.saternos.database.utilities.ExternalTableGenerator。示例成功運(yùn)行后所得到的輸出應(yīng)該類似于以下內(nèi)容（其路徑基于您的工作目錄）。 C:/Documents and Settings/Administrator/Desktop/XL2ETB>runExampleC:/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-20020615.jar com.saternos.database.utilities.ExternalTableGenerator new_department_data.xlsBegin 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.
這些代碼創(chuàng)建了三個(gè)數(shù)據(jù)文件（指定使用 .csv 擴(kuò)展名）并生成一個(gè)的 SQL 腳本，該腳本包含用于創(chuàng)建外部表的 DDL。以下是運(yùn)行該 DDL 腳本并測試所得結(jié)果的一個(gè)示例。通過 SQL*Plus 從創(chuàng)建 ExternalTables.sql 腳本的目錄連接到數(shù)據(jù)庫，開始進(jìn)行工作。 C:/XL2ETB>SQL*Plus testuser/mypassWord@orcladmSQL*Plus:Release 10.1.0.2.0 - ProdUCtion on Tue Dec 21 09:36:25 2004Copyright (c) 1982, 2004, Oracle.All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.1.0 - ProductionSQL> select * from tab;no rows selected 雖然本示例是一個(gè)空的模式，但是假如用戶擁有適當(dāng)?shù)臋?quán)限，并且沒有名稱沖突，則該腳本可以在任何模式中運(yùn)行。 SQL> @ExternalTablesDirectory created.Table created.Table created.Table created.該腳本被執(zhí)行，并且創(chuàng)建了目錄對象和三個(gè)外部表。SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------NEW_DEPARTMENTS TABLENEW_EMPLOYEES TABLEPA_ZIP_CODE_LOCATIONS TABLESQL> select count(*) from new_departments;COUNT(*)---------- 2SQL> select count(*) from new_employees;COUNT(*)---------- 14SQL> select count(*) from pa_zip_code_locations;COUNT(*)---------- 1458SQL> 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 50SQL>結(jié)論 本文介紹了 POI，但只是淺顯地討論了通過該 API 可以使用的功能。有多種方法可以增強(qiáng)本文中所描述的 ExternalTableGenerator 實(shí)用程序的功能：