Excel整合SharePoint打造輕量級BI解決方案
本文將帶您走進BI世界，并向您講述如何使SharePoint2007結合Excel提供一個輕量級的BI解決方案。這一點對于渴望使用電子表格的用戶極為重要。在本文中的例子截取自一個真實的項目，在這個項目中，某個公司需要從數百個Excel文檔、關系型數據庫以及Word文檔中選取數據，并使用SharePoint2007來展現我們對這些數據的切片和鉆取的結果。
這個項目的成功非常清楚地表明BI數據并不一定來自于數據庫或其他一些服務程序。它們也可以來自非常流行的管理和報表工具，如Excel。
在本文中我們將會看到在這個工程中解決一些具有挑戰的問題，并探索將Excel數據導入到SharePoint2007的一些可供選擇的方法。本文還提供了一些VBA代碼，這些代碼將幫助我們更進一步地格式化Excel數據以符合SharePoint的要求。
一、在項目中面臨的挑戰
經過和客戶的討論，我們得知他們的重要商業數據（就是一些涉及到100多家投資公司的財務信息）被存儲在兩個主要的地方：SQL Server2000數據庫保存了一些用于應用程序前端的財務數據，而數百個Excel文件則直接保存了每一個公司的財務信息。
我們曾經考慮過使用SharePoint2007的商業數據目錄功能，并建立可以連接到SQL Server2000的應用程序定義文件。但這種方案的主要弊病是SQL Server在統計數據時并不是100%準確。而前端的應用程序可以非常容易地建立Excel報表，而且可以讓更富有經驗的專家人工審核數據。因此，使用Excel驗證方式更容易讓人信服。所以我們的解決方案是使用SQL Server2000的導出功能將數據導出到Excel中，再使用Excel的驗證功能來處理這些財務數據。
在回顧正在從被管理的公司搜集財務數據的Excel電子表格后，我們確定了一個可以編輯的模板標準，這個模板可以支持在SharePoint2007站點中所需的所有數據。因此，第二套設計方案就是用Excel作為這些被管理公司財務數據的數據源。我們可以為每一個被管理的公司（如Company A,Company B,Company C等）建立一個SharePoint2007站點，然后將這些數據處理后，匯總到中心網站（這也稱為上鉆）。
二、怎么將Excel導出到SharePoint2007中
接下來要討論的是如何將數據從Excel移動SharePoint2007列表中。除了使用Excel服務外。我們還可以使用如下的方法來解決這個問題：
注：看到這也許有人會問，"什么是Excel服務呢？"，當然，這是一個很自然問題。因為微軟最近一直在鼓吹它的Excel服務能力在SharePoint2007推出之后得到了非同尋常的增強。在本文的案例中，要求客戶端使每一個Excel單元格和列表中的數據相對應，如果使用Excel服務就可以使數據被發布到SharePoint2007中，并通過Excel Web Access Web將這些數據提供給用戶，而不是一個個單元格地將數據移植到SharePoint2007列表中。
另外，Excel服務發布系統只能在Excel2007中使用，因此，這對于客戶端要求太苛刻，所以Excel服務目前還很難被廣泛采用。如果我們想學習更多的關于Excel服務的知識，可以參考相關的資料。
1. 從Excel電子表格中剪切和粘貼數據到SharePoint2007列表中。
2. 使用SharePoint2007的數據導入功能將Excel的數據導出到SharePoint2007中。
3. 使Excel和SharePoint2007進行同步來導出數據。
4. 使用一個第三方的產品將數據從Excel導出到SharePoint2007中。
在本文下面的部分將使用一些例子和屏幕截圖上述的幾種處理數據的方法，讀者將會從中看來它們的優劣。
三、從Excel導出到SharePoint2007
這種方法我們可以立即排除了，因為我們要面對的是成千上萬的單元格，而且不止一家公司需要處理。手工去做根本是不可能的。因此，我們可以得出結論，使用這種方法將會給我們帶來超大工作量和操作錯誤。
四、使用SharePoint2007將Excel數據導進來
在這種方法中，SharePoint2007可以基于Excel的內容建立一個列表（可以通過使用Create命令，然后選擇從定制列表部分導入Excel表來實現），但是這么做有一些缺陷。首先，這是一次性導入數據，因此，并沒有更容易的方法向加入多余的行，或是當Excel變化后更新已經存在的內容（在本文的實例中Excel文件是按月更新的）。第二，SharePoint2007需要按行組織的數據，而典型的財務報表是按列組織的。如圖1顯示了一個典型的Excel財務報表，而圖2顯示了將Excel表格導入到SharePoint2007的最終結果。從這兩個圖可以看出，使用SharePoint2007的導入功能對于標準的財務報表并不能很好的工作，而且由于是一次性處理，因此，它也不支持表格以后的更新同步操作。
圖1 一個典型的Excel財務報表
圖2 將Excel數據導入到SharePoint后的結果
但幸運的是，在Excel中有一個工作區的概念。如果我們簡單地將數據從一個Excel表單中復制并粘貼到一個另一個表單，在這個粘貼過程中，可以使用變換選項將行列數據互換。圖3就是一個互換的結果。我們仍然需要應用一個小的變化以使SharePoint的數據看上去更朋好。我們可以從圖3看到相應的變化，在圖3中顯示了行A的頭移動了行B。如果不做這個變化，SharePoint將假設只有在行A，而其他的還是老樣子。圖4顯示了使用這個Excel數據經過進一就處理后的結果。因此，這種方法的結論是使用互換和重新格式化功能來處理的標準財務數據可以非常有效的被導入到SharePoint2007中。
圖3 使用互換功能后Excel中的數據
圖4
在圖5中，現在每一行是一個日期，而每一列代表一種財務值，如實際收入（Actual Revenue）或Budget/Plan Revenue。
圖5 將處理后的Excel數據導入到SharePoint2007后的結果
接下來讓我們看一下如圖6的設置列表，這個列表顯示了SharePoint2007中對導入數據的一些錯誤假設。如它將dates和Revenue都設置成了"Single line of text."。很明顯，我們在使用這種方法導入數據時必須事先將數據格式化，才能在SharePoint2007中正確顯示。如在Excel的空單元格中使其包含"0"，這樣在導入SharePoint后，就可以被當成數值類型而不是文本來處理。因此，使用這種方法的結論是在將Excel數據導出之前需要對其進行必要的格式化，這樣才能確保SharePoint能正確識別。
圖6 SharePoint的設置對話框
五、使Excel和SharePoint2007同步
這是我們的第三種方法。要使用這種方法，需要從微軟下填鴨式Excel2007，而且還必須將文件保存成Excel2003的格式，否則無法同步。
為了將Excel數據發布到SharePoint2007中，并使它隨著數據的變化進行同步，我們必須從微軟下載一個Excel2007插件。
在安裝和配置完這個插件后，我們將在Office Ribbon上的標簽上找到一個"Publish and allow Sync"按鈕，這個按鈕可以讓我們將Excel數據發布到SharePoint2007中?，F在我們可以發布一個讀-寫列表到SharePoint2007中，但是這個操作只能在和Excel2003兼容的模式下進行。如果我們將文檔保存成Excel2007的格式，就不會發生同步事件。如果我們保存成Excel2007的格式，Excel會提示我們此功能失效。
現在我們可以訪問Excel2007的設計標簽了，然后單擊" Publish and allow Sync"按鈕。為了正確發布，我們還需要輸入SharePoint2007的網站名以及列表名。圖8顯示了將一個Excel表導出到SharePoint2007后的結果。
圖8 變化后的最終結果
為了使Excel和SharePoint2007同步，返回到Excel中，右擊單元格，選擇"Table, Synchronize with SharePoint."。如果我們在Excel或SharePoint2007中修改數據。但未進行同步。下一個用戶進入程序時將會收到一個選項“load the pending changes.”因此，我們可以斷定，使用這種方法可以使Excel和SharePoint2007列表進行雙向同步。
六、使用第三方工具導出數據
對第最后一種方法，使用一個第三方的工具來導入數據。我們也可以有很多選擇。如一個叫Bamboo Solutions的公司提供了一種叫List Bulk Import的產品，它可以將數據和文檔移植到SharePoint2007中，而且可以是不同的數據源，如數據庫，Excel表，或是老版本的SharePoint。換句話說，這個產品完全可以實現上述方法中描述的功能。
在List Bulk Import中，操作更象SharePoint2007，它希望數據被組織成行，而不是列。在這個產品中有一個強大的接口，可以將Excel的值映射到SharePoint列表中，然后提供選項來檢查在SharePoint列表中的復本數據，并更新已經存在的數據，然后可以建立一個導入工作計劃。我們還可以將導入模板保存在xml文件中以便重復使用或編輯。這個產品還可以產生一個日志文件，提供一個對導入是否成功的跟蹤。我們可以查看Bamboo Solutions的網站以得到更詳細的信息。從上面的描述看，使用這個工具要比使用其他方法更可靠，總之，它是一個強大的工具。
七、使用Excel宏預處理要導出的數據
從上述的討論可以看出，標準的財務報表所組織的數據對于SharePoint列表并不朋好（這點可以從圖1和圖2看出）。除非公司自愿改變它們的財務報表格式（這幾乎是不可能的），否則我們就需要使用某種方法來格式化這些數據。幸運的是，在Excel中提供了一種強有力的宏引擎。我們可以使用它們非常容易地格式化數據。
下面列表顯示了一些需要處理的任務，不管我們選擇哪種方法進行數據導出，都可以使用如下的方法進行自動格式化任務：
1. 將數據從標準的行格式轉換成列格式。在List Bulk Import的最新版本已經提供了這個功能。但如果我們不想使用它或沒有最新版本，就只能使用宏來做這件事了。
2. 在轉換后，我們將移動一些列標題，將它們放到同一行，代碼如下：
' '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. 財務報表經常在應該包含數字的單元格包含文本。典型的例子是在單元格中包含"NA"。SharePoint在一個為數值類型的單元格中并不允許有文本，因此我們需要使用如下代碼進行轉換：
'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導出數據到SharePoint的方法。雖然本項目使用了第三方工具來完成這個任務。但這不是必須的，如果我們不需要那么復雜的功能，如不需要保留日志，也可以使用其他的方法?？傊?，保證數據的準確性是進行進一步商業分析的有力保證。
CDA數據分析師考試相關入口一覽（建議收藏）：
? 想報名CDA認證考試，點擊>>>
“CDA報名”
了解CDA考試詳情；
? 想學習CDA考試教材，點擊>>> “CDA教材” 了解CDA考試詳情；
? 想加入CDA考試題庫，點擊>>> “CDA題庫” 了解CDA考試詳情；
? 想了解CDA考試含金量，點擊>>> “CDA含金量” 了解CDA考試詳情；
京公網安備 11010802034615號
經營許可證編號：京B2-20210330
