Hello again:
If you run the program below against the attached spreadsheet you’ll discover that the icons returned in the ConditionalFormattingIcon object are incorrect.
private static void TestIconSetConditionalFormatting()
{
Workbook workbook = new Workbook(@“C:\temp\spreadsheets\TestIconSetConditionalFormatting.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
workbook.CalculateFormula(true);
List cells = new List { “A2”, “B3”, “C3”, “D3”, “E3”, “F3”, “G3”, “H3”, “I3”, “J3”, “K3”, “L3”, “M3”};
foreach (string cellName in cells)
{
Cell cell = worksheet.Cells[cellName];
ConditionalFormattingResult conditionalFormattingResult = cell.GetConditionalFormattingResult();
ConditionalFormattingIcon icon = conditionalFormattingResult.ConditionalFormattingIcon;
Console.WriteLine(“CellName: {0}, Icon Index={1}, IconType={2}”, cellName, icon.Index, icon.Type);
}
}
The result is:
CellName: A2, Icon Index=2, IconType=TrafficLights32
CellName: B3, Icon Index=2, IconType=TrafficLights32
CellName: C3, Icon Index=1, IconType=TrafficLights32
CellName: D3, Icon Index=1, IconType=TrafficLights32
CellName: E3, Icon Index=1, IconType=TrafficLights32
CellName: F3, Icon Index=0, IconType=Symbols32
CellName: G3, Icon Index=2, IconType=TrafficLights32
CellName: H3, Icon Index=2, IconType=TrafficLights32
CellName: I3, Icon Index=2, IconType=TrafficLights32
CellName: J3, Icon Index=2, IconType=TrafficLights32
CellName: K3, Icon Index=2, IconType=TrafficLights32
CellName: L3, Icon Index=2, IconType=TrafficLights32
CellName: M3, Icon Index=1, IconType=TrafficLights32
If you look at Excel it displays exclamation marks (see the screenshot of the spreadsheet). The indexes are correct but some of the IconType values are incorrect.
The problem occurs when the iconset is custom and one of the icons is not from the same set as the others. Please see the screenshot of the Edit Rules dialog.
Can you please fix this?
Thanks
Just an update:
If you run the following code:
private static void TestIconSetConditionalFormatting2()
{
Workbook workbook = new Workbook(@“C:\temp\spreadsheets\TestIconSetConditionalFormatting.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
workbook.CalculateFormula(true);
List cells = new List { “A2”, “B3”, “C3”, “D3”, “E3”, “F3”, “G3”, “H3”, “I3”, “J3”, “K3”, “L3”, “M3” };
foreach (string cellName in cells)
{
Cell cell = worksheet.Cells[cellName];
FormatConditionCollection formatConditions = cell.GetFormatConditions();
for (int i = 0; i < formatConditions.Count; i++)
{
FormatCondition fc = formatConditions[i];
IconSet iconSet = fc.IconSet;
Console.WriteLine(“IconsSet: {0}”, iconSet.Type);
for (int j = 0; j < iconSet.CfIcons.Count; j++)
{
ConditionalFormattingIcon icon = iconSet.CfIcons[j];
ConditionalFormattingValue value = iconSet.Cfvos[j];
Console.WriteLine(" CellName: {0}, Icon Index={1}, IconType={2}, valueType={3}, value={4}", cellName, icon.Index, icon.Type, value.Type, value.Value);
}
}
// Console.WriteLine(“CellName: {0}, Icon Index={1}, IconType={2}”, cellName, icon.Index, icon.Type);
}
}
The output is:
IconsSet: CustomSet
CellName: A2, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: A2, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: A2, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: B3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: B3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: B3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: C3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: C3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: C3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: D3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: D3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: D3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: E3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: E3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: E3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: F3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: F3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: F3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: G3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: G3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: G3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: H3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: H3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: H3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: I3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: I3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: I3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: J3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: J3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: J3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: K3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: K3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: K3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: L3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: L3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: L3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
IconsSet: CustomSet
CellName: M3, Icon Index=1, IconType=Symbols32, valueType=Percent, value=0
CellName: M3, Icon Index=1, IconType=TrafficLights32, valueType=Percent, value=33
CellName: M3, Icon Index=2, IconType=TrafficLights32, valueType=Percent, value=67
The output is correct, which is very good. So the function that computes the ConditionalFormattingIcon is incorrect for some of the cells.
Hi Costa,
Thank you for sharing your findings with us. We are currently working on your presented scenario, and we will soon get back to you with updates in this regard.
Hi Costa,
Thank you again for sharing your findings with us.
I have worked on this scenario with your provided sample spreadsheet as well as with the one I have created on my own. It seems that the IconType values for a few cases are returned incorrectly. To probe further into this matter, I have logged an investigative ticket, and have requested the development team to share their views in this regard. The ticket Id for your future reference is CELLSNET-42119. Please spare us little time to properly analyze the problem on our end. In meanwhile, we will keep you posted with updates.
Hi,
Please download and try our latest version/fix: Aspose.Cells for .NET v7.7.0.2 and let us know your feedback.
We have fixed this issue.
Thank you.
The issues you have found earlier (filed as CELLSNET-42119) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.