PivotFormatCondition

Aspose.Cells Product Family
1

I’m working on upgrading my Aspose.Cells version in a C# app from 23.5.0 to 25.1.2

It appears some of the Pivot Formatting classes and methods have changed. I’ve gotten my code partially working, but having trouble with getting the PivotFormatCondition correctly working to use the new PivotConditionalFormat class.

Here is my original code:

    PivotFormatConditionCollection pfcs = pivotTable.PivotFormatConditions;
    //clear all the current conditional formats.
    pfcs.Clear();
    worksheet.ConditionalFormattings.Clear();

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

    pfc.AddDataAreaCondition("Inc to Earned LR");
    pfc.AddRowAreaCondition("CommonName");

    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.BackgroundArgbColor = Color.FromArgb(255, 199, 206).ToArgb();
    fc.Style.Font.ArgbColor = Color.FromArgb(156, 0, 6).ToArgb();

Could you please provide assistance with correctly rewriting this code to use the new PivotConditionalFormatCollection and PivotConditionalFormat classes?

I’ve noticed specifically that the AddDataAreaCondition(), AddRowAreaCondition(), and SetConditionalAreas() methods have been removed. What is the new way to properly call these methods?

2

@eforden

To update your code for the new PivotConditionalFormatCollection and PivotConditionalFormat classes in Aspose.Cells, you will need to adjust how you add conditions to your pivot table. The methods AddDataAreaCondition(), AddRowAreaCondition(), and SetConditionalAreas() have been replaced with new approaches.

Here’s how you can rewrite your code:

// Assuming 'pivotTable' is your PivotTable object
PivotConditionalFormatCollection pcfCollection = pivotTable.PivotConditionalFormats;

// Clear all the current conditional formats
pcfCollection.Clear();
worksheet.ConditionalFormattings.Clear();

// Add a new PivotConditionalFormat
int pIndex = pcfCollection.Add();
PivotConditionalFormat pcf = pcfCollection[pIndex];
pcf.ScopeType = PivotConditionFormatScopeType.Field;

// Add conditions for data area and row area
pcf.AddDataAreaCondition("Inc to Earned LR");
pcf.AddRowAreaCondition("CommonName");

// Create format conditions
FormatConditionCollection fcc = pcf.FormatConditions;
int idx = fcc.AddCondition(FormatConditionType.CellValue);
FormatCondition fc = fcc[idx];
fc.Formula1 = "0.4";
fc.Operator = OperatorType.GreaterOrEqual;
fc.Style.BackgroundColor = Color.FromArgb(255, 199, 206);
fc.Style.Font.Color = Color.FromArgb(156, 0, 6);

// Save the workbook
workbook.Save("output.xlsx");

Key Changes:

  1. PivotConditionalFormatCollection: Use PivotConditionalFormatCollection instead of PivotFormatConditionCollection.
  2. Adding Conditions: You can still use AddDataAreaCondition() and AddRowAreaCondition() methods, but ensure you are using the correct class.
  3. Setting Styles: The way to set styles remains similar, but ensure you are using the correct properties for the new classes.

Make sure to test the updated code to ensure it works as expected with the new version of Aspose.Cells. If you encounter any issues, refer to the latest documentation or community posts for additional guidance.

3

The problem I’m having is that the AddDataAreaCondition() and AddRowAreaCondition() methods don’t exist in the new PivotConditionFormat class so I can’t seem to get this working.

4

I think there may be a bug somewhere, I’ve nearly gotten this working but something appears to be not working as expected.

Here’s my updated code:

    PivotConditionalFormat pcf = pcfCollection[pcfCollection.Add()];
    pcf.ScopeType = PivotConditionFormatScopeType.Field;

    pcf.AddFieldArea(PivotFieldType.Data, "Inc to Earned LR");
    pcf.AddFieldArea(PivotFieldType.Row, "CommonName");

But the attached image is what is showing up in Excel, it is selecting the wrong Row field. I’m telling it to use the “CommonName” field but it’s incorrectly using “ClaimCount” which is another one of my Row fields. I’m not sure if this helps, but “ClaimCount” happens to be the last/bottom pivot Row field.

image.png (9.1 KB)

5

@eforden
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-57743

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

9

@eforden
Could you post your template file , sample codes and excepted files ? We need more info to check this issue .

10

AsposePivotTableSample1.zip (301.7 KB)

See the attached project.

Take a look in the Program.cs on line 230, you’ll see where I’ve added a PivotFieldType.Row for the “CommonName” field, but when you open up the Final.xlsx you can see that it is incorrectly selecting the “ClaimCount” field.

11

@eforden,

Thank you for providing the template file and sample application. After conducting initial test, I have been able to replicate the issue (regarding conditional formatting for pivot table) . I have recorded your template file and sample project under the existing ticket “CELLSNET-57743” in our database. We will investigate your issue thoroughly and will provide you with updates soon.

12

@eforden
Please change scope as data :

  PivotConditionalFormat pcf = pcfCollection[pcfCollection.Add()];
  pcf.ScopeType = PivotConditionFormatScopeType.Data;
13

Unfortunately, that didn’t work, this is what it looks like in Excel after making that change:

image.png (98.2 KB)

This is what I’m expecting it to look like. This was created using the older logic and Aspose.Cells version 23.5.0. Only the cells that are red should be applied the conditional format:

image.png (122.5 KB)

14

@eforden
Sorry for my confusion.
Now I got you.

The AddFieldArea() method here is ambiguous: is it to continuously add fields to one pivot area, or to add multiple pivot areas separately?
In order to be compatible with previous products, we will roll back and continuously add fields to a pivot area.

15

That does make sense, thank you very much for the follow-up! Will an update still be posted here when the roll back fix has been applied?

16

@eforden,

You are welcome. We have reopened the existing ticket “CELLSNET-57743” and we will update you here with link to fixed version once it is available.

17

@eforden
We have fixed this issue and will include the fix into the next version 25.2.
dest (2).zip (19.0 KB)

18

The issues you have found earlier (filed as CELLSNET-57743) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi