Pivot table conditional formatting not working on 24.12.0

Aspose.Cells Product Family
1

Hello,

After upgrading from 23.8.0 to 24.12.0, it looks like the code for pivot table conditional formatting stopped working - the rules are just not added in Excel.

Aspose.Cells version: 24.12.0
C# target framework: .NET 8

Code used to generate the xlsx file:

using Aspose.Cells;
using Aspose.Cells.Pivot;
using System.Drawing;
using System.Text;

byte[] SrcDataByteArray = Encoding.ASCII.GetBytes(
$@"City,Product,Sales
Paris,Cream,2300
Paris,Lotion,1600
Tunis,Cream,900
Tunis,Lotion,1400
Tunis,Cream,3090
Tunis,Lotion,6000
Paris,Cream,4320");

// Create a memory stream from the source data
MemoryStream dataStream = new MemoryStream(SrcDataByteArray);

// Create LoadOptions class object to load the comma-separated data given above
LoadOptions loadOptions = new LoadOptions(LoadFormat.Csv);

// Instantiate a workbook class object having above mentioned data
Workbook wbCSV = new Workbook(dataStream, loadOptions);

// Get access to the first worksheet in the collection
Worksheet targetSheet = wbCSV.Worksheets[0];

// Get collection of pivot tables in the target worksheet
Aspose.Cells.Pivot.PivotTableCollection pvTablesCollection = targetSheet.PivotTables;

// Get pivot table index after adding a new pivot table by provding source data range and destination cell
int iNewPivotTable = pvTablesCollection.Add("=A1:C8", "F3", "MyPivotTable");

// Get the instance of newly added pivot table for further processing
Aspose.Cells.Pivot.PivotTable excelPivot = pvTablesCollection[iNewPivotTable];

// Hide the grand total for rows in the output Excel file
excelPivot.RowGrand = false;
excelPivot.ColumnGrand = false;

// Add the first field to the column area
excelPivot.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 0);
// Add the second field to the row area
excelPivot.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);
// Add the third field to the data area
excelPivot.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);


// Conditional formatting

//int ind = targetSheet.ConditionalFormattings.Add();
//FormatConditionCollection fcs = targetSheet.ConditionalFormattings[ind];
//CellArea cellArea = new CellArea()
//{
//    StartRow = 1,
//    EndRow = 7,
//    StartColumn = 2,
//    EndColumn = 2
//};
//fcs.AddArea(cellArea);

//int indexCond = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.GreaterThan, "1", null);
//fcs[indexCond].Style.BackgroundColor = ColorTranslator.FromHtml("#e39e9e");


int ind = excelPivot.PivotFormatConditions.Add();
PivotFormatCondition pivForCond = excelPivot.PivotFormatConditions[ind];
pivForCond.ScopeType = PivotConditionFormatScopeType.Data;
pivForCond.AddDataAreaCondition(excelPivot.DataFields[0]);
pivForCond.SetConditionalAreas();

int indexCond = pivForCond.FormatConditions.AddCondition(FormatConditionType.CellValue, OperatorType.GreaterThan, "1", null);
pivForCond.FormatConditions[indexCond].Style.BackgroundColor = ColorTranslator.FromHtml("#e39e9e");


// Saving the output Excel file with pivot table
wbCSV.Save("OutputPivotTable.xlsx");

System.Console.WriteLine("Done");

Note also the commented out section which works on regular cells, but not on the pivot table.
The examples are taken from (I couldn’t find a pivot table cond format example)

Thank you

2

@pavle.aleksov,

Thanks for the details and code snippet.

After initial testing, I am able to reproduce the issue as you mentioned by using your sample code snippet. I found Pivot table conditional formatting is not working when using latest Aspose.Cells for .NET v24.12, although it works fine if I use Aspose.Cells for .NET v23.8.

We require thorough evaluation of the issue. 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-57427

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.

3

@pavle.aleksov
Please set data range for the conditional formatting:

int indexCond = pivForCond.FormatConditions.AddCondition(FormatConditionType.CellValue, OperatorType.GreaterThan, "1", null);
pivForCond.FormatConditions[indexCond].Style.BackgroundColor = ColorTranslator.FromHtml("#e39e9e");
//Solution 1:
pivForCond.FormatConditions.AddArea(excelPivot.DataBodyRange);
//Solution 2:
//excelPivot.CalculateData();
//pivForCond.SetConditionalAreas();
4

Thanks for the suggestions

Solution 1 works if we’re applying all rules to all data, however in my case (not the same as the one I mentioned above) I have different rules per data PivotField (Aspose.Cells.Pivot.PivotFieldType.Data), so I need to keep using AddDataAreaCondition - in which case Solution 2 is needed.

In the end, it seems that excelPivot.CalculateData() is the only thing that needs to be added to my solution, but I would need to add it inside a foreach (iterating over data PivotFields). I tried adding it outside but it didn’t solve the issue.
All of the data should already be inside the sheet before I’m adding conditional formatting rules, so I’m not sure why running it outside of the foreach doesn’t work.

Couple of questions:

  1. What exactly is CalculateData() doing?
  2. Are there any performance impacts for running it inside foreach?
  3. Why do we need CalculateData() now, but we didn’t need it in lower versions?

Thanks!

5

@pavle.aleksov

We added a check that whether the conditional formatting does not contains a specific range in the recent version. If no range, it will be removed when saving the file. We will fix this issue in the next version 25.1. Both solution 1 and 2 are used to add a range. Now you can simply add any range as solution 1, then we will not remove conditional formats. This range can be arbitrary, when Excel opens a file, it will be refreshed.

6

@pavle.aleksov
I am evaluating PivotFormatCondition.SetConditionalAreas() method and I plan to Implicitly call this method when calling AddDataAreaCondition() method in the next version.

7

@simon.zhao thanks! I’m still not sure how it relates to CalculateData though. We already call SetConditionalAreas so it’s not an issue here, it’s more why CalculateData is suddenly needed

8

@pavle.aleksov
1,CalculateData() will calculate actual ranges () of pivot conditional Format according to settings AddDataAreaCondition(PivotField dataField) and cache those ranges , SetConditionalAreas() method adds these cached ranges to FormatConditionCollection with FormatConditionCollection .AddArea() method.

2,

We found FormatConditionCollection is useless if there is no range setting . We added checks to remove excess data. We mistakenly deleted the conditional style of the pivot table.

3,In the next version,
a) We will directly add actual ranges to FormatConditionCollection when calling AddDataAreaCondition() method.
b) If CalculateData() is called, we will directly update ranges of FormatConditionCollection too without calling SetConditionalAreas() method. So SetConditionalAreas() will be useless and removed.

1 Like
9

@pavle.aleksov,

This is to inform you that your issue (Ticket ID: “CELLSNET-57427”) has been resolved. The enhancement/fix (as you mentioned in our previous reply) will be included in the next release (Aspose.Cells v25.1) scheduled for release in early January 2025. We will notify you when the new version is released.

1 Like