ColorScale conditional formatting

Aspose.Cells Product Family
1

Hello:

Is there a way to compute the color chosen by Excel when a ColorScale conditional formatting is used?

The ColorScale object doesn’t contain the result of the conditional formatting evaluation.

To reproduce run this function against the attached spreadsheet. Put a breakpoint on the closing curly bracket and inspect the ColorScale object.

public static void TestConditionalFormatting5()
{
Workbook workbook = new Workbook(@“C:\temp\spreadsheets\test_conditional_formatting4.xlsx”);

Worksheet worksheet = workbook.Worksheets[0];
Cell a1 = worksheet.Cells[“A1”];

ConditionalFormattingResult cfr1 = a1.GetConditionalFormattingResult();

ColorScale cs = cfr1.ConditionalFormattingColorScale;

}

Thanks

2

Hi Costa,

Thanks for your posting and using Aspose.Cells for .NET.

We have looked into this issue and it seems to be a New Feature. We have logged a New Feature Request for this issue in our database. We will look into it how to compute the color chosen by MS-Excel when the ColorScale conditional formatting is used. Once, this feature is available or we have some other update for you, we will let you know asap.

We have also attached the sample Excel using ColorScale feature for a reference.

This issue has been logged as CELLSNET-42109.

3

Hi Costa,


I think you may try some attributes e.g ColorScale.MinColor, ColorScale.MidColor, ColorScale.MaxColor etc. attributes to obtain the colors set to the cells for the conditional formatting.
See the sample code below for your needs:

Sample code:

Workbook workbook = new Workbook(@“e:\test2\test_conditional_formatting4.xlsx”);

Worksheet worksheet = workbook.Worksheets[0];
Cell a1 = worksheet.Cells[“A1”];

ConditionalFormattingResult cfr1 = a1.GetConditionalFormattingResult();

ColorScale cs = cfr1.ConditionalFormattingColorScale;

//ColorScale object cs contains all color’s attribute that you may try. e.g
Console.WriteLine(cs.MinColor);
Console.WriteLine(cs.MidColor);
Console.WriteLine(cs.MaxColor);

Let us know if you need further help or have other requirements.

Thank you.
4

Actually, this is not what I asked for. I asked for a way to get the color from the scale chosen based on the value of the cell. The values that you printed in your source code are simply the attributes of the color scale used in the evaluation.

I found some code for you to see what I actually need:
http://25newthings.azurewebsites.net/calculating-color-values-for-visualization-c/

I think I might use these functions until you add the feature.


5

Hi,


Thanks for providing us further details and sharing the URL for the topic for your needs.

I have logged it against your existing issue “CELLSNET-42109”. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
6

Hello:

I also want to add that GetDisplayStyle should probably report the background color of the cell as it is computed based on the cell formatting.

I attached another sample that you can try:

private static void TestColorScales()
{
Workbook workbook = new Workbook(@“C:\temp\spreadsheets\test_color_scales.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Cell cell = worksheet.Cells[“A1”];

ConditionalFormattingResult conditionalFormattingResult = cell.GetConditionalFormattingResult();
Style style = cell.GetDisplayStyle();

Console.WriteLine(GetCellBackgroundColor3(style));

}

public static Color GetCellBackgroundColor3(Style displayStyle)
{
// Background color
Color cellBackgroundColor = Color.Empty;

//Read the fill color
if (displayStyle.Pattern == BackgroundType.Solid)
{
cellBackgroundColor = displayStyle.ForegroundColor;
}
return cellBackgroundColor;
}

7

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

You are right. Cell.DisplayStyle() should return the background color of the cell based on the conditional formatting but it returns empty color. We have logged your comment in our database. We will investigate this issue and update you. Once, we will have some update or fix for you, we will let you know asap.

8

Hi,

Please download and try our latest version/ fix: Aspose.Cells for .NET v7.6.0.7

Your issue should be fixed in it. See the sample code below.
e.g.

Sample code:

ConditionalFormattingResult cfr1 = cell.GetConditionalFormattingResult();

Color c = cfr1.ColorScaleResult;

Let us know your feedback.

Thank you.

9

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.