Conditional Formatting test

Aspose.Cells Product Family
1

Hi

I am testing Aspose.Cells for a project and I have tried to write a unit test for conditional formatting.

The code I have written transforms a workbook correctly. eg; The conditional format works as expected when I load the workbook in Excel.

But when I write an assertion to test that a particular cell has been highlighted, I can’t seem to locate which object and which attribute the highlight colour is defined on.

It’s not on the cell style foreground or background.

Where does the cell Styling get set when it’s been conditionally formatted?

2

@BradvR,
Please share your sample file and code snippet with us for our testing. We will reproduce the problem and provide our feedback after analysis.

3

@BradvR
The style of conditional formatting are defined on class FormatCondition. You may get information(settings, rules, style, applied ranges, etc.) about conditional formatting by classes such as ConditionalFormattingCollection, FormatConditionCollection, FormatCondition, .etc.

Also you may take the online document as reference, such as Set Conditional Formats of Excel and ODS files.|Documentation

4

@ahsaniqbalsidiqui and @johnson.shi, the code I have written to test conditional formatting is below.

As can be seen in the test code, I expect the background for Sheet1!A3 to be System.Drawing.Color.Red. It turns out it’s null.

Where do I find the conditional formatting background colour for cell Sheet1!A3?

        // Set conditional formatting

        // Adds an empty conditional formatting
        int index = workbook.Worksheets["Sheet1"].ConditionalFormattings.Add();
        FormatConditionCollection fcs = workbook.Worksheets["Sheet1"].ConditionalFormattings[index];

        // Set the conditional format range.
        Aspose.Cells.Range aRange = workbook.Worksheets.GetRangeByName("aRange");
        CellArea ca = new CellArea();
        ca.StartRow = aRange.FirstRow;
        ca.EndRow = aRange.FirstRow + aRange.RowCount - 1;
        ca.StartColumn = aRange.FirstColumn;
        ca.EndColumn = aRange.FirstColumn + aRange.ColumnCount - 1;
        fcs.AddArea(ca);

        // Add conditions.
        int conditionIndex = fcs.AddCondition(FormatConditionType.ColorScale, OperatorType.Between, "=Min(A1:A5)", "=MAX(A1:A5)");

        conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "30", "50");
        
        // Change style on the second condition
        FormatCondition fc = fcs[conditionIndex];
        fc.Style.BackgroundColor = System.Drawing.Color.Red;

        workbook.Worksheets["Sheet1"].ConditionalFormattings.Should().NotBeNullOrEmpty();
        Cells cells = workbook.Worksheets["Sheet1"].Cells;

        Style myStyle = cells["A3"].GetStyle();
        myStyle.BackgroundThemeColor.Should().Be(System.Drawing.Color.Red);
5

@BradvR,
I have tried the scenario by modifying your code as follows and got the background color of the cell as follows:

// Set conditional formatting
Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];
// Adds an empty conditional formatting
int index = worksheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = workbook.Worksheets["Sheet1"].ConditionalFormattings[index];

int[] data = new int[] {30,31,32,33,34,35,36,37,38,39};
worksheet.Cells.ImportArray(data, 0, 0,true);
// Creating a named range
Range range = worksheet.Cells.CreateRange("A1", "A10");

// Setting the name of the named range
range.Name = "aRange";

// Set the conditional format range.
Aspose.Cells.Range aRange = workbook.Worksheets.GetRangeByName("aRange");
CellArea ca = new CellArea();
ca.StartRow = aRange.FirstRow;
ca.EndRow = aRange.FirstRow + aRange.RowCount - 1;
ca.StartColumn = aRange.FirstColumn;
ca.EndColumn = aRange.FirstColumn + aRange.ColumnCount - 1;
fcs.AddArea(ca);

// Add conditions.
int conditionIndex = fcs.AddCondition(FormatConditionType.ColorScale, OperatorType.Between, "=Min(A1:A5)", "=MAX(A1:A5)");

conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "30", "50");

// Change style on the second condition
FormatCondition fc = fcs[conditionIndex];
fc.Style.BackgroundColor = System.Drawing.Color.Red;

foreach(Cell cell in worksheet.Cells)
{
    Console.WriteLine($"{cell.GetDisplayStyle().ForegroundColor}");
}

workbook.Save("Output.xlsx");

Here is the program output:

Color [A=255, R=248, G=105, B=107]
Color [A=255, R=249, G=133, B=112]
Color [A=255, R=251, G=162, B=118]
Color [A=255, R=252, G=191, B=123]
Color [A=255, R=254, G=220, B=129]
Color [A=255, R=237, G=229, B=130]
Color [A=255, R=202, G=219, B=128]
Color [A=255, R=168, G=209, B=126]
Color [A=255, R=133, G=199, B=124]
Color [A=255, R=99, G=190, B=123]

P.S. I have tried to compile your code but could not succeed. Could you please share which version of Aspose.Cells are you using?

If your requirement is not fulfilled, you may write your code that can be compiled with the latest version Aspose.Cells for .NET 21.6 and share with us. Also, share an expected output file that is created using MS Excel only for our reference.

Following lines of code could not be compiled:

workbook.Worksheets["Sheet1"].ConditionalFormattings.Should().NotBeNullOrEmpty();
myStyle.BackgroundThemeColor.Should().Be(System.Drawing.Color.Red);