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 =


    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 =  

pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;

Excel.PivotField rowField =  

rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;


Add a data field for the sales for 2004:


    pivotTable.PivotFields("2004"), "Sum of 2004", Excel.XlConsolidationFunction.xlSum);

Done. The end result looks like this:

Comments (59)
  1. You’ve been kicked (a good thing) – Trackback from

  2. Alexandre Brisebois says:

    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.

  3. Garry Trinder says:

    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);




  4. Alexandre.Brisebois says:

    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,

  5. for those who have the same questions as I’ve been having.

  6. Garry Trinder says:

    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();


    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);





    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);





    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);





    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);




  7. shreyas says:

    How do i make this functionality work for SSAS OLAP Cube

  8. shreyas says:

    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 urgently



  9. shreyas says:


    once again mailing you.

    In my previos code if i change my adding pivot code to

    Microsoft.Office.Interop.Excel.PivotTable pvt = pivotCache.CreatePivotTable(sheet.Cells[8, 3], "PivotTable1", Type.Missing, Type.Missing);

    I get error something as OLE DB error saying problem in opening dialog box and the second time execute the same code i get the error as "Exception from HRESULT: 0x800A03EC"

  10. amrut says:

    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?

  11. Shreyas says:

    Has any one done this…….see the above threads 🙂



  12. Teme says:

    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";"


    pivotCache.CommandText = "name of the cube";

    pivotCache.CommandType = XlCmdType.xlCmdCube;

    And assignment of fields:

    pivotTable.CubeFields["Name of cube dimension"].Orientation = XlPivotFieldOrientation.xlRowField;

  13. Teme says:

    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";"


    pivotCache.CommandText = "name of the cube";

    pivotCache.CommandType = CmdType.xlCmdCube;

    And assignment of fields:

    pivotTable.CubeFields["Name of cube dimension"].Orientation = XlPivotFieldOrientation.xlRowField;

  14. Gaby says:

    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?


  15. 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 Brisebois

  16. Gaby says:

    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)?



  17. Gaby says:

    I made it work with this line

    table1.PivotFields("Data").Orientation = Excel.XlPivotFieldOrientation.xlColumnField

    before setting the RowColumn that caused the problem with too many rows.


  18. Teme need furhter help says:

    Hi Teme,

    Thanks…i tried your suggested code.

    I am getting error when assigning dimensions to the pivot table. The error is "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"

    pvt.CubeFields["Data Source Name"].Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField;

    here "Data Source Name" is the name of the dimension but it comes under dimension Dim Source, it is a hierachy under this dimension.

    Would be helpful if you can send me your working code

    My mail id is



  19. Teme...finally it worked says:

    Thanks Teme….finally it worked 🙂



  20. Teme..need help says:

    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.



  21. Shreyas,

    Why are you generating these on IIS, this might turn out to be very resource hungry?

    Have you installed the VSTO components on the server ? Haven you Installed Excel 2007 on the server ?

    Best regards,

    Alexandre Brisebois

  22. I have been working with PivotCharts,

    and I’m trying to wrap my head around getting the chart layout. I have only found the ApplyLayout [ ApplyLayout(1,Type.Missing); ] method of the Chart Object. Is if possible for me to retrieve this integer value ?

    Best regards,

    Alexandre Brisebois

  23. Microsoft.Office.Interop.Excel help says:


    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.



  24. Teme says:


    Sorry 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?


    For detailed questions:

  25. Garry Trinder says:

    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.

  26. 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.

  27. Helen says:


    If I already have a dataset with the data, how can I create the pivot table?

  28. Helen says:

    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?


  29. Garry Trinder says:

    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 =



       (Excel.Range)sheet.get_Range("A1", "C17"));

  30. Connie LeMaster says:

    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.

  31. Garry Trinder says:

    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();


    Excel.PivotItems items = (Excel.PivotItems)


    for (int i = 1; i < items.Count; i++)


       Excel.PivotItem item = (Excel.PivotItem)items.Item(i);

       builder.AppendLine(String.Format("{0}", item.Value));



  32. Connie LeMaster says:

    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!

  33. Dirk says:

    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, Dirk

  34. Dirk says:

    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.

  35. Garry Trinder says:

    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?

  36. Dirk says:

    Andrew –

    thanks for the quick feedback. It appears that Excel was not build to support my idea.



  37. Dirk says:

    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,


       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.xlRowField

    End Sub

  38. Garry Trinder says:

    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:

  39. Boris says:


    How can I create the pivotcache from DataSet?

    the dataset is created in the code, not from the external database.



  40. John says:

    How do I programatically change the SQL Server connection used by an existing pivot table?

  41. John says:

    The way you programatically change the connection is like this:

    The 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=Yes

  42. John says:

    The above connection string was truncated. Here it is again.

    ODBC;DRIVER=SQL Server;SERVER=<<your server>>;DATABASE=<<your database>>;Network=DBMSSOCN;Trusted_Connection=Yes

  43. naren says:

    how to remove/hide datafields from a pivot table using vba code??

  44. Garry Trinder says:

    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:

  45. Garry Trinder says:

    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;




  46. Sayantan Samanta says:

    How do i publish a pivot by code to a sharepoint site.In excel 2007

  47. Garry Trinder says:

    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:

  48. adolf garlic says:

    Does this work ok with excel 2003?

    Can the same technique be used for non pivot query tables?

  49. Garry Trinder says:

    adolf – yes, the general behavior is the same, although ome specific features have obviously changed from Excel 2003 to Excel 2007. For example, Excel 2003 did not provide ShowTableStyleRowStripes or TableStyle2 properties, so you’d have to use the similar TableStyle property instead, eg:

    //this.pivotTable.ShowTableStyleRowStripes = true;

    //this.pivotTable.TableStyle2 = "PivotStyleLight1";

    this.pivotTable.TableStyle = "PivotStyleLight1";

  50. ronaldfer747 says:

    Ho do I use a table from a Dataset as a source data in a pivot table properties?

  51. Garry Trinder says:

    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:

  52. Matt says:

    Did anyone have a solution for shreyas HRESULT: 0x800A03EC error? I’m having the same problem and have been unable to solve it. I believe the error has to do with the second parameter in PivotTables.Add() which is the tableDefinition. Application.ActiveCell doesn’t seem to work and any other explicit cell location also doesn’t work.


  53. Artur says:


    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 help

  54. Christophe T. Chavey says:

    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.

  55. R. Keeton says:

    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?

  56. Garry Trinder says:

    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:

  57. joeaxe says:

    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!

  58. Rolando Granera says:

    Hello, you help me?


    I 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 do

  59. joeaxe says:

    Solved! The problem was due to the lack of permissions. The solution was to define local impersonation of the published site and voilá!

Comments are closed.

Skip to main content