原文作者:andreww
原文链接: http://blogs.msdn.com/andreww/archive/2008/07/25/creating-a-pivottable-programmatically.aspx
我接收到一封客户的邮件,问我如何创建Excel数据透视表应用程序.Excel对象模型提供了一系列的对象和方法创建数据透视表(PivotTable).可以通过很多种方法创建数据透视表,在此用一种最简单的方法创建数据透视表从外部数据库读取数据.下面我们就开始我们例子.
我用的SQL数据库是AdventureWorks,数据库下载地址.在解决方案里,首先创建一个数据连接字符串获取视图vSalesPersonSalesByFiscalYears所有的销售记录.代码如下:
Code
string connection =@"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=AdventureWorks";
string command ="SELECT * FROM [Sales].[vSalesPersonSalesByFiscalYears]";
下一步,在Excel Workbook中,添加一个数据缓存(PivotCathe)到数据缓存集合.并且设置它的连接字符串和SQL Command属性,代码如下:
Code
Excel.PivotCache pivotCache =this.Application.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, missing);
pivotCache.Connection = connection;
pivotCache.MaintainConnection = true;
pivotCache.CommandText = command;
pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
下一步,添加一个PivotTable对象到worksheet对象中,PivoTable对象的创建是基于前面创建的PivotCathe对象.代码如下:
Code
Excel.Worksheet sheet = (Excel.Worksheet)this.Application.ActiveSheet;
Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(missing);
Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, this.Application.ActiveCell, "PivotTable1",missing, missing);
下一步,设置PivotTable显示格式,数据列表的提纲模式替代默认的2*2单元格模式.代码如下:
Code
pivotTable.SmallGrid = false;
pivotTable.ShowTableStyleRowStripes = true;
pivotTable.TableStyle2 = "PivotStyleLight1";
下一步,设置"SalesTerritory"字段为页查询字段,"FullName"为行查询字段,代码如下:
Code
Excel.PivotField pageField =(Excel.PivotField)pivotTable.PivotField("SalesTerritory");
pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
Excel.PivotField rowField =(Excel.PivotField)pivotTable.PivotFields("FullName");
下一步,为2004年的销售数据添加一个标题,代码如下:
Code
pivotTable.AddDataField(pivotTable.PivotFields("2004"), "Sum of 2004", Excel.XlConsolidationFunction.xlSum);
最后,运行一下应用程序,显示结果如下图:
备注:下载数据库跟据你本地的SQL版本下载.安装好数据库后.创建一个Offce Excel文档级项目.把上面的步骤的代码依次拷到Sheet1.cs代码中的Sheet1_Startup方法下.完成后,运行应用程序可以得到上面的运行结果.