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 ?