Conditional formating are deleted without reasons

Aspose.Cells Product Family
1

Hi

I load a template xls file which contains Logo, graphs…
After I insert date, update design (color, fonts,…) into this template to generate a final report.

but i have an issue with the latest version (18.10.0.0)

When i delete rows from a data range, all the conditional formating are deleted too and i don’t understand why.

i send you the template and the source code. See the fonction DeleteRowsFromRange.

In this template, I try to delete the data range GRAPHE_RULER.

Befote it’s running well and i use version 9.0.0.0

Best regards

Files.zip (239.2 KB)

2

@bvautier,

Thanks for your query.

I have tried to re-produce this issue using your sample code but could not compile it due to missing references. I tried the DeleteRowsFromRange function but could not observe the issue in the output XLS file.You may please send us a simple console application(runnable) which can be used for re-producing the issue. Also send us an expected XLS file which is created using template file and EXCEL (without using Aspose.Cells) and some image or detailed description of the comparison between the expected output file and program output files. It will help us to analyze the issue and provide our feedback.

3

Hi

At the beginning the template have confitional formating (Template.png)

I delete rows from range “GRAPHE_RULER” and the conditional formatting are deleted. this is the issue(Output.png)

See the attached winform application (WindowsApplication1.zip)

WindowsApplication1.zip (9.4 MB)

Output.png (40.8 KB)

Template.png (42.2 KB)

Best regards

4

@bvautier,

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-46433 - Conditional formatting are deleted while deleting named range
5

Hi

Thank you very much for your answer.
It’s very hard for me to wait because my clients are waiting for new release with new fonctionnalities.

Is there a way to save and restore conditional formating in the worksheet?

The idee is

  1. I save the conditional formating from worksheet in a temp list
  2. I delete the range name
  3. I restore the conditional formation from the temp list to the worsheet

If i can do that, may you want to send me an exemple in vb.net

I need to use the laster version of Aspose.cells for another fonctionalities.

Thank you for you help!

Best regards

6

@bvautier,

I have tested this requirement by comparing the conditional formatting collection of the worksheet before and after deleting this range name. It is observed that conditional formatting collection remains intact and no change occurs in this collection. This shows that even removing and adding this collection will not solve the issue. I am afraid that you have to wait until this issue is analyzed by us in detail on its turn and feedback is ready to provide here.

7

Thanks!
I add this following lines and it’s running well.
I add temporary sheet to the workbook where i save the collection of conditional formating.
i delete the data range and i restore the collection.

    If ws.ConditionalFormattings.Count > 0 Then
        Dim IndexTempWS As Integer = CurrentWorkbook.Worksheets.Add()
        CurrentWorkbook.Worksheets(IndexTempWS).ConditionalFormattings.Copy(ws.ConditionalFormattings)

        ws.Cells.DeleteRange(RangeToDelete.FirstRow, RangeToDelete.FirstColumn, RangeToDelete.FirstRow + RangeToDelete.RowCount - 1, RangeToDelete.FirstColumn + RangeToDelete.ColumnCount - 1, ShiftTypeValue)

        ws.ConditionalFormattings.Clear()
        ws.ConditionalFormattings.Copy(CurrentWorkbook.Worksheets(IndexTempWS).ConditionalFormattings)
        CurrentWorkbook.Worksheets.RemoveAt(IndexTempWS)
    Else
        ws.Cells.DeleteRange(RangeToDelete.FirstRow, RangeToDelete.FirstColumn, RangeToDelete.FirstRow + RangeToDelete.RowCount - 1, RangeToDelete.FirstColumn + RangeToDelete.ColumnCount - 1, ShiftTypeValue)
    End If

Best regards

8

@bvautier,

Good to know that your issue is sorted out by above method. I tried the scenario on same sheet and could not get the desired output. We will write back here once analysis on logged ticket is done and feedback is ready to provide.

9

@bvautier,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46433”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

10

@bvautier,

Please try our latest version/fix: Aspose.Cells for .NET v18.10.7:

Aspose.Cells18.10.7 For .Net2_AuthenticodeSigned.Zip (4.7 MB)
Aspose.Cells18.10.7 For .Net4.0.Zip (4.7 MB)

Your issue should be fixed in it. In this fix, we remove shapes in the range when calling Cells.DeleleRange() method. If you only want to remove the data and formatting of the cells, please use Cells.ClearRange() method.

Let us know your feedback.

11

The issues you have found earlier (filed as CELLSNET-46433) have been fixed in Aspose.Cells for .NET v18.11. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi