{"id":2732,"date":"2016-03-18T12:42:33","date_gmt":"2016-03-18T12:42:33","guid":{"rendered":"http:\/\/www.ericwhite.com\/home2\/bm8qcmjy\/public_html\/blog\/?page_id=2732"},"modified":"2016-03-18T12:48:08","modified_gmt":"2016-03-18T12:48:08","slug":"creating-pivot-tables-part-2","status":"publish","type":"page","link":"https:\/\/www.ericwhite.com\/blog\/creating-pivot-tables-part-2\/","title":{"rendered":"Creating Pivot Tables, Part 2"},"content":{"rendered":"

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<\/a>).<\/p>\n

\/\/ Create from scratch<\/span>
row <\/span>=<\/span> <\/span>1<\/span>;<\/span>
<\/span>int<\/span> maxColumn <\/span>=<\/span> <\/span>1<\/span>;<\/span>
<\/span>using<\/span> <\/span>(<\/span>OpenXmlMemoryStreamDocument<\/span> streamDoc <\/span>=<\/span> <\/span>OpenXmlMemoryStreamDocument<\/span>.<\/span>CreateSpreadsheetDocument<\/span>())<\/span>
<\/span>{<\/span>
    <\/span>using<\/span> <\/span>(<\/span>SpreadsheetDocument<\/span> doc <\/span>=<\/span> streamDoc<\/span>.<\/span>GetSpreadsheetDocument<\/span>())<\/span>
    <\/span>{<\/span>
        <\/span>WorksheetPart<\/span> sheet <\/span>=<\/span> <\/span>WorksheetAccessor<\/span>.<\/span>AddWorksheet<\/span>(<\/span>doc<\/span>,<\/span> <\/span>\"Range\"<\/span>);<\/span>
        <\/span>using<\/span> <\/span>(<\/span>StreamReader<\/span> source <\/span>=<\/span> <\/span>new<\/span> <\/span>StreamReader<\/span>(<\/span>\"..\/..\/PivotData.txt\"<\/span>))<\/span>
        <\/span>{<\/span>
            <\/span>while<\/span> <\/span>(!<\/span>source<\/span>.<\/span>EndOfStream<\/span>)<\/span>
            <\/span>{<\/span>
                <\/span>string<\/span> line <\/span>=<\/span> source<\/span>.<\/span>ReadLine<\/span>();<\/span>
                <\/span>if<\/span> <\/span>(<\/span>line<\/span>.<\/span>Length<\/span> <\/span>><\/span> <\/span>3<\/span>)<\/span>
                <\/span>{<\/span>
                    <\/span>string<\/span>[]<\/span> fields <\/span>=<\/span> line<\/span>.<\/span>Split<\/span>(<\/span>','<\/span>);<\/span>
                    <\/span>int<\/span> column <\/span>=<\/span> <\/span>1<\/span>;<\/span>
                    <\/span>foreach<\/span> <\/span>(<\/span>string<\/span> item <\/span>in<\/span> fields<\/span>)<\/span>
                    <\/span>{<\/span>
                        <\/span>double<\/span> num<\/span>;<\/span>
                        <\/span>if<\/span> <\/span>(<\/span>double<\/span>.<\/span>TryParse<\/span>(<\/span>item<\/span>,<\/span> <\/span>out<\/span> num<\/span>))<\/span>
                            <\/span>WorksheetAccessor<\/span>.<\/span>SetCellValue<\/span>(<\/span>doc<\/span>,<\/span> sheet<\/span>,<\/span> row<\/span>,<\/span> column<\/span>++,<\/span> num<\/span>);<\/span>
                        <\/span>else<\/span>
                            <\/span>WorksheetAccessor<\/span>.<\/span>SetCellValue<\/span>(<\/span>doc<\/span>,<\/span> sheet<\/span>,<\/span> row<\/span>,<\/span> column<\/span>++,<\/span> item<\/span>);<\/span>
                    <\/span>}<\/span>
                    maxColumn <\/span>=<\/span> column <\/span>-<\/span> <\/span>1<\/span>;<\/span>
                <\/span>}<\/span>
                row<\/span>++;<\/span>
            <\/span>}<\/span>
        <\/span>}<\/span>
        sheet<\/span>.<\/span>PutXDocument<\/span>();<\/span>
        <\/span>WorksheetAccessor<\/span>.<\/span>SetRange<\/span>(<\/span>doc<\/span>,<\/span> <\/span>\"Sales\"<\/span>,<\/span> <\/span>\"Range\"<\/span>,<\/span> <\/span>1<\/span>,<\/span> <\/span>1<\/span>,<\/span> row <\/span>-<\/span> <\/span>1<\/span>,<\/span> maxColumn<\/span>);<\/span>
        <\/span>WorksheetPart<\/span> pivot <\/span>=<\/span> <\/span>WorksheetAccessor<\/span>.<\/span>AddWorksheet<\/span>(<\/span>doc<\/span>,<\/span> <\/span>\"Pivot\"<\/span>);<\/span>
        <\/span>WorksheetAccessor<\/span>.<\/span>CreatePivotTable<\/span>(<\/span>doc<\/span>,<\/span> <\/span>\"Sales\"<\/span>,<\/span> pivot<\/span>);<\/span>
    <\/span>}<\/span>
    streamDoc<\/span>.<\/span>GetModifiedSmlDocument<\/span>().<\/span>SaveAs<\/span>(<\/span>\"..\/..\/NewPivot.xlsx\"<\/span>);<\/span>
<\/span>}<\/span><\/pre>\n
The first part of the code is much like the process I showed in Part 1<\/a>. 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.<\/p>\n