Creating Pivot Tables, Part 1
This is my first post about creating pivot tables using the Open XML SDK. I am assuming you already know how to create a pivot table using Excel.
The first approach I am going to show for creating pivot tables is simplified by using a spreadsheet with the pivot table already set up. The source for the pivot table will be a named range in another sheet. I created an example program that updates the source data, changes the named range to include the new rows, then saves the result. When that spreadsheet is opened in Excel, the user can then refresh the pivot table and see the new data.
static void Main(string[] args)
{
int row = 1;
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(SmlDocument.FromFileName("../../QuarterlySales.xlsx")))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart sheet = WorksheetAccessor.GetWorksheet(doc, "Range");
using (StreamReader source = new StreamReader("../../PivotData.txt"))
{
while (!source.EndOfStream)
{
string line = source.ReadLine();
if (line.Length > 3)
{
string[] fields = line.Split(',');
int column = 1;
foreach (string item in fields)
{
double num;
if (double.TryParse(item, out num))
WorksheetAccessor.SetCellValue(doc, sheet, row, column++, num);
else
WorksheetAccessor.SetCellValue(doc, sheet, row, column++, item);
}
}
row++;
}
}
sheet.PutXDocument();
WorksheetAccessor.UpdateRangeEndRow(doc, "Sales", row - 1);
}
streamDoc.GetModifiedSmlDocument().SaveAs("../../QuarterlyPivot.xlsx");
}
}
This example gets the new data from a simple text file and fills in the appropriate cells in the existing spreadsheet, QuarterlySales.xlsx. The static methods in WorksheetAccessor are a new addition to the PowerTools Core. That code can be found in version 2.2.1 on Codeplex (https://github.com/OfficeDev/Open-Xml-PowerTools). The classes OpenXmlMemoryStreamDocument and SmlDocument come from the PowerTools Core code as well. You can use the core code for your own programs in the same way that the examples do.
The other PowerTools Core examples don’t use the OpenXmlMemoryStreamDocument class. The reason I use it here is that I didn’t want to keep creating that object for every call to set a cell value. I would also have had to create an array of some kind to pass to a method that sets all the cell values. In this case, I chose to make separate calls for each value, but it could be done the other way.
In the next part, I will expand on this to create the entire spreadsheet from scratch. I will also post a more technical explanation of how all these pieces are created using SpreadsheetML. In the meantime, I hope you find this first sample useful.