Conditionalformatting copy issue

Hi.

I have found a cause for my previous post 
<a href="</a>

Our report generation engine assumes a copying a data range into workbook. For this purpose we perform following actions:
1) Insert a rows for new range (for move data, that located below copied range)
2) Copy all from source range to new

A following example shows enumeration of operations:
@Test
public void copyRangeTest() throws Exception {
    Workbook wb = new Workbook("D://in.xlsx");

    Cells cells = wb.getWorksheets().get(0).getCells();
    cells.insertRows(32, 24, true);

    Range range = createRange(cells, 32, 1, 24, 18);
    Range source = createRange(cells, 8, 1, 24, 18);

    copy(range, source, PasteType.ALL);

    wb.save("D://out.xlsx");
}
But at end this operation we will find a two conditional formatting rules per each cell (e.g. G52) 

But on the other hand, if we will open in.xlsx and pefrorm this actions manually, then we will receive only one conditional formatting for this cell.

Also i noticed, that after copy all by libary's API a formula of conditional formatting is not changed, but when i perform copy manually formula change according to new range position

Best regards. Alexey

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

When we tried to run your code, it gave compile time error… Few methods like copyrange() are not present. Please provide us your runnable sample code so that we could investigate this issue and log it in our database for a fix in case it is a bug. Thanks for cooperation.

I’m sorry. There is full example code, that show, that values for conditional formating i expect to have. You can provide insert range and copy action manually via excel and see, that results are different


@Test
public void copyRangeTest() throws Exception {
Workbook wb = new Workbook(“D://in.xlsx”);

Cells cells = wb.getWorksheets().get(0).getCells();
cells.insertRows(32, 24, true);

FormatConditionCollection formatConditions = cells.get(“G52”).getFormatConditions();
assertEquals(1, formatConditions.getCount());
assertEquals("=$B9=t1:org_00:org_01:upsng_00:shop_64:nsp_07_02:rvs_07_02_19", formatConditions.get(0).getFormula1());

Range range = cells.createRange(32, 1, 24, 18);
Range source = cells.createRange(8, 1, 24, 18);

PasteOptions options = new PasteOptions();
options.setPasteType(PasteType.ALL);
range.copy(source, options);

formatConditions = cells.get(“G52”).getFormatConditions();
assertEquals(1, formatConditions.getCount());
assertEquals("=$B33=t1:org_00:org_01:upsng_00:shop_64:nsp_07_02:rvs_07_02_19", formatConditions.get(0).getFormula1());

wb.save(“D://out.xlsx”);

wb = new Workbook(“D://out.xlsx”);

cells = wb.getWorksheets().get(0).getCells();
formatConditions = cells.get(“G52”).getFormatConditions();
//returns 1, but excel shows 2 format conditions for this cell
assertEquals(1, formatConditions.getCount());
assertEquals("=$B33=t1:org_00:org_01:upsng_00:shop_64:nsp_07_02:rvs_07_02_19", formatConditions.get(0).getFormula1());
}

Best regards. Alexey

Hi,

Thanks for your sample code and using Aspose.Cells.

We were able to get the output excel file successfully after running your code and partially noticed the issue but we still need your elaboration where the issue lies. It will be helpful if you point out the issues in the output excel file using some screenshots. This way, product team will be able to investigate and fix this issue more easily and quickly. Thanks for your cooperation.

Excuse me, i have changed a test again

There is video:

1) At first i perform actions manually (insert 24 rows and copy area) - see result
2) At second - open out.xlsx, that received after test and compare result

A password is 123

Best regards. Alexey

Hi Alexey,

Thanks for good video and describing the issue in detail and using Aspose.Cells.

We have logged this issue in our database for investigation. We will look into it and fix this issue if possible. In case, we need your more help, we will request you to provide it. Once, the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41522 - Conditionalformatting copy issue

Hi,

Thanks for using Aspose.Cells for Java.

Please download and try this fix: Aspose.Cells for Java v8.6.0.6 and let us know your feedback.

Hi.


After update to last version of library, i have founded, that Cell.getFormatConditions()
return now FormatConditionCollection[] (not FormatConditionCollection as earlier)

Hi,


Yes, it does, so you may change/ accommodate your code accordingly. Is there any problem with it?

Thank you.

I just think, that it is curiously, that returns collection of collections (array of collection).


In other it worked for me. Many thanks.

Best regards. Alexey

Hi,


Thanks for your feedback.

Good to know that it is working for you. Feel free to write us back if you have further comments or questions, we will be happy to assist you soon.

Thank you.

The issues you have found earlier (filed as CELLSJAVA-41522) have been fixed in newer version of

Aspose.Cells for Java (Download | Maven).


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.