Conditional formatting breaks when combining workbooks

Aspose.Cells Product Family
,
1

Hi support,

I’m running into an issue when combining two workbooks, one of which has conditional formatting that references a named range. I run into the issue when the following is true:

  • Workbook1 has a named range defined for a cell
  • Workbook2 has a named range defined for a cell
  • Workbook2 has a conditional formatting rule which references the named range

Attempting to combine these workbooks changes the conditional formatting rule to reference a different named range in the combined workbook.

See the test case below for an example:

//Create workbook #1 and add a named range
Workbook workbook1 = new Workbook();
Worksheet workbook1Sheet = workbook1.Worksheets[0];
workbook1Sheet.Name = "Workbook1Sheet";
Range workbook1Range = workbook1Sheet.Cells.CreateRange("A1");
workbook1Range.Name = "Workbook1Name";

//Create workbook #2 and add a named range/conditional formatting
Workbook workbook2 = new Workbook();
Worksheet workbook2Sheet = workbook2.Worksheets[0];
workbook2Sheet.Name = "Workbook2Sheet";
Range workbook2Range = workbook2Sheet.Cells.CreateRange("A1");
workbook2Range.Name = "Workbook2Name";

int conditionalFormattingIndex = workbook2Sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = workbook2Sheet.ConditionalFormattings[conditionalFormattingIndex];

CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;
fcs.AddArea(ca);

int conditionIndex = fcs.AddCondition(FormatConditionType.Expression);
FormatCondition fc = fcs[conditionIndex];

//The rule directly references a named range that exists in workbook #2
fc.Formula1 = "=Workbook2Name=1";

//Before combining workbooks, the conditional formatting rule references the named range in workbook #2
Assert.True(workbook2.Worksheets[workbook2Sheet.Name].ConditionalFormattings[conditionalFormattingIndex][conditionIndex].Formula1 == "=Workbook2Name=1");

// Combine the workbooks
workbook1.Combine(workbook2);

//After combining workbooks, the conditional formatting rule now references the named range in workbook #1
Assert.True(workbook1.Worksheets[workbook2Sheet.Name].ConditionalFormattings[conditionalFormattingIndex][conditionIndex].Formula1 == "=Workbook1Name=1");

Thanks,
Justin

2

@jziske

When combining two workbooks in Aspose.Cells, you may encounter issues with conditional formatting rules that reference named ranges, especially if both workbooks have named ranges defined. In your case, after combining the workbooks, the conditional formatting rule in Workbook2 is incorrectly referencing the named range from Workbook1 instead of its own.

This behavior can occur because when the workbooks are combined, Aspose.Cells may reassign named ranges to avoid conflicts, leading to the conditional formatting rules pointing to the wrong named range.

To address this issue, you can take the following steps:

  1. Rename Named Ranges: Before combining the workbooks, ensure that the named ranges in both workbooks have unique names. This can help prevent conflicts during the combination process.

  2. Reassign Conditional Formatting: After combining the workbooks, you may need to manually reassign the conditional formatting rules to ensure they reference the correct named ranges. You can do this by iterating through the conditional formatting rules and updating the formulas accordingly.

Here is an example of how you might reassign the conditional formatting after combining the workbooks:

// Combine the workbooks
workbook1.Combine(workbook2);

// Reassign conditional formatting rules in workbook1
foreach (var sheet in workbook1.Worksheets)
{
    var conditionalFormattings = sheet.ConditionalFormattings;
    for (int i = 0; i < conditionalFormattings.Count; i++)
    {
        var fcs = conditionalFormattings[i];
        for (int j = 0; j < fcs.Count; j++)
        {
            var condition = fcs[j];
            // Check and update the formula if it references the old named range
            if (condition.Formula1.Contains("Workbook2Name"))
            {
                condition.Formula1 = condition.Formula1.Replace("Workbook2Name", "NewUniqueName");
            }
        }
    }
}

In this example, replace "NewUniqueName" with the actual name you want to use for the named range in Workbook2. This way, you can ensure that the conditional formatting rules are correctly referencing the intended named ranges after the combination.

For more detailed information on handling named ranges and conditional formatting in Aspose.Cells, you can refer to the official documentation on Format and Modify Named Ranges and Set Conditional Formats of Excel and ODS files.

If you continue to experience issues, please provide additional details about your implementation, and I would be happy to assist further.

3

@jziske
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-56681

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.

4

@jziske
We are pleased to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose.Cells for .NET 24.9) that we plan to release in the first half of September 2024. You will be notified when the next version is released.

5

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