You can use array formulas in Spread.
An array formula is a formula that can perform multiple calculations on one or more items in an array. Array formulas can return multiple results or a single result.
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 SetFormulaArray method.
The following image displays an array formula in the Total column.
Using Code
The following example creates an array formula.
| C# |
Copy Code
|
|---|---|
| fpSpread1.ActiveSheet.ColumnHeader.Cells[0, 0].Text = "Units"; fpSpread1.ActiveSheet.ColumnHeader.Cells[0, 1].Text = "Price"; fpSpread1.ActiveSheet.ColumnHeader.Cells[0, 2].Text = "Total"; fpSpread1.ActiveSheet.Cells[0, 0].Value = 2; fpSpread1.ActiveSheet.Cells[1, 0].Value = 5; fpSpread1.ActiveSheet.Cells[2, 0].Value = 1; fpSpread1.ActiveSheet.Cells[3, 0].Value = 9; fpSpread1.ActiveSheet.Cells[0, 1].Value = 40; fpSpread1.ActiveSheet.Cells[1, 1].Value = 100; fpSpread1.ActiveSheet.Cells[2, 1].Value = 25; fpSpread1.ActiveSheet.Cells[3, 1].Value = 80; fpSpread1.AllowUserFormulas = true; fpSpread1.ActiveSheet.SetFormulaArray(0, 2, 3, 2, "A1:A4*B1:B4"); |
|
| VB |
Copy Code
|
|---|---|
| fpSpread1.ActiveSheet.ColumnHeader.Cells(0, 0).Text = "Units" fpSpread1.ActiveSheet.ColumnHeader.Cells(0, 1).Text = "Price" fpSpread1.ActiveSheet.ColumnHeader.Cells(0, 2).Text = "Total" fpSpread1.ActiveSheet.Cells(0, 0).Value = 2 fpSpread1.ActiveSheet.Cells(1, 0).Value = 5 fpSpread1.ActiveSheet.Cells(2, 0).Value = 1 fpSpread1.ActiveSheet.Cells(3, 0).Value = 9 fpSpread1.ActiveSheet.Cells(0, 1).Value = 40 fpSpread1.ActiveSheet.Cells(1, 1).Value = 100 fpSpread1.ActiveSheet.Cells(2, 1).Value = 25 fpSpread1.ActiveSheet.Cells(3, 1).Value = 80 fpSpread1.AllowUserFormulas = True fpSpread1.ActiveSheet.SetFormulaArray(0, 2, 3, 2, "A1:A4*B1:B4") |
|