What is limitation of number of columns in 2007 Excel format

Aspose.Cells Product Family
1

Hi,
What is the limitation of number of columns in single worksheet of 2007 Excel format?
The sheet I am going to generate is consisting more than 4068 columns.
It is throwing an exception :
com.aspose.cells.CellsException: Formula error. Cell[Patent1 To 322!EZQ11]: name index(-1) not found!

Thanks,
Vimlesh

2

Hi Vimlesh,

Well, there is no limitation in terms support for Excel 2007 format for Aspose.Cells for Java product. The component just supports the same as MS Excel (2007) does (Worksheet size = max 1,048,576 rows by max 16,384 columns). I have tested your scenario a bit using the following sample code with the attached version and it works fine.

Sample code:

Workbook workbook = new Workbook();
Worksheets worksheets = workbook.getWorksheets();

Worksheet worksheet = worksheets.getSheet(0);

for(int i = 0; i< 500; i++)
{
for(int j=0;j<5000;j++)
{
worksheet.getCells().getCell(i,j).setValue(i+j);

}


}


workbook.save(“d:\files\MyTestBook.xlsx”,FileFormatType.EXCEL2007);


Well, when filling large dataset into a sheet with large number of columns/rows, the process would surely require a lot of memory but there is not any known issue for it. You got to make sure that you have sufficient amount of memory assigned to your JVM too

I have also attached the latest fix for you if you can give it a try.

If you find any issue, kindly give us your sample code and template file(if you have) to reproduce the issue and we will check it asap.

Thank you.

3

Thanks for quick reply.
Yes, you are right. Number of columns is not creating a problem. Problem is with formula that is being set. If a formula being set in any cell is ‘EZQ11’. It is creating a problem while saving the workbook. This is one of the cases. May be there are many more column references, being used as formula, that are creating problems in being set.

Exception thrown in this case is :
java.io.IOException: Formula error. Cell[Patent1 To 322!EZQ11]: name index(-1) not found!
at com.aspose.cells.Workbook.save(Unknown Source)

Please look into this part of problem. May be the problem is related to formula being set in aspose java.

Thanks,
Vimlesh

4

Hi,

Thank you for considering Aspose.

Aspose.Cells does not support more than 256 columns (Excel 2003 max column count) in formulas. We have registered your required feature in our issue tracking system with the issue CELLSJAVA-14274. We will look into it and try to support it soon.

Thank you & Best Regards,

5

Hi,

Please try the attached version.

We have supported setting formulas that refer to row/column/parameters count that exceeds Excel 2003’s limit.

To remove the limit of Excel 2003 for row, column, and parameters count, please set the file format of Workbook to other file formats than EXCEL97TO2003. Code like following:

Workbook wb = new Workbook();
wb.setFileFormatType(FileFormatType.EXCEL2007);

0${
    Ollib$REVE_ALL-RevE:6\REF_K\  $$Con$8$@  $, :

    Cell cell = cells.getCell(0, 0);
    cell.setFormula("=EZQ11");

Thank you

6

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

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