Skip to main content Skip to footer

SpreadJS Array Formulas

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. SpreadJSarray 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. SpreadJSArrayForm1 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. SpreadJSArrayFormula 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. SpreadJSTypeForm1 Formula typed in cell Then press Ctrl + Shift + Enter to change the formula to an array formula as shown in the following figure. SpreadJSTypeForm2 Complete Formula

MESCIUS inc.

comments powered by Disqus