Creating Pivot Tables, Part 2
In this second part, I will show the code for creating a pivot table in Excel from scratch using Open XML. This post contains a brief explanation of the example that can be found in PowerTools Core 2.2.2 from Codeplex (https://github.com/OfficeDev/Open-Xml-PowerTools).
// Create from scratch
row = 1;
int maxColumn = 1;
using (OpenXmlMemoryStreamDocument streamDoc = OpenXmlMemoryStreamDocument.CreateSpreadsheetDocument())
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart sheet = WorksheetAccessor.AddWorksheet(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);
}
maxColumn = column - 1;
}
row++;
}
}
sheet.PutXDocument();
WorksheetAccessor.SetRange(doc, "Sales", "Range", 1, 1, row - 1, maxColumn);
WorksheetPart pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);
}
streamDoc.GetModifiedSmlDocument().SaveAs("../../NewPivot.xlsx");
}
The first part of the code is much like the process I showed in Part 1. It fills in the data from an external text file. After that, there are just a few method calls to set up an empty pivot table that is ready to be configured. I discuss both parts in the screencast below. I will also be posting another screencast in a few days that goes over the technical details of creating the XML for a pivot table. Then next week, I will post more information about how to configure the rows, columns, filters, and data values for the pivot table. That gets really technical, so be warned. I don’t have a sample for that, yet, but I might be encouraged to create one if there is enough interest.