Select all fields in a pivot table

Aspose.Cells Product Family
1

Can you please provide guidance on how to select all fields in a Pivot table? The attached is the sample file with a pivot table and some fields in the fields

Excel Fields.png (10.9 KB)

SampleFileWithPivot.zip (40.2 KB)

2

@bchovatiya1,

Please try using PivotTable.AddFieldToArea() overloaded method(s) to add your desired field to your desired area (e.g., Row, Column, Data, etc.)

Please see the topic on how to create and manipulate pivot tables for your reference.

3

Thank you for the quick response. I tried the following code and it didn’t work. The attached SampleFileWithPivot_intended result.zip shows the intended result.SampleFileWithPivot_intended result.zip (41.8 KB)

==== Code ====

        Aspose.Cells.Workbook lAsposeWorkbook = new Aspose.Cells.Workbook(@"C:\Users\Administrator\Desktop\SampleFileWithPivot.xlsx");

        foreach (var item in lAsposeWorkbook.Worksheets[2].PivotTables)
        {
            PivotFieldCollection pivotFieldCollection = item.Fields(PivotFieldType.Column);

            for (int i = 0; i < pivotFieldCollection.Count; i++)
            {
                item.AddFieldToArea(PivotFieldType.Column, pivotFieldCollection[i].BaseIndex);
            }

            pivotFieldCollection = item.Fields(PivotFieldType.Row);

            for (int i = 0; i < pivotFieldCollection.Count; i++)
            {
                item.AddFieldToArea(PivotFieldType.Row, pivotFieldCollection[i].BaseIndex);
            }

            pivotFieldCollection = item.Fields(PivotFieldType.Data);

            for (int i = 0; i < pivotFieldCollection.Count; i++)
            {
                item.AddFieldToArea(PivotFieldType.Data, pivotFieldCollection[i].BaseIndex);
            }
        
            pivotFieldCollection = item.Fields(PivotFieldType.Page);

            for (int i = 0; i < pivotFieldCollection.Count; i++)
            {
                item.AddFieldToArea(PivotFieldType.Page, pivotFieldCollection[i].BaseIndex);
            }

            pivotFieldCollection = item.Fields(PivotFieldType.Undefined);

            for (int i = 0; i < pivotFieldCollection.Count; i++)
            {
                item.AddFieldToArea(PivotFieldType.Undefined, pivotFieldCollection[i].BaseIndex);
            }
        }

        lAsposeWorkbook.Save(@"C:\Users\Administrator\Desktop\SampleFileWithPivot_Updated.xlsx");

Also, how can I get PivotFieldType from a field? Just want to reduce multiple looks to a single.

4

@bchovatiya1,

Are you creating the Pivot table from the scratch or manipulate existing PivotTable in the template file. Could you share complete sample code (runnable) and current output Excel file (by Aspose.Cells) containing the undesired pivot table, we will check your issue soon.

5

I am trying to manipulate an existing file with a pivot table. I already provided the source code and sample file in this thread, but here it is again. Code file is included in the attachment zip

Sample file and code.zip (109.7 KB)
WhatIsdToBeDone.png (21.0 KB)

6

@bchovatiya1,

Could you try the following sample code to accomplish your task:
e.g.
Sample code:

Aspose.Cells.Workbook lAsposeWorkbook = new Aspose.Cells.Workbook("e:\\test2\\Orignal.xlsx");

            PivotTable pivotTable = lAsposeWorkbook.Worksheets[2].PivotTables[0];

            for (int j = 0; j < pivotTable.BaseFields.Count; j++) 
            {
                
                string fieldName = pivotTable.BaseFields[j].Name;
                switch (fieldName)
                {
                    case "Year":
                    case "Quarter":
                    case "Amount":    
                    pivotTable.AddFieldToArea(PivotFieldType.Data, pivotTable.BaseFields[j]);
                    break;
                   
                    case "Timeline":
                    pivotTable.AddFieldToArea(PivotFieldType.Row, pivotTable.BaseFields[j]);
                    break;
                   
                }
                
            }
                

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

            pivotTable.RefreshData();
            pivotTable.CalculateRange();
            pivotTable.CalculateData();

            lAsposeWorkbook.Save("e:\\test2\\out1.xlsx");

Let us know if you still find the issue.

7

It worked. Thank you.

A follow-up question. How do we programmatically decide which field is the data field and which is the row field? You used a switch case with the field name, but we can’t do it because we may have 100 files at a time with pivots, with more than 10 pivot tables in each file. Obviously, we can’t modify the code for each table. So we have to check all the fields regardless of the type and name. How do we do it?

8

@bchovatiya1,

That might not be determined, it totally depends upon your choice/needs on which fields will move to which area (row, column or data area, etc.). Generally, numeric and summary values come under data area but this is not standard.