Create Pivot Table

Sometimes, developers may need to create Excel reports with Pivot Tables where data is coming from different data sources (such as a database) and not known at design time. Following sample provides an approach to dynamically change soure data of a pivot table.

Using OpenXML SDK

Example loads data from a text file and then builds Pivot Table using OpenXML SDK

#!c#

// 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");
}

Create Pivot Table Using Aspose SDK

#!c#

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aspose.Cells;

namespace CreatePivotTable
{
    class Program
    {
        static void Main(string[] args)
        {
            string projectFiles = Path.GetFullPath("../../Files/");

            //Instantiating a Workbook object
            Workbook workbook = new Workbook();

            //Obtaining the reference of the newly added worksheet
            Worksheet sheet = workbook.Worksheets[0];

            Cells cells = sheet.Cells;

            //Setting the value to the cells
            Cell cell = cells["A1"];
            cell.PutValue("Sport");
            cell = cells["B1"];
            cell.PutValue("Quarter");
            cell = cells["C1"];
            cell.PutValue("Sales");

            cell = cells["A2"];
            cell.PutValue("Golf");
            cell = cells["A3"];
            cell.PutValue("Golf");
            cell = cells["A4"];
            cell.PutValue("Tennis");
            cell = cells["A5"];
            cell.PutValue("Tennis");
            cell = cells["A6"];
            cell.PutValue("Tennis");
            cell = cells["A7"];
            cell.PutValue("Tennis");
            cell = cells["A8"];
            cell.PutValue("Golf");

            cell = cells["B2"];
            cell.PutValue("Qtr3");
            cell = cells["B3"];
            cell.PutValue("Qtr4");
            cell = cells["B4"];
            cell.PutValue("Qtr3");
            cell = cells["B5"];
            cell.PutValue("Qtr4");
            cell = cells["B6"];
            cell.PutValue("Qtr3");
            cell = cells["B7"];
            cell.PutValue("Qtr4");
            cell = cells["B8"];
            cell.PutValue("Qtr3");

            cell = cells["C2"];
            cell.PutValue(1500);
            cell = cells["C3"];
            cell.PutValue(2000);
            cell = cells["C4"];
            cell.PutValue(600);
            cell = cells["C5"];
            cell.PutValue(1500);
            cell = cells["C6"];
            cell.PutValue(4070);
            cell = cells["C7"];
            cell.PutValue(5000);
            cell = cells["C8"];
            cell.PutValue(6430);

            Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet.PivotTables;

            //Adding a PivotTable to the worksheet
            int index = pivotTables.Add("=A1:C8", "E3", "PivotTable2");

            //Accessing the instance of the newly added PivotTable
            Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

            //Unshowing grand totals for rows.
            pivotTable.RowGrand = false;

            //Draging the first field to the row area.
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);

            //Draging the second field to the column area.
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 1);

            //Draging the third field to the data area.
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);

            //Saving the Excel file
            workbook.Save( projectFiles + "output.xlsx");

            Console.WriteLine("File Saved Successfully.");
            Console.ReadKey();

        }
    }
}

Source: https://bitbucket.org/asposemarketplace/aspose-for-openxml/src/ffec3cde9d87/Aspose.Cells/CreatePivotTable/?at=master

Wiki

Aspose for OpenXML / Create Pivot Table

Create Pivot Table

Using OpenXML SDK

Create Pivot Table Using Aspose SDK