Conditional formatting questions

Aspose.Cells Product Family
1

Hello:

Questions:

1. Is it possible to change the ConditionalFormattingResult class to include the FormatCondition objects used in the evaluation? Currently ConditionalFormattingResult contains ConditionalFormattingDataBar, ConditionalFormattingColorScale and ConditionalFormattingIcon. I want to be able to trace back the FormatCondition object each of these 3 belongs to and actually the FormatConditionCollection the FormatCondition object belongs to.

2. For a given cell, I can get the FormatCondition objects attached to the cell using cell.GetFormatConditions(). How can I get the FormatConditionCollection object each of these FormatCondition objects belongs to without scanning downwards from Worksheet ConditionalFormattings -> FormatConditionCollection -> FormatCondition?

3. Is Cell GetFormatConditions() efficient or does it scan all the ConditionalFormattings?

I attached a spreadsheet that contains some databars, and some code I played with, however, I don’t have anything concrete that you can try.

private static void TestDataBarConditionalFormatting()
{
Workbook workbook = new Workbook(@“C:\temp\spreadsheets\TestDataBars.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Cell cell = worksheet.Cells[“A1”];

ConditionalFormattingResult conditionalFormattingResult = cell.GetConditionalFormattingResult();

DataBar dataBar = conditionalFormattingResult.ConditionalFormattingDataBar;

FormatConditionCollection formatConditions = cell.GetFormatConditions();


for (int i = 0; i < formatConditions.Count; i++)
{
FormatCondition fc = formatConditions[i];


Console.Out.WriteLine(“fc.Type = {0}”, fc.Type);

}

ConditionalFormattingCollection conditionalFormattingCollection = worksheet.ConditionalFormattings;

for (int i = 0; i < conditionalFormattingCollection.Count; i++)
{
FormatConditionCollection formatConditionCollection = conditionalFormattingCollection[i];

for (int j = 0; j < formatConditionCollection.RangeCount; j++)
{
CellArea cellArea = formatConditionCollection.GetCellArea(j);
Console.WriteLine(worksheet.CalculateFormula(String.Format("=MIN({0}:{1})", worksheet.Cells[cellArea.StartRow, cellArea.StartColumn].Name, worksheet.Cells[cellArea.EndRow, cellArea.EndColumn].Name)));

Console.Out.WriteLine(cellArea);
}

for (int j = 0; j < formatConditionCollection.Count; j++)
{
FormatCondition formatCondition = formatConditionCollection[j];
Console.Out.WriteLine(“formatCondition.Type = {0}”, formatCondition.Type);

}
}
}

Thanks

2

Hi Costa,

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

We will look into your issue and get back to you asap.

3

Hi Costa,

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

1 - You can get the applied FormatCondition objects on any cell using Cell.GetFormatConditions(). It should suffice your needs. Could you please elaborate where the FormatCondition is applicable in ConditionalFormattingResult using some source file?

2 - I have looked into this and found Cell.GetFormatConditions() already returns the FormatConditionCollection object. So you do not need to scan the Worksheet.ConditionalFormattings.

3 - Cell.GetFormatConditions() returns all the FormatCondition objects that are applied to cell. It does not need to scan Worksheet.ConditionalFormattings collection, so it is efficient.

4
  1. If conditional formatting was applied to a cell, I want to be able to know which FormatCondition objects were applied to a cell. ConditionalFormattingResult doesn’t return that. In my mind its properties should have been FormatCondition objects not DataBar, ColorScale or IconsSet objects. Ultimately the aspose api should give me back the FormatCondition that represents the result of the evaluation that Excel does.

    I am trying, for a given cell that has been applied DataBars, to figure out the min & max values that were computed to draw the bar. And I cannot determine them as is right now from the Aspose api, unless you know a way. So basically I am looking for a function:

    GetMinMaxConditionalFormattingValues(Cell cell, FormatConditionType formatConditionType, out Object min, out Object max)

    that would compute the min & max values used in computing and drawing the conditional formatting. formatConditionType is one of the following: FormatConditionType.DataBar, FormatConditionType.ColorScale.


    3. Are you sure 100% that Cell.GetFormatConditions doesn’t scan the worksheet ConditionalFormattings? Did you read the source code of the method?

    Excel stores the conditional formatting as follows, so to determine the conditional formattings that apply to a cell you need to scan the ConditionalFormattings.










    x14:id{FE82C7A7-0079-49FB-9FAC-B53AFFF56BAE}</x14:id>








5

Hi Costa,

Thanks for your further information and using Aspose.Cells.

We have logged your requirements in our database for investigation. We will analyze them and update you. If it is possible, we will implement your requested feature and let you know the internal mechanism of Cell.GetFormatConditions() if it is dependent on worksheet conditional formattings or not. Once, there is some fix or update for you, we will let you know asap.

This issue has been logged as CELLSNET-42131.

6

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;


7

Hello:

Why is the name of the zip file Aspose.Cells7.6.0.7+For+.Net2.zip, particularly why the suffix +For+.Net2.zip? Before this, you didn’t use to have this suffix.

I am using .Net 4.0. Is the dll the right dll for .Net 4.0?

Thanks

8
Hi,

Well, the provided version/fix is compiled on .NET 2.0 and targeted as any CPU (32bit/64bit) and it works fine on .NET framework versions greater than or equal to i.e. 2.0, 3.x, 4.0, 4.5 etc. So, you may use it on .NET 4.0 without any problem.


Thank you.