Display Calculated Fields as Columns

Aspose.Cells Product Family
,
1

How do you display calculated fields in a pivot table as columns?

2

@FireWave
Please simply add PivotTable.AddFieldToArea method to add calculated fields to area as normal pivotsheets.
See document https://docs.aspose.com/cells/net/create-pivot-table/

3

That doesn’t seem to work for Calculated fields.

I am attempting the following, and I get a “You could not drag calculated field to this area.” error.

Note: The only way I can get the PivotField object for the calculated field is using PivotFieldType.Undefined.

        PivotTable pivotTable = _Excel.Worksheets[sheetIdx].PivotTables[pivotIdx];

        pivotTable.AddCalculatedField(fieldName, formula, true);

        PivotFieldCollection pivotFields = pivotTable.Fields(PivotFieldType.Undefined);
        PivotField pivotField = pivotFields[fieldName];
        pivotTable.AddFieldToArea(PivotFieldType.Column, fieldName);
4

@FireWave,

Could you please create a sample console application (source code without compilation errors), zip the project and provide us with resource (e.g., Excel) files (if any) to reproduce the issue. We will check
your issue soon.

PS. please exclude Aspose.Cells.Dll to minimize the size of the zipped archive.

5

The code is right there. I just need to create calculated fields and display them as columns other than rows.

The fields are created properly, and if I drag the “summation values” field manually from the Rows area to the Columns area, I get what I want. I just need a sample doing the final step in code.

6

Okay, after much trial and error, I finally got it.

After adding all of my calculated fields, I called the following code:

        PivotFieldCollection fields = pivotTable.RowFields;
        PivotField pivotField = fields["Data"];
        pivotTable.AddFieldToArea(PivotFieldType.Column, pivotField);

Hope this is helpful to someone else as well.

7

@FireWave
Thanks for your codes and sorry for my delay reply.
After closely looking into Calculated Fields, it only be in data Area.
And if there two or more fields in the data area, please drag pivotTable.DataField to your excepted area.
See following codes:

  // Instantiating a 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
            Cell cell = cells["A1"];
            cell.PutValue("Sport");
            cell = cells["B1"];
            cell.PutValue("Quarter");
            cell = cells["C1"];
            cell.PutValue("Sales");

            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);

            Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet.PivotTables;

            // Adding a PivotTable to the worksheet
            int index = pivotTables.Add("=A1:C8", "E3", "PivotTable2");

            // Accessing the instance of the newly added PivotTable
            Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

            // Unshowing grand totals for rows.
            pivotTable.RowGrand = false;

            // Draging the first field to the row area.
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);

            // Draging the second field to the column area.
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 1);


            // Draging the third field to the data area.
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);
            //Adds a calculated field to pivot field and drag it to data area.

            pivotTable.AddCalculatedField("CalField", "='Sales'");
            //if there is one or two fields in  the data area, it will be data field
            if (pivotTable.DataFields.Count > 1)

            {
                PivotField pivotField = pivotTable.DataField;
                pivotTable.AddFieldToArea(PivotFieldType.Column, pivotField);
            }


           


            // Saving the Excel file
            workbook.Save(dir + "dest.xlsx");