We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Get visible cells from a worksheet (not those that are the dataset of a pivot table)

Hi ,

We have an issue regarding the Pivot Tables in a Spreadsheet. So if we have a spreadsheet with multiple worksheets and one of them has a pivot table how can I get the current visible cells ? We use the getCells but those return values that are hidden by the pivot table.
Thanks

Hi Fokion,


Thank you for contacting Aspose support.

If you wish to get the current visible range of the PivotTable then you can use any of the PivotTable.TableRange1 or PivotTable.TableRange2 properties. Please note, the first property returns a CellArea object that represents the range containing the entire PivotTable report, but doesn’t include page fields whereas the second property returns a CellArea object that represents the range containing the entire PivotTable report, including page fields. Once you have the CellArea of the PivotTable, you can devise your own custom logic to read the value of each cell. Please check the following piece of code and attached sample spreadsheet for elaboration.

Java

Workbook book = new Workbook(dir + “pivotTable_test.xls”);
Worksheet sheet = book.getWorksheets().get(“PivotTable”);
PivotTable table = sheet.getPivotTables().get(0);
CellArea range = table.getTableRange1();
for(int r = range.StartRow; r < range.EndRow; r++)
{
for(int c = range.StartColumn; c < range.EndColumn; c++)
{
if(sheet.getCells().checkCell(r,c).getValue() != null)
System.out.println(sheet.getCells().get(r,c).getValue());
}
}