8

We're trying to build a default Excel dashboard which our users can download. When downloading the Excel sheet, we want to populate a sheet in the file with their data.

We're using EPPlus for the manipulation of the Excel sheet.

In short, the structure of the sheet is as follows:

  1. Sheet A with reporting elements (pivot tables, pivot charts and slicers)
  2. Sheet B which contains the entire data set
  3. A named range defined as =OFFSET(Data!$A$1;0;0;COUNTA(Data!$A:$A);COUNTA(Data!$1:$1)) which simply adapts to the size of the dataset we insert into that sheet
  4. Whatever the named range contains i loaded into Power Query and added to the workbooks Data Model
  5. All the reporting elements from Sheet A is configured to load data from the Data Model

Overall, this works great as long as we're manually stuffing Sheet B with our data. When we try to use EPPlus for filling in the data we get an error when trying to save the file:

The cachesource is not a worksheet

By trial and error, we've stripped the sheet of it's parts to isolate the cause of the problem. We suspected that it might have been the slicers, the use of Power Query/Data Model or the trick with the named range. However, none of these looks to be the issue - if we remove all Pivot Tables from the sheet then we're able save the Workbook just fine. Surprising to me, we're able to use the Pivot Charts just fine, it's only the tables which are causing the issue.

Any suggestions as to how to avoid this problem with EPPlus? For now, we've continued without the use of Pivot Tables would we would like to have them return at some point :)

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
Christian A. Rasmussen
  • 250
  • 3
  • 17
  • 1
    Based on https://github.com/pruiz/EPPlus/blob/master/EPPlus/Table/PivotTable/ExcelPivotCacheDefinition.cs (and the error message) have you tried having the cachesource as a worksheet? – mjwills Oct 18 '17 at 11:21
  • To be honest, I don't have the slightest idea what that means or how to do it :) – Christian A. Rasmussen Oct 18 '17 at 11:23
  • @mjwills I've googled around a bit and found some tricks to disabling the Pivot Table cache. I can't find anything regarding changing the cache type anywhere. The trick should be to untick the "Save source data with file" but that option seems to be greyed out in our case when the Pivot Table is loading from the Data Model. – Christian A. Rasmussen Oct 18 '17 at 11:41
  • Reading further into it, that specific option seems to be disabled when the data source for the table is an OLAP cube. Looking at the property for the sheet's Data Model it does indeed list it as a Cube. – Christian A. Rasmussen Oct 18 '17 at 11:51
  • any chance you can post an example file somewhere? – Stewart_R Oct 26 '17 at 18:24
  • Did you find a solution, or did you use a different library? – Arturo Torres Sánchez Jun 01 '18 at 21:08

2 Answers2

1

Error - "The cachesource is not a worksheet" can be due to recognition of source data in range by Excel as range in worksheet.

You can change the dumped data in the range into Sheet B as a table, using ws.ListObjects.Add. Whenever pivot table is refreshed, it should automatically takes all the data within the table.

Please also check another alternative available from a related question -Defining a table rather than a range as a PivotTable 'cacheSource', if that helps.

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
IITC
  • 81
  • 5
  • Thank you for your feedback @IITC Can I have you elaborate a bit on the "You can change the dumped data in the range into Sheet B [...]" I'm not sure I completely follow you. Regarding changing the cache source, I don't think that's possible with my setup. As mentioned in one of my comments above, I'm using Power Query and its OLAP Cube/Data Model to hook up all the pivot charts and tables. This seems to limit my options considerably. – Christian A. Rasmussen Oct 24 '17 at 12:38
0

There are many issues related with cache when manipulating Pivot data. Once I had a look at it for this other bounty here.

In must be the same with EPPlus. You have to handle the Pivot data cache manually, like done here.

And here the guy gives complete solution for handling filters with EPPlus.

Hope it helps.

J. Chomel
  • 8,193
  • 15
  • 41
  • 69