This third part of the series is a screencast that explains the Open XML elements needed for a pivot table and how to create the parts and elements needed. Part 1 showed how to create a pivot table using an existing spreadsheet. Part 2 showed the example code for creating a pivot table from scratch.
The next step would be to add rows, columns, filters, and data values. If you are interested in code for that, please comment. The code involves a number of steps, but I would be happy to work through it, if there is interest.
Bob, I'm definitely interested in the code to add rows, columns, filters, and data values. Where can I download a copy of this solution (Part 3) ?
What Visual Studio 2010 PowerTool add-in are you using to view the *.xlsx files ? Where can I get it ?
Hello Bob, thanks for your recent blog posts covering pivot tables. Very useful and interesting! I'm also definitely very interested in the code to add rows, columns and data values. Is there also a way to refresh the pivot table without excel? I have to update the values existing in pivot tables with the values from a xml source file. Another way would also be like you've explained to create new pivot tables.
Thanks in advance!
There is a way to refresh without excel. The records cache would have to be updated after the source data is updated. The difficulty is that Excel encodes all or most of the string values, so the code is more complex than what I have shown so far. However, I would deal with that encoding to do any of the code for adding rows, columns, filters and data values to the pivot table.
Eric White has an excellent screen-cast about getting started with Open XML development. Part 2 mentions how to get the Package Editor for Visual Studio. Here is the link: openxmldeveloper.org/.../getting-started-with-open-xml-development-part-ii-tools.aspx
There was a problem with this example when using Excel 2010. The correction has been made to PowerTools Core 2.2.2. I have a detailed explanation of the problem and correction here: openxmldeveloper.org/.../creating-pivot-tables-updated.aspx
This example looks at a couple of OpenXML spreadsheet topics. I have been working with the cell styles
Could you provide the url where I can found the sample code for creating pivot table(part 3)