Conditional formatting. Compatibility problems with *.xlsb

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

Hi,


Thanks for the sample files and code segments.

I did notice some differences as you pointed out. We will look into it why it gives different values for XLSX and XLSB files although as you pointed out you just re-saved the XLSX file as XLSB file.

I have logged a ticket with an id: CELLSNET-40525. Once we have any update, we will let you know here.

Thank you.

Hi,


I found more few similar problems.

8. Type - UniqueValues (Excel: Format only unique or duplicate values).
Style:
*.xlsx:
Borders:
BorderType - LeftBorder
Color - Color [A=255, R=149, G=55, B=53]
LineStyle - Dashed
BorderType - RightBorder
Color - Color [A=255, R=149, G=55, B=53]
LineStyle - Dashed
BorderType - TopBorder
Color - Color [A=255, R=149, G=55, B=53]
LineStyle - Dashed
BorderType - BottomBorder
Color - Color [A=255, R=149, G=55, B=53]
LineStyle - Dashed
BackgroundColor Color [A=255, R=85, G=142, B=213]
BackgroundThemeColor:
ColorType - Text2
Tint - 0,40001
ForegroundColor - Color [A=0, R=255, G=255, B=0]
IsGradient - True
*.xlsb:
Borders
BorderType - LeftBorder
Color - Color [Black]
LineStyle - None
BorderType - RightBorder
Color - Color [Black]
LineStyle - None
BorderType - TopBorder
Color - Color [Black]
LineStyle - None
BorderType - BottomBorder
Color - Color [Black]
LineStyle - None
BackgroundColor - Color [Empty]
BackgroundThemeColor:
ForegroundColor - Color [Empty]
IsGradient - False
Cell in the attached document (Sheet1): A1
Code:
Style style = fc.Style;

if (style.Borders != null)
{
foreach (BorderType bt in Enum.GetValues(typeof(BorderType)))
{
Border border = style.Borders[bt];
printLine(“BorderType”, Enum.GetName(typeof(BorderType), bt), SPACE5); // my inner printing method
printLine(“Color”, border.Color.ToString(), SPACE6);
printLine(“LineStyle”, Enum.GetName(typeof(CellBorderType), border.LineStyle), SPACE6);
}
}
printLine(“BackgroundColor”, style.BackgroundColor.ToString(), SPACE4);
printLine(“BackgroundThemeColor”, string.Empty, SPACE4);
if (style.BackgroundThemeColor != null)
{
printLine(“ColorType”, Enum.GetName(typeof(ThemeColorType), style.BackgroundThemeColor.ColorType), SPACE5);
printLine(“Tint”, style.BackgroundThemeColor.Tint.ToString(), SPACE5);
}

printLine(“ForegroundColor”, style.ForegroundColor.ToString(), SPACE4);
printLine(“ForegroundThemeColor”, string.Empty, SPACE4);
if (style.ForegroundThemeColor != null)
{
printLine(“ColorType”, Enum.GetName(typeof(ThemeColorType), style.ForegroundThemeColor.ColorType), SPACE5);
printLine(“Tint”, style.ForegroundThemeColor.Tint.ToString(), SPACE5);
}

printLine(“IsGradient”, style.IsGradient.ToString(), SPACE4);

Thank you,
Alex Shloma

Hi,

Thanks for your posting describing few similar problems.

We have logged it against the issue id: CELLSNET-40525

We will get back to you asap.

Hi,

We. have fixed this issue. Please download and use the latest version: Aspose.Cells for .NET v7.1.2.6.

Thank you!


All works fine except third item:
3. I found a reverse problem.
ShowValue:
*.xlsx - True
*.xlsb - False

Range of cells (Sheet1): [G1:K1]
I use Aspose.Cells v7.1.2.6.

Thanks,
Alex

Hi,

Thanks for your feedback.

We have logged your comment in our database. We will look into and fix the remaining issues.

Once, we will have some update/fix for you, we will let you know asap.

The comment has been logged against the issue id: CELLSNET-40525

Hi,

We have fixed this issue. Please download and try the latest version: Aspose.Cells for .NET v7.1.2.7

Thank you! Issue fixed.


Best regards,
Alex Shloma

Hi,

It’s good to know your issues have been fixed.

Please feel free to post your any other problems, we will help you asap.

Thank you for great support!


Regards,
Alex

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


This message was posted using Notification2Forum from Downloads module by aspose.notifier.