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

Free Support Forum - aspose.com

Pivot Table Conditional Formatting and Applies To

Aspose.Cells Product Family
#1

I was wondering if you could provide an example that would demonstrate how to create a new Conditional Formatting rule inside a Pivot Table that uses the “Applies To” setting. I attached a picture and highlighted what I’m trying to accomplish programmatically.

Thank you!

image.png (10.6 KB)

#2

@softwarelicense_aixgroup_com,

Please create your desired pivot table in Ms Excel manually and specify your desired conditional formatting attribute, save the file and provide us, we will it soon.

PS. please zip the file prior attaching.

#3

Hello,

I have attached a zipped Excel file. On the Pivot tab, you will see the conditional formatting example that I would like to apply programmatically.

ConditionalFormattingSample.zip (34.7 KB)

#4

@softwarelicense_aixgroup_com,
We have reviewed your requirement and have logged it in our database for further investigation. We will write back here once any update is ready for sharing.

This requirement is logged as:
CELLSNET-48165 - Option to apply conditional formatting on PivotTable

#5

@softwarelicense_aixgroup_com,

We are pleased to inform you that we have supported the feature now. We will provide you the supported version with sample for your evaluation in the next few days.

#6

@softwarelicense_aixgroup_com,
Please try our latest version/fix: Aspose.Cells for .NET v21.6.3 (attached)
Aspose.Cells21.6.3 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.6.3 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.6.3 For .NetStandard20.Zip (5.5 MB)

There are some tips for you:
1.Adds PivotFormatCondition.AddDataAreaCondition(string fieldName) method which adds PivotTable conditional format limit in the data fields.
2.Adds PivotFormatCondition.AddDataAreaCondition(PivotField dataField) method which adds PivotTable conditional format limit in the data fields.
3.Adds PivotFormatCondition.AddRowAreaCondition(string fieldName) method which adds PivotTable conditional format range limit in the row fields.
4.Adds PivotFormatCondition.AddRowAreaCondition(PivotField rowField) method which adds PivotTable conditional format range limit in the row fields.
5.Adds PivotFormatCondition.AddColumnAreaCondition(string fieldName) method which adds PivotTable conditional format range limit in the column fields.
6.Adds PivotFormatCondition.AddColumnAreaCondition(PivotField columnField) method which adds PivotTable conditional format range limit in the column fields.
7.Adds PivotFormatCondition.SetConditionalAreas()method which sets conditional areas of PivotFormatCondition object.

The sample code in .NET as follows:
Workbook wb = new Workbook(filePath + “ConditionalFormattingSample.xlsx”);
PivotTable pivot = wb.Worksheets[0].PivotTables[0];

PivotFormatConditionCollection pfcs = pivot.PivotFormatConditions;
//clear all the current conditional formats.
pfcs.Clear();
wb.Worksheets[0].ConditionalFormattings.Clear();

int pIndex = pfcs.Add();
PivotFormatCondition pfc = pfcs[pIndex];
pfc.ScopeType = PivotConditionFormatScopeType.Field;

//Sample code 1:
pfc.AddDataAreaCondition(“LossRatio”);
pfc.AddRowAreaCondition(“CommonName”);
//Sample code 2:
// pfc.AddDataAreaCondition(pivot.DataFields[2]);
// pfc.AddRowAreaCondition(pivot.RowFields[0]);

pfc.SetConditionalAreas();

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

wb.Save(filePath + “out.xlsx”);
wb.Save(filePath + “out.pdf”);

Your issue should be fixed in it.
Let us know your feedback.

#7

I have implemented your sample code and it is working as expected, thank you very much!!

#8

@softwarelicense_aixgroup_com,

Good to know that the new APIs work for your needs. In the event of further queries or comments, feel free to contact us any time, we will be happy to assist you soon.