Clearing Pivot Table data without destroying the Pivot Table

[simon1689]

We have a situation where we have a spreadsheet with a sheet data and the second sheet containing a pivot table. Some of our customers open their spreadsheets in read only or protected mode in Excel, which causes a notification that the pivot table cannot be updated upon opening it. Only the first sheet with the data is updated when we create the spreadsheet. The sheet with the pivot table is updated upon opening the spreadsheet (except in this case).

I've been looking everywhere and trying anything to remove the cache or the fields and get it to work. I want the pivot table to stay intact but not to present un-updated data in the pivot table sheet. We have a way to update the pivot table upon opening, but the problem has to do with the workbook being opened readonly or in protected mode which shows un-updated and wrong data in the pivot table.

This is what we use to make the sheet updatable and I've been trying to remove some things and to keep the workbook from displaying errors that a part is missing or something else, but with no succes:

 void SetPivotRefresh(Sheet sheet, bool sourceLocked)
 {
     try
     {
         List<PivotTableCacheDefinitionPart> pivotTableCacheDefinitionParts =
            _openXmlSpreadsheetDocument.WorkbookPart.PivotTableCacheDefinitionParts.Where(x =>
                x.PivotCacheDefinition.CacheSource.WorksheetSource.Name == sheet.CompleteName || x.PivotCacheDefinition.CacheSource.WorksheetSource.Sheet == sheet.CompleteName).ToList();

         foreach (PivotTableCacheDefinitionPart pivotTableCacheDefinition in pivotTableCacheDefinitionParts)
         {
             pivotTableCacheDefinition.PivotCacheDefinition.EnableRefresh = true;
             pivotTableCacheDefinition.PivotCacheDefinition.RefreshOnLoad = true;
             pivotTableCacheDefinition.PivotCacheDefinition.RecordCount = Convert.ToUInt32(sheet.RowCount);                                 

             if (!sourceLocked)
             {
                 WorksheetSource worksheetSource = pivotTableCacheDefinition.PivotCacheDefinition.PivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource;
                 // otherwise a share violation will be given and files built with OpenXML or Excel use these properties differently
                 worksheetSource.Sheet = sheet.CompleteName;
                 worksheetSource.Name = null;

                 string startingCellReference = sheet.ColumnDefinitions.FirstOrDefault()?.CellReference;
                 worksheetSource.Reference = startingCellReference + ":" + SpreadsheetHelper.ConvertColumnNumberToName(sheet.ColumnCount) + (sheet.RowCount + 1);
             }

             // Remove cache so that new information is updated
             pivotTableCacheDefinition.PivotTableCacheRecordsPart.PivotCacheRecords.RemoveAllChildren();
             pivotTableCacheDefinition.PivotTableCacheRecordsPart.PivotCacheRecords.Count = 0;
             pivotTableCacheDefinition.PivotCacheDefinition.Save();
         }
     }
     catch (Exception e)
     {
         Log.LogHandledException(e);
     }
 }

"sheet" is our own object which contains all the data that should go into an Excel sheet.