VSTO學習筆記（十四）Excel數據透視表與PowerPivot
近期公司內部在做一種通用查詢報表，方便人力資源分析、統計數據。由于之前公司系統中有一個類似的查詢使用Excel數據透視表完成的，故我也打算借鑒一下。
原有系統是使用VBA編寫的，難以維護，且對新的操作系統如Windows 7、64位架構不支持，我準備用VSTO進行重寫。
數據透視表是一種交互式的表，可以進行某些計算，如求和與計數等。所進行的計算與數據跟數據透視表中的排列有關。
數據透視表是一種完全自助式的報表，功能很強大，對于提取、分析數據來說非常方便。
下面我們添加一張數據透視表。
1、在【數據】中，選擇從SQL Server中提取數據：
我從CodePlex上下載了SQL Server 2012的官方示例數據庫：AdventureWorks2012
CodePlex網站也改版了，大量Metro風格 J
2、連接數據庫：
3、當前僅僅是測試，選擇一個表。當然也可以自己寫SQL語句進行篩選數據：
4、點擊【完成】結束向導：
5、這里選擇【數據透視表報表】：
6、這樣一張數據透視表就完成了：
7、在右邊拖動我們想要分析的字段：
行維度選擇了產品模型和產品，列維度選擇了尺寸，最終統計了價格。
雙擊價格的單元格可以看到明細，相當于鉆取報表：
8、除了表格展現，數據透視表還支持各種圖表。
在【數據】中點擊【已存在的連接】：
9、選擇剛剛創建的數據庫連接：
10、選擇圖表類型：
11、類似剛才一樣拖動字段，一個圖表就生成了，是不是很強大？
右鍵可以更改圖表類型，如餅形圖、錐形圖等：
12、我打算把數據透視表功能集成到系統中，需要用代碼來生成。
通過跟蹤宏代碼，可以很容易用代碼來生成：
'
' Macro1 Macro
'
'
Workbooks("123.xlsx").Connections.Add ". AdventureWorks2012 Product", "", Array _
( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=.;Use Procedure for Prepare=1;Auto Translate" _
, _
"=True;Packet Size=4096;Workstation ID=WINDOWS8NB;Use Encryption for Data=False;Tag with column collation when possible=False;Ini" _
, "tial Catalog=AdventureWorks2012"), Array( _
"""AdventureWorks2012"".""Production"".""Product"""), 3
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections(". AdventureWorks2012 Product"), Version:= _
xlPivotTableVersion14).CreatePivotTable TableDestination:="Sheet1!R1C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductID")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("ListPrice"), "Sum of ListPrice", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Size")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductModelID")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductID")
.Orientation = xlRowField
.Position = 2
End With
End Sub
{
Excel.Application __app = new Excel.Application();
__app.DisplayAlerts = false;
Excel.Workbook __book = __app.Workbooks.Add();
//創建OleDB連接
Excel.WorkbookConnection __conn = __book.Connections.Add("Test", "PivotTable Test"
, "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=.;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WINDOWS8NB;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=AdventureWorks2012"
, "AdventureWorks2012.Production.Product", Excel.XlCmdType.xlCmdTable);
//創建數據透視表
Excel.PivotTable __table = __book.PivotCaches().Create(Excel.XlPivotTableSourceType.xlExternal
, __conn, Excel.XlPivotTableVersionList.xlPivotTableVersion14)
.CreatePivotTable("Sheet1!R1C1", "PivotTable1", __conn, Excel.XlPivotTableVersionList.xlPivotTableVersion14);
//添加行維度
__table.PivotFields("ProductModelID").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
__table.PivotFields("ProductModelID").Position = 1;
__table.PivotFields("ProductID").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
__table.PivotFields("ProductID").Position = 2;
//添加列維度
__table.PivotFields("Size").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
__table.PivotFields("Size").Position = 1;
//添加值維度
__table.AddDataField("ListPrice", "SUM(ListPrice)", Excel.XlConsolidationFunction.xlSum);
__book.SaveAs(@"D:\test.xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook);
__app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(__app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(__book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(__conn);
System.Runtime.InteropServices.Marshal.ReleaseComObject(__table);
__app = null;
__book = null;
__conn = null;
__table = null;
}
13、這樣我們就可以借助于Excel的數據透視表來展示、分析數據。
正因為PivotTable如此好用，微軟將PivotTable功能進行了升級，叫做PowerPivot，作為Excel 2010的一個免費插件發布，定位于企業級自助式的商務智能。
目前最新版本是Microsoft? SQL Server? 2012 PowerPivot? for Microsoft? Excel? 2010，下載地址：
與PowerPivot類似，微軟還推出了：
Microsoft? SQL Server? 2012 Data Mining Add-ins for Microsoft? Office? 2010
Microsoft? SQL Server? 2012 Master Data Services Add-in For Microsoft? Excel?
PowerPivot也是微軟商務智能的主推技術之一，官方網站：
以下摘自百度百科：
PowerPivot for Excel 是用于在 Excel 工作簿中創建 PowerPivot 數據的創作工具。您將使用數據透視表和數據透視圖等 Excel 數據可視化對象來顯示您在 Excel 工作簿 (.xlsx) 文件中嵌入或引用的 PowerPivot 數據。
PowerPivot for Excel 通過下列方式來支持自助商業智能。
取消當前 Excel 中的行和列限制，以便可以導入更多的數據。
通過數據關系層，您可以集成來自不同數據源的數據并全面處理所有數據。可以輸入數據、復制其他工作表中的數據或從企業數據庫中導入數據。可以在數據之間建立關系以分析數據，就好像所有數據都來自一個數據源一樣。
創建可移植、可重用的數據。數據保留在工作簿內。您無需管理外部數據連接。如果您發布、移動、復制或共享工作簿，所有的數據都會和工作簿在一起。
工作簿的其余部分可以立即使用所有的 PowerPivot 數據。可以在 Excel 和 PowerPivot 窗口之間切換，從而以交互方式處理數據及其在數據透視表或數據透視圖中的表示形式。處理數據或其表示形式不是單獨的任務。可以在同一個 Excel 環境中一起處理數據及其表示形式。
PowerPivot for Excel 可以讓您導入、篩選數百萬行數據以及對這些數據進行排序，遠遠超過 Excel 中一百萬行的限制。排序和篩選操作都非常快，因為它們是由在 Excel 內部運行的本地 Analysis Services VertiPaq 處理器執行的。
更重要的是，通過使用 PowerPivot for Excel，您可以在來自完全不同的數據源的數據之間建立關系，具體方法是映射包含類似或相同數據的列。在數據之間建立關系時，您是在 Excel 中創建了可在數據透視表、數據透視圖或任意 Excel 數據表示對象中使用的全新內容。
保存的數據存儲在 Excel 工作簿內部。數據經過高度壓縮，生成的文件的大小適合在客戶端工作站上進行管理。
最后，用戶會獲得一個包含嵌入數據的工作簿 (.xlsx) 文件，這些數據由內部處理器提取和處理，但完全通過 Excel 呈現。壓縮和處理是由 Analysis Services VertiPaq 引擎完成的。查詢處理在后臺透明地運行，以便在 Excel 中提供海量數據支持。因為由本地 Analysis VertiPaq 引擎執行，排序和篩選操作都非常快。
14、安裝完PowerPivot后打開Excel會多了一個Ribbon：
打開PowerPivot Windows后可以看到是一個增強的PivotTable：
結合數據挖掘插件已經可以做很多分析了：
小結：
Office 已經從辦公平臺開始轉換為了微軟的數據平臺、商務智能平臺，SQL Server 2012的發布加劇了這一過程。
PivotTable和PowerPivot可以很方便的做自助式商務智能分析，相較于SQL Server 分析服務和Oracle、IBM的重量級解決方案，是一種輕量級的解決方案，特別適合于業務、管理人員分析、提取需要的數據。
目前PowerPivot還沒有開放API，我也只是從表面了解下基本用途，期待Office 15的登場吧。
浙公網安備 33010602011771號