Not able to add filters to pivot table in aspose.cells

Aspose.Cells Product Family
, ,
43

@Shivammirje1998,
Please use the PivotTable.IsAutoFormat to control the “AutoFit columns widths on update” option.
The sample code is as follows:

Workbook workbook = new Workbook(filePath + “Sample.xlsx”);
Worksheet sheet = workbook.Worksheets.Add(“NewSheet”);
PivotTableCollection pivotTables = sheet.PivotTables;
// Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Masterfile!$A$1:$D$8", “A1”, “PivotTable3”);

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

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 1);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 3);

//Add the following code to move PivotTable.DataField to column area
if (pivotTable.DataField != null)
{
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);
}

pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;
pivotTable.DataFields[0].NumberFormat = “0.0,”;
pivotTable.DataFields[1].NumberFormat = “0.0,”;

#region sort pivot table data columns in descending order
PivotField currRowField = pivotTable.RowFields[0];
currRowField.IsAutoSort = true;
currRowField.IsAscendSort = false;
//Sort PivotField in row area via DataField named “Quantity”;
currRowField.AutoSortField = 0;
#endregion

#region add conditional foramts
int formatIndex = pivotTable.PivotFormatConditions.Add();
PivotFormatCondition pfc = pivotTable.PivotFormatConditions[formatIndex];

//Set conditional styles only in the DataField named “Quantity”
pfc.AddDataAreaCondition(pivotTable.DataFields[0]);
pfc.SetConditionalAreas();

FormatConditionCollection fcc = pfc.FormatConditions;
int idx = fcc.AddCondition(FormatConditionType.CellValue);
FormatCondition fc = fcc[idx];
fc.Formula1 = “3000”;
fc.Operator = OperatorType.GreaterOrEqual;
fc.Style.BackgroundColor = Color.Red;
#endregion

//add this line to control the “autofit column width on update” option
pivotTable.IsAutoFormat = false;
CellArea area = pivotTable.RowRange;
int start = area.StartColumn;

sheet.Cells.SetColumnWidthPixel(start, 220);

// Saving the Excel file
workbook.Save(filePath + “sample_out.xlsx”);

44

Hi @John.He,

It worked nicely but now the problem is that data column values are on right side and I want them in middle.

I tried with this:

sheet.Cells.Style.HorizontalAlignment = TextAlignmentType.Center;
sheet.Cells.Style.VerticalAlignment = TextAlignmentType.Center;

But this didn’t work.

Maybe you could help me out :slight_smile:

45

@Shivammirje1998,

Please try to add the following code segment to set horizontal alignment to data fields cells:
e.g.
Sample code:

........
CellArea area1 = pivotTable.DataBodyRange;
            Style style = workbook.CreateStyle();
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.VerticalAlignment = TextAlignmentType.Center;
                        
            for (int r = area1.StartRow; r <= area1.EndRow; r++)
            {
                for (int c = area1.StartColumn; c <= area1.EndColumn; c++)
                {
                    pivotTable.Format(r, c, style);
                    
                }

            }

Hope, this helps a bit.

46

Thanks @Amjad_Sahi for your quick response.

It worked perfectly. :slight_smile:

47

@Shivammirje1998,

Good to know that the suggested code segment works for your needs well. In the event of further queries or issue, feel free to write us back.

48

Hi @John.He and @Amjad_Sahi,

Could you please tell me if the AddDataAreaCondition is present in Aspose.Cells 20.3 version? If yes, then could you please share that version?

49

@Shivammirje1998,

No, we supported relevant APIs for conditional formatting on pivot tables in newer versions (i.e., >= Aspose.Cells 21.7). We recommend you to kindly upgrade to and try latest version of the APIs.

50

I am using Aspose with Bizagi. And Bizagi’s latest version supports use of Aspose.Cells 20.3 Version.
What should I do?

51

@Shivammirje1998,

Please note, we cannot include features or fixes to older versions of Aspose.Cells APIs. The features or fixes are added to latest APIs set only. You should upgrade to latest Aspose.Cells version and then make use it with Bizagi.