歡迎您光臨本站 註冊首頁

Excel 數據轉存資料庫的應用框架

←手機掃碼閱讀     火星人 @ 2014-03-12 , reply:0
  
如何把 Excel 中的數據移植到關係型資料庫中,一直是大家關心的話題,本文提出了一種解決方案,即使用 Apache POI 按一定的 Excel 文件到 資料庫表的映射規則來解析數據,然後使用 JDBC 或者 Hibernate/JPA 等技術把數據轉存到資料庫中。

引言

隨著企業 IT 系統的不斷升級,很多企業有提升遺留系統的強烈需求。而整合遺留系統中的信息資源是其中最為關鍵的一步,進而可對信息進行數據挖掘等創新工作。本文涉及這樣的一個應用案例,企業的數據信息是以 Excel 文件作為其附件的方式,分散存儲在企業不同的伺服器上的,客戶要求把這些文件裡面的數據信息轉存到資料庫中。

讀者可能也會遇到其它的應用場景,要求把 Excel 文件中的數據信息解析出來並存入到資料庫中。

本文則提供了一種相對高效而智能的解決方案用以解析 excel 文件,並轉存入資料庫中。

Apache POI (POI-HSSF and POI-XSSF) 簡介

POI 是 Apache 基金組織的子項目,POI(Poor Obfuscation Implementation)的目標就是提供一組 Java API 來使得基於 Microsoft OLE 2 Compound Document 格式的 Microsoft Office 文件易於操作。 HSSF(Horrible Spreadsheet Format)是 POI 項目 Excel 文件格式(97 - 2007)的純 java 實現,通過 HSSF,開發者可用純 Java 代碼來讀取、寫入、修改 Excel 文件。而 XSSF 則是 Excel 2007 OOXML(.xlsx) 文件格式的純 java 實現。

本文主要涉及到應用 HSSF 和 XSSF 讀取 Excel 文件中的數據。





Excel 文件到表的映射元數據定義

如何把 Excel 表裡面的數據映射為關係資料庫表中的數據呢?關係資料庫中的二維表是結構化的數據存儲,而一個 Excel 文件的一個 Sheet 頁面就可能包含多個可映射為資料庫表結構的信息塊。這個信息塊可能很簡單,也可能非常複雜。在本文中,針對 Excel 中信息塊的實際情況,我們定義了幾種 Excel 文件到資料庫表的映射規則。這些規則就是我們用以解析的元數據。離開了這些元數據,我們就談不上智能而高效的解析了。

映射規則一:單元格單一映射

Excel 表中的一個單元格(cell)對應關係資料庫中某一個模式(Schema)下一張表(Table)的一個域(Field)。 如: A1 -> name

映射規則二:單元格組合映射

Excel 表中的多個單元格對應關係資料庫中某一個模式(Schema)下一張表(Table)的一個域(Field)。 組合的方式是字元串的連接,比如 C1,C2, F3 -> address 。可以在映射規則里定義字元串的分隔符,例如,在上面的例子中是用逗號“,”組合的。

映射規則三:Excel 列的單一映射

Excel 表中的某一列的數據對應關係資料庫中某一個模式(Schema)下一張表(Table)的一個域(Field)的數據。和前面兩條規則相比較,該規則是將信息塊中行的記錄和資料庫中表的行記錄相對應起來的。例如 Column H -> 出口額。

映射規則四:Excel 列的組合映射

如下圖 1 所示,Excel 表中的多列的數據組合對應關係資料庫中某一個模式(Schema)下一張表(Table)的一個域(Field)的數據。組合的方式是字元串數據的拼接,分隔符也可以在映射規則中定義。例如 Column A, Column B, Column C -> 授信統計類型。


圖 1. Excel 列的組合映射

以上四種規則比較常用,但由於 Excel 文件中信息塊結構的複雜性,我們還可以根據需要定義其它的映射規則。(比如,Excel 文件以附近形式放置在 Domino 伺服器上的,則可以結合 Domino 文檔中的域來定義映射規則)

讀者可能會問,如何自動的生成這些映射規則呢?完全自動的生成,是很難做到的。我們是應用 Symphony Container,構建複合應用程序輔助“專家”來生成映射規則的。(感興趣的讀者請參考 http://symphony.lotus.com/)換句話說,一定有一個“專家”需要根據領域業務需求,完成資料庫表的設計。在設計表的過程中,知道那些 excel 文件里的信息塊需要提取出來。基於這些知識,並利用一些輔助工具生成出映射規則元數據信息。

下面是基於 XML 語法結構的映射元數據片段,如清單 1 所示(如果是 Domino 的應用,可以創建 Domino 的文檔用以保存映射元數據,進而應用 Notes 的 Java API 來解析)。


清單 1. 映射規則(Mapping Rule)示例
			<?xml version="1.0" encoding="UTF-8"?>    <tns:mappingRule xmlns: tns=http://sample.com.cn/mappingRule    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xsi:schemaLocation="http://sample.com.cn/profile MPSchema.xsd ">        <excelType> 授信 </excelType>    <excelVersion>2008</excelVersion>         <rules>   	 <! — mapping rule type 1 -->   	 <rule1> 	               <sheetNumber>0</sheetNumber>                 <sheetName>sheet1</sheetName >                <cellPos>A1</cellPos>                <DBschema>LitieDS</DBschema>                <DBTable>employee</DBTable>                <DBField>name</DBField>   	 </rule1>   	 <! — mapping rule type 2 -->   	 <rule2> 	               ……               <cellPoses>C1,C2,F3</cellPoses>                <separator>,</separator>                <DBField>address</DBField>                ……  	 </rule2>   	 <! — mapping rule type 3 -->   	 <rule3> 	               ……               <sheetColumn>H</sheetColumn>                <DBField> 出口額 </DBField>                ……  	 </rule3>   	 <! — mapping rule type 4 -->   	 <rule4> 	               ……               <sheetColumns>A,B,C</sheetColumns>                <separator>,</separator>                <DBField> 授信統計類型 </DBField>                ……  	 </rule4>           …       </rules>    </tns:mappingRule >





轉換框架的介紹

如圖 2 所示,本文介紹的框架程序有兩個入口,針對的是兩種情況(如果,這兩種情況都不是讀者所遇見的,比如 excel 文件是以大對象形式存儲在 Oracle 資料庫中,那麼讀者需要自己寫解析器,拿到 excel 文件,本文略之)。

如果 Excel 文件在文件系統中,則輸出 Agent 模塊是主程序。它首先獲取文件系統中的 Excel 文件,然後可以啟動多個線程去處理一批 Excel 文件。獲取 Excel 文件的類型和版本號,根據 Excel 類型和版本號去獲取用以描述 Excel 和關係資料庫的映射元數據,就是上節我們講述的內容。進而解析元數據構建元數據的內存模型。並且採用緩存機制,同一種類型,並且是同一種版本的 Excel 文件應用內存中已經構建好的元數據模型來解析,不必每次都去重新獲取元數據。這樣可以顯著的提高性能。

如果 Excel 文件是存放在 Domino 伺服器上的,則需要一個 Domino Agent 程序主動調用輸出 Agent 所提供的介面,批量的轉換用解析程序從 domino 數據文檔中解析出來的 excel 文件。(關於 Domino Agent 以及如何開發一個 Agent 程序,請見參考文獻 2)

按照映射的元數據模型,應用 HSSF/XSSF 解析 Excel 的相應單元格、列,進而構造 SQL 語句(採用 JDBC 方式的模式)。在一個事務中提交該 Excel 文件所要執行的所有 SQL 語句, 保證一個 Excel 文件寫入或者完全成功,或者出錯回滾,並報告錯誤信息。如清單 2 所示。


圖 2. 轉換框架


清單 2. 插入 SQL 語句
			private void insertSQLStatements(){           //Insert into database           List<String> sqlList = new ArrayList<String>();           CADataStore  dataStore = new CADataStore();             if(tableSQLsMap!= null && tableSQLsMap.size()!= 0){               Iterator<String> itsql = tableSQLsMap.keySet().iterator();               while(itsql.hasNext()){                   String tableName = itsql.next();                   List<String> theList = tableSQLsMap.get(tableName);                   sqlList.addAll(theList);               }               dataStore.write(sqlList); // 一個事務中完成所有 sql 語句的插入操作,事務是以一個 excel    // 文件為單位的,可涉及到若干個表          }       }

本轉換框架具有很大的可擴展性,我們不局限於遺留系統中已有的 Excel 文件信息,遺留系統可以繼續使用,比如用戶可以繼續提交新類型的以 Excel 文件為存儲格式的數據信息。系統可以定期不定期的進行轉換工作。由圖 2 可見,該轉換框架清晰明了,是解決這類問題的一個通用模式。

在 Excel 解析的過程中,對於映射規則四,我們需要額外的演算法支持。





Excel 列的組合映射的演算法分析

在映射規則四中,我們定義的規則是,Excel 表的多列對應關係資料庫中表的一個域。組合的方式是字元串的連接。問題是 Excel 表中,有很多單元格是合併的單元格,對於合併的單元格我們需要進行特殊的處理,目的是使得組合后的數據內容比較準確的表達了原 Excel 文件信息塊中的內容信息。清單 3 是 POI API 獲取 Excel 一個給定單元格值的程序。


[火星人 ] Excel 數據轉存資料庫的應用框架已經有552次圍觀

http://coctec.com/docs/linux/show-post-68832.html