DataBar negative values and bar fill type problem

Aspose.Cells Product Family
1

This may be related to posts CELLSNET-41803 and CELLSNET-42158 regarding negative databar values not exporting to PDF, but I am not even getting that far. Trying to create an Excel file from scratch with databars, the axis is always left-justified, making the lowest negative number “zero”. On top of that, the barfilltype is always gradient, even when set to solid. Attached is an image where the first column is the data, the second is the Aspose.Cells generated databars , and the third is done by hand in LibreOffice with the desired bar style. I am using Aspose.Cells 7.6.1.0 evaluation.


Here is the code I used:
int idx = fcs.AddCondition(FormatConditionType.DataBar);
FormatCondition fc = fcs[idx];
fc.DataBar.Color = Color.Black;
fc.DataBar.BarFillType = DataBarFillType.DataBarFillSolid;
fc.DataBar.ShowValue = false;
fc.DataBar.AxisPosition = DataBarAxisPosition.DataBarAxisMidpoint;
fc.DataBar.NegativeBarFormat.Color = Color.Red;
fc.DataBar.NegativeBarFormat.ColorType = DataBarNegativeColorType.DataBarColor;

2

Hi Sam,

Thank you for using Aspose products.

I have evaluated your presented scenario with latest build of Aspose.Cells for .NET 7.7.0, and I am able to achieve your desired results with below provide code snippet. Please have a look at the attached snapshot, you will notice that the axis is set in the middle of the cell and the BarFillType is set to solid, as per your requirement.

C#


Workbook book = new Workbook(myDir + “Databar.xlsx”);
Worksheet sheet = book.Worksheets[0];

FormatConditionCollection conds = sheet.ConditionalFormattings[sheet.ConditionalFormattings.Add()];
int idx = conds.AddCondition(FormatConditionType.DataBar);
FormatCondition cond = conds[idx];
cond.DataBar.BarFillType = DataBarFillType.DataBarFillSolid;
cond.DataBar.BarBorder.Type = DataBarBorderType.DataBarBorderSolid;
cond.DataBar.BarBorder.Color = Color.Plum;
cond.DataBar.NegativeBarFormat.ColorType = DataBarNegativeColorType.DataBarColor;
cond.DataBar.NegativeBarFormat.Color = Color.Red;
cond.DataBar.NegativeBarFormat.BorderColorType = DataBarNegativeColorType.DataBarColor;
cond.DataBar.NegativeBarFormat.BorderColor = Color.Yellow;
cond.DataBar.AxisColor = Color.Blue;
cond.DataBar.AxisPosition = DataBarAxisPosition.DataBarAxisMidpoint;

CellArea ca = new CellArea();
ca.StartRow = 2;
ca.EndRow = 12;
ca.StartColumn = 1;
ca.EndColumn = 1;

conds.AddArea(ca);

book.Save(myDir + “output.xlsx”);

3

With the latest version, and with your example, I was able to get the data bars exactly how I wanted. And with 7.7.0.2 mentioned in CELLSNET-42158, I was able to get the pdf output to look correct as well. Thank you!

4

Hi Sam,

Thank you for your confirmation. Good to know that you are up and running again. Please feel free to write back in case you need any further assistance.