天天躁日日躁狠狠躁婷婷,狠狠综合久久久久综合网站

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

Excel整合SharePoint打造輕量級BI解決方案

2016-04-14

Excel整合SharePoint打造輕量級BI解決方案

本文將帶您走進BI世界，并向您講述如何使SharePoint2007結(jié)合Excel提供一個輕量級的BI解決方案。這一點對于渴望使用電子表格的用戶極為重要。在本文中的例子截取自一個真實的項目，在這個項目中，某個公司需要從數(shù)百個Excel文檔、關(guān)系型數(shù)據(jù)庫以及Word文檔中選取數(shù)據(jù)，并使用SharePoint2007來展現(xiàn)我們對這些數(shù)據(jù)的切片和鉆取的結(jié)果。

這個項目的成功非常清楚地表明BI數(shù)據(jù)并不一定來自于數(shù)據(jù)庫或其他一些服務(wù)程序。它們也可以來自非常流行的管理和報表工具，如Excel。

在本文中我們將會看到在這個工程中解決一些具有挑戰(zhàn)的問題，并探索將Excel數(shù)據(jù)導(dǎo)入到SharePoint2007的一些可供選擇的方法。本文還提供了一些VBA代碼，這些代碼將幫助我們更進一步地格式化Excel數(shù)據(jù)以符合SharePoint的要求。

一、在項目中面臨的挑戰(zhàn)

經(jīng)過和客戶的討論，我們得知他們的重要商業(yè)數(shù)據(jù)（就是一些涉及到100多家投資公司的財務(wù)信息）被存儲在兩個主要的地方：SQL Server2000數(shù)據(jù)庫保存了一些用于應(yīng)用程序前端的財務(wù)數(shù)據(jù)，而數(shù)百個Excel文件則直接保存了每一個公司的財務(wù)信息。

我們曾經(jīng)考慮過使用SharePoint2007的商業(yè)數(shù)據(jù)目錄功能，并建立可以連接到SQL Server2000的應(yīng)用程序定義文件。但這種方案的主要弊病是SQL Server在統(tǒng)計數(shù)據(jù)時并不是100%準(zhǔn)確。而前端的應(yīng)用程序可以非常容易地建立Excel報表，而且可以讓更富有經(jīng)驗的專家人工審核數(shù)據(jù)。因此，使用Excel驗證方式更容易讓人信服。所以我們的解決方案是使用SQL Server2000的導(dǎo)出功能將數(shù)據(jù)導(dǎo)出到Excel中，再使用Excel的驗證功能來處理這些財務(wù)數(shù)據(jù)。

在回顧正在從被管理的公司搜集財務(wù)數(shù)據(jù)的Excel電子表格后，我們確定了一個可以編輯的模板標(biāo)準(zhǔn)，這個模板可以支持在SharePoint2007站點中所需的所有數(shù)據(jù)。因此，第二套設(shè)計方案就是用Excel作為這些被管理公司財務(wù)數(shù)據(jù)的數(shù)據(jù)源。我們可以為每一個被管理的公司（如Company A,Company B,Company C等）建立一個SharePoint2007站點，然后將這些數(shù)據(jù)處理后，匯總到中心網(wǎng)站（這也稱為上鉆）。

二、怎么將Excel導(dǎo)出到SharePoint2007中

接下來要討論的是如何將數(shù)據(jù)從Excel移動SharePoint2007列表中。除了使用Excel服務(wù)外。我們還可以使用如下的方法來解決這個問題：

注：看到這也許有人會問，"什么是Excel服務(wù)呢？"，當(dāng)然，這是一個很自然問題。因為微軟最近一直在鼓吹它的Excel服務(wù)能力在SharePoint2007推出之后得到了非同尋常的增強。在本文的案例中，要求客戶端使每一個Excel單元格和列表中的數(shù)據(jù)相對應(yīng)，如果使用Excel服務(wù)就可以使數(shù)據(jù)被發(fā)布到SharePoint2007中，并通過Excel Web Access Web將這些數(shù)據(jù)提供給用戶，而不是一個個單元格地將數(shù)據(jù)移植到SharePoint2007列表中。

另外，Excel服務(wù)發(fā)布系統(tǒng)只能在Excel2007中使用，因此，這對于客戶端要求太苛刻，所以Excel服務(wù)目前還很難被廣泛采用。如果我們想學(xué)習(xí)更多的關(guān)于Excel服務(wù)的知識，可以參考相關(guān)的資料。

1. 從Excel電子表格中剪切和粘貼數(shù)據(jù)到SharePoint2007列表中。

2. 使用SharePoint2007的數(shù)據(jù)導(dǎo)入功能將Excel的數(shù)據(jù)導(dǎo)出到SharePoint2007中。

3. 使Excel和SharePoint2007進行同步來導(dǎo)出數(shù)據(jù)。

4. 使用一個第三方的產(chǎn)品將數(shù)據(jù)從Excel導(dǎo)出到SharePoint2007中。

    在本文下面的部分將使用一些例子和屏幕截圖上述的幾種處理數(shù)據(jù)的方法，讀者將會從中看來它們的優(yōu)劣。
三、從Excel導(dǎo)出到SharePoint2007

    這種方法我們可以立即排除了，因為我們要面對的是成千上萬的單元格，而且不止一家公司需要處理。手工去做根本是不可能的。因此，我們可以得出結(jié)論，使用這種方法將會給我們帶來超大工作量和操作錯誤。

四、使用SharePoint2007將Excel數(shù)據(jù)導(dǎo)進來

    在這種方法中，SharePoint2007可以基于Excel的內(nèi)容建立一個列表（可以通過使用Create命令，然后選擇從定制列表部分導(dǎo)入Excel表來實現(xiàn)），但是這么做有一些缺陷。首先，這是一次性導(dǎo)入數(shù)據(jù)，因此，并沒有更容易的方法向加入多余的行，或是當(dāng)Excel變化后更新已經(jīng)存在的內(nèi)容（在本文的實例中Excel文件是按月更新的）。第二，SharePoint2007需要按行組織的數(shù)據(jù)，而典型的財務(wù)報表是按列組織的。如圖1顯示了一個典型的Excel財務(wù)報表，而圖2顯示了將Excel表格導(dǎo)入到SharePoint2007的最終結(jié)果。從這兩個圖可以看出，使用SharePoint2007的導(dǎo)入功能對于標(biāo)準(zhǔn)的財務(wù)報表并不能很好的工作，而且由于是一次性處理，因此，它也不支持表格以后的更新同步操作。

圖1 一個典型的Excel財務(wù)報表

圖2 將Excel數(shù)據(jù)導(dǎo)入到SharePoint后的結(jié)果

但幸運的是，在Excel中有一個工作區(qū)的概念。如果我們簡單地將數(shù)據(jù)從一個Excel表單中復(fù)制并粘貼到一個另一個表單，在這個粘貼過程中，可以使用變換選項將行列數(shù)據(jù)互換。圖3就是一個互換的結(jié)果。我們?nèi)匀恍枰獞?yīng)用一個小的變化以使SharePoint的數(shù)據(jù)看上去更朋好。我們可以從圖3看到相應(yīng)的變化，在圖3中顯示了行A的頭移動了行B。如果不做這個變化，SharePoint將假設(shè)只有在行A，而其他的還是老樣子。圖4顯示了使用這個Excel數(shù)據(jù)經(jīng)過進一就處理后的結(jié)果。因此，這種方法的結(jié)論是使用互換和重新格式化功能來處理的標(biāo)準(zhǔn)財務(wù)數(shù)據(jù)可以非常有效的被導(dǎo)入到SharePoint2007中。

圖3 使用互換功能后Excel中的數(shù)據(jù)

圖4

在圖5中，現(xiàn)在每一行是一個日期，而每一列代表一種財務(wù)值，如實際收入（Actual Revenue）或Budget/Plan Revenue。

圖5 將處理后的Excel數(shù)據(jù)導(dǎo)入到SharePoint2007后的結(jié)果

接下來讓我們看一下如圖6的設(shè)置列表，這個列表顯示了SharePoint2007中對導(dǎo)入數(shù)據(jù)的一些錯誤假設(shè)。如它將dates和Revenue都設(shè)置成了"Single line of text."。很明顯，我們在使用這種方法導(dǎo)入數(shù)據(jù)時必須事先將數(shù)據(jù)格式化，才能在SharePoint2007中正確顯示。如在Excel的空單元格中使其包含"0"，這樣在導(dǎo)入SharePoint后，就可以被當(dāng)成數(shù)值類型而不是文本來處理。因此，使用這種方法的結(jié)論是在將Excel數(shù)據(jù)導(dǎo)出之前需要對其進行必要的格式化，這樣才能確保SharePoint能正確識別。

圖6 SharePoint的設(shè)置對話框

五、使Excel和SharePoint2007同步

    這是我們的第三種方法。要使用這種方法，需要從微軟下填鴨式Excel2007，而且還必須將文件保存成Excel2003的格式，否則無法同步。

    為了將Excel數(shù)據(jù)發(fā)布到SharePoint2007中，并使它隨著數(shù)據(jù)的變化進行同步，我們必須從微軟下載一個Excel2007插件。

    在安裝和配置完這個插件后，我們將在Office Ribbon上的標(biāo)簽上找到一個"Publish and allow Sync"按鈕，這個按鈕可以讓我們將Excel數(shù)據(jù)發(fā)布到SharePoint2007中?，F(xiàn)在我們可以發(fā)布一個讀-寫列表到SharePoint2007中，但是這個操作只能在和Excel2003兼容的模式下進行。如果我們將文檔保存成Excel2007的格式，就不會發(fā)生同步事件。如果我們保存成Excel2007的格式，Excel會提示我們此功能失效。

現(xiàn)在我們可以訪問Excel2007的設(shè)計標(biāo)簽了，然后單擊" Publish and allow Sync"按鈕。為了正確發(fā)布，我們還需要輸入SharePoint2007的網(wǎng)站名以及列表名。圖8顯示了將一個Excel表導(dǎo)出到SharePoint2007后的結(jié)果。

圖8 變化后的最終結(jié)果

    為了使Excel和SharePoint2007同步，返回到Excel中，右擊單元格，選擇"Table, Synchronize with SharePoint."。如果我們在Excel或SharePoint2007中修改數(shù)據(jù)。但未進行同步。下一個用戶進入程序時將會收到一個選項“l(fā)oad the pending changes.”因此，我們可以斷定，使用這種方法可以使Excel和SharePoint2007列表進行雙向同步。

六、使用第三方工具導(dǎo)出數(shù)據(jù)

    對第最后一種方法，使用一個第三方的工具來導(dǎo)入數(shù)據(jù)。我們也可以有很多選擇。如一個叫Bamboo Solutions的公司提供了一種叫List Bulk Import的產(chǎn)品，它可以將數(shù)據(jù)和文檔移植到SharePoint2007中，而且可以是不同的數(shù)據(jù)源，如數(shù)據(jù)庫，Excel表，或是老版本的SharePoint。換句話說，這個產(chǎn)品完全可以實現(xiàn)上述方法中描述的功能。

    在List Bulk Import中，操作更象SharePoint2007，它希望數(shù)據(jù)被組織成行，而不是列。在這個產(chǎn)品中有一個強大的接口，可以將Excel的值映射到SharePoint列表中，然后提供選項來檢查在SharePoint列表中的復(fù)本數(shù)據(jù)，并更新已經(jīng)存在的數(shù)據(jù)，然后可以建立一個導(dǎo)入工作計劃。我們還可以將導(dǎo)入模板保存在xml文件中以便重復(fù)使用或編輯。這個產(chǎn)品還可以產(chǎn)生一個日志文件，提供一個對導(dǎo)入是否成功的跟蹤。我們可以查看Bamboo Solutions的網(wǎng)站以得到更詳細(xì)的信息。從上面的描述看，使用這個工具要比使用其他方法更可靠，總之，它是一個強大的工具。

七、使用Excel宏預(yù)處理要導(dǎo)出的數(shù)據(jù)

從上述的討論可以看出，標(biāo)準(zhǔn)的財務(wù)報表所組織的數(shù)據(jù)對于SharePoint列表并不朋好（這點可以從圖1和圖2看出）。除非公司自愿改變它們的財務(wù)報表格式（這幾乎是不可能的），否則我們就需要使用某種方法來格式化這些數(shù)據(jù)。幸運的是，在Excel中提供了一種強有力的宏引擎。我們可以使用它們非常容易地格式化數(shù)據(jù)。

下面列表顯示了一些需要處理的任務(wù)，不管我們選擇哪種方法進行數(shù)據(jù)導(dǎo)出，都可以使用如下的方法進行自動格式化任務(wù)：

1. 將數(shù)據(jù)從標(biāo)準(zhǔn)的行格式轉(zhuǎn)換成列格式。在List Bulk Import的最新版本已經(jīng)提供了這個功能。但如果我們不想使用它或沒有最新版本，就只能使用宏來做這件事了。

2. 在轉(zhuǎn)換后，我們將移動一些列標(biāo)題，將它們放到同一行，代碼如下：

' 'Consolidate header information into a single column in those 'cases where the headers are split between two columns. ' 'Parameters: xlWS, the Worksheet object to manipulate ' strCol, the column to be edited ' PublicFunction EditHeaders(xlWS As Excel.Worksheet, _ strCol As String) As BooleanDim iRowCount As Integer Dim i As Integer Dim strRange As String DimstrHeader As String Dim xlCell As Range iRowCount =xlWS.UsedRange.Rows.Count strRange = strCol & "1:" & strCol & iRowCount ForEach xlCell In xlWS.Range(strRange) strHeader = xlCell.Value If (strHeader<> "" And xlCell.Offset(0, 1) = "") Then xlCell.Offset(0, 1).Value =strHeader End If Next xlCell End Function

3. 財務(wù)報表經(jīng)常在應(yīng)該包含數(shù)字的單元格包含文本。典型的例子是在單元格中包含"NA"。SharePoint在一個為數(shù)值類型的單元格中并不允許有文本，因此我們需要使用如下代碼進行轉(zhuǎn)換：

'Edit the cells with "NA". 'Parameters: xlWS, the Worksheet object to be edited PublicFunction EditFormulas(xlWS As Excel.Worksheet) As Boolean xlWS.Columns.Replace"NA", "" End Function

在本文中介紹了四種從Excel導(dǎo)出數(shù)據(jù)到SharePoint的方法。雖然本項目使用了第三方工具來完成這個任務(wù)。但這不是必須的，如果我們不需要那么復(fù)雜的功能，如不需要保留日志，也可以使用其他的方法?？傊?，保證數(shù)據(jù)的準(zhǔn)確性是進行進一步商業(yè)分析的有力保證。

CDA數(shù)據(jù)分析師考試相關(guān)入口一覽（建議收藏）：

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