Search code examples
c#excelpivot-tableepplus

EPPlus: How to update Pivot Table SourceRange

I'm getting stuck trying to update the SourceRange of a Pivot-Table with EPPLus inside a C# class. I've found that CacheDefinition.SourceRange contains the DataSource of my existing Pivot-Table but I don't know how to change it. Existing Pivot-Table datasource is a range on a data worksheet in the same Excel file.

Any advice?

Thanks in advance, Alessandro

Solution

  • This might work:

    You can create a self-dimensioning defined name that encompasses your data range. I use this all the time.

    Open Name Manager.

    Click New.

    Enter a Name for your range.

    Put the following in the Refers to: line

    OFFSET(DataSource!$A$1,0,0,COUNTA(DataSource!$A:$A),COUNTA(DataSource!$1:$1))

    syntax: OFFSET(reference, rows, cols, [height], [width])

    Substitute your sheet/tab name for DataSource. This assumes that the table starts in A1 (first section) and you that you want a defined name as long as the number of values in column A and as wide as the values in row 1. It is a very flexible and useful method for making sure your defined names encompass all the data on the sheet.