Question:
Cannot get any data from the resulting pivotTable. Please comment!!!
Log Output:
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable startColumn: 4, endColumn:6
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable startRow: 2, endRow:7
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable RowRange startColumn: 4, endColumn:4
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable RowRange startRow: 3, endRow:7
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable ColRange startColumn: 5, endColumn:6
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable ColRange startRow: 2, endRow:3
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable columnFields:1
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable pivotField:Quarter
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable pivotItem:Qtr3
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable pivotItem:Qtr4
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable rowFields:1
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable pivotField:Sport
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable pivotItem:Golf
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable pivotItem:Tennis
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable pivotItem:Hello
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable dataFields:1
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable pivotField:Sales
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable pivotField:Sales
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable dataField - pivotField.getDisplayName():Sum of Sales
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable dataField - cell.getStringValue():6430
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable dataField - cell.getStringValue():
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable dataField - cell.getStringValue():
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable dataField - cell.getStringValue():
[12/Aug/2008:19:27:15] INFO ( 7394): testPivotTable dataField - pivotTable.saveData():true
[12/Aug/2008:19:27:17] INFO ( 7394): testPivotTable dataField - cell2.getStringValue():6430
[12/Aug/2008:19:27:17] INFO ( 7394): testPivotTable dataField - cell2.getStringValue():
[12/Aug/2008:19:27:17] INFO ( 7394): testPivotTable dataField - cell2.getStringValue():
[12/Aug/2008:19:27:17] INFO ( 7394): testPivotTable dataField - cell2.getStringValue():
[12/Aug/2008:19:27:17] INFO ( 7394): testPivotTable dataField - pivotTable.saveData():true
Source Code:
(Following the code form “Advance Topic”)
Workbook workbook = new Workbook();
// Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.getWorksheets().addSheet(“ppp”);
Cells cells = sheet.getCells();
// Setting the value to the cells
Cell cell = cells.getCell(“A1”);
cell.setValue(“Sport”);
cell = cells.getCell(“B1”);
cell.setValue(“Quarter”);
cell = cells.getCell(“C1”);
cell.setValue(“Sales”);
cell = cells.getCell(“A2”);
cell.setValue(“Golf”);
cell = cells.getCell(“A3”);
cell.setValue(“Golf”);
cell = cells.getCell(“A4”);
cell.setValue(“Tennis”);
cell = cells.getCell(“A5”);
cell.setValue(“Hello”);
cell = cells.getCell(“A6”);
cell.setValue(“Tennis”);
cell = cells.getCell(“A7”);
cell.setValue(“Tennis”);
cell = cells.getCell(“A8”);
cell.setValue(“Golf”);
cell = cells.getCell(“B2”);
cell.setValue(“Qtr3”);
cell = cells.getCell(“B3”);
cell.setValue(“Qtr4”);
cell = cells.getCell(“B4”);
cell.setValue(“Qtr3”);
cell = cells.getCell(“B5”);
cell.setValue(“Qtr4”);
cell = cells.getCell(“B6”);
cell.setValue(“Qtr3”);
cell = cells.getCell(“B7”);
cell.setValue(“Qtr4”);
cell = cells.getCell(“B8”);
cell.setValue(“Qtr3”);
cell = cells.getCell(“C2”);
cell.setValue(1500);
cell = cells.getCell(“C3”);
cell.setValue(2000);
cell = cells.getCell(“C4”);
cell.setValue(600);
cell = cells.getCell(“C5”);
cell.setValue(1500);
cell = cells.getCell(“C6”);
cell.setValue(4070);
cell = cells.getCell(“C7”);
cell.setValue(5000);
cell = cells.getCell(“C8”);
cell.setValue(6430);
PivotTables pivotTables = sheet.getPivotTables();
// Adding a PivotTable to the worksheet
int index = pivotTables.add("=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);
// Draging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW,0);
// Draging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN,1);
// Draging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA,2);
CellArea cellArea = pivotTable.getTableRange1();
logger.log(Level.INFO, "testPivotTable startColumn: " + cellArea.getStartColumn() + “, endColumn:” + cellArea.getEndColumn());
logger.log(Level.INFO, "testPivotTable startRow: " + cellArea.getStartRow() + “, endRow:” + cellArea.getEndRow());
cellArea = pivotTable.getRowRange();
logger.log(Level.INFO, "testPivotTable RowRange startColumn: " + cellArea.getStartColumn() + “, endColumn:” + cellArea.getEndColumn());
logger.log(Level.INFO, "testPivotTable RowRange startRow: " + cellArea.getStartRow() + “, endRow:” + cellArea.getEndRow());
cellArea = pivotTable.getColumnRange();
logger.log(Level.INFO, "testPivotTable ColRange startColumn: " + cellArea.getStartColumn() + “, endColumn:” + cellArea.getEndColumn());
logger.log(Level.INFO, "testPivotTable ColRange startRow: " + cellArea.getStartRow() + “, endRow:” + cellArea.getEndRow());
PivotFields pivoteFields = pivotTable.getColumnFields();
logger.log(Level.INFO, “testPivotTable columnFields:” + pivoteFields.size());
PivotField pivotField = pivoteFields.get(0);
logger.log(Level.INFO, “testPivotTable pivotField:” + pivotField.getName());
PivotItems pivotItems = pivotField.getPivotItems();
PivotItem pivotItem = pivotItems.get(0);
logger.log(Level.INFO, “testPivotTable pivotItem:” + pivotItem.getStringValue());
pivotItem = pivotItems.get(1);
logger.log(Level.INFO, “testPivotTable pivotItem:” + pivotItem.getStringValue());
pivoteFields = pivotTable.getRowFields();
logger.log(Level.INFO, “testPivotTable rowFields:” + pivoteFields.size());
pivotField = pivoteFields.get(0);
logger.log(Level.INFO, “testPivotTable pivotField:” + pivotField.getName());
pivotItems = pivotField.getPivotItems();
pivotItem = pivotItems.get(0);
logger.log(Level.INFO, “testPivotTable pivotItem:” + pivotItem.getStringValue());
pivotItem = pivotItems.get(1);
logger.log(Level.INFO, “testPivotTable pivotItem:” + pivotItem.getStringValue());
pivotItem = pivotItems.get(2);
logger.log(Level.INFO, “testPivotTable pivotItem:” + pivotItem.getStringValue());
pivoteFields = pivotTable.getDataFields();
logger.log(Level.INFO, “testPivotTable dataFields:” + pivoteFields.size());
pivotField = pivoteFields.get(0);
logger.log(Level.INFO, “testPivotTable pivotField:” + pivotField.getName());
// ERROR at index 0
// pivotItems = pivotField.getPivotItems();
// pivotItem = pivotItems.get(0);
// logger.log(Level.INFO, “testPivotTable pivotItem:” + pivotItem.getStringValue());
// pivotField = pivotTable.getDataField();
logger.log(Level.INFO, “testPivotTable pivotField:” + pivotField.getName());
logger.log(Level.INFO, “testPivotTable dataField - pivotField.getDisplayName():” + pivotField.getDisplayName());
// pivotItems = pivotField.getPivotItems();
cell = cells.getCell(“C8”);
logger.log(Level.INFO, “testPivotTable dataField - cell.getStringValue():” + cell.getStringValue());
cell = cells.getCell(5, 4);
logger.log(Level.INFO, “testPivotTable dataField - cell.getStringValue():” + cell.getStringValue());
cell = cells.getCell(5, 5);
logger.log(Level.INFO, “testPivotTable dataField - cell.getStringValue():” + cell.getStringValue());
cell = cells.getCell(6, 5);
logger.log(Level.INFO, “testPivotTable dataField - cell.getStringValue():” + cell.getStringValue());
sheet.calculateFormula(true, true);
workbook.calculateFormula();
logger.log(Level.INFO, “testPivotTable dataField - pivotTable.saveData():” + pivotTable.saveData());
// Saving the Excel file
try {
File newFileB4DBInsert = new File( “/www/sunSystems/doc/book1.xls”);
workbook.save(new FileOutputStream(newFileB4DBInsert));
} catch (Exception e) {
logger.log(Level.INFO, “testPivotTable:” + e);
}
Workbook workbook2 = new Workbook();
try {
File newFileB4DBInsert2 = new File( “/www/sunSystems/doc/book1.xls”);
workbook2.open(new FileInputStream(newFileB4DBInsert2));
} catch (Exception e) {
logger.log(Level.INFO, “testPivotTable:” + e);
}
Worksheet sheet2 = workbook2.getWorksheets().getSheet(“ppp”);
Cells cells2 = sheet2.getCells();
Cell cell2 = cells2.getCell(“C8”);
logger.log(Level.INFO, “testPivotTable dataField - cell2.getStringValue():” + cell2.getStringValue());
cell2 = cells2.getCell(5, 4);
logger.log(Level.INFO, “testPivotTable dataField - cell2.getStringValue():” + cell2.getStringValue());
cell2 = cells2.getCell(5, 5);
logger.log(Level.INFO, “testPivotTable dataField - cell2.getStringValue():” + cell2.getStringValue());
cell2 = cells2.getCell(6, 5);
logger.log(Level.INFO, “testPivotTable dataField - cell2.getStringValue():” + cell2.getStringValue());
logger.log(Level.INFO, “testPivotTable dataField - pivotTable.saveData():” + pivotTable.saveData());
Hi,
Thanks for your details.
Well, I 'm afraid Aspose.Cells for Java does not support to manipulate pivot table (create, retrieve pivot data etc.) rather it only sets the format for the pivot table report and forces MS Excel (turns on the MS Excel pivot table option i.e.., "Refresh on open") to create the table when the resultant file is opened into it. So, generally, you can not get / retrieve the pivot data. I think, as a workaround, to access the data of the pivot table, you have to open the resultant excel file by MS Excel, choose Save option to save the data of pivot table, now access it by Aspose.Cells for Java API it will work fine.
Thank you.
Hi,
Actually, do you mean that Aspose.Cells for .Net works without opening the resultant excel file by MS Excel??
Thanks for the reply.
Hi,
Yes, it does so according to your code. The resultant excel file is not opened by MS Excel, so consequently, the "Refresh on open" option never used. I think, currently, you should try the workaround suggested by me in my previous post and we will check the feasiblity if we can support the feature in our future versions.
Thank you.