I do the following to update/calculate/refresh all pivot tables in the file before saving the file:
For Each _sheet As GemBox.Spreadsheet.ExcelWorksheet In gemboxFile.Worksheets
For Each _table As GemBox.Spreadsheet.PivotTables.PivotTable In _sheet.PivotTables
_table.PivotCache.Refresh()
_table.Calculate()
NextNext
gemboxFile.Save(strOutputfile)
If I now open the file in MS-Excel I warning: “We found a problem with some content…”
If I then click “Yes” to open the file I get:
Repairs to “filename.xlsx”:
Repaired Records: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)
I just wanted to check, why are you calculating the PivotTable when saving to Excel file?
Note that PivotTable are automatically refreshed when opened in the Excel application.
Anyway, can you send us your XLSX file so that we can reproduce this issue and investigate it?
You are correct. I do not have to calculate the pivottables when saving the file.
I think the fact that I didn’t execute a pivottable.calculate when I save the file (in a new session) to PDF was making me think there was something wrong with the file, because it then used the old data. I now do a pivottable.calculate and I no longer have this problem.
So for my understanding:
there is no need to do a pivottable.pivotcache.refresh and pivottable.calculate when saving the file
when saving to pdf you always first have to do a pivottable.calculate
Correct?
I still wonder though what is causing the (error)message in MS-Excel when I open the file where I did a refresh and calculate!?
You can use the same file I sent you for ticket “Save to PDF doesn’t honour “Insert page break after each item””…to test: in “Sheet1” I just clear cells A2 to T1500 and insert a new row at A2…then refresh/calculate all pivot tables…then save the file…if I then open the file in MS-Excel I get the warning/error.
I did some more testing, but I still have problems related to this:
I update data in a worksheet with pivot tables
I save the file using gembox
I open the file again (in gembox), do a “Calculate” on all pivot tables and save a sheet to pdf: the result is not correct: the pdf is not the same as when I: open the file in MS-Excel. I see the sheet updating…I then save the file (to .xlsx) and save the file to pdf. Then the pdf is correct.
If I now open the file I saved with MS-Excel in gembox and then save it to pdf the pdf is also fine.
…so I can’t seem to correctly save the updated file with gembox and doing a cache.refresh/table.calculate before saving results in the error I get when I open the file in MS-Excel…
I’m afraid we were unable to reproduce these issues.
First, we tried to reproduce the issue with a corrupted file (can you check whether the IF on line 4 is actually what you meant). This works without any problems:
var file = ExcelFile.Load("Excel-efter-RSOutput2File.xlsx");
foreach (var sheet in file.Worksheets)
{
if (sheet.Name != "Sheet1") // data sheet
{
sheet.Cells.GetSubrange("A2:T1500").Clear(ClearOptions.All);
sheet.Rows.InsertEmpty(1);
}
foreach (var table in sheet.PivotTables)
{
table.PivotCache.Refresh();
table.Calculate();
}
}
file.Save("output.xlsx");
Then we tried to change some values, recalculate the pivot table, and save it as a PDF.
var file = ExcelFile.Load("Excel-efter-RSOutput2File.xlsx");
var dataSheet = file.Worksheets["Sheet1"];
dataSheet.Cells["C41"].Value = -1500;
dataSheet.Cells["C39"].Value = 20000;
var pivotSheet = file.Worksheets[0];
pivotSheet.PivotTables[0].PivotCache.Refresh();
pivotSheet.PivotTables[0].Calculate();
file.Save("out.pdf");
file.Save("out.xlsx");
The data in PDF looks the same as in Excel.
Can you please send us the exact code that reproduces your issues so that we can investigate them?
I have send you a zip from my hotmail account…this file contains examples of my test…
The code I use is a bit spread out over different modules but it’s not that spectacular;
I open the file, clear a range of cells, then update some cell’s and then:
For Each _sheet As GemBox.Spreadsheet.ExcelWorksheet In gemboxFile.Worksheets
For Each _table As GemBox.Spreadsheet.PivotTables.PivotTable In _sheet.PivotTables
_table.PivotCache.Refresh()
_table.Calculate()
Next
Next
gemboxFile.Save(strOutputfile)
If I now open the file in MS-Excel I get the error…and if I save this to pdf the output is not correct.
I have made a small test app which replicates the problem:
Dim gemboxFile As GemBox.Spreadsheet.ExcelFile = GemBox.Spreadsheet.ExcelFile.Load("c:\test.xlsx")
For Each gemboxSheet As GemBox.Spreadsheet.ExcelWorksheet In gemboxFile.Worksheets
If gemboxSheet.Name = "Sheet1" Then
gemboxSheet.Cells.GetSubrange("A2", "T1500").Clear(GemBox.Spreadsheet.ClearOptions.All)
gemboxSheet.Cells("A2").Value = "7503193950 some text"
gemboxSheet.Cells("B2").Value = "999 ---------------"
gemboxSheet.Cells("C2").Value = "100"
gemboxSheet.Cells("D2").Value = "1-1-2022 00:00:00"
gemboxSheet.Cells("E2").Value = "MW"
gemboxSheet.Cells("F2").Value = "9999"
gemboxSheet.Cells("G2").Value = "9999"
gemboxSheet.Cells("H2").Value = "9999"
gemboxSheet.Cells("I2").Value = "9999"
gemboxSheet.Cells("J2").Value = "9999"
gemboxSheet.Cells("K2").Value = "9999"
gemboxSheet.Cells("L2").Value = "9999"
gemboxSheet.Cells("M2").Value = "9999"
For Each _sheet As GemBox.Spreadsheet.ExcelWorksheet In gemboxFile.Worksheets
For Each _table As GemBox.Spreadsheet.PivotTables.PivotTable In _sheet.PivotTables
_table.PivotCache.Refresh()
_table.Calculate()
Next
Next
gemboxFile.Save("c:\new.xlsx")
gemboxFile.Save("c:\new.pdf")
Exit For
End If
Next
If I open the new xlsx file I get an error in MS-Excel and the pdf file is also wrong.