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

Free Support Forum - aspose.com

Pivot Tables - AddCalculatedField - not working - ASPOSE.CELLS 4.9

Hi ,

i want to add the calculated filed to the pivot tables using C# .NET as below.

pivotTable1.AddCalculatedField(“presentationsHour”, “=IF(ISERROR(presentations/prodHours),0,5)”, true)

Above field “presentationsHour” is adding to pivot tables but we are getting #DIV errors. samething if done from excel manually its working fine.

currently we are using the ASPOSE.CELLS 4.9 version.

Please help me on this ASAP.

Thanks
Venkata

Hi,

I have tested your case and it works fine with the attached latest version v4.9.1.3.

Following is my test code and attached is the generated file.

Sample code:

//Instantiating an Workbook object
Workbook workbook = new Workbook();

//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];

Cells cells = sheet.Cells;
//Setting the value to the cells
Aspose.Cells.Cell cell = cells[“A1”];
cell.PutValue(“Sport”);
cell = cells[“B1”];
cell.PutValue(“Quarter test”);
cell = cells[“C1”];
cell.PutValue(“Sales”);
cell = cells[“D1”];
cell.PutValue(“Sales1”);

cell = cells[“A2”];
cell.PutValue(“Golf”);
cell = cells[“A3”];
cell.PutValue(“Golf”);
cell = cells[“A4”];
cell.PutValue(“Tennis”);
cell = cells[“A5”];
cell.PutValue(“Tennis”);
cell = cells[“A6”];
cell.PutValue(“Tennis”);
cell = cells[“A7”];
cell.PutValue(“Tennis”);
cell = cells[“A8”];
cell.PutValue(“Golf”);


cell = cells[“B2”];
cell.PutValue(“Qtr3”);
cell = cells[“B3”];
cell.PutValue(“Qtr4”);
cell = cells[“B4”];
cell.PutValue(“Qtr3”);
cell = cells[“B5”];
cell.PutValue(“Qtr4”);
cell = cells[“B6”];
cell.PutValue(“Qtr3”);
cell = cells[“B7”];
cell.PutValue(“Qtr4”);
cell = cells[“B8”];
cell.PutValue(“Qtr3”);


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

cell = cells[“D2”];
cell.PutValue(0);
cell = cells[“D3”];
cell.PutValue(0);
cell = cells[“D4”];
cell.PutValue(0);
cell = cells[“D5”];
cell.PutValue(0);
cell = cells[“D6”];
cell.PutValue(0);
cell = cells[“D7”];
cell.PutValue(0);
cell = cells[“D8”];
cell.PutValue(0);

PivotTables pivotTables = sheet.PivotTables;


//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:D8", “E20”, “PivotTable1”);

//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];

//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);
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
pivotTable.AddCalculatedField(“test”, “=IF(ISERROR(Sales/Sales1),0,5)”, true);

//Saving the Excel file
workbook.Save(@“e:\test\mycalculatedfieldsbook.xls”);


Please try the attached version, if you still find the #DIV issue, kindly create a sample code (same as mine) and paste it here to show the issue. Also post your template and output files here.
We will check your issue soon.


Thank you.