Array Formulas

SpreadJS supports array formulas.

Use Ctrl + Shift + Enter to create an array formula after entering the formula at run time if the users are allowed to create formulas, or you can use the setArrayFormula method.

Array formulas are supported when exporting or importing to Excel-formatted files and JSON objects.

Array formulas have the following limitations:

• Array formulas are not supported in ranges that contain merged cells.

• The formula cannot be changed in part of the range.

• Cut and copy applies to the entire range.

• Columns and rows cannot be inserted or removed from part of the range.

The following code sample uses the setArrayFormula method.

activeSheet.getCell(1,1).value(3);
activeSheet.getCell(2,1).value(1);
activeSheet.getCell(3,1).value(3);
activeSheet.getCell(4,1).value(7);
activeSheet.getCell(1,2).value(7);
activeSheet.getCell(2,2).value(7);
activeSheet.getCell(3,2).value(7);
activeSheet.getCell(4,2).value(7);
spread.options.allowUserEditFormula = true;
activeSheet.setArrayFormula(0, 3, 4, 1, "B2:B5*C2:C5");