Update Source Data for Pivot Table

using OpenXML SDK

This code snippet of 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. This 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.

#!c#

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

Using Aspose SDK

Steps to Change Source Data of a Pivot Table

1. Creating a New Designer Template

Please create a new designer template file as folowing chart. Then define a named range, DataSource, which refers to this range of cells.

2. Creating a Pivot Table Based on this Named Range

In Microsoft Excel, choose Data | PivotTable and PivotChart Report.... Then create a pivot table based on the named range (DataSource) that we created in first step.

Drag the corresponding field to pivot table row and column, then create the resulting pivot table as following

Right click the pivot table, select Table Options . Check Refresh on open in Data options settings.

Now, you can save this file as your designer template file.

3. Populating New Data and Changing Source Data of Pivot Table

After the designer template is created, you can use the following code to change the source data of the pivot table.

#!c#

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

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

            Workbook workbook = new Workbook(projectFiles + "Book.xlsx");

            //Accessing the first worksheet in the Excel file
            Worksheet worksheet = workbook.Worksheets[0];

            //Populating new data to the worksheet cells
            worksheet.Cells["A10"].PutValue("Golf");
            worksheet.Cells["B10"].PutValue("Qtr4");
            worksheet.Cells["C10"].PutValue(5000);

            //Changing named range "DataSource"
            Range range = worksheet.Cells.CreateRange(0, 0, 10, 3);
            range.Name = "DataSource";

            //Saving the modified 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/PivotTableUpdateRange/?at=master

Wiki

Aspose for OpenXML / Update Source Data for Pivot Table

Update Source Data for Pivot Table

using OpenXML SDK

Using Aspose SDK