如果工作簿包含数据透视表，添加数据后无法保存Excel表

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.

我们正在尝试构建一个用户可以下载的默认Excel仪表板。当下载Excel表时，我们希望在文件中填充包含数据的表。

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

我们使用EPPlus操作Excel表。

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

简而言之，这张纸的结构如下:

1. Sheet A with reporting elements (pivot tables, pivot charts and slicers)
2. 带有报告元素的表格A(数据透视表、数据透视表和切片机)
3. Sheet B which contains the entire data set
4. 包含整个数据集的表B
5. 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
6. 一个命名范围定义为=偏移量(数据!$1;0;COUNTA(数据!$A:$A);COUNTA(数据!$1:$1))，它仅仅适应我们插入到数据表中的数据集的大小
7. Whatever the named range contains i loaded into Power Query and added to the workbooks Data Model
8. 无论指定的范围包含什么，我都将其加载到Power Query并添加到工作簿数据模型中
9. All the reporting elements from Sheet A is configured to load data from the Data Model
10. 表A中的所有报表元素都配置为从数据模型加载数据

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:

总而言之，只要我们用我们的数据手工填入表格B，这个方法就很有效。当我们尝试使用EPPlus来填充数据时，当我们尝试保存文件时，我们会得到一个错误:

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.

通过反复试验，我们剥离了它的各个部分，以隔离问题的原因。我们怀疑可能是切片器、Power查询/数据模型的使用或命名范围的技巧。然而，这些似乎都不是问题所在——如果我们从表中删除所有的Pivot表，那么我们就可以保存工作簿。令我惊讶的是，我们能够很好地使用主图，这只是导致问题的表格。

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 :)

关于如何避免EPPlus出现这个问题，有什么建议吗?目前，我们一直没有使用Pivot表，我们希望它们在某个时候返回:)

2 个解决方案

#1

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

错误—“cachesource不是工作表”可能是由于在工作表中Excel的范围内的源数据的识别。

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.

您可以使用ws.ListObjects.Add将范围内的转储数据更改为表B。无论何时刷新pivot表，它都应该自动获取表中的所有数据。

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

还请检查相关问题的另一个可用选项——定义一个表，而不是将一个范围定义为数据透视表的“cacheSource”(如果有帮助的话)。

#2

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

在操作Pivot数据时，存在许多与缓存相关的问题。有一次，我在这里看到了其他的赏金。

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

必须和EPPlus一样。您必须手动处理主数据缓存，就像这样。

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

这里他给出了用EPPlus处理过滤器的完整解决方案。

Hope it helps.

希望它可以帮助。