We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Invalid max arguments in functions

Hi,

Excel specifications and limits states the following :
...
Calculation specifications and limits
  • Arguments in a function - 255

https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

But the following code throws an error:

@Test
public void maxFormulaArgsTest() throws Exception {
Workbook wb = new Workbook(FileFormatType.XLSX);
Worksheet worksheet = wb.getWorksheets().get(0);
Cells cells = worksheet.getCells();
StringBuilder sb = new StringBuilder("MAX(A1");
for (int i = 2; i <= 255; i++) {
sb.append(",A")
.append(i);
}
sb.append(")");

cells.get(0,0).setValue(1);
cells.get(1,0).setValue(7);
cells.get(2,0).setValue(17);

cells.get(1,1).setFormula(sb.toString());

wb.calculateFormula();

System.out.println(cells.get(1,1).getStringValue());

wb.save("c:/temp/tmp.xlsx");
}


Output looks the follows:

com.aspose.cells.CellsException: [0]Sheet1!B2-Invalid formula: More than one token in stack
...
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
...

To make tmp.xlsx file calculateFormula call should be commented out, when you open this file in MS Excel MAX function calc is perfomed without any errors.


Could you review and fix this issue?

Thanks.

Hi,


Thanks for providing us details and sample code.

After an initial test, I observed the issue as you mentioned by using your sample code. I found an exception “[0]Sheet1!B2-Invalid formula: More than one token in stack” on Workbook.calculateFormula() method". If we comment Workbook.calculateFormula call and generate the file, when I open the file into MS Excel manually, MAX function calculation is performed without any errors. I used the following sample code:
e.g
Sample code:

Workbook wb = new Workbook(FileFormatType.XLSX);
Worksheet worksheet = wb.getWorksheets().get(0);
Cells cells = worksheet.getCells();

StringBuilder sb = new StringBuilder("=MAX(A1");
for (int i = 2; i <= 255; i++) {
sb.append(",A")
.append(i);
}
sb.append(")");

cells.get(0,0).setValue(1);
cells.get(1,0).setValue(7);
cells.get(2,0).setValue(17);

cells.get(1,1).setFormula(sb.toString());
System.out.println(cells.get(1,1).getFormula());
wb.calculateFormula();//Exception


System.out.println(cells.get(1,1).getStringValue());

wb.save(“out1.xlsx”);

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

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

Thank you.
Hi,

Could you estimate time frame to resolve this issue?

Thanks.

Hi,

Thanks for your posting and using Aspose.Cells.

We are afraid, there is no update available for you regarding this ticket. However, your issue is in progress. We have logged your comment in our database for product team consideration and requested them to provide some ETA or fix for this issue. Once there is some news for you, we will let you know asap.

Hi,

Thanks for using Aspose.Cells.

It is to inform you that we have fixed your issue CELLSJAVA-41732 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

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


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