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.

Answer 1

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