Excel "Refresh All" with OpenXML

Question

I have an excel 2007 file (OpenXML format) with a connection to an xml file. This connection generates an excel table and pivot charts.

I am trying to find a way with OpenXML SDK v2 to do the same as the "Refresh All" button in Excel. So that I could automatically update my file as soon as a new xml file is provided.

Thank you.

Mudskipper

Concerning the pivot tables, I simply checked the option "Refresh the data on opening the file" of each pivot table. – Mudskipper

Answer 1

You can't do this with Open XML. Open XML allows you to work with the data stored in the file and change the data and formulas and definitions and such. It doesn't actually do any calculations.

Excel automation technically would work, but it's absolutely not recommended for a server environment and is best avoided on the desktop if at all possible.

Embargo

Answer 2

Well there is quite good workaround for this. Using OpenXML you can turn on "refresh data when opening the file" option in pivot table (right click on pivot table->PivotTable Options->Data tab). This result in auto refresh pivot table when user first opens spreadsheet. The code:

  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;               
        }

you need to determine "path" to yours pivotCacheDefinition - use OpenXML SDK 2.0 Productivity Tool to look for it.

BuildUriPartDictionary is a standard method generated by OpenXML SDK 2.0 Productivity Tool

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;
    }

Another solution is to convert your spreadsheet to macroenabled, embed there a VBA script that will refresh all pivot tables. This can happen on button click or again when user opens spreadsheet. Here you can find VBA code to refresh pivot tables: http://www.ozgrid.com/VBA/pivot-table-refresh.htm

Holcomb

Answer 3

I think the only way you can do this is following this type of method..

1. Save Open XML workbook back to a xlsx file.
2. Load the workbook using the Excel object model.
3. Call either

ThisWorkbook.PivotCaches(yourIndex).Refresh();

or

ThisWorkbook.RefreshAll();

although I was pretty sure RefreshAll would also work.

4. Use the object model to Save the workbook and close it.
5. Reopen for use with xml namespaces.

Lowney

This is a method from the COM excel class. It is unavailable in the OpenXML classes since it is to manipulate the XML. Finally, I don't think it is possible. – Mudskipper

Refresh All should refresh pivot tables. I'll edit my answer in line with your comment above. – Lowney

Thanks for you time but I finally found another solution for my problem: I set the option "refresh when opening the file" of all my pivot tables. Then I simply export data to a main table so that the excel file automatically changes. – Mudskipper

yep you can definately set pivot tables to refresh automatically, thats just a configuration change in Excel. I assumed you were looking for a means of manual refresh due to performance isuses, sorry my bad for making assumption. You should post your answer and accept it for others to refer to. – Lowney

Answer 4

The solution provided by Bartosz Strutyński will only work if the workbook does contain pivot tables and they share the same cache. If the workbook does not contain pivot tables, the code will throw a NullPointerException. If the workbook contains pivot tables that use different caches (which is the case when data sources are different), only one group of pivot tables that use the same cache will be refreshed. Below is the code based on Bartosz Strutyński's code, free of the aforementioned limitation, and not relying on knowing the "path" of PivotCacheDefinition object. The code also inlines BuildUriPartDictionary, which allows avoiding enumeration of uriPartDictionary in case it’s not used somewhere else, and uses explicit types, to ease searching documentation for the used classes.

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;
        }
    }
}

Ladew