Refresh PivotTable Grouped Data

v 8.9.0
Excel 2013

Getting an error trying to refresh a pivottable with the “Add this data to the Data Model” checkbox selected and with a distinct count.

var worksheet = workBook.Worksheets[0];

if (worksheet.PivotTables.Count > 0)
{
foreach (Aspose.Cells.Pivot.PivotTable pivotTable in worksheet.PivotTables)
{
pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.RefreshDataFlag = false;
pivotTable.CalculateData();
}
}

Thanks.

Hi,


Thank you for contacting support. You are using quite an old version (8.9.0) of Aspose.Cells for .NET API. Please download and try this latest Hotfix version:

Aspose.Cells for .NET v17.03.1 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v17.03.1 (.NET 4.0) compiled in .NET Framework 4.0.

Please also let us know how that goes on your side. However, if this does not resolve the problem, then kindly provide us your source excel file. We shall investigate and reply you appropriately. Your response is awaited.

Thanks for the reply. I downloaded the latest version and am still getting an error.



Attached is an example of the type of pivot I’m trying to refresh.

Hi,


Thank you for posting the source Excel file. We managed to replicate the error on refreshing data of the pivot table. It has been logged under ticket ID CELLSNET-45245 in our bug tracking system. Your post has also been linked to this ticket. We shall keep you informed regarding any available updates. We are sorry for the inconvenience caused.

Thanks for looking into this. Can you provide an ETA on when a fix might be in place? We need to determine if this tool is going to be a viable solution for our use case.

Hi,


In reference to the ticket ID CELLSNET-45245, when we refreshed the pivot table manually using Microsoft Office Excel. It shows a warning dialog. It prevents to get data from the data model. Kindly check and share a correct Excel template.

PS: We are in progress to analyze the problem. Once we complete the analysis phase, then we could share an ETA.

Sorry, I have not encountered this error. What does it say? Also, not sure what you mean by a “correct” file. Was the one I submitted corrupted?

I think the requirement is pretty straightforward, can you replicate on your end with a new file? Just create a pivot on two columns of data using the “data model” checkbox I referred to.

Hi,


Thank you for the reply. We have shared our findings with the product team. We shall let you know if we require any more details on it.

Hi,

In reference to the ticket ID CELLSNET-45245, when we refreshed the pivot table manually using Microsoft Office Excel 2013. It shows a warning dialog. We have attached its screenshot to this reply for your kind reference. This error prevents Aspose.Cells API to access the data model of table.
STA:
can you replicate on your end with a new file? Just create a pivot on two columns of data using the "data model" checkbox I referred to.
We do not prefer to create the source Excel file at our end because later this may not work in the working environment of the customer. Kindly create an Excel file manually and then test using the latest version of Aspose.Cells API: Aspose.Cells for .NET 17.3.4:

Aspose.Cells for .NET v17.03.4 (.NET 2.0) compiled in .NET Framework 2.0
Aspose.Cells for .NET v17.03.4 (.NET 4.0) compiled in .NET Framework 4.0

In that way, if you are able to replicate this problem in your working environment, then please share newly created Excel file in this forum thread. Your response is awaited.

Okay thanks. I do not get that error on my side when manually refreshing. Also, the latest version of does not work either.

Attached is another file.

Can you confirm, using my file or your own, whether this issue exists on your end?

Hi,


Thank you for sending us a source XLSX file. We have recorded your sample Excel file in our database against the ticket ID CELLSNET-45245. We shall let you know whenever an update is available.