Excel “Refresh All” with OpenXML

excel-2007office-2007openxmlopenxml-sdk

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.

Best Answer

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

Related Solutions

.net – OpenXML SDK: Make Excel recalculate formula

spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

Works for me!

How change excel 2007 document orientation to landscape by OpenXML sdk

You're on the right track with the OrientationValue.Landscape. You just need to loop through all worksheets and set the orientation attribute on the PageSetup element in order to set all worksheets to landscape:

    public static void SetLandscape(SpreadsheetDocument document)
        {
            WorkbookPart workbookPart = document.WorkbookPart;
            IEnumerable<string> worksheetIds = workbookPart.Workbook.Descendants<Sheet>().Select(w => w.Id.Value);
            WorksheetPart worksheetPart;
            foreach (string worksheetId in worksheetIds)
            {
                worksheetPart = ((WorksheetPart)workbookPart.GetPartById(worksheetId));
                PageSetup pageSetup = worksheetPart.Worksheet.Descendants<PageSetup>().FirstOrDefault();
                if (pageSetup != null) 
                {
                     pageSetup.Orientation = OrientationValues.Landscape;
                }
                worksheetPart.Worksheet.Save();
            }
            workbookPart.Workbook.Save();
        }

The pattern I use to manipulate documents is to first open excel and create a blank document and save it. I then use my code to open that document and doing any work I need to it. This way I don't have to be bothered with creating a the elements and making sure things are in the right place. The code I use to achieve this is here:

public byte[] Export(string pathToExcelFile)
    {
        // Open the file from the drive
        byte[] byteArray = File.ReadAllBytes(pathToExcelFile)
        using (MemoryStream stream = new MemoryStream())
        {
            stream.Write(byteArray, 0, (int)byteArray.Length);
            using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
            {
                // Do all work on excel doc here
                SetLandscape(spreadsheetDoc);

                // Save all the changes
            }

            return stream.ToArray();
        }
    }

So here I open the file from the drive into a memory stream so I can perform all the edits in memory. I then pass that document in the SetLandscape method and it will set the landscape property on all three sheets (3 sheets since that is the default for a blank excel 2007 document). I then save my changes and return the stream as a byte array. I do this so the file can be downloaded. I recommend that you create a blank file and open it into memory like this instead of manually trying to build up the file from scratch. That would explain why you are getting so many null pointers.

Best Answer

Related Solutions

.net – OpenXML SDK: Make Excel recalculate formula

How change excel 2007 document orientation to landscape by OpenXML sdk

Related Topic