Hi,
I created a simple *.xlsx document and converted it to *.xlsb by Excel 2010 (unfortunately I can’t attach xlsb, so I zipped it).
Base block of code, that used in all samples:
gXl = new Workbook(fName);
foreach (Worksheet ws in gXl.Worksheets)
{
foreach (FormatConditionCollection fcc in ws.ConditionalFormattings)
{
for (int i = 0; i < fcc.Count; i++)
{
FormatCondition fc = fcc[i];
// Checking type of current object to print valid fields
// Here we call code from samples
}
}
}
1. Type - ColorScale (Excel: Format all cells based on their values - 2 - Color Scale).
Type - Number
Value:
*.xlsx - ‘=0’
*.xlsb - ‘0’
Such type of value has the same incompatibilities in the other rules.
Cell in the attached document (Sheet1): A2
Code:
ConditionalFormattingValue cfv;
cfv = fc.ColorScale.MinCfvo;
printLine(“Value”, cfv.Value.ToString(), SPACE4); // my inner printing method
2. Type - ColorScale (Excel: Format all cells based on their values - 2 - Color Scale).
Type - Number
Value (min):
*.xlsx - ‘=$A$2’
*.xlsb - ‘0’
Value (max):
*.xlsx - ‘=10’
*.xlsb - ‘0’
Such type of value has the same incompatibilities in the other rules.
Cell in the attached document (Sheet1): A4
Code:
ConditionalFormattingValue cfv;
cfv = fc.ColorScale.MinCfvo;
printLine(“Value”, cfv.Value.ToString(), SPACE4); // my inner printing method
cfv = fc.ColorScale.MaxCfvo;
printLine(“Value”, cfv.Value.ToString(), SPACE4);
3. Type - IconSet (Excel: Format all cells based on their values).
Type - TrafficLights31
ShowValue:
*.xlsx - False
*.xlsb - True
IsGTE:
*.xlsx - True - False - False
*.xlsb - True - True - True
Range of cells (Sheet1): [G1:K1]
Code:
printLine(“ShowValue”, fc.IconSet.ShowValue.ToString(), SPACE3); // my inner printing method
foreach (ConditionalFormattingValue cfv in fc.IconSet.Cfvos)
{
printLine(“IsGTE”, cfv.IsGTE.ToString(), SPACE4);
}
4. Type - CellValue (Excel: Format only cells that contain - Cell Value)
Operator: all types from enumeration OperatorType
*.xlsb - empty string or exception
Cells in the attached document (Sheet2): A1 - A8
Code:
printLine(“Formula1”, formulaWrapper(fc, 1), SPACE3); // my inner printing method
if ((fc.Operator == OperatorType.Between) || (fc.Operator == OperatorType.NotBetween)) printLine(“Formula2”, formulaWrapper(fc, 2), SPACE3);
private static string formulaWrapper(FormatCondition fc, int idx)
{
try
{
if (idx == 1) return fc.Formula1;
if (idx == 2) return fc.Formula2;
return “???”;
}
catch
{
return ERROR;
}
}
5. Type - TimePeriod (Excel: Format only cells that contain - Dates Occurring).
TimePeriod - Yesterday, Tomorrow, Last7Days, LastWeek, ThisWeek, NextWeek, LastMonth, ThisMonth, NextMonth,
*.xlsb - ‘Expression’ with empty formula
Cells in the attached document (Sheet2): E1, E3 - E10
Code:
printLine(“TimePeriod”, Enum.GetName(typeof(TimePeriodType), fc.TimePeriod), SPACE3); // my inner printing method
6. Type - Top10 (Excel: Format only top or bottom ranked values).
IsPercent:
*.xlsx - True
*.xlsb - False
Cell in the attached document (Sheet3): A1
IsBottom:
*.xlsx - True
*.xlsb - False
Cell in the attached document (Sheet3): A2
Code:
printLine(“IsBottom”, fc.Top10.IsBottom.ToString(), SPACE3); // my inner printing method
printLine(“IsPercent”, fc.Top10.IsPercent.ToString(), SPACE3);
7. Type - AboveAverage (Excel: Format only values that are above or below average).
We are reading correct type from *.xlsb with these values:
IsAboveAverage - True
IsEqualAverage - False
Cells in the attached document (Sheet3): C1, C5, C7, C9
*.xlsb - StdDev is always zero.
Rules with other values have a type ‘Expression’ in *.xlsb document.
Cells in the attached document (Sheet3): C2 - C4, C6, C8, C10
Code:
printLine(“Type”, Enum.GetName(typeof(FormatConditionType), fc.Type), SPACE2); // my inner printing method
printLine(“IsAboveAverage”, fc.AboveAverage.IsAboveAverage.ToString(), SPACE3);
printLine(“IsEqualAverage”, fc.AboveAverage.IsEqualAverage.ToString(), SPACE3);
printLine(“StdDev”, fc.AboveAverage.StdDev.ToString(), SPACE3);
I use Aspose.Cells v7.1.2.0.
Thanks so much for your help.
Alex