Problem with Calculatted Field in Pivot

Aspose.Cells Product Family
1

Hi,

I have to create a calculated field in Pivot table. I am able to create it succesfully.

but the problem is that calculated field is showing in Row label. I want to show in Column labels of pivot table.

So, please if you can provide code for how to move this calculated field to column label.

I tried pt.BaseFields[4].DragToColumn = true; it is not working.

HERE pt = my pivot table instance.

Thanks in advance,

2
Hi,

Could you try the following if it may help a bit:
e.g

PivotField pf = pivotTable.BaseFields[index];
pivotTable.AddFieldToArea(PivotFieldType.Column,pf);

And, please try our latest version/fix i.e. v7.5.0.

If you still have any issue, kindly give us complete runnable sample code and attach your template file here, we will check your issue soon.

Thank you.
3

Thanks for your response.

I am using v7.4.3.5.

I did try with your code but i am not getting any results.

i did not use v7.5.0, before using that version I want you to please check my code and sheet.

I have attached the sample excel file

.In first tab, i got the result from my code.

In the second tab, my requirement is there.

Please see the below code and attached sample.

sheet.Cells["F1"].PutValue("Store");
sheet.Cells["F2"].PutValue("bStore");
sheet.Cells["F3"].PutValue("AStore");
sheet.Cells["F4"].PutValue("bStore");
sheet.Cells["F5"].PutValue("AStore");
sheet.Cells["F6"].PutValue("AStore");


sheet.Cells["G1"].PutValue("GAme");
sheet.Cells["G2"].PutValue("Golf");
sheet.Cells["G3"].PutValue("Tennis");
sheet.Cells["G4"].PutValue("Golf");
sheet.Cells["G5"].PutValue("Tennis");
sheet.Cells["G6"].PutValue("Football");

sheet.Cells["H1"].PutValue("Day");
sheet.Cells["H2"].PutValue("Day1");
sheet.Cells["H3"].PutValue("DAy2");
sheet.Cells["H4"].PutValue("day3");
sheet.Cells["H5"].PutValue("day4");
sheet.Cells["H6"].PutValue("day4");

sheet.Cells["I1"].PutValue("Sales");
sheet.Cells["I2"].PutValue(5);
sheet.Cells["I3"].PutValue(20);
sheet.Cells["I4"].PutValue(10);
sheet.Cells["I5"].PutValue(5);
sheet.Cells["I6"].PutValue(15);

PivotTableCollection pcs = sheet.PivotTables;

int idx = pcs.Add("=F1:I6", "J10", "Ptable1");

PivotTable pt = pcs[idx];

pt.AddFieldToArea(PivotFieldType.Row, 1);
pt.AddFieldToArea(PivotFieldType.Column, 2);
pt.AddFieldToArea(PivotFieldType.Data, 3);
pt.AddFieldToArea(PivotFieldType.Page, 0);

pt.PageFields[0].IsMultipleItemSelectionAllowed = true;
pt.RowGrand = true;


pt.AddCalculatedField("Calculated", "='Sales'/2" );
//pt.AddFieldToArea(PivotFieldType.Column, 4);
pt.BaseFields[4].DragToRow = false;
pt.BaseFields[4].DragToData = false;
pt.BaseFields[4].DragToColumn = true;sheet.Cells["F1"].PutValue("Store");
sheet.Cells["F2"].PutValue("bStore");
sheet.Cells["F3"].PutValue("AStore");
sheet.Cells["F4"].PutValue("bStore");
sheet.Cells["F5"].PutValue("AStore");
sheet.Cells["F6"].PutValue("AStore");


sheet.Cells["G1"].PutValue("GAme");
sheet.Cells["G2"].PutValue("Golf");
sheet.Cells["G3"].PutValue("Tennis");
sheet.Cells["G4"].PutValue("Golf");
sheet.Cells["G5"].PutValue("Tennis");
sheet.Cells["G6"].PutValue("Football");

sheet.Cells["H1"].PutValue("Day");
sheet.Cells["H2"].PutValue("Day1");
sheet.Cells["H3"].PutValue("DAy2");
sheet.Cells["H4"].PutValue("day3");
sheet.Cells["H5"].PutValue("day4");
sheet.Cells["H6"].PutValue("day4");

sheet.Cells["I1"].PutValue("Sales");
sheet.Cells["I2"].PutValue(5);
sheet.Cells["I3"].PutValue(20);
sheet.Cells["I4"].PutValue(10);
sheet.Cells["I5"].PutValue(5);
sheet.Cells["I6"].PutValue(15);

PivotTableCollection pcs = sheet.PivotTables;

int idx = pcs.Add("=F1:I6", "J10", "Ptable1");

PivotTable pt = pcs[idx];

pt.AddFieldToArea(PivotFieldType.Row, 1);
pt.AddFieldToArea(PivotFieldType.Column, 2);
pt.AddFieldToArea(PivotFieldType.Data, 3);
pt.AddFieldToArea(PivotFieldType.Page, 0);

pt.PageFields[0].IsMultipleItemSelectionAllowed = true;
pt.RowGrand = true;


pt.AddCalculatedField("Calculated", "='Sales'/2" );
//pt.AddFieldToArea(PivotFieldType.Column, 4);
pt.BaseFields[4].DragToRow = false;
pt.BaseFields[4].DragToData = false;
pt.BaseFields[4].DragToColumn = true;

Please provide the solution for this.

Thanks in advance

4

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have tested your code and found the calculated field could not be moved to column labels and it throws exception.

We have logged this issue in our database. We will look into it and provide you a solution. Once, there is some update for you, we will let you know asap.

This issue has been logged as CELLSNET-41800.

5

Hi,


Please replace the following lines of your code segment, i.e:
//pt.AddFieldToArea(PivotFieldType.Column, 4);
pt.BaseFields[4].DragToRow = false;
pt.BaseFields[4].DragToData = false;
pt.BaseFields[4].DragToColumn = true;

with the following line of code:
pt.AddFieldToArea(PivotFieldType.Column, pt.DataField);

it works fine as I tested for your needs.

Thank you.

6

Hi,

Thanks for your response.

Could you please tell, how much time will it take to fix the issue for CELLSNET-41800.

Thanks,


7

Hi,


Please do what is suggested in my previous reply in this thread:
https://forum.aspose.com/t/91780

I have tested and it works fine according to your requirements.

Thank you.

8

Hi,

Please use the following code. It will created a pivot table as expected by you. I have attached the output file for your reference.

C#


Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];


sheet.Cells[“F1”].PutValue(“Store”);

sheet.Cells[“F2”].PutValue(“bStore”);

sheet.Cells[“F3”].PutValue(“AStore”);

sheet.Cells[“F4”].PutValue(“bStore”);

sheet.Cells[“F5”].PutValue(“AStore”);

sheet.Cells[“F6”].PutValue(“AStore”);



sheet.Cells[“G1”].PutValue(“GAme”);

sheet.Cells[“G2”].PutValue(“Golf”);

sheet.Cells[“G3”].PutValue(“Tennis”);

sheet.Cells[“G4”].PutValue(“Golf”);

sheet.Cells[“G5”].PutValue(“Tennis”);

sheet.Cells[“G6”].PutValue(“Football”);


sheet.Cells[“H1”].PutValue(“Day”);

sheet.Cells[“H2”].PutValue(“Day1”);

sheet.Cells[“H3”].PutValue(“DAy2”);

sheet.Cells[“H4”].PutValue(“day3”);

sheet.Cells[“H5”].PutValue(“day4”);

sheet.Cells[“H6”].PutValue(“day4”);


sheet.Cells[“I1”].PutValue(“Sales”);

sheet.Cells[“I2”].PutValue(5);

sheet.Cells[“I3”].PutValue(20);

sheet.Cells[“I4”].PutValue(10);

sheet.Cells[“I5”].PutValue(5);

sheet.Cells[“I6”].PutValue(15);


PivotTableCollection pcs = sheet.PivotTables;


int idx = pcs.Add("=F1:I6", “J10”, “Ptable1”);



PivotTable pt = pcs[idx];


pt.AddFieldToArea(PivotFieldType.Row, 1);


pt.AddFieldToArea(PivotFieldType.Column, 2);


pt.AddFieldToArea(PivotFieldType.Data, 3);


pt.AddFieldToArea(PivotFieldType.Page, 0);




pt.PageFields[0].IsMultipleItemSelectionAllowed = true;

pt.RowGrand = true;



pt.AddCalculatedField(“Calculated”, “=‘Sales’/2”);


pt.AddFieldToArea(PivotFieldType.Column, pt.DataField);



workbook.Save(“output.xlsx”, SaveFormat.Xlsx);



9

Thanks Amjad and Shakeel…

10

The below code is showing error , when I just want to show Calculated Fields in pivot table.

ERROR : Object referenece set to null reference object.

Please see the below code and provide the solution.

Thanks in advance.

PivotTableCollection pcs = sheet.PivotTables;


int idx = pcs.Add("=F1:I6", "J10", "Ptable1");



PivotTable pt = pcs[idx];


pt.AddFieldToArea(PivotFieldType.Row, 1);


pt.AddFieldToArea(PivotFieldType.Column, 2);


//pt.AddFieldToArea(PivotFieldType.Data, 3); -- comment this code


pt.AddFieldToArea(PivotFieldType.Page, 0);




pt.PageFields[0].IsMultipleItemSelectionAllowed = true;

pt.RowGrand = true;



pt.AddCalculatedField("Calculated", "='Sales'/2");


pt.AddFieldToArea(PivotFieldType.Column, pt.DataField);



workbook.Save("output.xlsx", SaveFormat.Xlsx);
v

11

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We were able to notice the exception. We have logged the exception in our database. We will look into it and once there is some fix or update for you, we will let you know asap.

12
Hi,

[quote user="jack123"]

The below code is showing error , when I just want to show Calculated Fields in pivot table.

ERROR : Object referenece set to null reference object.

Please see the below code and provide the solution.

Thanks in advance.

PivotTableCollection pcs = sheet.PivotTables;


int idx = pcs.Add("=F1:I6", "J10", "Ptable1");



PivotTable pt = pcs[idx];


pt.AddFieldToArea(PivotFieldType.Row, 1);


pt.AddFieldToArea(PivotFieldType.Column, 2);


//pt.AddFieldToArea(PivotFieldType.Data, 3); -- comment this code


pt.AddFieldToArea(PivotFieldType.Page, 0);




pt.PageFields[0].IsMultipleItemSelectionAllowed = true;

pt.RowGrand = true;



pt.AddCalculatedField("Calculated", "='Sales'/2");


pt.AddFieldToArea(PivotFieldType.Column, pt.DataField);



workbook.Save("output.xlsx", SaveFormat.Xlsx);
v

[/quote]

Well, surely, it will give you "null reference object" exception, the reason is simple since you are not adding any pivot data field (as you are commenting out the line: "pt.AddFieldToArea(PivotFieldType.Data, 3);"), the line "pt.AddFieldToArea(PivotFieldType.Column, pt.DataField);" will give you exception because there is no data filed added and pt.DataField would be null obviously.

I think for your needs, if you don't want data field to be added, then kindly just skip or eliminate the line of code:
pt.AddFieldToArea(PivotFieldType.Column, pt.DataField); // considering the fact that you have commented out the line at first: pt.AddFieldToArea(PivotFieldType.Data, 3);

If you still have any confusion or issue, kindly provide us the sample file containing your desired pivot table, we will check it soon.

Thank you.
13

Hi Amjad,

Thanks for your response.

I know that the error is showing because we are not adding any Datafield to pivot table.

But if you open any excel file and and add just Calculated fields to pivot table. we can able to that.

but using aspose.cells we cant accomplish the same.

So, If I just want to add calculated fields to the pivot table using Aspose.Cells, how can i achieve this.

I hope you understand what i mean to say.

So, please kindly provide the code, if possible..

Thanks in advance

14

Hi,


Please provide your expected file having your desired PivotTable by creating it manually in MS Excel, we will check it soon.

Thank you.
15

Hi Amjad,

Please find the attached sample excel file.

Thanks in advance

16

Hi,


Please see the sample code below to accomplish your needs for your reference. It generates the PivotTable as per your sample (expected) Excel file:

Sample code:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];


sheet.Cells[“F1”].PutValue(“Store”);
sheet.Cells[“F2”].PutValue(“bStore”);
sheet.Cells[“F3”].PutValue(“AStore”);
sheet.Cells[“F4”].PutValue(“bStore”);
sheet.Cells[“F5”].PutValue(“AStore”);
sheet.Cells[“F6”].PutValue(“AStore”);


sheet.Cells[“G1”].PutValue(“GAme”);
sheet.Cells[“G2”].PutValue(“Golf”);
sheet.Cells[“G3”].PutValue(“Tennis”);
sheet.Cells[“G4”].PutValue(“Golf”);
sheet.Cells[“G5”].PutValue(“Tennis”);
sheet.Cells[“G6”].PutValue(“Football”);

sheet.Cells[“H1”].PutValue(“Day”);
sheet.Cells[“H2”].PutValue(“Day1”);
sheet.Cells[“H3”].PutValue(“DAy2”);
sheet.Cells[“H4”].PutValue(“day3”);
sheet.Cells[“H5”].PutValue(“day4”);
sheet.Cells[“H6”].PutValue(“day4”);

sheet.Cells[“I1”].PutValue(“Sales”);
sheet.Cells[“I2”].PutValue(5);
sheet.Cells[“I3”].PutValue(20);
sheet.Cells[“I4”].PutValue(10);
sheet.Cells[“I5”].PutValue(5);
sheet.Cells[“I6”].PutValue(15);

PivotTableCollection pcs = sheet.PivotTables;

int idx = pcs.Add("=F1:I6", “J10”, “Ptable1”);

PivotTable pt = pcs[idx];

pt.AddFieldToArea(PivotFieldType.Row, 1);
// pt.AddFieldToArea(PivotFieldType.Column, 2);
// pt.AddFieldToArea(PivotFieldType.Data, 3);//
pt.AddFieldToArea(PivotFieldType.Page, 0);

pt.PageFields[0].IsMultipleItemSelectionAllowed = true;
pt.RowGrand = true;

pt.AddCalculatedField(“Calculated1”, “=‘Sales’/2”);
pt.AddCalculatedField(“Calculated2”, “=‘Sales’/10”);
pt.AddFieldToArea(PivotFieldType.Column, pt.RowFields[1]);

pt.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;
workbook.Save(“e:\test2\output1.xlsx”);

Thank you.