Aspose Cells - Pivot Table

Aspose.Cells Product Family
1

Hi I am trying to create Pivot table and it been troubleing me since I upgraded to latest current verison.

the problem seems to be with the sheet name which causes error.

Consider this example where you set the data range for pivot table

Sheet Name : “Data”

pivotTables.Add("=Data!A1:F30", “B3”, “PivotTable1”);

If my Sheet name was Data-1 , look at hyphen in which I cannot avoid it throws error.

This was not problem when I had version aspose-cells-7.3.1 ever since we got 8.8.0 this is problem.

if Possible please don’t ask for sample java code this explanation should be good enough for you to instantly reproduce this issue on your side.

2

Hi Ritesh,


Thank you for contacting Aspose support.

Please enclose the worksheet name in single quotes while adding the Pivot Table to the collection. Please check the following piece of code which I have tested against the latest release of the API, that is; Aspose.Cells for Java 8.8.3.7.

In case the problem persists, we need the sample application as well as the complete error details to look further into this matter.

Java

//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the newly added worksheet
int sheetIndex = workbook.getWorksheets().add();
Worksheet sheet = workbook.getWorksheets().get(sheetIndex);
sheet.setName(“Data-1”);

Cells cells = sheet.getCells();
//Setting the value to the cells
Cell cell = cells.get(“A1”);
cell.setValue(“Sport”);
cell = cells.get(“B1”);
cell.setValue(“Quarter”);
cell = cells.get(“C1”);
cell.setValue(“Sales”);

cell = cells.get(“A2”);
cell.setValue(“Golf”);
cell = cells.get(“A3”);
cell.setValue(“Golf”);
cell = cells.get(“A4”);
cell.setValue(“Tennis”);
cell = cells.get(“A5”);
cell.setValue(“Tennis”);
cell = cells.get(“A6”);
cell.setValue(“Tennis”);
cell = cells.get(“A7”);
cell.setValue(“Tennis”);
cell = cells.get(“A8”);
cell.setValue(“Golf”);

cell = cells.get(“B2”);
cell.setValue(“Qtr3”);
cell = cells.get(“B3”);
cell.setValue(“Qtr4”);
cell = cells.get(“B4”);
cell.setValue(“Qtr3”);
cell = cells.get(“B5”);
cell.setValue(“Qtr4”);
cell = cells.get(“B6”);
cell.setValue(“Qtr3”);
cell = cells.get(“B7”);
cell.setValue(“Qtr4”);
cell = cells.get(“B8”);
cell.setValue(“Qtr3”);

cell = cells.get(“C2”);
cell.setValue(1500);
cell = cells.get(“C3”);
cell.setValue(2000);
cell = cells.get(“C4”);
cell.setValue(600);
cell = cells.get(“C5”);
cell.setValue(1500);
cell = cells.get(“C6”);
cell.setValue(4070);
cell = cells.get(“C7”);
cell.setValue(5000);
cell = cells.get(“C8”);
cell.setValue(6430);

sheet = workbook.getWorksheets().add(“Pivot Table”);
PivotTableCollection pivotTables = sheet.getPivotTables();

//Adding a PivotTable to the worksheet
int index = pivotTables.add(“=‘Data-1’!A1:C8”,“E3”,“PivotTable2”);

//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(index);
//Unshowing grand totals for rows.
pivotTable.setRowGrand(false);
//Dragging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW,0);
//Dragging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN,1);
//Dragging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA,2);
//Saving the Excel file
workbook.save(dir + “book1.xls”);