This project has moved and is read-only. For the latest updates, please go here.

Updata Pivot Table

May 29, 2012 at 1:06 PM

Hi, 

I have a pivot table from a template file, i need to update the data from a datarange, there's no method in the Pivot class.

How can i achieve this?

May 30, 2012 at 8:08 PM
This function is dynamic as far as length and width of the source data.  You could easily update datarange manually if you know the values.

//srcFile is the file to edit
//pivotSheet is the name of the sheet with the pivot table
//srcSheet is the data source sheet for the pivot table
//srcCell is the top left corner of the srcSheet (like A1)
//your srcCell should include the column header row

public static void UpdatePivotTable(string srcFile, string pivotSheet, string srcSheet, string srcCell)
        {            
            try
            {      
                FileInfo file = new FileInfo(srcFile);
                ExcelPackage package = new ExcelPackage(file);
                //get a reference to the Pivot and Details tables
                ExcelWorksheet piv = package.Workbook.Worksheets.First(x=>x.Name==pivotSheet);
                ExcelWorksheet det = package.Workbook.Worksheets.First(x => x.Name == srcSheet);

                //build the range from top left to the far right column and bottom row (removes blanks)               
                var dataRange = det.Cells[srcCell+":" + det.Dimension.End.Address.ToString()];
                piv.PivotTables[0].CacheDefinition.SourceRange = dataRange;               
                package.Save();                
            }
            catch (Exception ex)
            {
                //your error handling
            }
        }

Sep 12, 2014 at 11:14 AM
This does not work in version 4.

CacheDefinition.SourceRange always returns null so when you try to set it falls over (this is because it does some validation that the range you're trying to set has the same number of columns as the current source range.

From the source code:
                var sr = SourceRange;
                if (value.End.Column - value.Start.Column != sr.End.Column - sr.Start.Column)
                {
                    throw (new ArgumentException("Can not change the number of columns(fields) in the SourceRange"));
                }
SourceRange is null so you get a nullreferenceexception on the evaluation
Oct 10, 2014 at 5:44 AM
So is there anyway to update the source range/data range for pivots via EPPLUS? I really need to update bunch of pivots from a template via epplus.

Thanks!
Oct 10, 2014 at 4:34 PM
Not unless you want to hack the source code. I ended up abandoning EPPlus - it's a great project but not really mature enough. My current solution uses OpenXML to change the connections on the spreadsheet, injecting connection string and sql command, and then refresh the sheet on open. This loads the data I want to display in the pivot for the user.
Oct 10, 2014 at 4:44 PM
thecolour wrote:
Not unless you want to hack the source code. I ended up abandoning EPPlus - it's a great project but not really mature enough. My current solution uses OpenXML to change the connections on the spreadsheet, injecting connection string and sql command, and then refresh the sheet on open. This loads the data I want to display in the pivot for the user.
Would you please share your code of how you change the connections on the spreadsheet and refresh on open?

Thanks a lot for your help!
Nov 28, 2014 at 12:41 AM
This code works for me for setting Pivot Table data source:
var sheet1 = pck.Workbook.Worksheets["Sheet1"];
var sourceRange = "$A$5:$AG25";
var pivotTable = sheet1.PivotTables.First();
pivotTable.CacheDefinition.SourceRange = new ExcelNamedRange("", sheet1, sheet1, sourceRange);
Apr 18, 2016 at 5:52 PM
Edited Apr 18, 2016 at 5:52 PM
Seems like this is still a problem. Has anyone found the solution? In my case, I can find the pivot table, updating the sourcerange, saving it, but it does not get saved.
var dataRange = sheet.Cells["A1:" + sheet.Dimension.End.Address.ToString()];
pivotTable.CacheDefinition.SourceRange = dataRange;