Excel formula issue- formual is getting changed on setFormula function

APEX · August 19, 2015, 6:13pm

Hi I am trying to set formula in a worksheet which references cells from other workbook's multiple worksheets. I am using aspose.cells.java version 8.2.1.3

Here is the formula that is prepared

formula = [1.xlsx]ALL_0010_1!E62 + [1.xlsx]ALL_0011_2!E65 + [1.xlsx]ALL_0012_3!E62 + [1.xlsx]ALL_0020_4!E73 + [1.xlsx]ALL_0030_5!E64 + [1.xlsx]ALL_0040_7!E75 + [1.xlsx]ALL_0050_13!E75 + [1.xlsx]ALL_0070_19!E75 + [1.xlsx]ALL_0080_25!E75 + [1.xlsx]ALL_0090_31!E75 + [1.xlsx]ALL_0100_36!E75 + [1.xlsx]ALL_0110_40!E75 + [1.xlsx]ALL_0130_44!E67 + [1.xlsx]ALL_0160_47!E62 + [1.xlsx]ALL_0230_57!E75 + [1.xlsx]ALL_1010_64!E62 + [1.xlsx]ALL_1011_65!E65 + [1.xlsx]ALL_1020_66!E73 + [1.xlsx]ALL_1030_67!E64 + [1.xlsx]ALL_1040_69!E75 + [1.xlsx]ALL_1050_74!E75 + [1.xlsx]ALL_1060_79!E75 + [1.xlsx]ALL_1070_83!E75 + [1.xlsx]ALL_1080_87!E75 + [1.xlsx]ALL_1120_91!E75 + [1.xlsx]ALL_1121_92!E74 + ALL_1150_4!E75 + ALL_1560_16!E75

and that formula gets set using setFormula function

totalCell.setFormula(formula) and

But I noticed that totalCell has formula as follows -which is incorrect - my question is why does aspose changes the formula e.g. [1.xlsx]ALL_0011_2!E65 ====> 'c:\Rita\40\[1.xlsx]ALL_0011_2:ALL_0010_1'!C65

'c:\Rita\40\[1.xlsx]ALL_0010_1'!C62+'c:\Rita\40\[1.xlsx]ALL_0011_2:ALL_0010_1'!C65+'c:\Rita\40\[1.xlsx]ALL_0012_3:ALL_0010_1'!C62+'c:\Rita\40\[1.xlsx]ALL_0020_4:ALL_0010_1'!C73+'c:\Rita\40\[1.xlsx]ALL_0030_5:ALL_0010_1'!C64+'c:\Rita\40\[1.xlsx]ALL_0040_7:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_0050_13:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_0070_19:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_0080_25:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_0090_31:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_0100_36:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_0110_40:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_0130_44:ALL_0010_1'!C67+'c:\Rita\40\[1.xlsx]ALL_0160_47:ALL_0010_1'!C62+'c:\Rita\40\[1.xlsx]ALL_0230_57:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_1010_64:ALL_0010_1'!C62+'c:\Rita\40\[1.xlsx]ALL_1011_65:ALL_0010_1'!C65+'c:\Rita\40\[1.xlsx]ALL_1020_66:ALL_0010_1'!C73+'c:\Rita\40\[1.xlsx]ALL_1030_67:ALL_0010_1'!C64+'c:\Rita\40\[1.xlsx]ALL_1040_69:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_1050_74:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_1060_79:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_1070_83:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_1080_87:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_1120_91:ALL_0010_1'!C75+'c:\Rita\40\[1.xlsx]ALL_1121_92:ALL_0010_1'!C74+ALL_1150_4!C75+ALL_1560_16!C75

Appreciate your help.

Thank you,

Rita Patel

shakeel.faiz · August 19, 2015, 10:12pm

Hi Rita,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version: Aspose.Cells
for Java v8.5.1.6 and see if it makes any difference and resolves your issue.

If your issue still occurs, then please provide us your runnable sample code and source excel file. Please also provide us your actual and expected excel files. You can create expected excel file manually using Microsoft Excel and attach it here fore our reference.

We will look into your issue and help you asap. Thanks for your cooperation.

APEX · August 20, 2015, 10:22am

Hi Faiz,

Thank you for your prompt response. I tried the code with the new version that you provided Aspose.Cells for Java v8.5.1.6 - and I noticed the same issue. Please take a look at attached sample code and files that are used in the code for formula.

1.xlsx and 2.xlsx are sample workbooks and AsposeCellsJava8516test.txt contains the java code.

Let me know if you need any other information.

Thanks,

Rita

shakeel.faiz · August 20, 2015, 10:59am

Hi Rita,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue by executing the following code with the latest version: Aspose.Cells
for Java v8.5.1.6. When the formula is inserted manually using Microsoft Excel, it evaluates to 0 but when it is inserted using Aspose.Cells API, it evaluates to #REF! which is wrong and means formula is getting changed.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

CELLSJAVA-41443 - Excel formula issue- formula is getting changed on setFormula function

I have also attached the output excel file generated with the following code for a reference.

Java
String file1=“1.xlsx”;
String file2=“2.xlsx”;
String formula = “=[1.xlsx]ALL_0010_1!C62 + [1.xlsx]ALL_0011_2!C65 + [1.xlsx]ALL_0012_3!C62 + [1.xlsx]ALL_0020_4!C73 + [1.xlsx]ALL_0030_5!C64 + [1.xlsx]ALL_0040_7!C75 + [1.xlsx]ALL_0050_13!C75 + [1.xlsx]ALL_0070_19!C75 + [1.xlsx]ALL_0080_25!C75 + [1.xlsx]ALL_0090_31!C75 + [1.xlsx]ALL_0100_36!C75 + [1.xlsx]ALL_0110_40!C75 + [1.xlsx]ALL_0130_44!C67 + [1.xlsx]ALL_0160_47!C62 + [1.xlsx]ALL_0230_57!C75 + [1.xlsx]ALL_1010_64!C62 + [1.xlsx]ALL_1011_65!C65 + [1.xlsx]ALL_1020_66!C73 + [1.xlsx]ALL_1030_67!C64 + [1.xlsx]ALL_1040_69!C75 + [1.xlsx]ALL_1050_74!C75 + [1.xlsx]ALL_1060_79!C75 + [1.xlsx]ALL_1070_83!C75 + [1.xlsx]ALL_1080_87!C75 + [1.xlsx]ALL_1120_91!C75 + [1.xlsx]ALL_1121_92!C74 + ALL_1150_4!C75 + ALL_1560_16!C75”;

com.aspose.cells.Workbook dd633Workbook2 = new com.aspose.cells.Workbook(file2);
dd633Workbook2.getWorksheets().get(“ALL_TOTALS_17”).getCells().get(“c84”).setFormula(formula);

dd633Workbook2.save(“output.xlsx”);

APEX · August 20, 2015, 11:43am

Thank you for the update. Looking forward for the fixed release.

Thanks again,

Rita

amjad.sahi · August 21, 2015, 2:50am

Hi,

And here is the parallel JAVA code that you try for your reference:

e.g

Sample code:

[JAVA]

String file1 = “1.xlsx”;

String file2 = “2.xlsx”;

String formula = “=[1.xlsx]ALL_0010_1!C62 + [1.xlsx]ALL_0011_2!C65 + [1.xlsx]ALL_0012_3!C62 + [1.xlsx]ALL_0020_4!C73 + [1.xlsx]ALL_0030_5!C64 + [1.xlsx]ALL_0040_7!C75 + [1.xlsx]ALL_0050_13!C75 + [1.xlsx]ALL_0070_19!C75 + [1.xlsx]ALL_0080_25!C75 + [1.xlsx]ALL_0090_31!C75 + [1.xlsx]ALL_0100_36!C75 + [1.xlsx]ALL_0110_40!C75 + [1.xlsx]ALL_0130_44!C67 + [1.xlsx]ALL_0160_47!C62 + [1.xlsx]ALL_0230_57!C75 + [1.xlsx]ALL_1010_64!C62 + [1.xlsx]ALL_1011_65!C65 + [1.xlsx]ALL_1020_66!C73 + [1.xlsx]ALL_1030_67!C64 + [1.xlsx]ALL_1040_69!C75 + [1.xlsx]ALL_1050_74!C75 + [1.xlsx]ALL_1060_79!C75 + [1.xlsx]ALL_1070_83!C75 + [1.xlsx]ALL_1080_87!C75 + [1.xlsx]ALL_1120_91!C75 + [1.xlsx]ALL_1121_92!C74 + ALL_1150_4!C75 + ALL_1560_16!C75”;

Workbook dd633Workbook2 = new Workbook(file2);

{

LoadOptions options = new LoadOptions();

options.setLoadDataAndFormatting(true);

Workbook extWorkbook = new Workbook(file1, options);

String[] sheetNames = new String[extWorkbook.getWorksheets().getCount()];

for (int i = 0; i < extWorkbook.getWorksheets().getCount(); i++)

{

sheetNames[i] = extWorkbook.getWorksheets().get(i).getName();

}

dd633Workbook2.getWorksheets().getExternalLinks().add(“1.xlsx”, sheetNames);

}

dd633Workbook2.getWorksheets().get(“ALL_TOTALS_17”).getCells().get(“c84”).setFormula(formula);

System.out.println(dd633Workbook2.getWorksheets().get(“ALL_TOTALS_17”).getCells().get(“c84”).getFormula());

dd633Workbook2.save(“dest.xlsx”);

Thank you.

APEX · September 9, 2015, 12:56pm

Hello Amjad,

Thank you for suggested java code- multiple workbook linked formula issue is being resolved by your suggested code sample.

Thanks again,

Rita

amjad.sahi · September 9, 2015, 1:20pm

Hi,

Good to know that the suggested code works fine for your requirements. Feel free to write us back if have further queries or comments, we will be happy to assist you soon.

Thank you.