Aspose.Cells.FormatConditionType.ColorScale: 2-Color Scale format type?

Hi Aspose -


I’m attempting to build an Excel worksheet that has conditional formatting (specifically). My condition code looks like this so far:

var conditionIndex2 = fcs.AddCondition(Aspose.Cells.FormatConditionType.ColorScale, Cells.OperatorType.Between, “0”, “.9999”);
fc = fcs[conditionIndex2];
fc.ColorScale.MinColor = System.Drawing.Color.White;
fc.ColorScale.MinCfvo.Type = Cells.FormatConditionValueType.AutomaticMin;
fc.ColorScale.MaxColor = System.Drawing.Color.Red;
fc.ColorScale.MaxCfvo.Type = Cells.FormatConditionValueType.AutomaticMax;

If you check the attachment, the first 2 images in the PDF shows what Excel interprets the code as. It defaults to “3-Color Scale”. I only want to use the “2-Color Scale” as indicated in the Format Style select box of the 3rd image.

How do I go about accomplishing this? I see that the ColorScale object has a MidCfvo but I can’t figure out how to tell it to ignore.

Thanks!

Hi,

Thanks for your posting and using Aspose.Cells.

Please see the following sample code. It will help you accomplish your task. I have also attached the output excel file generated by this code for your reference.

C#

Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];

ws.Cells[“A1”].PutValue(1);
ws.Cells[“A2”].PutValue(2);
ws.Cells[“A3”].PutValue(3);
ws.Cells[“A4”].PutValue(4);
ws.Cells[“A5”].PutValue(5);

int idx = ws.ConditionalFormattings.Add();

FormatConditionCollection fcc = ws.ConditionalFormattings[idx];

CellArea ca = CellArea.CreateCellArea(“A1”, “A5”);
fcc.AddArea(ca);

idx = fcc.AddCondition(FormatConditionType.ColorScale);

FormatCondition fc = fcc[idx];
fc.ColorScale.MaxCfvo.IsGTE = true;
fc.ColorScale.MaxCfvo.Type = FormatConditionValueType.Max;
fc.ColorScale.MaxCfvo.Value = null;
fc.ColorScale.MaxColor = Color.Yellow;

fc.ColorScale.MidCfvo.IsGTE = true;
fc.ColorScale.MidCfvo.Type = FormatConditionValueType.Percentile;
fc.ColorScale.MidCfvo.Value = 50;
fc.ColorScale.MidColor = Color.Green;

fc.ColorScale.MinCfvo.IsGTE = true;
fc.ColorScale.MinCfvo.Type = FormatConditionValueType.Min;
fc.ColorScale.MinCfvo.Value = null;
fc.ColorScale.MinColor = Color.Red;

wb.Save(“output.xlsx”);

Hi,


Thanks for getting back to me quickly. The code you provided doesn’t do what I’m looking for.

See the Result.png. That is what the code you provided generates. It is still a 3-Color scale. If you look at Desired.png, you’ll see I’m looking to have it generate a 2-Color scale.

Thanks!

Hi,

Thanks for your clarification and using Aspose.Cells.

I found there is a bug in Aspose.Cells because of which it is unable to create 2-Color Scale conditional formatting. I will look into it further and log this issue in our database for a fix.

Thank you. Much appreciated.

Hi,

Thanks for using Aspose.Cells.

We have logged this issue in our database for investigation. We will look into it and see why Aspose.Cells is unable to create 2-Color Scale conditional formatting and fix this issue and provide you a sample code to create it. Once, there is some fix or other news for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-44168 - Unable to create 2-Color Scale conditional formatting

Hi,

Thanks for using Aspose.Cells.

We will support set ColorScale.MidCfvo property, then we can create 2-color scale with ColorScale.MidCfvo = null.

Hopefully fix will be available for you in next 2-5 days and once it is available, we will update you by posting it in this thread.

Hi,

Please try our latest version/fix: Aspose.Cells for .NET v8.6.3.4

We have fixed your issue “CELLSNET-44168” now.

We have added a new property i.e., ColorScale.Is3ColorScale to
check whether the conditional format is 3 color scale.<o:p></o:p>

See the following sample code:

e.g.

Sample code:

Workbook workbook = new Workbook();

Worksheet ws = workbook.Worksheets[0];

ws.Cells["A1"].PutValue(1);

ws.Cells["A2"].PutValue(2);

ws.Cells["A3"].PutValue(3);

ws.Cells["A4"].PutValue(4);

ws.Cells["A5"].PutValue(5);

int idx = ws.ConditionalFormattings.Add();

FormatConditionCollection fcc = ws.ConditionalFormattings[idx];

CellArea ca = CellArea.CreateCellArea("A1", "A5"); fcc.AddArea(ca);

idx = fcc.AddCondition(FormatConditionType.ColorScale);

FormatCondition fc = fcc[idx];

fc.ColorScale.Is3ColorScale = false;

fc.ColorScale.MaxCfvo.IsGTE = true;

fc.ColorScale.MaxCfvo.Type = FormatConditionValueType.Max;

fc.ColorScale.MaxCfvo.Value = null;

fc.ColorScale.MaxColor = Color.Yellow;

fc.ColorScale.MinCfvo.IsGTE = true;

fc.ColorScale.MinCfvo.Type = FormatConditionValueType.Min;

fc.ColorScale.MinCfvo.Value = null;

fc.ColorScale.MinColor = Color.Red;

Let us know your feedback.

Thank you.

Worked like a charm! Thanks so much for the quick response and fix.

Hi,


Good to know that your issue is sorted out by the new fix using new API. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.

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


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