Formula problem

Aspose.Cells Product Family
1

Hello,

Following formula doesn't calculate in my workbook.

=IF(ISERROR(INDEX($L$4:$BL$4;1;MATCH(0;K7:BL7;1)));"";INDEX($L$4:$BL$4;1;MATCH(0;K7:BL7;1)))

When I go into the sheet and edit the cell (F2) and press Enter, the formula calculates.

Can you have a look at it ?

2
Hi,

Thanks for considering Aspose.

I think you are using Aspose.Cells for Java. I tried the following code with the latest version / fix and it works fine, I check the output file and the formula is calculated fine:

Workbook workbook = new Workbook();
com.aspose.cells.Cell cell1 = workbook.getWorksheets().getSheet(0).getCells().getCell("B7");
cell1.setFormula("=IF(ISERROR(INDEX($L$4:$BL$4,1,MATCH(0,K7:BL7,1))),\"case1\",INDEX($L$4:$BL$4,1,MATCH(0,K7:BL7,1)))");
workbook.save("e:\\Files\\out_formulabk.xls");

Kindly try to replace " ; " with " , " in your formula.

Which version you are using?

Thank you.

3

I am using a ',' iso ';' but without result.

Is there a limitation in use of formula's in a sheet.

I get following error when creating a sheet with a lot of fomula's:

Error : Java throwable -2143288121 when inserting a formula at row 5706 --> =BC5706-BD5705

Thanks !

4

Are you using the latest v1.9.3?

5

I'm using a fix from 21/01/2008.

Can I see the version from the jar-file ?

6

V1.9.3 is released on 25/01/2008. You can try it. We will also check your reported problems.

7

Hi,

I have tested your formula, and created more than 10000 formulas in a sheet, Aspose.Cells works fine. Please try our latest version, if the issue still persists, please give us your template and code to reproduce the issue, that will help us to figure it out soon, thank you.

To get version of Aspose.Cells, you can simply use System.out.println(CellsHelper.getReleaseVersion());.

8

Hello,

The problem is located within aspose !

I reduced the number of formula's and everything works fine !

When I'm working with +- 350.000 formula's everything works fine, but when I try to use approximatly 550.000 formula's the system crashes !

Could you please have another look ?

I'm using version 1.9.2.8, should this make a big difference ?

Thanks.

9

Hi,

Please try the latest version of Aspose.Cells for Java downloading @:

and give us your feedback. If you still find any issue, post your template file with sample code, we will check it soon.

Thank you.

10

Hello,

I downloaded the latest fix, but still encounter the same problem !

Is there a limit in objectreferences ???

11

Hi,

Thanks for considering Aspose.

Could you create a sample test code with template file (if you have) to reproduce the issue. It will help us to figure out the issue soon.

Thank you.

12

I don't have a template.

I'm building a sheet from scratch.

I have a loop (6000 times)
In this loop I create a data-row (containing 55 data cells)
The second row is based on previous row where formula cell[x][y] = cell[x][y-1]-cell[x-1][y]
The third row is based on the second (again formula's)

See attachement

Thanks

13

Hi

I have tested with your file "Formulaxlt.xls" by following code and the attachment in this post is the result created by it, I havn't found this issue:

Workbook workbook = new Workbook();
workbook.open("Formulaxlt.xls");
Cells cells = workbook.getWorksheets().getSheet(0).getCells();
for(int i=5; i<10000; i++)
{
for(int j=1; j<55; j++)
{
Cell cell = cells.getCell(i, j);
cell.setFormula("="+CellsHelper.convertCellIndexToName(i, j-1)+"-"+CellsHelper.convertCellIndexToName(i-1, j));
}
}
workbook.save("out_formulabk.xls");

However, so many formulas will require lots of memory, please make sure you have given enough memory to JVM(i think you need to use -Xmx to specify more memory than default for JVM).

And if my code doesn’t simulate your case properly, please post your sample code here, which will help us to get the workaround and figure the issue out. Thank you.

14

Hello,

I'm still experiencing troubles when I use formula's.

I tried to analyse the JVM. Attached the result.

What am I doing wrong ??

Thanks

15

Hi,

Do you mean that the system crash problem with 550000 formulas still persists?

From some other posts of you, I think maybe you use Aspose.Cells for Java in RPGLE, which I am not familiar with, would you please pick out the code using API of Aspose.Cells for Java from your RPGLE code and run it by JVM stand alone to check whether the problem still persists? Or, please create and send us a sample project including code and template to simulate your case, we will check it soon. Otherwise, just as my prior post, we tested with some code and can't find this problem.

16

Yes, we still have this problem.

I think this problem occurs when a formula in one cell points to a cell on another sheet or not near by this cell.

I'll send you a sample code and created workbook.

Thanks