熱線電話：13121318867

登錄
首頁精彩閱讀Excel 數據轉存數據庫的應用框架
Excel 數據轉存數據庫的應用框架
2016-08-22
收藏

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

隨著企業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 ?？梢栽谟成湟巹t里定義字符串的分隔符，例如，在上面的例子中是用逗號“，”組合的。

映射規則三：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，構建復合應用程序輔助“專家”來生成映射規則的。換句話說，一定有一個“專家”需要根據領域業務需求，完成數據庫表的設計。在設計表的過程中，知 道那些 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 文件。

按照映射的元數據模型，應用 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 一個給定單元格值的程序。

清單 3. 獲取單元格的值
        private static Object getCellValue(Cell cell) {
        Object obj = null;
        if(cell == null) return null;
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            obj = cell.getRichStringCellValue().getString().trim();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                obj = cell.getDateCellValue();
            } else {
                obj = cell.getNumericCellValue();
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            obj =  cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_FORMULA:
            obj = cell.getCellFormula();
            break;
        default:
        }
        return obj;
    }

對 于合并的單元格，應用上面的 API，除了左上角第一個單元格有值外，其它已經合并起來的單元格返回值均是 null 。 HSSF 的解析 API 中對此有一個類叫 CellRangeAddress，該類用以記錄 Excel 表中被合并（Merged）的一個區域。在程序中，我們只關心給定區域內的合并單元塊，這樣可以極大的提高性能，如清單 4 所示。

在清單 5 中，我們給出了如何獲取某一行內指定列的組合值。

清單 4. 獲取指定合并區域
public static List<CellRangeAddress> getCellRawRangeAddresses(HSSFSheet sheet1,
    int startIndex, int endIndex, short[] indexes){ //indexes, excel column indexes
    List<CellRangeAddress> resultList = new ArrayList<CellRangeAddress>();
        for (int i = 0; i < sheet1.getNumMergedRegions(); i++) {
            CellRangeAddress cellRA = sheet1.getMergedRegion(i);
            int firstRow = cellRA.getFirstRow();
            int lastRow =cellRA.getLastRow();
            int firstCol = cellRA.getFirstColumn();
            int lastCol = cellRA.getLastColumn();         
            if(lastRow >= startIndex && firstRow <= endIndex){
                if(lastCol>= getMin(indexes) && firstCol <= getMax(indexes))
                resultList.add(cellRA);
            }
        }
        return resultList;
    }
清單 5. 獲取給定行的組合值
public static String getCominbedValue(HSSFSheet sheet1, Row row, short[] indexes ,
    List<CellRangeAddress> limitedCellRAs){  
    StringBuffer resultBf = new StringBuffer(); // 用以存儲最終結果
    Map<CellPosition, CellRangeAddress> theMap =
    new LinkedHashMap<CellPosition, CellRangeAddress>();
    // 記錄被合并的單元格所在的合并區域
    int rowIndex = row.getRowNum();
        Iterator<CellRangeAddress> itcra = limitedCellRAs.iterator();
        while(itcra.hasNext()){
            CellRangeAddress cra = itcra.next();
            int firstRow = cra.getFirstRow();
            int lastRow =cra.getLastRow();
            if(rowIndex>= firstRow && rowIndex<= lastRow){
                int firstCol = cra.getFirstColumn();
                int lastCol = cra.getLastColumn();    
                for(int j=0; j<indexes.length;j++){
                    short index = indexes[j];
                    if(index >= firstCol && index <= lastCol){   
                        theMap.put(new CellPosition(rowIndex,index),cra);
                    }
                }      
            }
        }
        // 記錄合并單元格所對應的列號
        Iterator<CellPosition> itpos = theMap.keySet().iterator();
        short[] compIndexes = new short[theMap.size()];
        int count =0;
        while(itpos.hasNext()){
            CellPosition cellPos = itpos.next();
            int colIndex = cellPos.getCol();
            compIndexes[count]=(short)colIndex;
            count++;
        }
        List<CellPosition> cachedList =new ArrayList<CellPosition>();
        for(int i =0;i< indexes.length;i++){
            if(!constains(compIndexes, indexes[i])){
               // 如果不包含此列號，則對應的 cell 不是一個合并單元格，調用清單 2 中的方法獲取其值
                Cell cell = row.getCell(indexes[i]);
                Object result= getCellValue(cell);
                if(!StringUtility.checkNull(result))
                    resultBf.append( result+","); //$NON-NLS-1$
            }else{  
           CellRangeAddress cellRA = theMap.get(new CellPosition (rowIndex, indexes[i]));
                if(cellRA != null){
                    int cellrow = cellRA.getFirstRow();
                    int cellcol = cellRA.getFirstColumn();
                    CellPosition cPos = new CellPosition(cellrow,cellcol);
                    //If it is Not the cached CellRangeAddress object
                    if(!cachedList.contains(cPos)){
                        cachedList.add(cPos); // 放到緩存中，如果是同一個合并單元格，我們取其值一次
                        Object value = getMergedRegionValue(sheet1,cellRA); // 獲得合并區域的值
                        resultBf.append(value +","); //$NON-NLS-1$
                    }
                }
            }
        }
        //resultBf 可能不包含任何數據
        if(resultBf.length()>0 && resultBf.charAt(resultBf.length()-1)==','){
            resultBf.deleteCharAt(resultBf.lastIndexOf(",")); //$NON-NLS-1$
        }
        return resultBf.toString();
    }

本 文的解決方案不局限于 IBM DB2 數據庫，同時支持 MySQL 等若干數據庫。針對不同數據庫的數據類型，解析框架會動態的加載相應的類型配置文件，并對解析到的 Excel 信息作相應的修整（比如：DB2 數據庫某一字段的數據類型要求是 decimal 的 , 如果解析器取到的 excel 相應的內容是字符串格式的話，那么需要進行轉換，并保證一定的容錯性），從而保證生成正確的 SQL 語句信息。

小結

本 文提供了一種解決Excel數據轉存數據庫的通用解決方案。重點介紹了幾種映射規則，這些規則都是最基本的，也是最常用的，讀者可以根據需要，基于此而設計更復雜的映射規則。同時本 文也著重介紹了，映射規則四的一些實現算法，希望能夠為被這類問題所困擾的同志們提供一些有價值的參考。


CDA數據分析師考試相關入口一覽（建議收藏）：

? 想報名CDA認證考試，點擊>>>   “CDA報名” 了解CDA考試詳情；

? 想學習CDA考試教材，點擊>>> “CDA教材” 了解CDA考試詳情；

? 想加入CDA考試題庫，點擊>>> “CDA題庫” 了解CDA考試詳情；

? 想了解CDA考試含金量，點擊>>> “CDA含金量” 了解CDA考試詳情；

SQL 字段 表結構 數據挖掘

數據分析咨詢請掃描二維碼

若不方便掃碼，搜微信號：CDAshujufenxi

上一篇圖論在大數據分析中的作用！
下一篇CDA認證再升一檔！與國家共同推進大數據人才培養標準教育事業！
數據分析師考試動態
CDA報考指南
數據分析學習
數據分析師資訊
更多

CDA教育閉環

常見問題

關于我們

CDA數據分析師公眾號

  • CDA數據分析師公眾號

CDA考試中心小程序

  • CDA考試中心服務號

CDA數據分析師App下載

  • CDA數據分析師App下載
Copyright © 2015-2021, www.ruiqisteel.com All Rights Reserved. CDA數據分析師(北京國富如荷網絡科技有限公司) 版權所有 京ICP備11001960號-9 京公網安備 11010802034615號 經營許可證編號：京B2-20210330
聯系電話：13321103290 (微信同號)

OK
客服在線
立即咨詢
日韩人妻系列无码专区视频,先锋高清无码,无码免费视欧非,国精产品一区一区三区无码
客服在線
立即咨詢