Create Pivot Table with Calculated Item (not field) feature

Aspose.Cells Product Family
1

Dears,


My company needs to decide whether we could buy your product ( Aspose.cells for Java) or not.

We need to make sure the following feature is covered.

·
<span style=“font-size:
11.0pt;font-family:“Calibri”,“sans-serif”;color:#1F497D”>We need to construct a
formula out of the data values of a given field.

· So if a I have a field called Measure which has data values (Volume, GSV, TTS Fixed, TTS, TO,…).

· Another field called Output which has numeric values for a given Measure.

· I want to add a Measure called “TTS % TO” in which in the Output column, its value is calculated as a formula from another two Measures (=TTS/TO).

· TTS and TO are not fields. They are data values(items) for the field Measure.


***Please check attached for a detailed description on how you would do this manually on excel.


***Also if this feature is not clearly included , is it possible to add a Macro that will do this functionality to the resulted excel file with the pivot table generated?


Many thanks in advance :)

Regards,

Doaa Ashour

2

Hi,


Thanks for the document to demonstrate your requirements.

I think Aspose.Cells can suit your needs well. Aspose.Cells provide specific APIs to create, manipulate or render PivotTables. Aspose.Cells component provides all the relative APIs set (members) regarding PivotTables for the task. I think as you need to add calculated item in the pivot table report, you may simply use PivotField…addCalculatedItem() method for your needs. I have created a sample code for your reference, you may refer to it and write your own codes accordingly.
e.g
Sample code:

//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();


//Setting the value to the cells
Cell cell = cells.get(“A1”);
cell.putValue(“Sport”);
cell = cells.get(“B1”);
cell.putValue(“Quarter”);
cell = cells.get(“C1”);
cell.putValue(“Sales”);
cell = cells.get(“D1”);
cell.putValue(“Sales1”);
cell = cells.get(“A2”);
cell.putValue(“Golf”);
cell = cells.get(“A3”);
cell.putValue(“Golf”);
cell = cells.get(“A4”);
cell.putValue(“Tennis”);
cell = cells.get(“A5”);
cell.putValue(“Tennis”);
cell = cells.get(“A6”);
cell.putValue(“Tennis”);
cell = cells.get(“A7”);
cell.putValue(“Tennis”);
cell = cells.get(“A8”);
cell.putValue(“Golf”);
cell = cells.get(“B2”);
cell.putValue(“Qtr3”);
cell = cells.get(“B3”);
cell.putValue(“Qtr4”);
cell = cells.get(“B4”);
cell.putValue(“Qtr3”);
cell = cells.get(“B5”);
cell.putValue(“Qtr4”);
cell = cells.get(“B6”);
cell.putValue(“Qtr3”);
cell = cells.get(“B7”);
cell.putValue(“Qtr4”);
cell = cells.get(“B8”);
cell.putValue(“Qtr3”);
cell = cells.get(“C2”);
cell.putValue(1500);
cell = cells.get(“C3”);
cell.putValue(2000);
cell = cells.get(“C4”);
cell.putValue(600);
cell = cells.get(“C5”);
cell.putValue(1500);
cell = cells.get(“C6”);
cell.putValue(4070);
cell = cells.get(“C7”);
cell.putValue(5000);
cell = cells.get(“C8”);
cell.putValue(6430);


PivotTableCollection pivotTables = sheet.getPivotTables();
//Adding a PivotTable to the worksheet
int index = pivotTables.add(“=A1:C8”, “E20”, “PivotTable1”);
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(index);
pivotTable.addFieldToArea(PivotFieldType.ROW, 0);
//Dragging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 1);
pivotTable.addFieldToArea(PivotFieldType.DATA, 2);

pivotTable.getColumnFields().get(0).addCalculatedItem(“AverageQtrs”, “=Average(‘Qtr3’,‘Qtr4’)”);
workbook.save(“outpivottableaddcalcitem1.xlsx”);



We also recommend you to kindly see the documents in the section for your reference:


Hope, this helps a bit.

Thank you.