Hi Aspose,
I have code which injects data into the Data_1 sheet, then it needs to refresh the pivots on sheet Report_1, then read off the values that appear in Output_1. This all works fine in Excel using vba.
I have rewritten it using aspose and it mostly works but the pivots appear empty and therefore the Output_1 sheet is also empty. When I manually open the file in Excel I can refresh the pivots and can see the correct values in Output_1, I have attached the workbook so you can also try that.
I have tried all the variations of code that I have found in your forums, below is the one with all possible combinations of refreshing, but it does not work.
If I use RefreshDataOnOpeningFile then of course Excel sorts it out when it opens the file, but I need the values without using Excel.
I am using the latest - 20.5.0.0
foreach (Worksheet thisworksheet in workbook.Worksheets) {
foreach (var pt2 in thisworksheet.PivotTables) {
foreach (var pt in pt2.GetChildren()) {
pt.RefreshDataFlag = true;
//pt.RefreshDataOnOpeningFile = true;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = false;
pt.CalculateRange();
}
pt2.RefreshDataFlag = true;
//pt2.RefreshDataOnOpeningFile = true;
pt2.RefreshData();
pt2.CalculateData();
pt2.RefreshDataFlag = false;
pt2.CalculateRange();
}
thisworksheet.RefreshPivotTables();
}
workbook.CalculateFormula();
pivot.zip (136.2 KB)
@catnoise,
Please try our latest version/fix: Aspose.Cells for .NET v20.5.3:
Aspose.Cells20.5.3 For .Net4.0.Zip (5.4 MB)
Aspose.Cells20.5.3 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
I have tested using the following sample code with your file, it works fine:
e.g
Sample code:
var workbook = new Workbook("e:\\test2\\Pivot.xlsx");
foreach (Worksheet thisworksheet in workbook.Worksheets) {
thisworksheet.RefreshPivotTables();
}
workbook.CalculateFormula();
workbook.Save("e:\\test2\\out1.xlsx");
workbook.Save("e:\\test2\\out1.pdf");
Let us know if you still find the issue.
Thank you Amjad, if I follow your script with that latest version then yes it works, but I am not keen on having to close and reopen the file just to get the pivot to refresh. I am attaching 3 files this time to show how PivotAfter.xlsx still has empty pivots, and only when I close and reopen the file does it actually populate, as seen at PivotAfterWithReopening.xlsx
Here is the code, you don’t need the first two lines, they are only there to give you a clue what happens before PivotBefore.xlsx is created.
//…code before this updates the data…
workbook.Save(@“c:\temp\PivotBefore.xlsx”);
//…from here we start fresh so Amjad can reproduce the error
workbook = new Workbook(@“c:\temp\PivotBefore.xlsx”);
workbook.CalculateFormula();
foreach (Worksheet thisworksheet in workbook.Worksheets) {
thisworksheet.RefreshPivotTables();
}
workbook.CalculateFormula();
//In case it helps, lets refresh them all again
foreach (Worksheet thisworksheet in workbook.Worksheets) {
thisworksheet.RefreshPivotTables();
}
workbook.CalculateFormula();
workbook.Save(@“c:\temp\PivotAfter.xlsx”);
var workbook2 = new Workbook(@“c:\temp\PivotAfter.xlsx”);
foreach (Worksheet thisworksheet in workbook2.Worksheets) {
thisworksheet.RefreshPivotTables();
}
workbook2.CalculateFormula();
workbook2.Save(@“c:\temp\PivotAfterWithReopening.xlsx”);
PivotSteps.zip (125.2 KB)
@catnoise,
Your attached zipped archive contains only one file, i.e., “PivotAfter.xlsx”, so it does not contain your other two files. Please provide all the files to evaluate your issue precisely.
You do not need to close the file and then re-open it to get the updated/calculated pivot tables and data. Also, I could not understand you, please elaborate. Did you try our attached fix with the sample code (we provided) to generate both XLSX and PDF files and were the output files not fine?
Sorry, here is the correct zip with 3 files. You will see that you do need to close and reopen to refresh. Very strange!
Pivot3Files.zip (378.1 KB)
@catnoise,
Thanks for the sample files.
After an initial test, I am able to reproduce the issue as you have mentioned by using the following sample code with your template file. I found I have to manually refresh the pivot tables to get the updated data in the sheets:
e.g
Sample code:
var workbook = new Workbook("e:\\test2\\PivotBefore.xlsx");
workbook.CalculateFormula();
foreach (Worksheet thisworksheet in workbook.Worksheets) {
thisworksheet.RefreshPivotTables();
}
workbook.Save("e:\\test2\\PivotAfter.xlsx")
But if I use your code segment, it works with the final output file. Anyways, I have logged a ticket with an id “CELLSNET-47402” for your issue. We will look into it soon.
Once we have an update on it, we will let you know.
Hi Amjad,
That new patch version of Aspose.Cells for .NET v20.5.3 which you sent me has caused me an embarrassing downtime with my client. It began giving this error:
The subscription included in this license allows free upgrades until 13 May 2020, but this version of the product was released on 14 May 2020. Please renew the subscription or use a previous version of the product.
I have had to roll back to an older version so they could get their report out, but I will need to explain to them in the morning why a third party patch caused the outage. I am sure there is a good reason but please can you explain why I received that error so I can pass it on. Many thanks in advance.
@catnoise,
Just for your information, when you purchase a license for a product (e.g Aspose.Cells) or a set of products, you are authorized to use this license file with any new ( and upcoming (official versions)) versions or hot fixes of the components for the next whole year. Moreover, your license will never expire if you use your license with the product’s version/hot fix that should be released before your subscription expiry date (you may open the license file into notepad and check the expiry date, please do not modify your license least it would not work any more).
Moreover, if you need to use any version of the product(s) which is released after your subscription expiry date, then you have to upgrade your subscription. For more details, please contact or post a query in Aspose.Purchase forum: Aspose.Purchase - Free Support Forum - aspose.com
But my license runs until 2021, I have checked and the file on the server confirms it. Please advise.
Screenshot_20200616-084819_Chrome.jpg (428.2 KB)
@catnoise,
It looks like in your application, you are still using older license. So, kindly make sure that you are using latest license file in code (you should set to latest license file path or embedded it as resource).
I see 20.6 is out so I have grabbed that one and the licensing works fine. I have not changed my license file so that indicates your dll patch caused the licensing issue, just so you are aware.
The patch was not the original issue here anyway so now I will wait for the case CELLSNET-47402 to be resolved so I can refresh my pivots without having to close and reopen the file. Thank you for your swift responses.
@catnoise,
Good to know that your issue regarding license is fixed using the new (official) release of the product.
Regarding your issue “CELLSNET-47402” for refreshing pivot tables, we are working over it and hopefully it will be fixed soon.
Once we have an update on it, we will let you know.
@catnoise,
This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
@catnoise,
There are some tips for you:
Please see the following code:
var workbook = new Workbook(filePath + "PivotBefore.xlsx");
//Because the data in worksheet named "Data_1" is linked to the data of PivotTable named "PivotTable1"
//in the worksheet named "Report_1" via formula. And the data in the worksheet named "Data_1" will be used in
// all the PivotTables in the workbook. so you should refresh the following PivotTable firstly.
PivotTable pt = workbook.Worksheets["Report_1"].PivotTables["PivotTable1"];
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = false;
workbook.CalculateFormula();
//For better performance, you can substitute workbook.Worksheets.RefreshPivotTables() for worksheet.RefreshPivotTables().
//If you call workbook.Worksheets.RefreshPivotTables(), the same PivotCache which are used by many PivotTables in the Workbook
//will be refreshed only once. If you call worksheet.RefreshPivotTables(),the same PivotCache which are used by many PivotTables
//in current worksheet will be refreshed only once.
workbook.Worksheets.RefreshPivotTables();
//foreach (Worksheet thisworksheet in workbook.Worksheets)
//{
// thisworksheet.RefreshPivotTables();
//}
workbook.Save(filePath + "out.xlsx");
Please try it with latest version/fix: Aspose.Cells for .NET v20.6.4:
Aspose.Cells20.6.4 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.6.4 For .Net4.0.Zip (5.4 MB)
Let us know your feedback.
The issues you have found earlier (filed as CELLSNET-47402) have been fixed in Aspose.Cells for .NET v20.7. This message was posted using Bugs notification tool by ahsaniqbalsidiqui
Please can I reopen this case, I have another file which I cannot get the pivot to refresh whatever tricks I try!
If you open the attached file in Excel and refresh the pivot in cell M2 then the pivot refreshes fine. If I try to open it and refresh using Aspose, nothing works.
var workbook = new Workbook(@"c:\temp\PivotM2BeforeUpdate.xlsx");
workbook.Worksheets.RefreshPivotTables();
workbook.CalculateFormula();
workbook.Save(@"c:\temp\PivotM2AfterUpdate.xlsx");
The file was an xlsx which we opened in Aspose then injected scores into the Data_1 sheet, then we want to read off the pivot in the Report_1 sheet but this is blocking us from getting it out. I am using Aspose Cells v20.10.0 nuget. Many thanks for your support over the years!
PivotM2BeforeUpdate.zip (2.5 MB)
@catnoise,
We have observed the issue with the latest version and logged this issue in our database for further investigation and a fix. You will be notified here once any update is ready for sharing.
This issue is logged as:
CELLSNET-47680 - Pivot tables not refreshed
Please note that as previous issue was closed, so we cannot reopen it and a new issue is logged.
@catnoise,
Please try our latest version/fix: Aspose.Cells for .NET v20.10.8 (attached)
Aspose.Cells20.10.8 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.10.8 For .Net4.0.Zip (5.4 MB)
Your issue (logged earlier as “CELLSNET-47680”) is fixed in it.
Let us know your feedback.
The issues you have found earlier (filed as CELLSNET-47680) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao