Creating a PivotTable Programmatically
I received an email from a customer the other day asking how to set up an Excel pivot table programmatically. The Excel OM exposes a number of objects and methods that you can use to create pivot tables, some more sophisticated than others. I wanted the simplest possible way to set up a pivot table from an external data source, and this is what I ended up with...
I used the AdventureWorks SQL database, which you can download from here. In my solution, I first set up a data connection to the database, with a SQL select statement to fetch all the SalesPerson sales records:
string connection =
@"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=AdventureWorks";
string command =
"SELECT * FROM [Sales].[vSalesPersonSalesByFiscalYears]";
Next, I added a new PivotCache to the PivotCaches collection in the active workbook, and set its data connection and SQL command properties:
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;
I can then add a new PivotTable to the worksheet, based on the PivotCache I’ve just configured:
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);
Then, set the PivotTable to use the pivot table stencil outline instead of the default 2x2 cell grid, and format it with grey alternating row shading:
pivotTable.SmallGrid = false;
pivotTable.ShowTableStyleRowStripes = true;
pivotTable.TableStyle2 = "PivotStyleLight1";
Set up the SalesTerritory field as the page field, and FullName as the row field:
Excel.PivotField pageField =
(Excel.PivotField)pivotTable.PivotFields("SalesTerritory");
pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
Excel.PivotField rowField =
(Excel.PivotField)pivotTable.PivotFields("FullName");
rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Add a data field for the sales for 2004:
pivotTable.AddDataField(
pivotTable.PivotFields("2004"), "Sum of 2004", Excel.XlConsolidationFunction.xlSum);
Done. The end result looks like this:
Comments
Anonymous
July 25, 2008
You've been kicked (a good thing) - Trackback from DotNetKicks.comAnonymous
August 01, 2008
How can I loop through all the PivotFields of a Pivot table in C# ?I currently need to find all the PivotFields of an already built PivotTable.Anonymous
August 01, 2008
Alexandre - you can use the PivotFields method to retrieve the PivotFields collection, and then iterate through it. The exact mechanism is slightly obscure, but pretty simple. Here's an example: Excel.PivotFields fields = (Excel.PivotFields)this.pivotTable.PivotFields(missing); int fieldCount = fields.Count; StringBuilder builder = new StringBuilder(); for (int i = 1; i <= fieldCount; i++) { Excel.PivotField field = (Excel.PivotField)this.pivotTable.PivotFields(i); builder.AppendLine(field.Name); } MessageBox.Show(builder.ToString());Anonymous
August 01, 2008
thank you for the prompt reply,I found out about the optional parameter while looking through msdn a couple of hours after asking here, but I still have one problem.I now can find the column fields and the row fields.If i understand how this works,There is a root PivotField for the row and column, the subsequent PivotFields in each are then added as a child PivotField and so on. please correct me if i'm wrong.I do not seem to be able to get the DataFields, "xlDataField" is there something special I must do to access these fields.At the moment I am trying to crawl the pivot table so that I may be able to record what fields are in what Orientation. I am doing this so that I may create a PivotTable definition, which can be used at a later time to recreate the PivotTable.Best regards,Anonymous
August 02, 2008
for those who have the same questions as I've been having.Anonymous
August 02, 2008
Alexandre - the data, row, column etc fields are not exactly children of the PivotField. Rather, they are all PivotFields that form subsets of the PivotFields collection in the PivotTable. The simplest approach is to use the corresponding RowFields, DataFields etc collection properties of the PivotTable itself. For example:StringBuilder builder = new StringBuilder();builder.AppendLine("PivotFields:");Excel.PivotFields pivotFields = (Excel.PivotFields)this.pivotTable.PivotFields(missing);for (int i = 1; i <= pivotFields.Count; i++){ Excel.PivotField pivotField = (Excel.PivotField)this.pivotTable.PivotFields(i); builder.AppendLine(pivotField.Name);}builder.AppendLine();builder.AppendLine("ColumnFields:");Excel.PivotFields columnFields = (Excel.PivotFields)this.pivotTable.get_ColumnFields(missing);for (int i = 1; i <= columnFields.Count; i++){ Excel.PivotField columnField = (Excel.PivotField)this.pivotTable.get_ColumnFields(i); builder.AppendLine(columnField.Name);}builder.AppendLine();builder.AppendLine("RowFields:");Excel.PivotFields rowFields = (Excel.PivotFields)this.pivotTable.get_RowFields(missing);for (int i = 1; i <= rowFields.Count; i++){ Excel.PivotField rowField = (Excel.PivotField)this.pivotTable.get_RowFields(i); builder.AppendLine(rowField.Name);}builder.AppendLine();builder.AppendLine("DataFields:");Excel.PivotFields dataFields = (Excel.PivotFields)this.pivotTable.get_DataFields(missing);for (int i = 1; i <= dataFields.Count; i++){ Excel.PivotField dataField = (Excel.PivotField)this.pivotTable.get_DataFields(i); builder.AppendLine(dataField.Name);}MessageBox.Show(builder.ToString());Anonymous
August 11, 2008
How do i make this functionality work for SSAS OLAP CubeAnonymous
August 12, 2008
Hi, I tried your code posted as “Creating a PivotTable Programmatically”, it works perfectly. I need to get exactly similar functionality with OLAP Cube. Below is the code I tried but I get error at line marked as (), when i try to add Pivot Table. I get exception as “Exception from HRESULT: 0x800A03EC”.Microsoft.Office.Interop.Excel.Application app; app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wkbk = (Microsoft.Office.Interop.Excel.Workbook)app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)wkbk.ActiveSheet; string connection = @"OLEDB; Provider=msolap; Integrated Security=SSPI ; Datasource=localhost; Initial Catalog=MSLMKTG_DemoCube; UID = Administrator; Password = cybage@123"; string command = "CubeUserMSLMKTG"; Microsoft.Office.Interop.Excel.PivotCache pivotCache; pivotCache = wkbk.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlExternal,Type.Missing); pivotCache.Connection = connection; pivotCache.MaintainConnection = true; pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdCube; pivotCache.CommandText = command; Microsoft.Office.Interop.Excel.PivotTables pvtTables = (Microsoft.Office.Interop.Excel.PivotTables)sheet.PivotTables(Type.Missing); ()Microsoft.Office.Interop.Excel.PivotTable pvt = pvtTables.Add(pivotCache,sheet.Cells[1,1], "PivotTable1", true, Type.Missing);Please help urgentlyThanks,ShreyasAnonymous
August 12, 2008
The comment has been removedAnonymous
August 16, 2008
Im not sure if this was answered in the previous codes as I am new to VB. If i were to try to access all the datafields in my pivotfield to make changes to formatting how could i do that?Also I want to remove the "sum of" out of each datafield. So it says Billpayers instead of Sum of Billpayers. Is there a way to do that to all datafields at once?Anonymous
August 17, 2008
Has any one done this.......see the above threads :)Thanks,ShreyasAnonymous
August 22, 2008
Thanks for this blog post!To Shreyas.It can be used to create connection to OLAP cube.Connection string I used in test case was:"OLEDB;Provider=MSOLAP.1; Location=ServerName;Connect Timeout=60; Initial Catalog=Analysis Services ProjectTest;Client Cache Size=25";"Properties:pivotCache.CommandText = "name of the cube";pivotCache.CommandType = XlCmdType.xlCmdCube;And assignment of fields:pivotTable.CubeFields["Name of cube dimension"].Orientation = XlPivotFieldOrientation.xlRowField;Anonymous
August 22, 2008
Thanks for this blog post!To Shreyas.It can be used to create connection to OLAP cube.Connection string I used in test case was:"OLEDB;Provider=MSOLAP.1; Location=ServerName;Connect Timeout=60; Initial Catalog=Analysis Services ProjectTest;Client Cache Size=25";"Properties:pivotCache.CommandText = "name of the cube";pivotCache.CommandType = CmdType.xlCmdCube;And assignment of fields:pivotTable.CubeFields["Name of cube dimension"].Orientation = XlPivotFieldOrientation.xlRowField;Anonymous
August 28, 2008
Hello Andrew,I'm having a couple of issues with a PivotTable:1) I get the following error when setting the Orientation property of the fifth PivotField: "Unable to set the Orientation property of the PivotField class". It seems that the error is related to the number of fields, not to the type of orientation being used.2) I don't know how to put the data fields as columns (as when you right-click the "Data" cell, select Order and choose "Move to Column"). Can this be accomplished programatically?Thanks!Anonymous
August 28, 2008
Dear Gaby,I was facing the exact same problem a few weeks ago.This is what I found:You need to create the pivot table in steps. The first of these steps is add all the data fields:if (orientation.Equals("xlDataField")){ string source = field.Attribute("SourceName").Value; string function = field.Attribute("Function").Value; pivot.AddDataField(pivot.PivotFields(source), name, SelectFunction(function));}Then set the orientation of the PivotFields. Be sure not to set the orientation of the "Data" named PivotField.Once all the fields have been set, you may then set the orientation of the "Data" field. You need to do this last since by placing PivotFields the value PivotField is automatically generated.Please correct me if I'm wrong.Now you can set the Position and Caption of the fields.This is how i went about it. Let me know if this works.Best regards,Alexandre BriseboisAnonymous
August 29, 2008
Hello Alexandre,It didn't work. It seems that error # 1 appears because there are too many rows. I'll try to sort that out later.Do you have any idea on how to accomplish # 2 ("Move to Columns" programatically)?Regards,GabyAnonymous
August 29, 2008
The comment has been removedAnonymous
August 31, 2008
The comment has been removedAnonymous
September 01, 2008
Thanks Teme....finally it worked :)Thanks,ShreyasAnonymous
September 01, 2008
Hi Teme,I am done with the implementation but when i deploy it on IIS server, nothing is getting generated in the excel.Please share your contact information.Thanks,ShreyasAnonymous
September 01, 2008
The comment has been removedAnonymous
September 01, 2008
The comment has been removedAnonymous
September 02, 2008
Hi,when i run my code in Visual studio it works perfectly fine...but when i deploy it on IIS server then my excel sheet doesn't show anything.How do get this done.Some where i read about Microsoft.Office.Interop.Excel which IIS does not support.Please let me know the detail steps.Thanks,ShreyasAnonymous
September 02, 2008
HiSorry haven't had the time to check replies. It can be done on IIS. Have you included all the dlls that needed to Bin folder of website? I had Microsoft.Office.Interop.Excel.dll and office.dll.For first time I tried it on IIS I ran to the language version bug. Before using Excel operations set your culture to US.System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");You must disable all popup questions of Excel because IIS runs it without console. So no one can reply to question -> process hangs._exelApp.DisplayAlerts = false;Have you set user rights for folders that are used?-TemeFor detailed questions:teme1011@gmail.comAnonymous
September 08, 2008
Alexandre - I don't believe there is any way to retrieve the chart layout. When you call ApplyLayout, the integer you pass as the first parameter is an identifier into the list of possible layouts for this chart type (you can see these values in the Design tab of the Ribbon). These layouts affect one or more values (such as the existence and/or position of the Legend) - not necessarily just a single property.Anonymous
September 08, 2008
Andrew,after some time i decided on setting the '1' layout for all my charts and have the user customize it before printing it out.Thanks for the response. I do think that this would be an interesting addition to future versions.Anonymous
September 18, 2008
Hi,If I already have a dataset with the data, how can I create the pivot table?Anonymous
September 18, 2008
I tried the code and works but when I tried the line: Excel.PivotTable pivotTable = pivotTables.Add( pivotCache, Globals.ThisAddIn.Application.ActiveCell, "PivotTable1", missing, missing);the programs shows me an SQL connection windwos ask me for a databaseName, id an password?Why??Anonymous
September 23, 2008
Helen - the code I posted specifically uses a SQL connection, so if you use this code, the PivotCache is set up to use a SQL database. Are you saying that you have the data in an Excel Range, and that you want to use that data for your PivotTable? If so, you can do so very simply. For example:Excel.PivotCache pivotCache = this.Application.ActiveWorkbook.PivotCaches().Add( Excel.XlPivotTableSourceType.xlDatabase, (Excel.Range)sheet.get_Range("A1", "C17"));Anonymous
November 17, 2008
How can I get to the PivotItems of the individual PivotFields objects? I am trying to convert some VBA logic to C# .NET. In the VBA code I could loop through the PivotItems that were created for each PivotField and make them Visible or not depending on the caption. I cannot seem to find the right object/method to get to those same PivotItem objects in .NET VSTO.Anonymous
November 17, 2008
Connie - you can simply iterate through the collection of PivotItems in C# in pretty much the same way you can do it in VBA - after all, you're still using the same exposed Excel object model. For example:StringBuilder builder = new StringBuilder();builder.AppendLine("PivotItems:");Excel.PivotItems items = (Excel.PivotItems) ((Excel.PivotField)this.pivotTable.PivotFields("2004")).PivotItems(missing);for (int i = 1; i < items.Count; i++){ Excel.PivotItem item = (Excel.PivotItem)items.Item(i); builder.AppendLine(String.Format("{0}", item.Value));}MessageBox.Show(builder.ToString());Anonymous
November 18, 2008
Thanks so much! That worked like a charm. I did end up using it as a foreach (PivotItem item in items) but the principle is the same. The collections that are available in the Excel .NET tools feel a little different than the normal .NET collections. I always forget to try to use the Type.Missing object in place of an index.Thanks again!Anonymous
November 27, 2008
Andrew - great article!I like to create a Pivot table from a SSAS cube and wonder if you could post the complete code here. I suppose you have it anyways.Also I was able to get the MDX scripts that Excel generates and saved those. Now, what would I have to do to recreate a Pivot table with that. Would that be the COMMAND text?Your help in this matter is greatly appreciated.Thanks, DirkAnonymous
November 27, 2008
Dirk again. I missed to mention that I have created an Excel 2007 Add-in that I like to use to create these Pivot tables based on the MDX queries saved beforehand.Anonymous
November 28, 2008
Dirk - I'm afraid I don't know the answer to your question, although I suspect your best bet would be to use an AdomdCommand from the AnalysisServices library.Have you looked at the OLAP PivotTable Extensions utilities on codeplex?http://www.codeplex.com/OlapPivotTableExtendAnonymous
November 28, 2008
Andrew -thanks for the quick feedback. It appears that Excel was not build to support my idea.Thanks,DirkAnonymous
November 29, 2008
Andrew -could you please take a quick look at my code and tell me why it is not working. I all it from within an Excel Add-in.I really would appreciate your help.Thanks in advance,Dirk-- Sub CreateOLAPPivotTable() 'Declare variables Dim objMyPivotCache As Excel.PivotCache Dim objMyPivotTable As Excel.PivotTable Dim App As Excel.Application App = Globals.ThisAddIn.Application 'Create PivotCache objMyPivotCache = App.ActiveWorkbook.PivotCaches.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlExternal) 'Retrieve data With objMyPivotCache .Connection = "OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=server;Initial Catalog=DuPont_EMEA" .CommandText = "server cube FreeMining" .CommandType = Excel.XlCmdType.xlCmdCube End With 'Create PivotTable objMyPivotTable = App.ActiveSheet.PivotTables.Add( _ PivotCache:=objMyPivotCache, _''I believe the RANGE is crashing?'Exception from HRESULT: 0x800A03EC' TableDestination:=App.Range("A1"), _ TableName:="OLAP PivotTable") objMyPivotTable.CubeFields("[REVIEWPERIOD]").Orientation = Microsoft.Office.Core.XlPivotFieldOrientation.xlDataField objMyPivotTable.CubeFields("[DMDUNIT]").Orientation = Microsoft.Office.Core.XlPivotFieldOrientation.xlRowFieldEnd Sub--Anonymous
December 01, 2008
Dirk - I've looked at your code and I can't see anything wrong with it. TBH you might be better off posting OLAP questions to one of the Excel team's blogs, for instance: http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspxAnonymous
December 04, 2008
Hi,How can I create the pivotcache from DataSet?the dataset is created in the code, not from the external database.Thanks,BorisAnonymous
December 08, 2008
How do I programatically change the SQL Server connection used by an existing pivot table?Anonymous
December 09, 2008
The way you programatically change the connection is like this:http://support.microsoft.com/kb/327572The support article only updates the server name. If you want to change the whole string, it must be in the following format. Otherwise you'll get a cryptic 0x800A03EC exception.ODBC;DRIVER=SQL Server;SERVER=<<yourserver>>;DATABASE=<<yourdatabase>>;Network=DBMSSOCN;Trusted_Connection=YesAnonymous
December 09, 2008
The above connection string was truncated. Here it is again.ODBC;DRIVER=SQL Server;SERVER=<<your server>>;DATABASE=<<your database>>;Network=DBMSSOCN;Trusted_Connection=YesAnonymous
December 25, 2008
how to remove/hide datafields from a pivot table using vba code??Anonymous
December 26, 2008
Boris - you can create a PivotCache from an ADO Recordset, using the PivotCache.Recordset property. So, if you're starting from an ADO.NET DataSet, you can convert that to a Recordset first. See here for details: http://support.microsoft.com/kb/316337Anonymous
December 26, 2008
naren - to show/hide pivot fields, you can simply use the Visible property exposed by the Excel object model. For example:Excel.PivotFields rowFields = (Excel.PivotFields)this.pivotTable.get_RowFields(missing);for (int r = 1; r <= rowFields.Count; r++){ Excel.PivotField rowField = (Excel.PivotField)this.pivotTable.get_RowFields(r); Excel.PivotItems items = (Excel.PivotItems)rowField.PivotItems(missing); for (int i = 1; i < items.Count; i++) { Excel.PivotItem item = (Excel.PivotItem)items.Item(i); string s = item.Value; if (s.StartsWith("J")) { item.Visible = false; } }}Anonymous
February 10, 2009
How do i publish a pivot by code to a sharepoint site.In excel 2007Anonymous
February 10, 2009
Sayantan - this is really a question about usage of Excel and SharePoint, not really related to programmatic development of pivot tables. For the best answer, please post your question to one of the Excel and/or SharePoint forums: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel.programming&lang=en&cr=UShttp://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel&cat=en_US_e064c9ec-14bf-4ef7-ba07-6a1970559f64&lang=en&cr=USAnonymous
April 08, 2009
Does this work ok with excel 2003?Can the same technique be used for non pivot query tables?Anonymous
April 08, 2009
The comment has been removedAnonymous
April 16, 2009
The comment has been removedAnonymous
April 16, 2009
ronaldfer747 - see my previous reply to Boris, viz:You can create a PivotCache from an ADO Recordset, using the PivotCache.Recordset property. So, if you're starting from an ADO.NET DataSet, you can convert that to a Recordset first. See here for details: http://support.microsoft.com/kb/316337Anonymous
April 28, 2009
The comment has been removedAnonymous
May 29, 2009
Hi!Great article! I habe build a Cube PivotTable i can browse any PivotItem. Thats fine byu i'm not able to set any PivotFilter and also not able so set some attributes visible false. Has anyone an idee what i'm doing wrong.I got every time the same HRSEULT Error like Matt.Thx for any helpAnonymous
May 31, 2009
Hi, thank you for your great article.Someone know how can I hide the connection string from the "user's eyes"? The PivotTable continue working, updating data from database etc. but I must hide the connection string (or encrypt it) because the user can't see it (the credentials used to connect to database). If he go to the datasource's properties he can see what user and password was used to connect to the database, or if I can denied access to this property's dialog?Thanks for any help.Anonymous
June 25, 2009
I have a pivot table that is populated from an Oracle database thru a rather cumbersome query. I would like to use the raw data in the pivot table in a custom function to summarize the data. The majority of the time, the data population exceeds 65,536, which makes it impossible to show the detailed records. Does anybody know how to access the data from the pivot table memory?Anonymous
June 25, 2009
R.Keeton - sorry, I don't know the answer, and if you don't get a response from anyone else on this blog, I suggest you re-post to the Excel team's blog, eg:http://blogs.msdn.com/excel/archive/tags/PivotTables/default.aspxAnonymous
July 12, 2009
Thanks andreww for this great article! It helped me to build a pivot table programatically on my local machine but when I publish the project on my server and try to do the same from server side it gives me a blank excel file. Is this due to MSQuery permissions or what? Did anybody had the same issue?Thanks in advance for any help you can provide me!Anonymous
July 14, 2009
Hello, you help me?andrewwI need not show options in PivoteTable; Data; connection properties to prevent the connection string can not be viewed by the user. You might tell me how to doAnonymous
July 22, 2009
Solved! The problem was due to the lack of permissions. The solution was to define local impersonation of the published site and voilá!