99999久久久久久亚洲,欧美人与禽猛交狂配,高清日韩av在线影院,一个人在线高清免费观看,啦啦啦在线视频免费观看www

熱線電話：13121318867

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

Excel 數(shù)據(jù)轉存數(shù)據(jù)庫的應用框架

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

讀者可能也會遇到其它的應用場景，要求把 Excel 文件中的數(shù)據(jù)信息解析出來并存入到數(shù)據(jù)庫中。

本文則提供了一種相對高效而智能的解決方案用以解析 excel 文件，并轉存入數(shù)據(jù)庫中。

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 實現(xiàn)，通過 HSSF，開發(fā)者可用純 Java 代碼來讀取、寫入、修改 Excel 文件。而 XSSF 則是 Excel 2007 OOXML(.xlsx) 文件格式的純 java 實現(xiàn)。

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

Excel 文件到表的映射元數(shù)據(jù)定義

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

映射規(guī)則一：單元格單一映射

Excel 表中的一個單元格（cell）對應關系數(shù)據(jù)庫中某一個模式（Schema）下一張表（Table）的一個域（Field）。 如： A1 -> name

映射規(guī)則二：單元格組合映射

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

映射規(guī)則三：Excel 列的單一映射

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

映射規(guī)則四：Excel 列的組合映射

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

圖 1. Excel 列的組合映射

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

讀者可能會問，如何自動的生成這些映射規(guī)則呢？完全自動的生成，是很難做到的。我們是應用 Symphony Container，構建復合應用程序輔助“專家”來生成映射規(guī)則的。換句話說，一定有一個“專家”需要根據(jù)領域業(yè)務需求，完成數(shù)據(jù)庫表的設計。在設計表的過程中，知 道那些 excel 文件里的信息塊需要提取出來?；谶@些知識，并利用一些輔助工具生成出映射規(guī)則元數(shù)據(jù)信息。

下面是基于 XML 語法結構的映射元數(shù)據(jù)片段，如清單 1 所示（如果是 Domino 的應用，可以創(chuàng)建 Domino 的文檔用以保存映射元數(shù)據(jù)，進而應用 Notes 的 Java API 來解析）。

清單 1. 映射規(guī)則（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> 授信統(tǒng)計類型 </DBField>
             ……
     </rule4>
        …
     </rules>
 </tns:mappingRule >

轉換框架的介紹

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

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

如 果 Excel 文件是存放在 Domino 服務器上的，則需要一個 Domino Agent 程序主動調用輸出 Agent 所提供的接口，批量的轉換用解析程序從 domino 數(shù)據(jù)文檔中解析出來的 excel 文件。

按照映射的元數(shù)據(jù)模型，應用 HSSF/XSSF 解析 Excel 的相應單元格、列，進而構造 SQL 語句（采用 JDBC 方式的模式）。在一個事務中提交該 Excel 文件所要執(zhí)行的所有 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
 // 文件為單位的，可涉及到若干個表
        }
    }

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

在 Excel 解析的過程中，對于映射規(guī)則四，我們需要額外的算法支持。

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

在 映射規(guī)則四中，我們定義的規(guī)則是，Excel 表的多列對應關系數(shù)據(jù)庫中表的一個域。組合的方式是字符串的連接。問題是 Excel 表中，有很多單元格是合并的單元格，對于合并的單元格我們需要進行特殊的處理，目的是使得組合后的數(shù)據(jù)內容比較準確的表達了原 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）的一個區(qū)域。在程序中，我們只關心給定區(qū)域內的合并單元塊，這樣可以極大的提高性能，如清單 4 所示。

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

清單 4. 獲取指定合并區(qū)域
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>();
    // 記錄被合并的單元格所在的合并區(qū)域
    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); // 獲得合并區(qū)域的值
                        resultBf.append(value +","); //$NON-NLS-1$
                    }
                }
            }
        }
        //resultBf 可能不包含任何數(shù)據(jù)
        if(resultBf.length()>0 && resultBf.charAt(resultBf.length()-1)==','){
            resultBf.deleteCharAt(resultBf.lastIndexOf(",")); //$NON-NLS-1$
        }
        return resultBf.toString();
    }

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

小結

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

數(shù)據(jù)分析咨詢請掃描二維碼

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

數(shù)據(jù)分析師考試動態(tài)
數(shù)據(jù)分析師資訊
更多

OK
客服在線
立即咨詢
客服在線
立即咨詢
') } function initGt() { var handler = function (captchaObj) { captchaObj.appendTo('#captcha'); captchaObj.onReady(function () { $("#wait").hide(); }).onSuccess(function(){ $('.getcheckcode').removeClass('dis'); $('.getcheckcode').trigger('click'); }); window.captchaObj = captchaObj; }; $('#captcha').show(); $.ajax({ url: "/login/gtstart?t=" + (new Date()).getTime(), // 加隨機數(shù)防止緩存 type: "get", dataType: "json", success: function (data) { $('#text').hide(); $('#wait').show(); // 調用 initGeetest 進行初始化 // 參數(shù)1：配置參數(shù) // 參數(shù)2：回調，回調的第一個參數(shù)驗證碼對象，之后可以使用它調用相應的接口 initGeetest({ // 以下 4 個配置參數(shù)為必須，不能缺少 gt: data.gt, challenge: data.challenge, offline: !data.success, // 表示用戶后臺檢測極驗服務器是否宕機 new_captcha: data.new_captcha, // 用于宕機時表示是新驗證碼的宕機 product: "float", // 產品形式，包括：float，popup width: "280px", https: true // 更多配置參數(shù)說明請參見：http://docs.geetest.com/install/client/web-front/ }, handler); } }); } function codeCutdown() { if(_wait == 0){ //倒計時完成 $(".getcheckcode").removeClass('dis').html("重新獲取"); }else{ $(".getcheckcode").addClass('dis').html("重新獲取("+_wait+"s)"); _wait--; setTimeout(function () { codeCutdown(); },1000); } } function inputValidate(ele,telInput) { var oInput = ele; var inputVal = oInput.val(); var oType = ele.attr('data-type'); var oEtag = $('#etag').val(); var oErr = oInput.closest('.form_box').next('.err_txt'); var empTxt = '請輸入'+oInput.attr('placeholder')+'！'; var errTxt = '請輸入正確的'+oInput.attr('placeholder')+'！'; var pattern; if(inputVal==""){ if(!telInput){ errFun(oErr,empTxt); } return false; }else { switch (oType){ case 'login_mobile': pattern = /^1[3456789]\d{9}$/; if(inputVal.length==11) { $.ajax({ url: '/login/checkmobile', type: "post", dataType: "json", data: { mobile: inputVal, etag: oEtag, page_ur: window.location.href, page_referer: document.referrer }, success: function (data) { } }); } break; case 'login_yzm': pattern = /^\d{6}$/; break; } if(oType=='login_mobile'){ } if(!!validateFun(pattern,inputVal)){ errFun(oErr,'') if(telInput){ $('.getcheckcode').removeClass('dis'); } }else { if(!telInput) { errFun(oErr, errTxt); }else { $('.getcheckcode').addClass('dis'); } return false; } } return true; } function errFun(obj,msg) { obj.html(msg); if(msg==''){ $('.login_submit').removeClass('dis'); }else { $('.login_submit').addClass('dis'); } } function validateFun(pat,val) { return pat.test(val); }