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

熱線電話：13121318867

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

Excel 數(shù)據(jù)轉(zhuǎn)存數(shù)據(jù)庫的應(yīng)用框架

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

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

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

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

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

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

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

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

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

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

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

Excel 表中的多個(gè)單元格對(duì)應(yīng)關(guān)系數(shù)據(jù)庫中某一個(gè)模式（Schema）下一張表（Table）的一個(gè)域（Field）。 組合的方式是字符串的連接，比如 C1,C2, F3 -> address ?？梢栽谟成湟?guī)則里定義字符串的分隔符，例如，在上面的例子中是用逗號(hào)“，”組合的。

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

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

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

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

圖 1. Excel 列的組合映射

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

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

下面是基于 XML 語法結(jié)構(gòu)的映射元數(shù)據(jù)片段，如清單 1 所示（如果是 Domino 的應(yīng)用，可以創(chuàng)建 Domino 的文檔用以保存映射元數(shù)據(jù)，進(jìn)而應(yīng)用 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)計(jì)類型 </DBField>
             ……
     </rule4>
        …
     </rules>
 </tns:mappingRule >

轉(zhuǎn)換框架的介紹

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

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

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

按照映射的元數(shù)據(jù)模型，應(yīng)用 HSSF/XSSF 解析 Excel 的相應(yīng)單元格、列，進(jìn)而構(gòu)造 SQL 語句（采用 JDBC 方式的模式）。在一個(gè)事務(wù)中提交該 Excel 文件所要執(zhí)行的所有 SQL 語句， 保證一個(gè) Excel 文件寫入或者完全成功，或者出錯(cuò)回滾，并報(bào)告錯(cuò)誤信息。如清單 2 所示。

圖 2. 轉(zhuǎn)換框架

轉(zhuǎn)換框架

清單 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); // 一個(gè)事務(wù)中完成所有 sql 語句的插入操作，事務(wù)是以一個(gè) excel
 // 文件為單位的，可涉及到若干個(gè)表
        }
    }

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

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

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

在 映射規(guī)則四中，我們定義的規(guī)則是，Excel 表的多列對(duì)應(yīng)關(guān)系數(shù)據(jù)庫中表的一個(gè)域。組合的方式是字符串的連接。問題是 Excel 表中，有很多單元格是合并的單元格，對(duì)于合并的單元格我們需要進(jìn)行特殊的處理，目的是使得組合后的數(shù)據(jù)內(nèi)容比較準(zhǔn)確的表達(dá)了原 Excel 文件信息塊中的內(nèi)容信息。清單 3 是 POI API 獲取 Excel 一個(gè)給定單元格值的程序。

清單 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;
    }

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

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

清單 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(); // 用以存儲(chǔ)最終結(jié)果
    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);
                    }
                }      
            }
        }
        // 記錄合并單元格所對(duì)應(yīng)的列號(hào)
        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])){
               // 如果不包含此列號(hào)，則對(duì)應(yīng)的 cell 不是一個(gè)合并單元格，調(diào)用清單 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); // 放到緩存中，如果是同一個(gè)合并單元格，我們?nèi)∑渲狄淮?/span>
                        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ù)庫，同時(shí)支持 MySQL 等若干數(shù)據(jù)庫。針對(duì)不同數(shù)據(jù)庫的數(shù)據(jù)類型，解析框架會(huì)動(dòng)態(tài)的加載相應(yīng)的類型配置文件，并對(duì)解析到的 Excel 信息作相應(yīng)的修整（比如：DB2 數(shù)據(jù)庫某一字段的數(shù)據(jù)類型要求是 decimal 的 , 如果解析器取到的 excel 相應(yīng)的內(nèi)容是字符串格式的話，那么需要進(jìn)行轉(zhuǎn)換，并保證一定的容錯(cuò)性），從而保證生成正確的 SQL 語句信息。

小結(jié)

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

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

若不方便掃碼，搜微信號(hào)：CDAshujufenxi

數(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(), // 加隨機(jī)數(shù)防止緩存 type: "get", dataType: "json", success: function (data) { $('#text').hide(); $('#wait').show(); // 調(diào)用 initGeetest 進(jìn)行初始化 // 參數(shù)1：配置參數(shù) // 參數(shù)2：回調(diào)，回調(diào)的第一個(gè)參數(shù)驗(yàn)證碼對(duì)象，之后可以使用它調(diào)用相應(yīng)的接口 initGeetest({ // 以下 4 個(gè)配置參數(shù)為必須，不能缺少 gt: data.gt, challenge: data.challenge, offline: !data.success, // 表示用戶后臺(tái)檢測(cè)極驗(yàn)服務(wù)器是否宕機(jī) new_captcha: data.new_captcha, // 用于宕機(jī)時(shí)表示是新驗(yàn)證碼的宕機(jī) product: "float", // 產(chǎn)品形式，包括：float，popup width: "280px", https: true // 更多配置參數(shù)說明請(qǐng)參見：http://docs.geetest.com/install/client/web-front/ }, handler); } }); } function codeCutdown() { if(_wait == 0){ //倒計(jì)時(shí)完成 $(".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 = '請(qǐng)輸入'+oInput.attr('placeholder')+'！'; var errTxt = '請(qǐng)輸入正確的'+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); }