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

Free Support Forum - aspose.com

Formula on array values: not working?

Hello,

Please find attached an Excel file.
Cells A1 to A5 are an array {={6;7;8;9;10}}. Cell C3 is =sum(A1:A5). I have disabled the automatic calculation in Excel, so the cell C3 does not reflect the actual sum: 15 is displayed instead of 40.

Following is the java code I execute (using Aspose.Cells for Java 2.1.1.6):

package poc;

import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheets;

public class PocXLSX {

private void run() throws Exception {
Workbook wb = new Workbook();
wb.open(“matrix.xlsx”);
Worksheets worksheets = wb.getWorksheets();

System.out.println(“Cells A1:A5”);
for (int i = 0; i<5; i++) {
System.out.println(worksheets.getSheet(“Sheet1”).getCells().getCell(i, 0).getValue());
}
System.out.println(“Cell C3”);
System.out.println(worksheets.getSheet(“Sheet1”).getCells().getCell(“C3”).getValue());

System.out.println("\nCalculation\n");
wb.calculateFormula();

System.out.println(“Cells A1:A5”);
for (int i = 0; i<5; i++) {
System.out.println(worksheets.getSheet(“Sheet1”).getCells().getCell(i, 0).getValue());
}

System.out.println(“Cell C3”);
System.out.println(worksheets.getSheet(“Sheet1”).getCells().getCell(“C3”).getValue());
}


public static void main(String[] args) throws Exception {
new PocXLSX().run();
}
}

And here is the result in standard output:
Cells A1:A5
6.0
7.0
8.0
9.0
10.0
Cell C3
15.0

Calculation

Cells A1:A5
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
Cell C3
#NAME?

Values are correctly read once. Then after wb.calculateFormula() is called the array is lost.

Have I done something wrong? Aren’t the array formulas supported in Aspose.Cells for Java ?

Hi,

Thanks for the sample code and file.

Well, I am afraid, array formulas are not parsed fine, so you are getting this issue. We have already logged it into our issue tracking system with an id: CELLSJAVA-18342. Once we sort it out, we will notify you here.

Thank you.

Any idea when such a fix can be made available?

Hi,

We will check if we can provide you an eta for the fix.

Thank you.

Hi,

Well, it is a bit complex feature, we will try to support it in about one month’s time.

Thanks for your understanding!

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


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