Conditional Formatting Corrupts Spreadsheet

Aspose.Cells Product Family
1

Hi,

I have been able to apply conditional formatting to text values across multiple sheets within a workbook (using VB.net) without issue up to version 18.4

Applying the colours as follows:

        Dim Index As Integer = XL.ConditionalFormattings.Add()

        Dim Fcs As FormatConditionCollection = XL.ConditionalFormattings(Index)

        Dim Ca As CellArea = New CellArea()

        Ca.StartRow = 4
        Ca.EndRow = XL.Cells.MaxDataRow
        Ca.StartColumn = 1
        Ca.EndColumn = XL.Cells.MaxDataColumn

        Fcs.AddArea(Ca)

        ' Loop Through Database & Apply Specified RGB Colours
        Do While Not cRst.EOF

            ' Adds condition
            Dim ConditionIndex As Integer = Fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, cRst.Fields("Action").Value, "")

            ' Sets the background cell colours
            Dim FcIdx As Aspose.Cells.FormatCondition = Fcs(ConditionIndex)

            ' Apply the Colours
            FcIdx.Style.BackgroundColor = Color.FromArgb(cRst.Fields("R").Value, cRst.Fields("G").Value, cRst.Fields("B").Value)

            cRst.MoveNext()

Save routine as follows:

xl.Settings.Password = cPassWord
xl.CalculateFormula(True)
xl.Settings.ReCalculateOnOpen = False
xl.Save(String.Format("{0}{1}.xlsx", cLoc, rsSalOrg("FileName").Value), SaveFormat.Xlsx)

Any version post 18.4 (up to 18.8) - the workbook is corrupted - Opening message: We found a problem with some content in xxxxx, Excel then states opening by repairing the unreadable content, the respective sheets have all formatting removed.

If i remove the option of applying the colours, the rest of the application works as expected so have to assume something has changed between versions for conditional formatting - can you offer any suggestions?

Many thanks

Tony

2

@tony_phillips,

Thanks for your query.

We are working on this issue and will share our feedback soon.

3

@tony_phillips,

We have tried to reproduce the issue but need more data for a complete analysis. As this code is not runnable because it uses values from the database, so it is difficult to observe the exact issue. Please provide us a sample console application(runnable) which contains all the values hard coded (no reference to database) along with the template Excel file. This running application will help us to observe the scenario and provide our feedback.

4

Hi,

Have uploaded console application, plus example output in Data folder showing working in 18.4 and the error using latest 18.9

Many thanks

Tony

Aspose_Test.zip (692.7 KB)

5

@tony_phillips,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46391 - Conditional formatting corrupts spreadsheet
6

@tony_phillips,

Please change the code as:

Fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, "=\"Right to work Expired\"", "");

The formula1 and formula2 must start with “=”.

Let us know your feedback.

7

Hi,

I get invalid expression - the code example I supplied is VB.Net

Tony

8

@tony_phillips,

Double quotes are required around the string “Right to work Expired” and also “=” operator is required. You may please user Chr(34) for adding quotation marks as shown in the following VB .NET line of code. It creates correct output file as attached here.

Dim ConditionIndex As Integer = Fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, "=" + Chr(34) + "Right to work Expired" + Chr(34), "")
Test_OutputV18.9.5.zip (12.3 KB)

9

Hi,

Many thanks, confirm working as expected - thanks for your help

Tony

10

@tony_phillips,

Good to know that your issue is sorted out by the suggested line of code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.