Problem adding formula referencing column > 255

[sjk1471]

Hi,

I received the following Exception: caused by:

java.lang.ArrayIndexOutOfBoundsException: -1

at java.util.ArrayList.get(ArrayList.java:324)

at com.aspose.cells.NameCollection.get(Unknown Source)

[...]

at com.aspose.cells.Workbook.save(Unknown Source)

We are attempting to write a large .xlsx file with many formulas. I tracked the root cause of the problem to the fact that I tracked the root cause of the problem to be that there are too many Names being added to the spreadsheet (>256*256).

However we are not trying to create that many Names since all our formulas only contain basic cell references using CellsHelper.cellIndexToName().

The problem is that any Cell references that reference a column after column 255 that are included in formulas get added to the spreadsheet as names. Please see the attached code to reproduce.

We are using the latest version of Aspose Cells (7.3.2.0).

Thanks for your help in this matter.

Simon Kelly
Agentrics UK ltd

[sjk1471]

package com.du.sjk.scrap;

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

import com.aspose.cells.Cell;
import com.aspose.cells.CellsHelper;
import com.aspose.cells.SaveFormat;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.aspose.cells.XlsSaveOptions;

public class AsposeXLSXWriteTest
{
public static void main(String[] args) throws Exception
{
System.out.println(CellsHelper.getVersion());//7.3.2.0

// TEST 1 *********************************************
Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(wb.getWorksheets().add());
Cell cell=ws.getCells().get(0, 0);
//will not be added as a name
cell.setFormula("SUM("+CellsHelper.cellIndexToName(0,1)+")");

//WILL be added as a name
cell.setFormula("SUM("+CellsHelper.cellIndexToName(0,257)+")");

System.out.println("names "+wb.getWorksheets().getNames().getCount());//1
for(int n = 0; n<wb.getWorksheets().getNames().getCount();n++){
System.out.println(wb.getWorksheets().getNames().get(n));// Aspose.Cells.Name [ IX1; ReferTo:null]
}

FileOutputStream fos = new FileOutputStream(new File("C:\\testbatchXLSX.xlsx"));
wb.save(fos, new XlsSaveOptions(SaveFormat.XLSX));
fos.close();

// TEST 2 *******************************************
wb = new Workbook();
ws = wb.getWorksheets().get(wb.getWorksheets().add());

for(int col=0;col<256;col++){
for(int row=0;row<257;row++){
cell=ws.getCells().get(row, col);
//will not be added as a name
cell.setFormula("SUM("+CellsHelper.cellIndexToName(row,col+256)+")");
}
}
System.out.println("names "+wb.getWorksheets().getNames().getCount());//65792

fos = new FileOutputStream(new File("C:\\testbatchXLSX.xlsx"));
wb.save(fos, new XlsSaveOptions(SaveFormat.XLSX));//java.lang.ArrayIndexOutOfBoundsException: -1
fos.close();

}
}
v

[shakeel.faiz]

Hi,

Thanks for your posting and using Aspose.Cells for Java.

Your problem might be occurring because Worbook by default is created with Xls (Excel2003) format. Xls format only supports 255 columns.

You need to instantiate and save your workbook in Xlsx (Excel2007/2010) format.

In order to save your workbook into Xlsx format, you need to use OoxmlSaveOptions object instead of XlsSaveOptions

So please change your following line

Workbook wb = new Workbook();

into this

Workbook wb = new Workbook(FileFormatType.XLSX);

Also change

wb.save(fos, new XlsSaveOptions(SaveFormat.XLSX));

into this

wb.save(fos, new OoxmlSaveOptions(SaveFormat.XLSX));

[sjk1471]

Hi Shakeel,

Thanks, your suggestion fixes this problem.

However, we would have preferred either a warning of using the wrong file format or a failure straight way when trying to add the formula rather than this strange behaviour of adding names. That way we might have picked up this issue in testing, before it went into Production.

Maybe you guys can take a look at this and find a more robust way of handling this situation.

Thanks again for your help,

Simon Kelly.

[shakeel.faiz]

Hi,

Thanks for your posting and your suggestion.

It’s good to know that your issue is resolved now.

We will keep your recommendation of handling such an issue in our mind.