Hello,

here at Infoland we are using aspose.cells 4.5.1

Recently we have tried to update to aspose.cells 5.1.0 because of several bugfixes and new features. However, we've run into a problem with conditional formatting. The behavior seems to have changed as of aspose.cells 4.8.1.

I've created a test scenario to clarify the problem. We're using an empty excel sheet called cellstest.xls (see attachment). We've added a conditional formatting to the entire first worksheet (using the "applies to" with the value: =$1:$65536) with the following formula:
=AND(MOD(ROW();2);ROW() <> 1)

We use this formula to alternate the rows of the workbook. Now we use aspose.cells to insert a range into the worksheet using the following code:

Dim objWorkbook As New Aspose.Cells.Workbook()
objWorkbook.Open("cellstest.xls")

Dim objCellArea As New CellArea()
objCellArea.StartColumn = 2
objCellArea.StartRow = 2
objCellArea.EndColumn = 2
objCellArea.EndRow = 2

objWorkbook.Worksheets(0).Cells.InsertRange(objCellArea, 8, ShiftType.Down)

objWorkbook.Save("cellstest_modified.xls")

If we run this code using an aspose.cells prior to 4.8.1 (in our case, 4.5.1, but also tested with 4.7.1) the conditional formatting rule remains unchanged. When we use an aspose.cells 4.8.1 or higher, the "applies to" section of the conditional formatting changes to "=$D:$IV;$C:$C;$A:$B"

I've attached both the original test workbook along with the generated output of aspose 4.5.1 and and 4.8.1.

This issue messes up a lot of our formatting in the generated excel worksheets we use in our products. I've seen in the release notes of aspose.cells 4.8.1 that issue 11097 has been fixed, which is related to conditional formatting.


What I would like to know is whether or not this is a bug, and if not, what we can to do make sure the conditional formatting doesn't change so we can implement the new aspose.cells version in our products

Thanks in advance for the reply

Paul Vrugt
Sr Software Engineer
Infoland BV
The Netherlands

Hi,

After an initial test with your template file and sample code, I am able to reproduce the issue you have talked about. I have logged your issue into our issue tracking system with an id: CELLSNET-18791. We will investigate the issue and get back to you soon.

Thank you.

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

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

---

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

Hello,

I'd like to re-open issue 18791.

We've downloaded aspose.cells 5.1.1, and the issue is fixed for the specific situation in the test case. However, if i change the ShiftType.Down to ShiftType.Right, it still messes up my conditional formatting area from "$1:$65536" to "$4:$65536;$3:$3;$1:$2"

All you need to do to reproduce it is take the code from the original post, change ShiftType.Down to ShiftType.Right, and try it with aspose.cells 5.1.1

Regard,

Paul Vrugt
Sr Software Engineer
Infoland BV
The Netherlands

Hi,

Yes, I can find the issue you have mentioned by changing ShiftType.Down to ShiftType.Right in the code.

I have reopened your issue again and we will fix it soon.

Thank you.

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

Hi,

Please try the attached version, we have fixed your issue.

Thank you

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

we've tested the new version and can confirm that this indeed fixed our problem. Thank you

Hello,

I'd like re-open this issue (again)

If you take the attached excel document and the following code, aspose.cells up to version 7.0.3 will mess up the range of the conditional formatting. As for aspose.cells 7.1.0, this version will throw an error trying to save the file ("Invalid Condition formatting: index 1,Invalid cell range: index 0")

        Dim objWorkbook As New Aspose.Cells.Workbook("cellstest.xls")

        Dim objCellArea As New CellArea()
        objCellArea.StartColumn = 2
        objCellArea.StartRow = 0
        objCellArea.EndColumn = 2
        objCellArea.EndRow = 0

        objWorkbook.Worksheets(0).Cells.InsertRange(objCellArea, 8, ShiftType.Down)

        objWorkbook.Save("cellstest_modified.xls")

I want aspose.cells to leave my conditional formatting alone and just insert cells where i want them to. Can you confirm this issue?

Paul Vrugt
Sr. Software engineer
Infoland BV

Hi,

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

Hello Amjad,

unfortunately, saving as xlsx is not an option. In versions before 7.1.0, the file is saved correctly, but the conditional formatting is moved a few columns. Both the error and the moving of the formatting range is unwanted behavior.

When i do save the document to xlsx in aspose.cells 7.1.0, the conditional formatting is still messed up. The original file has 2 conditional formatting rules, both set to $6:$65536. When saving the file as xlsx the file has 4 conditional formatting rules, the first two rules apply to the first two columns, and the second two rules apply to the third column. All other column no longer have conditional formatting.

When i save the document to xlsx in aspose.cells 7.0.3 if get six (!) conditional formatting sections.

What i need is the range of my conditional formatting not to change when inserting cells. How can i accomplish this?

Hi,

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

Hi,

We have fixed this issue. Please download: Aspose.Cells for .NET v7.1.0.1

---

Many Thanks and Kind Regards,

Shakeel Faiz
Support Developer
Aspose Sialkot Team
Contact Us
Aspose - The .NET and Java Component Publisher

Keep in touch! We're on Twitter and Facebook