Conditional Formating- detect 'Automatic' font color

Aspose.Cells Product Family
1

Hi there
I am using a comparison between myCell.GetStyle() and myCell.GetDisplayStyle() to determine whether the condition for a conditional formatting of a cell is met. In our case, we have a cell that has a font color of say [A=0, R=242, G=242, B=242]. If we set the font color of the conditional formatting to say [A=0, R=0, G=0, B=0] everything works fine and
myCell.GetStyle().Equals(myCell.GetDisplayStyle())
returns false (as is expected) if the condition is met.

However if the font color of the conditional formatting is set to 'Automatic', myCell.GetDisplayStyle() returns [A=0, R=242, G=242, B=242] as well and the equality comparison returns true wether the condition is met or not, which is wrong. Is there a way around this? I.e. how would one detect a font color setting of 'Automatic'?

Thanks
Lukas


2

Hi Lukas,

Thank you for considering Aspose.

We have found your mentioned issue after an initial test. We will look into it and get back to you soon. Your issue has been registered in our internal issue tracking system with issue id CELLSNET-13511

Thank you & Best Regards,

3

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have added the following new methods as per your need:

  • Cell.GetConditionalStyle: Gets the conditional formatted style when the cell value fits the condition.
  • Cell.GetFormatConditions: Gets format conditions which apply to this cell.

Please try them and let us know if they fit your need

Thank You & Best Regards,

4

Hi there
Thanks very much, it is exactly what I need as I only need to decide whether the conditions are met or not. So I can check if GetConditionalStyle() returns a style or null.

However while testing I came across a bug which you may consider fixing. In the attached excel sheet look at cell B2. It has two conditions attached to it. If cell A2 is > 1 the font color is black, if A2 is > 2 the font color is red. The excel sheet processes this correctly. If you look at the attached C# file, line 65 should pick up a style with font color red as I set A2 to 3 in line 60. But the font color I get back is still black as it was in line 48 when A2 had a value of 2.

EDIT: Please also note that the conditional style retrieved has a vertical alignment of 'Center' although the base style has 'Bottom'. As a conditional style can not set this value they should never be different. GetDisplayStyle() returns 'Bottom' correctly. => GetConditionalStyle() does not pick up vertical alignment correctly.

EDIT2: Please also note that the styles returned by GetStyle(), GetDisplayStyle() and GetConditionalStyle() respectively return different ThemeColor values for the Fonts, which makes comparing the styles difficult. => GetDisplayStyle() does not pick up Font.ThemeColor correctly.

Thanks again for the quick response.
Lukas

5

Hi Lukas,

For your issues/queries:

  1. MS Excel does not support Alignment settings in the conditional style.

  2. We just simply copied the RGB color of the conditional style to the displayed style. We will support copying the theme color soon.

Please do not simply compare the conditional style and displayed style. If the condition is true, MS Excel just merges the modified properties of the conditional style and the cell’s base style to get the displayed style.

We will add a new method to help you get which property is modified. We have added the feature request to an existing issue ID: CELLSNET-13511.

Thank you.

6

Hi Amjad

Thanks for clarification. I agree and see now that comparing styles is not suitable. This still leaves the question open, how do I determine whether the condition(s) for a cell are met. With the new version I tried cell.GetConditionalStyle() == null to test whether conditions are met. But this seems not to always work. I try to supply an example later that day *).

As for my previous post, please note that the problem is not to determine which property has changed. The conditional style given back by GetConditionalStyle() is plain wrong (black instead of red font color).

Thanks for the support

Lukas

*) The example is now attached. The issue occurs when saving the workbook to a stream. I have cell that is conditionally formatted, depending on the value in a linked cell of a combobox. If I open the excel book as a resource it is fine. If I save the book to a stream and re-open it from this stream, the conditional style is not applied even if the condition is met.

7

Hi,

Thanks for providing us the template project.

We have already re-opened the issue, we will inform you when it is resolved.

Thank you.

8

Hi

Please try the attached version. We have fixed the issue of opening Xlsx files.

And, if you want to check whether the display font color is from conditional formatting, please try the following sample code:

Style conditional = cell.GetConditionalStyle();

    if (conditional != null)
    {
        if (conditional.IsModified(StyleModifyFlag.FontColor))
        {
            // the display font color is from conditional formatting
        }
    }

Thank you.

9

Hi,

Thanks a lot, the latest build does solve the majority of problems and it certainly solves all problems I'm concerned about at the moment. Just as a heads up, the latest build does not solve the problem with two conditional formats I mentioned earlier:

Lukasl:

However while testing I came across a bug which you may consider fixing. In the attached excel sheet look at cell B2. It has two conditions attached to it. If cell A2 is > 1 the font color is black, if A2 is > 2 the font color is red. The excel sheet processes this correctly. If you look at the attached C# file, line 65 should pick up a style with font color red as I set A2 to 3 in line 60. But the font color I get back is still black as it was in line 48 when A2 had a value of 2.

Sorry for putting several issues in one post.

Cheers

Lukas

10

Hi Lukas,

We have found the issue and will fix it soon.



Thank you.

11

Hi,

Please try the attached version. We have fixed your issue.

Thank you.

12

Thanks a lot - Problem solved.

13

The issues you have found earlier (filed as 13511) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.