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
- Calculate and report initial values
- Clear value of Inputs!C5
- Calculate
- Report values.
- Set value of Inputs!C5 to 1
- Calculate
- Report values.
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();
}
}
}