Calculating formulas not working when we have more than 65356 named cells

Aspose.Cells Product Family
1

Hi,


When we try to build a excel file (.xlsx) with more than 65535 named cells and using this named cells in formulas it is throwing the error below:

com.aspose.cells.CellsException: -1
Error in calculating cell D65536 in Worksheet Sheet1
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)

Here is code to reproduce error:

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;

import com.aspose.cells.Cell;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.OoxmlSaveOptions;
import com.aspose.cells.Range;
import com.aspose.cells.SaveFormat;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.aspose.cells.WorksheetCollection;

@org.testng.annotations.Test
public class Test {

public void test() throws Exception{
File file = new File(“D:/file.xlsx”);
Workbook workbook = new Workbook(FileFormatType.XLSX);
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.get(0);
//LIMIT 65535
for (int i = 0; i < 65536; i++) {
final Cell c = worksheet.getCells().get(i, 1);
c.setValue(35);
final Range r = worksheet.getCells().createRange(i, 1, 1, 1);
r.setName(“range” + i);

// this way it works
// final Cell cFormula = worksheet.getCells().get(i, 2);
// cFormula.setFormula(“B” + (i+1) + “* 2”);

final Cell cNamedFormula = worksheet.getCells().get(i, 3);
cNamedFormula.setFormula(“range” + i + “* 2”);

}
// it fails here, but if comment this line will
workbook.calculateFormula(false);
OutputStream output = new FileOutputStream(file);
// also fails here too
workbook.save(output, new OoxmlSaveOptions(SaveFormat.XLSX));
}

}

thanks

Anderson Kruger
NeoGrid - Brazil
2

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue after running your sample code with the latest version: Aspose.Cells
for Java v8.5.0.2. The code throws exception when there are more than 65536 iterations. However, this might be some limitation of excel itself.

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-41396 - Calculating formulas not working when we have more than 65356 named cells

3

Hi,

Thanks for using Aspose.Cells for Java.

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

4

Hi,


Thanks for this patch, but now error is in save method.

Exception in thread “main” java.lang.ArrayIndexOutOfBoundsException: -1
at java.util.ArrayList.elementData(ArrayList.java:400)
at java.util.ArrayList.get(ArrayList.java:413)
at com.aspose.cells.NameCollection.get(Unknown Source)
at com.aspose.cells.zcep.b(Unknown Source)
at com.aspose.cells.zcep.a(Unknown Source)
at com.aspose.cells.zcep.a(Unknown Source)
at com.aspose.cells.zxy.a(Unknown Source)
at com.aspose.cells.Cell.u(Unknown Source)
at com.aspose.cells.zty.t(Unknown Source)
at com.aspose.cells.zty.a(Unknown Source)
at com.aspose.cells.ztw.p(Unknown Source)
at com.aspose.cells.ztw.b(Unknown Source)
at com.aspose.cells.ztw.g(Unknown Source)
at com.aspose.cells.ztw.a(Unknown Source)
at com.aspose.cells.ztv.a(Unknown Source)
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.save(Unknown Source)
at auladummy.CopyOfCopyOfTest.main(CopyOfCopyOfTest.java:42)


thanks

Anderson Kruger
5

Hi Anderson Kruger,


Thanks for your feedback.

I used the following sample code and I got “java.lang.ArrayIndexOutOfBoundsException” error on Workbook.save() method as you mentioned.
e.g
Sample code:

File file = new File(“f:/files/file1.xlsx”);
Workbook workbook = new Workbook(FileFormatType.XLSX);
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.get(0);
//LIMIT 65535
for (int i = 0; i < 65536; i++) {
final Cell c = worksheet.getCells().get(i, 1);
c.setValue(35);
final Range r = worksheet.getCells().createRange(i, 1, 1, 1);
r.setName(“range” + i);

// this way it works
// final Cell cFormula = worksheet.getCells().get(i, 2);
// cFormula.setFormula(“B” + (i+1) + “* 2”);

final Cell cNamedFormula = worksheet.getCells().get(i, 3);
cNamedFormula.setFormula(“range” + i + “* 2”);

}
// it fails here, but if comment this line will
workbook.calculateFormula(false);
OutputStream output = new FileOutputStream(file);
// also fails here too
workbook.save(output, new OoxmlSaveOptions(SaveFormat.XLSX));

I have logged a separate ticket with an id “CELLSJAVA-41405” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.


6

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

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