Conditional formatting and DataBars - some questions

Aspose.Cells Product Family
1

Hello:

To reproduce these issues run the following function against the attached spreadsheet. Before doing that, place a breakpoint on the closing curly bracket so you can inspect dataBar1, dataBar2 and dataBar3 in the Locals debug window.

Workbook workbook = new Workbook(@“C:\temp\spreadsheets\test_conditional_formatting3.xlsx”);

Worksheet worksheet = workbook.Worksheets[0];

Cell a1 = worksheet.Cells[“A1”];
Cell a2 = worksheet.Cells[“A2”];
Cell a3 = worksheet.Cells[“A3”];

DataBar dataBar1 = a1.GetConditionalFormattingResult()
.ConditionalFormattingDataBar;

DataBar dataBar2 = a1.GetConditionalFormattingResult()
.ConditionalFormattingDataBar;

DataBar dataBar3 = a1.GetConditionalFormattingResult()
.ConditionalFormattingDataBar;

Questions:

1. What do the DataBar MinLength and MaxLength properties do? They seem to be invariably set to 0 respective 100?

2. For all DataBar object above MinCfvo.Value and MaxCfvo.Value are set to null. Is it normal? Is there a way to find the maximum and minimum using the the conditional formatting related objects api?

3. Why some colors have the alpha member set 0 (which means fully transparent): DataBar.Color, DataBar.BarBorder.Color? When the axis color is set the DataBar.AxisColor has the alpha property set to 255. So, it is a bit inconsistent.

4. I didn’t include a formula type Min or Max, but based on my tests, for instance, when a Min is set via a formula, then MinCfvo.Value contains that formula instead of the real value. Is there way to get the real Min and Max values, for all types Min and Max (the Number type works)?

Thanks


2

Hi Costas,

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

1 - Yes, you are right. MaxLength and MinLength does not change, so it seems they are constant values. We will look into it and remove these properties.

2 - MinCfvo.Value and MaxCfvo.Value gets changed when you apply Number or Percent or Formula etc. You can find the type of the MaxCfvo from MaxCfvo.Type variable and its value using the MaxCfvo.Value property which will be dependent on its type.

3 - Alpha values are invalid values. MS-Excel follows the RGB model not ARGB color model. So you can discard the value of alpha and just take the RGB values only.

4 - MS-Excel will also show you the formula instead of value. Aspose.Cells behave same as MS-Excel. You cannot retrieve the numeric value instead of formula.

3

Is there a way to provide a feature that returns the computed min and max values used in generating the databars? Excel uses the min & max values to draw the databars.

4

Hi Costa,

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

Please provide us your sample Excel file illustrating your issue and screenshot highlighting it with red circles. It will help us look into your issue more precisely and we will be able to help you asap.

5

To understand my request you need to understand how databars work. It is not about sending you code. You can also use the spreadsheet I attached in the other post as a sample.

I will try to explain it. When a databar is rendered it needs among other things three values, a minimum (min), a maximum (max) and a value (v). Supposedly v >= min and v <= max, but of course, excel doesn’t have a problem if v < min or v > max. Let’s assume for now v >= min and v <= max. v is actually the value in the cell. You specify min and max in the Edit Rule dialog. min and max can be specified as numeric values or percentages or percentile or as the lowest/highest value in a range or formulas.

So, what I am saying is that when Excel renders the databar it needs to compute the three values, v, min and max. v is the easiest because it is the value in the cell. min and max are trickier to compute. If they are simply numbers then that’s easy, but if they are formulas they need to be evaluated. If they are percentages or lowest/highest value and ranges are specified then the min & max values depend on the values in the range. Again it becomes more complicated.

So, my question to you is this: for a given cell with a databar is there a way to find the computed min & max values used to display the databar?


I also included this link about conditional formatting:

HowDoesitWork</a>: http://msdn.microsoft.com/en-us/library/bb229709%28office.12%29.aspx#OfficeTalk02012007_HowDoesItWork

I hope you find it useful.

Thanks

6

Hi Costa,

Thanks for your posting and using Aspose.Cells.

If the MaxCfvo.Type is Formula, then you can gets its formula value and calculate it using the Worksheet.CalculateFormula(string) method. You can do the same for calculating the minimum value. Please see the following code and the source file attached with this post.

The maximum value of conditional formatting databar is formula which is =Sum(D1:D2) and its calculated value is 52. You can get the calculated value using this code.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


Cell a1 = worksheet.Cells[“A1”];


ConditionalFormattingResult cfr = a1.GetConditionalFormattingResult();


if (cfr.ConditionalFormattingDataBar.MaxCfvo.Type == FormatConditionValueType.Formula)

{

//Calculate the max value from the formula

var maxValue = worksheet.CalculateFormula(cfr.ConditionalFormattingDataBar.MaxCfvo.Value.ToString());

}

7

Hello:

Thank you for this. Yes, if the Min/Max type is Number, Percentage or Formula I can compute the Min and Max values. But what about the cases when the types are one of the following: Lowest/Highest value, Percentile or Automatic. In those cases Excel looks at a range.

So, there are a lot of permutations to be taken into account, not only the code above.

It would be nice if the ConditionalFormattingDataBar had members to store the results of the evaluation.

Thanks

8

Hi Costa,

Thanks for your feedback and using Aspose.Cells.

Yes, MS-Excel looks at range to calculate the Minimum and Maximum values for Databars conditional formatting. We will look into it how these values can be calculated using Aspose.Cells. It is a New Feature, so we have added a new feature request in our database.We will investigate and implement it if possible and once it is available, we will let you know asap.

This issue has been logged as CELLSNET-42116.

9

Hi,

Thanks for using Aspose.Cells.

Please download and try the new fix: Aspose.Cells for .NET (Latest Version) . Your issue should be fixed in it.

C#


ConditionalFormattingResult cfr1 = a1.GetConditionalFormattingResult();

DataBar dataBar = cfr1.ConditionalFormattingDataBar;

Object maxvalue= dataBar.MaxCfvo.Value;

Object minvalue= dataBar.MinCfvo.Value;




10

The changes that you guys made do not work properly.

Please run this program against the attached spreadsheet:

private static void TestAsposeDataBars()
{
Workbook workbook = new Workbook(@“C:\temp\spreadsheets\test_data_bars.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];

string[] cells = new string[] { “A1”, “A2”, “A3”, “A4”, “A5”, “A6”, “A7”, “A8”, “A9”, “A15”, “A16”, “A17”, “A18”, “A19”, “A20”, “A21”, “A22”, “A23”, “D10”, “D11”, “D12”, “D13”, “D14”, “H2”, “H3”, “H4”, “H5”, “H6”, “G10”, “G11”, “G12”, “G13” };

foreach (string cellName in cells)
{
Cell cell = worksheet.Cells[cellName];

ConditionalFormattingResult conditionalFormattingResult = cell.GetConditionalFormattingResult();

DataBar dataBar = conditionalFormattingResult.ConditionalFormattingDataBar;

Console.WriteLine(“Cell: {0}, Min: {1}, Max: {2}”, cellName, dataBar.MinCfvo.Value, dataBar.MaxCfvo.Value);
}
}

This program produces the following output:

Cell: A1, Min: -5, Max: 3
Cell: A2, Min: -5, Max: 3
Cell: A3, Min: -5, Max: 3
Cell: A4, Min: -5, Max: 3
Cell: A5, Min: -5, Max: 3
Cell: A6, Min: -5, Max: 3
Cell: A7, Min: -5, Max: 3
Cell: A8, Min: -5, Max: 3
Cell: A9, Min: -5, Max: 3
Cell: A15, Min: -5, Max: 3
Cell: A16, Min: -5, Max: 3
Cell: A17, Min: -5, Max: 3
Cell: A18, Min: -5, Max: 3
Cell: A19, Min: -5, Max: 3
Cell: A20, Min: -5, Max: 3
Cell: A21, Min: -5, Max: 3
Cell: A22, Min: -5, Max: 3
Cell: A23, Min: -5, Max: 3
Cell: D10, Min: -5, Max: 3
Cell: D11, Min: -5, Max: 3
Cell: D12, Min: -5, Max: 3
Cell: D13, Min: -5, Max: 3
Cell: D14, Min: -5, Max: 3
Cell: H2, Min: -5, Max: 3
Cell: H3, Min: -5, Max: 3
Cell: H4, Min: -5, Max: 3
Cell: H5, Min: -5, Max: 3
Cell: H6, Min: -5, Max: 3
Cell: G10, Min: -5, Max: 3
Cell: G11, Min: -5, Max: 3
Cell: G12, Min: -5, Max: 3
Cell: G13, Min: -5, Max: 3

Some of these are incorrect:
For A1-A9 min is -6.
For D10-D14 min is -5 and max is -1.
For H2-H6 min is -150 and max is 100
For G10-G13 min = max = -1

How did you test your changes?

Thanks

11

Hi Costa,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue. The min and max values returned are not correct. We have reopened this issue for investigation. We will look into it and resolve this issue. Once, there is some fix or update for you, we will let you know asap.

12

Hi,

Please download and try our latest version/fix: Aspose.Cells for .NET (Latest Version)


We have fixed the issue now.

Let us know your feedback.

Thank you.

13

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

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