Can't dynamically refresh pivot table data by changing filter value

We have an Excel workbook, and we need to read data off a pivot table on one of the worksheets. We need to be able to dynamically change one of the pivot filters, refresh the pivot table, and read the appropriate value. We have tried lots of things, so far without success. For example, the code below fails at the RefreshPivotTables() with error “Aspose.Cells.CellsException this data source is not supported.”. This seems odd, as pivot tables are only relevant in Excel workbooks. What other data sources does this work on? Can you please tell us for sure if the Aspose library can do what we want to do? We’re seeing lots of similar queries on forums, but few answers.

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(memStream);
var flds = workbook.Worksheets[“Producción Gas miles m3”].PivotTables[0].PageFields;
workbook.Worksheets[“Producción Gas miles m3”].PivotTables[0].PageFields[“anio”].CurrentPageItem = 4;
workbook.Worksheets[“Producción Gas miles m3”].Cells[“B3”].PutValue(2012);
workbook.Worksheets[“Producción Gas miles m3”].PivotTables[0].RefreshDataFlag = true;
workbook.Worksheets[“Producción Gas miles m3”].PivotTables[0].RefreshDataOnOpeningFile = true;
workbook.Worksheets[“Producción Gas miles m3”].RefreshPivotTables();

@jamesbond0073,

Please note, currently, Aspose.Cells for .NET does not support to refresh a PivotTable with Data Model (as its source) for now. If the source data is present in the same workbook where the pivot table is created, it should work fine. Anyways, you may try to refresh and calculate your specific pivot table separately (using PivotTable.RefreshData() and PivotTable.CalculateData() method) if it makes any difference.

If you still find any issue, kindly do create a sample console demo application (runnable), zip the project and post us to show the issue, we will check it soon.

This is regarding the below topic.

Problem is I can’t upload the file, says it’s too big, yet it’s less than the 50MB maximum. Please could you give me an email where I can directly mail the console app?

aspose_upload_error.PNG (9.9 KB)

@jamesbond0073,
You may please upload the file to some file sharing server and share the download link with us.

Here’s the link to the zip that holds the Excel workbook referred to in the code above:
https://www.energia.gob.ar/contenidos/archivos/Reorganizacion/informacion_del_mercado/mercado_hidrocarburos/tablas_dinamicas/upstream/sescoweb_produccion.zip

Just download it, unzip, get the file, add to project, and set Copy to Output Directory to “Copy if newer”. See if you can run it past workbook.Worksheets[“Producción Gas miles m3”].RefreshPivotTables();

But, generally, I want to be able to change the year (Anio), and refresh the workbook. We’re getting Exception at RefreshPivotTables()

@jamesbond0073,
We have observed this issue and logged it in our database for further investigation. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSNET-48533 - Can't dynamically refresh pivot table data by changing filter value

Thanks guys. The quicker you find a solution the better for us.

@jamesbond0073,
You are welcome and you will be notified here for any update.

@jamesbond0073,

We evaluated your ticket further and found data source of your both pivot tables in second and third worksheet is external (Data model source). For confirmation, you may open your template Excel file into MS Excel and check “Connections” and their properties. In short, as we told you Aspose.Cells cannot manipulate or refresh pivot tables with data model or whose data source is external data source.

We are sorry but this feature (Manipulate or Refresh Pivot tables with data model/external data source) is not supported at the moment.

Thanks anyway

@jamesbond0073,

You are welcome.