Add PivotTable from DataSource dynamically in Java

Aspose.Cells Product Family
1

Hi to all,


i’ve created a sheet and populate it with my data, now i would like to add a pivot table to another sheet:

int sheetIndex = workbook.getWorksheets().add();

Worksheet sheet2 = workbook.getWorksheets().get(sheetIndex);

sheet2.setName(“Pivot”);

PivotTableCollection pivotTables = sheet2.getPivotTables();
with this code, i create a new sheet2, with name Pivot where i create a PivotTable, now i have to retrive data from the first sheet (for example, the name of it is Data). In the examples iv'e seen on documentation, i've found this:

int index = pivotTables.add("=Data!A1:E3", "A4", "PivotTable1");

where A1:E3 is the range value that i get from the Data Sheet, and A4 is the start position of the PivotTable on PivotTable1 Sheet.

But, how can i get the range of data in my Data sheet dynamically ? I've see Cells have two methods:

getMaxDataColumn() and getMaxDataRow -> I think i should use it, but dont know how to call the pivotTables.add() with this two methods.... maybe i'm in the wrong way...
2

I’ve “solved” in this way:


Cells cells = dataSource.getCells();

int maxCol = cells.getMaxDataColumn();

int maxRow = cells.getMaxDataRow();

String name = cells.get(maxRow, maxCol).getName();

PivotTableCollection pivotTables = newSheet.getPivotTables();

int index = pivotTables.add("=" + dataSource.getName() + “!A1:” + name, “A4”, “PivotTable1”);
3

Hi,

Thanks for your posting and using Aspose.Cells.

It is good to know that you were able to sort out this issue. If you encounter any other issue, please feel free to post. We will be glad to help you further.