How to add a condition on a databar

Aspose.Cells Product Family
1
Hi,

I want to add a databar to an area with a range from 0 to 1 and that is green when the value is beneath 1, and red if the value exceeds 1. I was trying to add two databars (one green and one red) and put a condition on them to make them (in)visible depending on the value of the cell. But I can't find a way to add these conditions. Is this possible or is there another way to achieve it?

CellArea ca = new CellArea();


...



int index = sheet.ConditionalFormattings.Add();
FormatConditions fcs = sheet.ConditionalFormattings[index];

fcs.AddArea(ca);


int idx = fcs.AddCondtion(FormatConditionType.DataBar);
FormatCondition cond = fcs[idx];

DataBar dataBar = cond.DataBar;
dataBar.Color = Color.Green;

dataBar.MinCfvo.Type = FormatConditionValueType.Number;
dataBar.MinCfvo.Value = 0;
dataBar.MaxCfvo.Type = FormatConditionValueType.Number;
dataBar.MaxCfvo.Value = 1;

dataBar.Formula ???
dataBar.Condition ???

Thanks!
2

Hi,

Thanks for your posting and using Aspose.Cells.

You can add Data Bar conditional formatting using Aspose.Cells. Please provide us your sample Excel file with your desired Data Bar conditional formatting. You can create one using Microsoft Excel. We will then provide you a sample code to create the same Data Bar conditional formatting using Aspose.Cells APIs.

Thanks for your cooperation.

3

Hi,

I added an excel file with the wanted behaviour. There are a red and green databar in the first column. The red one is only shown when the cellvalue exceeds 1.

I can’t make this file in Excel itself, I had to generate it.

Here is what I want to achieve with Aspose.Cells:

https://www.microsoft.com/en-us/microsoft-365/blog/2006/02/24/conditional-formatting-trick-1-multi-coloured-data-bars/

I hope this is also possible with Aspose.

4

Hi,

Thanks for your posting and using Aspose.Cells.

I have written the following sample code to implement this conditional formatting but it is not working properly. We will look into it further and provide you a working code.

C#
string filePath = @“F:\Shak-Data-RW\Downloads\DataBar+Test.xlsx”;

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

var o = worksheet.ConditionalFormattings;
var f = o[0][1];

int idx = worksheet.ConditionalFormattings.Add();
FormatConditionCollection fcc = worksheet.ConditionalFormattings[idx];

CellArea area = CellArea.CreateCellArea(“B1”, “B1048576”);
fcc.AddArea(area);

idx = fcc.AddCondition(FormatConditionType.DataBar);
FormatCondition fc = fcc[idx];

fc.DataBar.BarFillType = DataBarFillType.Gradient;
fc.DataBar.Color = Color.Green;
fc.DataBar.MaxCfvo.Type = FormatConditionValueType.Number;
fc.DataBar.MaxCfvo.IsGTE = true;
fc.DataBar.MaxCfvo.Value = 1;

fc.DataBar.MinCfvo.Type = FormatConditionValueType.Number;
fc.DataBar.MaxCfvo.IsGTE = true;
fc.DataBar.MinCfvo.Value = 0;

fc.DataBar.AxisPosition = DataBarAxisPosition.None;

var p = fc.DataBar.NegativeBarFormat;

//-----------------------------------
idx = worksheet.ConditionalFormattings.Add();
fcc = worksheet.ConditionalFormattings[idx];

area = CellArea.CreateCellArea(“B1”, “B1048576”);
fcc.AddArea(area);

idx = fcc.AddCondition(FormatConditionType.DataBar);
fc = fcc[idx];

fc.DataBar.BarFillType = DataBarFillType.Gradient;
fc.DataBar.Color = Color.Red;
fc.DataBar.MaxCfvo.Type = FormatConditionValueType.Number;
fc.DataBar.MaxCfvo.IsGTE = true;
fc.DataBar.MaxCfvo.Value = 1;

fc.DataBar.MinCfvo.Type = FormatConditionValueType.Min;
fc.DataBar.MaxCfvo.IsGTE = true;

fc.DataBar.AxisPosition = DataBarAxisPosition.None;



workbook.Save(“output.xlsx”);

5

Hi,


I have tried the code, and it does not work indeed. I’m afraid that there isn’t going to be a way to add a formula to the databars. (A formula to decide when (not) to show the green and the red databar). But if there is, I would be happy to here it from you.

I have also tried in Excel to add a macro that adds the databars with the formulas for me. This works, but I think that I can’t execute the macro with Aspose? So that does not help neither.
6

Hi,

Thanks for your posting and using Aspose.Cells.

Please let us know the manual steps taken by you in MS-Excel to create the Conditional Formatting applied in the DataBar Test.xlsx file.

I have tried to apply the Green and Red conditional formatting in new excel file from scratch but it always makes the number green and it never makes it red even though both conditional formatting look exactly same as yours.

I have attached the source excel file created by me in MS-Excel 2010 and screenshot for your reference.

Once, we will know the exact steps to achieve your conditional formatting, it will help us replicate the same thing with Aspose.Cells.