Range.Copy incorrectly updates conditional formatting

Aspose.Cells Product Family
1

The Copy method when used for Conditional Formatting may have some unintended consequences, especially for formats like Data Bars.

In screenshot1 you can see 2 rows, each containing a conditional format with databars. Databars use the max value in the cell area to determine what a 100% bar looks like and then formats all the rest of the cells based on that max value. So it’s expected that row 1 and row 2 in this screenshot have the same size databars even though the values in row 2 are double those in row 1.

screenshot1.jpg (14.4 KB)

Using Aspose we copy the formatting from row 1 to row 2 with the technique:
sheet.Cells.CreateRange(“A2:C2”).Copy(sheet.Cells.CreateRange(“A1:C1”), new PasteOptions()
{
PasteType = PasteType.Formats
});

It would be expected that Aspose would create a second Conditional Format entry with cell areas updated for the target range. However it does not. Instead it adds the new cell areas to the Applies To list of the original Conditional Format.

So instead of this:
Format 1 - Applies to: =$A$1:$C$1
Format 2 - Applies to: =$A$2:$C$2

Aspose creates this:
Applies to: =$A$1:$C$1,$A$2:$C$2

As you can see in screenshot2 below this causes an unintended consequence with the databars algorithm. Now all 6 cells are formatted based on the single largest cell in all of the cell areas, as opposed to the largest cell in each cell area.
screenshot2.jpg (13.1 KB)

I’ve tried workaround with creating my own clones of Conditional Formatting objects, however this area of the API is difficult to work in. It doesn’t seem that you can create a new conditional format object from an existing one, instead you would have to manually copy over each property, so it’s not a reliable alternative.

I appreciate your help with this issue.

Thank you,
-Andy

2

@weissa,

Could you please zip your template Excel file and attach it here. we will evaluate your issue soon.

3

DataBarsExample.xlsx is the source file.
out.xlsx is the output from the code below
files.zip (13.2 KB)

		[Test]
	public void TestConditonalFormatting()
	{
		Aspose.Cells.License license = new Aspose.Cells.License();
		license.SetLicense("Aspose.Cells.lic");

		string desktop= Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
		string path = Path.Combine(desktop, "DataBarsExample.xlsx");
		string savepath = Path.Combine(desktop, "out.xlsx");

		var workbook = new Workbook(path);
		var sheet = workbook.Worksheets["Sheet1"];
		sheet.Cells.CreateRange("A2:C2").Copy(sheet.Cells.CreateRange("A1:C1"), new PasteOptions()
		{
			PasteType = PasteType.Formats
		});

		workbook.Save(savepath);
	}
4

@weissa,

Please notice, we were able to reproduce the issue as you mentioned using your sample file. We found Range.Copy incorrectly updates conditional formatting. We have logged a ticket with an id “CELLSNET-47910” for your issue. We will look into it soon.

Once we figure it out or we have some other updates, we will let you know.

5

@weissa,
This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

6

@weissa
Please try the latest fix 21.2.9.
Aspose.Cells21.2.9 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.2.9 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.2.9 For .NetStandard20.Zip (5.5 MB)

7

@simon.zhao Thank you for the update. I’ve tested and found a problem with the fix.

If the conditional formatting on a row is for a contiguous range e.g. A1:C1 then the fix correctly creates a new conditional formatting entry, e,g, A2:C2.

However Conditional Formatting allows for a list of non-contiguous ranges. For example A1,C1 . The expected behavior would be a single new conditional formatting entry, e.g. A2,C2. However the fix is creating 2 new entries, one for A2 and one for C2. So in this case the databar would show as 100% for each cell.

Thank You,
-Andy

8

@weissa,

We noticed this behavior/issue using our test file. We have reopened your issue and we will be looking into it to figure it out soon.

9

@Amjad_Sahi, I do appreciate your quick turnaround and support. I’ll be happy to test a new version when you have it.

Thanks,
-Andy

10

@weissa,
You are welcome. We will notify you here immediately once the new version is ready for sharing.

11

@weissa,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

12

@weissa,

Please try our latest version/fix: Aspose.Cells for .NET v21.3.1 (attached).
Aspose.Cells21.3.1 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.3.1 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.3.1 For .NetStandard20.Zip (5.5 MB)

Your issue should be fixed in it.

Let us know your feedback.

13

Amjad,

I tested and everything looks good with my test cases.

Thank you for the support. We appreciate your help.

-Andy

14

@weissa,

Good to know that your issue is resolved by the new fix. Feel free to contact us any time if you have further queries or find any other issue, we will be happy to assist you soon.

15

The issues you have found earlier (filed as CELLSNET-47910) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao