Problem with array formulas

olberg · November 12, 2009, 5:23am

Hi,

(This is related to an issue posted by hanneaustad )

The Inputs!C5 will a determin tha value of Calcs!M10
The Calcs!M10 is part of the array formula in Calcs!D23-25

Attached you will find two template files.

ArrayFormula1.xls having an initial empty value for Inputs!C5
ArrayFormula1.xls having an initial value 1 for Inputs!C5

Below you will find a test program which does:

Calculate and report initial values
Clear value of Inputs!C5
Calculate
Report values.
Set value of Inputs!C5 to 1
Calculate
Report values.

The problem is that some of the reported values for is wrong.

Test program:
-------------------
import java.io.FileInputStream;
import java.io.IOException;

import com.aspose.cells.AsposeLicenseException;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.License;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class ArrayFormula {

public static void main(String[] args) throws IOException, AsposeLicenseException {

License lic = new License();
lic.setLicense(new FileInputStream("lib/Aspose.Cells.lic"));

for (int i=1;i<3;i++){

String fileName = "Excel/ArrayFormula" + i + ".xls";
System.out.println();
System.out.println("Excel template: " + fileName);
System.out.println("---------------------------------------------");

Workbook workbook = new Workbook();

workbook.open(fileName);

Worksheet inputs = workbook.getWorksheets().getSheet("Inputs");
Worksheet calcs = workbook.getWorksheets().getSheet("Calcs");

workbook.calculateFormula();

System.out.println("Inputs!C5 in template file: " + inputs.getCells().getCell("C5").getValue());
System.out.println("Calcs!M10 in template file: " + calcs.getCells().getCell("M10").getValue());
System.out.println("Calcs!M11 in template file: " + calcs.getCells().getCell("M11").getValue());
System.out.println();
System.out.println("D21 (Excel 20): "+calcs.getCells().getCell("D21").getValue());
System.out.println("D22 (Excel 3): "+calcs.getCells().getCell("D22").getValue());
System.out.println("D23 (Excel 3): "+calcs.getCells().getCell("D23").getValue());
System.out.println("D24 (Excel 12): "+calcs.getCells().getCell("D24").getValue());
System.out.println("D25 (Excel 18): "+calcs.getCells().getCell("D25").getValue());
System.out.println("D26 (Excel 0): "+calcs.getCells().getCell("D26").getValue());
System.out.println("D27 (Excel 0): "+calcs.getCells().getCell("D27").getValue());

inputs.getCells().getCell("C5").clearContent();
workbook.calculateFormula();

System.out.println();
System.out.println("C5 is cleared");
System.out.println("Inputs!C5: " + inputs.getCells().getCell("C5").getValue());
System.out.println("Calcs!M10: " + calcs.getCells().getCell("M10").getValue());
System.out.println("Calcs!M11: " + calcs.getCells().getCell("M11").getValue());
System.out.println();
System.out.println("D21 (Excel 20): "+calcs.getCells().getCell("D21").getValue());
System.out.println("D22 (Excel 3): "+calcs.getCells().getCell("D22").getValue());
System.out.println("D23 (Excel 3): "+calcs.getCells().getCell("D23").getValue());
System.out.println("D24 (Excel 12): "+calcs.getCells().getCell("D24").getValue());
System.out.println("D25 (Excel 18): "+calcs.getCells().getCell("D25").getValue());
System.out.println("D26 (Excel 0): "+calcs.getCells().getCell("D26").getValue());
System.out.println("D27 (Excel 0): "+calcs.getCells().getCell("D27").getValue());

inputs.getCells().getCell("C5").setValue(1);

workbook.calculateFormula();

System.out.println();
System.out.println("C5 is set to 1");
System.out.println("Inputs!C5: " + inputs.getCells().getCell("C5").getValue());
System.out.println("Calcs!M10: " + calcs.getCells().getCell("M10").getValue());
System.out.println("Calcs!M11: " + calcs.getCells().getCell("M11").getValue());
System.out.println();
System.out.println("D21 (Excel 61): "+calcs.getCells().getCell("D21").getValue());
System.out.println("D22 (Excel 1): "+calcs.getCells().getCell("D22").getValue());
System.out.println("D23 (Excel 1): "+calcs.getCells().getCell("D23").getValue());
System.out.println("D24 (Excel 16): "+calcs.getCells().getCell("D24").getValue());
System.out.println("D25 (Excel 16): "+calcs.getCells().getCell("D25").getValue());
System.out.println("D26 (Excel 0): "+calcs.getCells().getCell("D26").getValue());
System.out.println("D27 (Excel 0): "+calcs.getCells().getCell("D27").getValue());

System.out.println();
}
}

}

amjad.sahi · November 12, 2009, 12:57pm

Hi,

Thanks for providing us the template files with sample code.

We found the issue after an initial test. Your issue has been logged into our issue tracking system with an issue id: CELLSJAVA-11757. We will figure it out.

Thank you.

olberg · November 13, 2009, 2:32am

There is a "bug" in the sample code but I guess you have already identified it.

In the first block (before we set the Inputs!C5)

Worksheet inputs = workbook.getWorksheets().getSheet("Inputs");
Worksheet calcs = workbook.getWorksheets().getSheet("Calcs");

workbook.calculateFormula();

System.out.println("Inputs!C5 in template file: " + inputs.getCells().getCell("C5").getValue());
System.out.println("Calcs!M10 in template file: " + calcs.getCells().getCell("M10").getValue());
System.out.println("Calcs!M11 in template file: " + calcs.getCells().getCell("M11").getValue());
System.out.println();
System.out.println("D21 (Excel 20): "+calcs.getCells().getCell("D21").getValue());
System.out.println("D22 (Excel 3): "+calcs.getCells().getCell("D22").getValue());
System.out.println("D23 (Excel 3): "+calcs.getCells().getCell("D23").getValue());
System.out.println("D24 (Excel 12): "+calcs.getCells().getCell("D24").getValue());
System.out.println("D25 (Excel 18): "+calcs.getCells().getCell("D25").getValue());
System.out.println("D26 (Excel 0): "+calcs.getCells().getCell("D26").getValue());
System.out.println("D27 (Excel 0): "+calcs.getCells().getCell("D27").getValue());

Here the expected excel values (Excel ##) is only valid for one of the template files.

Sorry

nausherwan.aslam · November 16, 2009, 5:59am

Hi,

Thank you for considering Aspose.

Please try the attached latest version. We have fixed your mentioned issue.

Thank You & Best Regards,

olberg · November 16, 2009, 10:27am

Now it works - thank you

aspose.notifier · November 18, 2009, 6:47am

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

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

olberg · November 28, 2011, 10:52am

This problem is not solved for Aspose version : 7.0.2.0

Test program converted to new API:
--------------------------------------------

package com.ec.frmw.test.aspose;
import java.io.FileInputStream;

import com.aspose.cells.Cell;
import com.aspose.cells.CellValueType;
import com.aspose.cells.License;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class ArrayFormula {

public static void main(String[] args) throws Exception {

License lic = new License();
lic.setLicense(new FileInputStream(“lib/Aspose.Cells.lic”));

for (int i = 1; i < 3; i++) {

String fileName = “Excel/ArrayFormula” + i + “.xls”;
System.out.println();
System.out.println(“Excel template: " + fileName);
System.out.println(”---------------------------------------------");

Workbook workbook = new Workbook(fileName);

// workbook.open(fileName);
Worksheet inputs = workbook.getWorksheets().get(“Inputs”);
Worksheet calcs = workbook.getWorksheets().get(“Calcs”);

workbook.calculateFormula();

System.out.println("Inputs!C5 in template file: " + inputs.getCells().get(“C5”).getValue());
System.out.println("Calcs!M10 in template file: " + calcs.getCells().get(“M10”).getValue());
System.out.println("Calcs!M11 in template file: " + calcs.getCells().get(“M11”).getValue());
System.out.println("Celltypes: " );
System.out.println("Celltype BOOLEAN: " + CellValueType.IS_BOOL);
System.out.println("Celltype DATE_TIME: " + CellValueType.IS_DATE_TIME);
System.out.println("Celltype ERROR: " + CellValueType.IS_ERROR);
System.out.println("Celltype NULL: " + CellValueType.IS_NULL);
System.out.println("Celltype NUMERIC: " + CellValueType.IS_NUMERIC);
System.out.println("Celltype STRING: " + CellValueType.IS_STRING);
System.out.println("Celltype UNKNOWN: " + CellValueType.IS_UNKNOWN);
System.out.println();
System.out.println("D21 Cell Type: " + calcs.getCells().get(“D21”).getType());
System.out.println("D21: " + calcs.getCells().get(“D21”).getValue());
System.out.println("D22 Cell Type: " + calcs.getCells().get(“D22”).getType());
System.out.println("D22: " + calcs.getCells().get(“D22”).getValue());
System.out.println("D23 Cell Type: " + calcs.getCells().get(“D23”).getType());
System.out.println("D23: " + calcs.getCells().get(“D23”).getValue());
System.out.println("D24 Cell Type: " + calcs.getCells().get(“D24”).getType());
System.out.println("D24: " + calcs.getCells().get(“D24”).getValue());
System.out.println("D25 Cell Type: " + calcs.getCells().get(“D25”).getType());
System.out.println("D25: " + calcs.getCells().get(“D25”).getValue());
System.out.println("D26 Cell Type: " + calcs.getCells().get(“D26”).getType());
System.out.println("D26: " + calcs.getCells().get(“D26”).getValue());
System.out.println("D27 Cell Type: " + calcs.getCells().get(“D27”).getType());
System.out.println("D27: " + calcs.getCells().get(“D27”).getValue());

Cell c5 = inputs.getCells().get(“C5”);
inputs.getCells().clearContents(c5.getRow(), c5.getColumn(), c5.getRow(), c5.getColumn());
workbook.calculateFormula();

System.out.println();
System.out.println(“C5 is cleared”);
System.out.println("Inputs!C5: " + inputs.getCells().get(“C5”).getValue());
System.out.println("Calcs!M10: " + calcs.getCells().get(“M10”).getValue());
System.out.println("Calcs!M11: " + calcs.getCells().get(“M11”).getValue());
System.out.println();
System.out.println("D21 (Excel 20): " + calcs.getCells().get(“D21”).getValue());
System.out.println("D22 (Excel 3): " + calcs.getCells().get(“D22”).getValue());
System.out.println("D23 (Excel 3): " + calcs.getCells().get(“D23”).getValue());
System.out.println("D24 (Excel 12): " + calcs.getCells().get(“D24”).getValue());
System.out.println("D25 (Excel 18): " + calcs.getCells().get(“D25”).getValue());
System.out.println("D26 (Excel 0): " + calcs.getCells().get(“D26”).getValue());
System.out.println("D27 (Excel 0): " + calcs.getCells().get(“D27”).getValue());

inputs.getCells().get(“C5”).setValue(1);

workbook.calculateFormula();

System.out.println();
System.out.println(“C5 is set to 1”);
System.out.println("Inputs!C5: " + inputs.getCells().get(“C5”).getValue());
System.out.println("Calcs!M10: " + calcs.getCells().get(“M10”).getValue());
System.out.println("Calcs!M11: " + calcs.getCells().get(“M11”).getValue());
System.out.println();
System.out.println("D21 (Excel 61): " + calcs.getCells().get(“D21”).getValue());
System.out.println("D22 (Excel 1): " + calcs.getCells().get(“D22”).getValue());
System.out.println("D23 (Excel 1): " + calcs.getCells().get(“D23”).getValue());
System.out.println("D24 (Excel 16): " + calcs.getCells().get(“D24”).getValue());
System.out.println("D25 (Excel 16): " + calcs.getCells().get(“D25”).getValue());
System.out.println("D26 (Excel 0): " + calcs.getCells().get(“D26”).getValue());
System.out.println("D27 (Excel 0): " + calcs.getCells().get(“D27”).getValue());

System.out.println();
}
}

}
----------------------------------------------

Output:
----------------------------------------------

=======================================
>>> Aspose version : 7.0.2.0
---------------------------------------

Excel template: Excel/ArrayFormula1.xls
---------------------------------------------
Inputs!C5 in template file: null
Calcs!M10 in template file: Jones
Calcs!M11 in template file: Fax
Celltypes:
Celltype BOOLEAN: 0
Celltype DATE_TIME: 1
Celltype ERROR: 2
Celltype NULL: 3
Celltype NUMERIC: 4
Celltype STRING: 5
Celltype UNKNOWN: 6

D21 Cell Type: 4
D21: 20.0
D22 Cell Type: 2
D22: #N/A
D23 Cell Type: 4
D23: 3.0
D24 Cell Type: 4
D24: 12.0
D25 Cell Type: 4
D25: 18.0
D26 Cell Type: 4
D26: 0.0
D27 Cell Type: 4
D27: 0.0

C5 is cleared
Inputs!C5: null
Calcs!M10: Jones
Calcs!M11: Fax

D21 (Excel 20): 20.0
D22 (Excel 3): #N/A
D23 (Excel 3): 3.0
D24 (Excel 12): 12.0
D25 (Excel 18): 18.0
D26 (Excel 0): 0.0
D27 (Excel 0): 0.0

C5 is set to 1
Inputs!C5: 1
Calcs!M10: Brown
Calcs!M11: Fax

D21 (Excel 61): 61.0
D22 (Excel 1): #N/A
D23 (Excel 1): 1.0
D24 (Excel 16): 16.0
D25 (Excel 16): 16.0
D26 (Excel 0): 0.0
D27 (Excel 0): 0.0

Excel template: Excel/ArrayFormula2.xls
---------------------------------------------
Inputs!C5 in template file: 1.0
Calcs!M10 in template file: Brown
Calcs!M11 in template file: Fax
Celltypes:
Celltype BOOLEAN: 0
Celltype DATE_TIME: 1
Celltype ERROR: 2
Celltype NULL: 3
Celltype NUMERIC: 4
Celltype STRING: 5
Celltype UNKNOWN: 6

D21 Cell Type: 4
D21: 61.0
D22 Cell Type: 2
D22: #N/A
D23 Cell Type: 4
D23: 1.0
D24 Cell Type: 4
D24: 16.0
D25 Cell Type: 4
D25: 16.0
D26 Cell Type: 4
D26: 0.0
D27 Cell Type: 4
D27: 0.0

C5 is cleared
Inputs!C5: null
Calcs!M10: Jones
Calcs!M11: Fax

D21 (Excel 20): 20.0
D22 (Excel 3): #N/A
D23 (Excel 3): 3.0
D24 (Excel 12): 12.0
D25 (Excel 18): 18.0
D26 (Excel 0): 0.0
D27 (Excel 0): 0.0

C5 is set to 1
Inputs!C5: 1
Calcs!M10: Brown
Calcs!M11: Fax

D21 (Excel 61): 61.0
D22 (Excel 1): #N/A
D23 (Excel 1): 1.0
D24 (Excel 16): 16.0
D25 (Excel 16): 16.0
D26 (Excel 0): 0.0
D27 (Excel 0): 0.0

amjad.sahi · November 28, 2011, 12:11pm

Hi,

Thanks for pointing it out.

It might be a regression bug in the new versions. I have reopened your issue “CELLSJAVA-11757” again. We will soon figure it out.

Thank you.

amjad.sahi · December 1, 2011, 5:28am

Hi,

Please try the attached version/fix: v7.0.3.3.

We have fixed the issue regarding array formulas.

Thank you.

amjad.sahi · December 20, 2011, 5:18am

The issues you have found earlier have been fixed in (v7.0.4 ) this update.