Excel“全部刷新”使用 OpenXML

Question

我有一个 excel 2007 文件（OpenXML 格式），它连接到 xml 文件。此连接会生成 Excel 表格和数据透视图。

我正在尝试找到一种使用 OpenXML SDK v2 执行与 Excel 中的“全部刷新”按钮相同的操作的方法。这样我就可以在提供新的 xml 文件后自动更新我的文件。

谢谢。

Answer 1

对此有一个很好的解决方法。
使用 OpenXML，您可以在数据透视表中打开“打开文件时刷新数据”选项（右键单击数据透视表 -> 数据透视表选项 -> 数据选项卡）。
当用户首次打开电子表格时，这会导致自动刷新数据透视表。
代码：

  using (var document = SpreadsheetDocument.Open(newFilePath, true))
        {
            var uriPartDictionary = BuildUriPartDictionary(document);

            PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1 = (PivotTableCacheDefinitionPart)uriPartDictionary["/xl/pivotCache/pivotCacheDefinition1.xml"]; 
            PivotCacheDefinition pivotCacheDefinition1 = pivotTableCacheDefinitionPart1.PivotCacheDefinition;
            pivotCacheDefinition1.RefreshOnLoad = true;               
        }

您需要确定您的数据透视缓存定义的“路径” - 使用 OpenXML SDK 2.0 生产力工具来查找它。

BuildUriPartDictionary 是由 OpenXML SDK 2.0 生产力工具生成的标准方法。

protected Dictionary<String, OpenXmlPart> BuildUriPartDictionary(SpreadsheetDocument document)
    {
        var uriPartDictionary = new Dictionary<String, OpenXmlPart>();
        var queue = new Queue<OpenXmlPartContainer>();
        queue.Enqueue(document);
        while (queue.Count > 0)
        {
            foreach (var part in queue.Dequeue().Parts.Where(part => !uriPartDictionary.Keys.Contains(part.OpenXmlPart.Uri.ToString())))
            {
                uriPartDictionary.Add(part.OpenXmlPart.Uri.ToString(), part.OpenXmlPart);
                queue.Enqueue(part.OpenXmlPart);
            }
        }
        return uriPartDictionary;
    }

另一个解决方案是将您的电子表格转换为启用宏，并在其中嵌入一个将刷新所有数据透视表的 VBA 脚本。
单击按钮时可能会发生这种情况，或者当用户打开电子表格时也会发生这种情况。
在这里您可以找到用于刷新数据透视表的 VBA 代码：
http://www.ozgrid.com/VBA/pivot-table-refresh.htm

Answer 2

使用 Open XML 无法做到这一点。 Open XML 允许您处理文件中存储的数据并更改数据、公式和定义等。它实际上并不进行任何计算。

从技术上讲，Excel 自动化是可行的，但绝对不建议在服务器环境中使用它，并且如果可能的话最好避免在桌面上使用。

Answer 3

我认为您可以执行此操作的唯一方法是遵循这种类型的方法。

1. 将 Open XML 工作簿保存回 xlsx 文件。
2. 使用 Excel 对象模型加载工作簿。
3. 调用

ThisWorkbook.PivotCaches(yourIndex).Refresh();

或

ThisWorkbook.RefreshAll();

尽管我非常确定 RefreshAll 也可以工作。

4. 使用对象模型保存工作簿并关闭它。
5. 重新打开以与 xml 命名空间一起使用。

Answer 4

Bartosz Strutyński 提供的解决方案仅在工作簿包含数据透视表且共享相同的缓存。如果工作簿不包含数据透视表，代码将抛出 NullPointerException。如果工作簿包含使用不同缓存的数据透视表（这是数据源不同的情况），则只会刷新一组使用相同缓存的数据透视表。下面是基于 Bartosz Strutyński 的代码，没有上述限制，并且不依赖于了解 PivotCacheDefinition 对象的“路径”。该代码还内联了 BuildUriPartDictionary，它允许避免枚举 uriPartDictionary（以防在其他地方使用它），并使用显式类型来简化对所用类的文档搜索。

Dictionary<String, OpenXmlPart> uriPartDictionary = new Dictionary<String, OpenXmlPart>();
Queue<OpenXmlPartContainer> queue = new Queue<OpenXmlPartContainer>();
queue.Enqueue(document);
while (queue.Count > 0)
{
    foreach (IdPartPair part in queue.Dequeue().Parts.Where(part => !uriPartDictionary.Keys.Contains(part.OpenXmlPart.Uri.ToString())))
    {
        uriPartDictionary.Add(part.OpenXmlPart.Uri.ToString(), part.OpenXmlPart);
        queue.Enqueue(part.OpenXmlPart);
        PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart;
        if ((pivotTableCacheDefinitionPart = part.OpenXmlPart as PivotTableCacheDefinitionPart) != null)
        {
            pivotTableCacheDefinitionPart.PivotCacheDefinition.RefreshOnLoad = true;
        }
    }
}