Conditional Formatting on a Pivot Table

[Steve Good]

Hello ~

I am trying to put conditional formatting on a pivot table using this:

IPivotCache cacheMyPivot = workbook.PivotCaches.Add(sData["A4:F13281]);

IPivotTable pivotMyPivot = pivotMyPivot.PivotTables.Add("Remaining Locations", pivotMyPivot["A4"], cacheMyPovit);

IConditionalFormats condition = pivotMyPivot.Range["E2:E5000"].ConditionalFormats;

IConditionalFormat condition1 = condition.AddCondition();

condition1.FormatType = ExcelCFType.ColorScale; 

In the spreadsheet that is created, the conditional formatting on the column is there, but only on the rows outside of the pivot table.  See screen print below from created spreadsheet.  How can I get the formatting on the pivot table itself?      

Thank you!

Gina

[Karthikeyan Chandrasekar]

Hi Steve, 

Thank you for contacting Syncfusion support. 

As per Microsoft Excel behavior, when a pivot table is added in a range where already data exist then the conditional format address in that range will be split into range above and below pivot table. This is the reason for the conditional format address change in the worksheet. So, we suggest you to set the pivot table location below the pivot table data range. The following notification pop up will be displayed in Microsoft Excel when this scenario occurs. 

Screenshot: 

 

If you click “Yes” then the data will be changed, otherwise the pivot table will not be inserted. 

Please let us know if you have any other queries. 

Regards, 

Karthikeyan  

[Steve Good]

Thank you for the suggestion.  I put the pivot table in a new tab, starting in cell A1 and now the formatting is below the pivot table.  Is there a way to get the formatting on the pivot table?  I can do it in Excel after the spreadsheet is created.  Is there a way to do that in code?

[Karthikeyan Chandrasekar]

Hi Steve, 

We suspect that the conditional formatting is not applied because of Microsoft Excel refresh the layout of the pivot table while opening the output document. So, we suggest you to use IPivotTable.Layout() method before saving the workbook.  

We have shared a simple sample to achieve your requirement which can be downloaded from the following location. 

Sample Link : http://www.syncfusion.com/downloads/support/forum/140872/ze/XlsIOSample992907393  

 

Kindly try this and let us know whether it helps. 

Regards, 

Karthikeyan  

[Steve Good]

I tried that and it did fix the conditional formatting on the pivot able.  However, it removed the Tabular style that I had added earlier in the program with this line:

myPivotTable.Options.RowLayout = PivotTableRowLayout.Tabular;

I did try to put that line back in after the myPivotTable.Layout() call, but it still showed them grouped.

[Karthikeyan Chandrasekar]

Hi Steve, 

We are not able to reproduce the issue from our end. So, we request you modify the sample provided in our previous update and share us along with issue reproducing input files. It will be helpful for us to analyze further on this and provide you the solution at earliest. 

Regards, 

Karthikeyan