Conditional formatting databar generated for zero value

Aspose.Cells Product Family
1
Having a problem generating a set of data bars. A 'zero value' is generating a small positive bar to the right of the axis. I don't see this behavior natively in Excel. Is this a bug or am I doing something wrong?

Code is below, the resulting excel sheet is attached.

Using Aspose Cells 8.6.2, Visual Studio 2013, Excel 2013.

Thanks,

Jim


Dim book As New Aspose.Cells.Workbook
Dim sheet = book.Worksheets(0)

'Sample values.
Dim values As New List(Of Integer) From {-1, 0, 1, 2, 3}

'Populate the column with the values.
For i As Integer = 0 To values.Count - 1
sheet.Cells(i, 0).PutValue(values(i))
Next

'Set up data bar conditional formatting.
Dim index As Integer = sheet.ConditionalFormattings.Add()
Dim conds As FormatConditionCollection = sheet.ConditionalFormattings(index)

'Set the conditional format range.
Dim ca As CellArea = New CellArea()
ca.StartRow = 0
ca.EndRow = values.Count - 1
ca.StartColumn = 0
ca.EndColumn = 0
conds.AddArea(ca)

Dim idx As Integer = conds.AddCondition(FormatConditionType.DataBar)
Dim cond As FormatCondition = conds(idx)
cond.DataBar.BarFillType = DataBarFillType.Solid
cond.DataBar.AxisPosition = DataBarAxisPosition.Midpoint

'Make the column wider.
sheet.Cells.SetColumnWidth(0, 40)

'Save the results.
book.Save("c:\temp\test.xlsx", SaveFormat.Xlsx)



2

Hi,


Thanks for providing us template file and sample code.

I have evaluated your scenario/ case a bit. Well, this is Ms Excel’s behavior and nothing to do with Aspose.Cells as MS Excel shows a little data bar on right for “zero” value. You may confirm this behavior in MS Excel(2010/2013) as I checked. I even tried to set some further settings (e.g set minimum value and its type for DataBar’s condition) by adding the following lines to your code but to no avail:
e.g
Sample code:

…
cond.DataBar.MinCfvo.Type = FormatConditionValueType.Number;
cond.DataBar.MinCfvo.Value = -1;
…

If you still think this is an issue with Aspose.Cells, kindly do provide a sample file which contains your desired Data Bar conditional formatting set, we will check it soon.

Thank you.
3
I don't see this behavior in Excel. Try this (I'm using 2013).

(1) Open a new worksheet.
(2) Going down column A, enter these numbers: -1, 0, 1, 2, 3
(3) Set the width of column A to 40
(4) Highlight cells A1:A5
(5) On the Home tab, select Conditional formatting > Data Bars > Solid Fill

Attached is a screenshot showing the result. The zero value (cell A2) does not show a bar.

Thanks,

Jim
4

Hi,


Thanks for the screenshot and steps involved.

I have evaluated your issue in details. I found that databar is always generated for zero value in conditional formattings using the below sample code (I even tried different combinations of values for MaxCfvo/MinCfvo.Type and MaxCfvo/MinCfvo.Value etc.). I also observed that Aspose.Cells follows MS Excel 2007 standards as when we manually follow the steps as mentioned below to accomplish the task produces same output (regarding conditional formattings where databar is always generated for 0 value) as per Aspose.Cells.
(1) Open a new worksheet.
(2) Going down column A, enter these numbers: -1, 0, 1, 2, 3
(3) Set the width of column A to 40
(4) Highlight cells A1:A5
(5) On the Home tab, select Conditional formatting > Data Bars > Solid Fill

But when I performed the above mentioned steps in MS Excel 2010 and 2013 manually, it does produce the same output as per your attached screenshot here (https://forum.aspose.com/t/35729)
e.g
Sample code:
[VB.NET]
Dim book As New Aspose.Cells.Workbook
Dim sheet = book.Worksheets(0)

'Sample values.
Dim values As New List(Of Integer) From {-1, 0, 1, 2, 3}

'Populate the column with the values.
For i As Integer = 0 To values.Count - 1
sheet.Cells(i, 0).PutValue(values(i))
Next

'Set up data bar conditional formatting.
Dim index As Integer = sheet.ConditionalFormattings.Add()
Dim conds As FormatConditionCollection = sheet.ConditionalFormattings(index)

'Set the conditional format range.
Dim ca As CellArea = New CellArea()
ca.StartRow = 0
ca.EndRow = values.Count - 1
ca.StartColumn = 0
ca.EndColumn = 0
conds.AddArea(ca)

Dim idx As Integer = conds.AddCondition(FormatConditionType.DataBar)
Dim cond As FormatCondition = conds(idx)
cond.DataBar.BarFillType = DataBarFillType.Solid
cond.DataBar.AxisPosition = DataBarAxisPosition.Midpoint

'Make the column wider.
sheet.Cells.SetColumnWidth(0, 40)

'Save the results.
book.Save(“c:\temp\test.xlsx”, SaveFormat.Xlsx)

I have logged an investigation ticket with an id “CELLSNET-44233” for your issue. We will check if we could provide you some workaround or enhanced APIs to accomplish the task for your requirements.

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

Thank you.

5

Yes, I also see what you see. If I take the Excel file that was generated using aspose Cells, and open it in Excel and manually try to change the conditional formatting (minimum value, axis position, etc.) the short zero-value bar remains. I can’t make it go away.


But as soon as I delete the aspose-generated conditional formatting, and in Excel simply re-format the data bar column, the problem goes away.

I will be curious what your team finds.

Jim
6

Hi Jim,


Thank you for sharing additional details. We have logged your comments to the aforementioned ticket, and have requested the concerned member of the product team to schedule the ticket for thorough investigation. As soon as we have completed preliminary analysis, we will share the results here for your kind reference.
7

Hi,


We have evaluated your issue further. Please add the following lines of code to your code segment, it would work fine:
e.g
Sample code:

cond.DataBar.MinLength = 0
cond.DataBar.MaxLength = 100

Let us know if you still have any issue.

Thank you.
8

Thanks Amjad, your solution worked perfectly.


It also fixes a related problem we had where the bar lengths were not proportional to their values. For example, a bar with a value of 20 was not rendered with twice the length of a bar of value of 10. Now they render with exactly the right length.

Thanks again,

Jim
9

Hi,


Thanks for your feedback.

Good to know that it figures out your issue now. 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.