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).
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)+")");
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();
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.