Array formulas allow you to do complex tasks with formulas. They allow you to calculate the values of multiple cells at once. You can replace multiple standard formulas with a single array formula. For general information about array formulas, refer to this web site: https://support.office.com/en-au/article/Guidelines-and-examples-of-array-formulas-3be0c791-3f89-4644-a062-8e6e9ecee523. 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.
This example uses the setArrayFormula method. Array Formula JavaScript
activeSheet.getCell(0,1).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(0,2).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(0,3).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(0,3).text("Result");
activeSheet.getCell(0,1).text("Column B");
activeSheet.getCell(0,2).text("Column C");
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.canUserEditFormula(true);
activeSheet.setArrayFormula(1, 3, 4, 1, "B2:B5*C2:C5");
This example creates an array formula with the setArrayFormula method. Array Formula JavaScript
activeSheet.getCell(1,0).text("Quantity");
activeSheet.getCell(2,0).text("Price");
activeSheet.getCell(4,0).text("Total Value");
activeSheet.getColumn(0).width(80);
activeSheet.getCell(1,1).value(445);
activeSheet.getCell(1,2).value(100);
activeSheet.getCell(2,1).value(15);
activeSheet.getCell(2,2).value(30);
activeSheet.setArrayFormula(4, 2, 1, 1, "SUM(B2:C2*B3:C3)");
This example creates an array formula with a condition. Array Formula with Condition JavaScript
activeSheet.getCell(0, 1).text("Value B");
activeSheet.getCell(0, 2).text("Value C");
activeSheet.getCell(6, 0).text("Sum (B*C if B < 5)");
activeSheet.getColumn(0).width(120);
spread.canUserEditFormula(true);
activeSheet.getCell(0,1).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(0,2).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(0,3).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(1,1).value(4);
activeSheet.getCell(1,2).value(3);
activeSheet.getCell(2,1).value(2);
activeSheet.getCell(2,2).value(2);
activeSheet.getCell(3,1).value(5);
activeSheet.getCell(3,2).value(7);
activeSheet.getCell(4,1).value(6);
activeSheet.getCell(4,2).value(2);
activeSheet.setArrayFormula(6, 2, 1, 1, "Sum(IF(B2:B5<5,B2:B5*C2:C5))");
You can use Ctrl + Shift + Enter to create an array formula when using the formula text box. The formula text box is displayed when editing or creating formulas at run time. Type the formula as shown in the following figure. Formula typed in cell Then press Ctrl + Shift + Enter to change the formula to an array formula as shown in the following figure. Complete Formula