列出并刷新所有Pivot表

I'm trying to create a script that refresh all data in a worksheet and then refreshes the pivot tables afterward (because data in pivot tables are normally refreshed before the data from Databases the result is not correct by default).

我正在尝试创建一个脚本，该脚本可以刷新工作表中的所有数据，然后在随后刷新数据透视表(因为在数据来自数据库的数据之前通常会刷新数据，默认情况下，结果是不正确的)。

To do this i use this script (because I'm starting this script automatically each day at 9.00).

为此，我使用这个脚本(因为我每天9点自动启动这个脚本)。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            // Get fully qualified path for xlsx file
            var spreadsheetLocation = "C:\\update_rapport\\Salgsrapport.xlsx";

            var exApp = new Microsoft.Office.Interop.Excel.Application();
            var exWbk = exApp.Workbooks.Open(spreadsheetLocation);
            //var exWks = (Microsoft.Office.Interop.Excel.Worksheet)exWbk.Sheets["responses(7)"];

            exWbk.RefreshAll();

            exApp.DisplayAlerts = false;

            // This part is not correct. Need to find all pivot tables and update them
            Object PivotTables(
                Object Index
            );


            string save_file_name = "C:\\temp\\updated\\Salgsrapport.xlsx";
            exWbk.SaveAs(save_file_name);
            exWbk.Close(true);
            exApp.Quit();

        }

    }
}

The closest thing i have found to looping through all pivot tables is this: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.pivottables.aspx

我发现，在所有的透视表中循环的最接近的东西是:http://msdn.microsoft.com/en- us/library/microsoft.office.tools.excel.worksheet.aspx。

However it's not a complete example, and I have never really programmed C# before so I'm kind of lost here. There might be a simpler solution to this problem, any hint is appreciated.

但是这并不是一个完整的例子，而且我以前从来没有真正编程过c#，所以我有点迷失了。对于这个问题可能有一个更简单的解决方案，任何提示都是值得赞赏的。

2 个解决方案

#1

Since it's a year later, I'm guessing you've already found a solution to your problem... that said, for the benefit of future generations, I was also seeking a solution for a similar problem.

一年后的今天，我想你已经找到解决问题的办法了……也就是说，为了子孙后代的利益，我也在寻找一个类似问题的解决方案。

As far as I can tell, the PivotCaches method at the workbook level appears to expose all Pivot Table objects. I am still working through my issue, but if your goal is to refresh all pivot tables, then something like this should work:

据我所知，工作簿级别的数据透视缓存方法显示了所有的数据透视表对象。我还在研究我的问题，但是如果你的目标是刷新所有的数据透视表，那么类似这样的东西应该是有用的:

foreach (Microsoft.Office.Interop.Excel.PivotCache pt in exWbk.PivotCaches())
    pt.Refresh();

I do know for sure that if you know the name of your pivot table, you can do this:

我确信，如果你知道你的数据透视表的名字，你可以这样做:

exWbk.Sheets["Sheet1"].PivotTables["PivotTable1"].PivotCache.Refresh();

I have been using this successfully for a while.

我已经成功地使用了一段时间了。

One thing that does confuse me about your question, however, is that I was under the impression that exWbk.RefreshAll(); refreshes every object in the workbook and takes dependencies into account. I was surprised to hear that invoking that did not also update the pivot tables.

关于你的问题，有一件事确实让我困惑，那就是我的印象是exwbk . reshreshall ();刷新工作簿中的每个对象并考虑依赖项。我很惊讶地听到调用它并没有更新数据透视表。

Update:

更新:

I found a better solution here. This is what I was looking for to begin with.

我找到了更好的解决办法。这就是我开始寻找的。

http://www.pcreview.co.uk/threads/vsto-how-to-find-all-pivot-tables-in-the-workbook.3476010/

Excel.PivotTables pivotTables1 =
   (Excel.PivotTables)ws.PivotTables(Type.Missing);

if (pivotTables1.Count > 0)
{
    for (int j = 1; j <= pivotTables1.Count; j++)
}

#2

foreach (Microsoft.Office.Interop.Excel.PivotCache pt in exWbk.PivotCaches())

foreach(Microsoft.Office.Interop.Excel。在exWbk.PivotCaches PivotCache pt())

pt.Refresh();

#1