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();