Version 7.x and Worksheet.copy

Aspose.Cells Product Family
1

Hello,

It seems the behavior of the method Worksheet.copy has changed in version 7.0.2 (comparing to version 2).

In v2 when calling this methods, Aspose.Cells was doing a fine job at identifying the cells where a formula could not be copied as is: if the forumla was refering to a cell in another worksheet, then only the value was copied. Now, there is no more analysis of this kind and the formula is copied, and the calculation is failing.

Is there any way we can easily replicate this behavior in a migration from v2 to v7?

Thanks and regards
Vincent

2

Hi,

Thanks for your posting.

Will you please demonstrate this issue with a simple console application? We will investigate it and fix the problem if any and update you asap.

3

Please find attached a sample xlsx file.

Using this code with Aspose.Cells 2.5.0.4:
// Open a file into the first book.
Workbook excelWorkbook0 = new Workbook();
excelWorkbook0.open(“copytest.xlsx”);
// Create another Workbook.
Workbook excelWorkbook1 = new Workbook();
// Calculate the formulas
excelWorkbook0.calculateFormula(true);
// Copy the first sheet of the first book into second book.
excelWorkbook1.getWorksheets().getSheet(0).copy(excelWorkbook0.getWorksheets().getSheet(“Sheet1”));
// Save the file.
excelWorkbook1.save(“copyresult.xlsx”, FileFormatType.XLSX);
System.out.println(“Completed”);

Using this code with Aspose.Cells 7.0.3:
// Open a file into the first book.
Workbook excelWorkbook0 = new Workbook(“copytest.xlsx”);
// Create another Workbook.
Workbook excelWorkbook1 = new Workbook();
// Calculate the formulas
excelWorkbook0.calculateFormula(true);
// Copy the first sheet of the first book into second book.
excelWorkbook1.getWorksheets().get(0).copy(excelWorkbook0.getWorksheets().get(“Sheet1”));
// Save the file.
excelWorkbook1.save(“copyresult.xlsx”, FileFormatType.XLSX);
System.out.println(“Completed”);

Does not give the same result output file. Cells C1:C5 from Sheet1 differ.

4

Hi,

Thanks for your sample code and illustrating the issue. We have logged this issue in our database.

We will investigate this issue and fix it and update you asap.

This issue has been logged as CELLSJAVA-40038.

5

Hi,

For such kind of issue, in fact both v2 and current version (v7.x) do not work in the correct way. If you want to keep the original value when opening the generated file, we think you can disable the automatic formula calculation of Excel by using following code:

workbook.getSettings().setCalcMode(CalcModeType.MANUAL);

Thank you.

6

Hi

Thank you for your answer.
But I am afraid this is not what was expected: we have code running in production using this feature. We want to continue using new versions of Aspose because they contain bugfixes and enhancements. Our problem now is that on few features or API, it is not backward compatible.

No plan to redeploy this feautre, maybe with a new API name or parameters?

Regards
Vincent

7

Hi,

Thanks for your feedback/comment.

I have added it in our database. We will look into it and update you asap.

8

Hi,

We will look into your requirement to see whether we can provide an option for users to determine how to copy such kind of formulas when doing worksheet copy. We need more time to look into this feature.

9

Hi,

Please try the new fix: v7.0.3.2.

To copy cell values only for those formulas with invalid reference when copying worksheet, please use the following code with the new fix:

CopyOptions opt = new CopyOptions();

opt.setCopyInvalidFormulasAsValues(true);

sheetDest.copy(sheetSrc, opt);

10

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

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