Skip to main content Skip to footer

Spread WPF and Array Formulas

Array formulas allow you to do complex tasks with formulas. They allow you to calculate the values of multiple cells at once and they are useful for summarizing a spreadsheet database. 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. Spread WPF supports array formulas. An array formula can perform multiple calculations on one or more items in an array. Array formulas can return multiple results or a single result. To create an array formula, select the cells you wish to put the formula in, type the formula, and then use Ctrl + Shift + Enter. This puts braces around the formula and places an instance of the formula in each cell of the selected range. In code, use the SetArrayFormula method instead of the SetFormula method. In order to remove or change an array formula, you must select the original formula range first. This example uses the SetArrayFormula method to create an array formula. sumarrary Result of Sample Code C#

gcSpreadSheet1.Sheets[0].Cells[0, 1].Text = "Value A";  
gcSpreadSheet1.Sheets[0].Cells[0, 2].Text = "Value B";  
gcSpreadSheet1.Sheets[0].Cells[0, 3].Text = "Result";  
gcSpreadSheet1.Sheets[0].Cells[0, 1].HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center;  
gcSpreadSheet1.Sheets[0].Cells[0, 2].HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center;  
gcSpreadSheet1.Sheets[0].Cells[0, 3].HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center;  
gcSpreadSheet1.Sheets[0].Cells[1, 1].Value = 4;  
gcSpreadSheet1.Sheets[0].Cells[1, 2].Value = 3;  
gcSpreadSheet1.Sheets[0].Cells[2, 1].Value = 2;  
gcSpreadSheet1.Sheets[0].Cells[2, 2].Value = 2;  
gcSpreadSheet1.Sheets[0].Cells[3, 1].Value = 5;  
gcSpreadSheet1.Sheets[0].Cells[3, 2].Value = 7;  
gcSpreadSheet1.Sheets[0].Cells[4, 1].Value = 6;  
gcSpreadSheet1.Sheets[0].Cells[4, 2].Value = 2;  
gcSpreadSheet1.Sheets[0].SetArrayFormula(1, 3, 4, 1, "B2:B5*C2:C5");

VB


GcSpreadSheet1.Sheets(0).Cells(0, 1).Text = "Value A"  
GcSpreadSheet1.Sheets(0).Cells(0, 2).Text = "Value B"  
GcSpreadSheet1.Sheets(0).Cells(0, 3).Text = "Result"  
GcSpreadSheet1.Sheets(0).Cells(0, 1).HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center  
GcSpreadSheet1.Sheets(0).Cells(0, 2).HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center  
GcSpreadSheet1.Sheets(0).Cells(0, 3).HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center  
GcSpreadSheet1.Sheets(0).Cells(1, 1).Value = 4  
GcSpreadSheet1.Sheets(0).Cells(1, 2).Value = 3  
GcSpreadSheet1.Sheets(0).Cells(2, 1).Value = 2  
GcSpreadSheet1.Sheets(0).Cells(2, 2).Value = 2  
GcSpreadSheet1.Sheets(0).Cells(3, 1).Value = 5  
GcSpreadSheet1.Sheets(0).Cells(3, 2).Value = 7  
GcSpreadSheet1.Sheets(0).Cells(4, 1).Value = 6  
GcSpreadSheet1.Sheets(0).Cells(4, 2).Value = 2  
GcSpreadSheet1.Sheets(0).SetArrayFormula(1, 3, 4, 1, "B2:B5*C2:C5")  

This example sums two columns if the values in Column B are less than five. arrayform2 Result of Sample Code C#

gcSpreadSheet1.Sheets[0].Cells[0, 1].Text = "Value A";  
gcSpreadSheet1.Sheets[0].Cells[0, 2].Text = "Value B";  
gcSpreadSheet1.Sheets[0].Cells[6, 0].Text = "Sum (B*C if B < 5)";  
gcSpreadSheet1.CanCellOverflow = true;  
gcSpreadSheet1.Sheets[0].Cells[0, 1].HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center;  
gcSpreadSheet1.Sheets[0].Cells[0, 2].HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center;  
gcSpreadSheet1.Sheets[0].Cells[0, 3].HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center;  
gcSpreadSheet1.Sheets[0].Cells[1, 1].Value = 4;  
gcSpreadSheet1.Sheets[0].Cells[1, 2].Value = 3;  
gcSpreadSheet1.Sheets[0].Cells[2, 1].Value = 2;  
gcSpreadSheet1.Sheets[0].Cells[2, 2].Value = 2;  
gcSpreadSheet1.Sheets[0].Cells[3, 1].Value = 5;  
gcSpreadSheet1.Sheets[0].Cells[3, 2].Value = 7;  
gcSpreadSheet1.Sheets[0].Cells[4, 1].Value = 6;  
gcSpreadSheet1.Sheets[0].Cells[4, 2].Value = 2;  
gcSpreadSheet1.Sheets[0].SetArrayFormula(6, 2, 1, 1, "Sum(IF(B2:B5<5,B2:B5*C2:C5))");  

VB

GcSpreadSheet1.Sheets(0).Cells(0, 1).Text = "Value A"  
GcSpreadSheet1.Sheets(0).Cells(0, 2).Text = "Value B"  
GcSpreadSheet1.Sheets(0).Cells(6, 0).Text = "Sum (B*C if B < 5)"  
GcSpreadSheet1.CanCellOverflow = True  
GcSpreadSheet1.Sheets(0).Cells(0, 1).HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center  
GcSpreadSheet1.Sheets(0).Cells(0, 2).HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center  
GcSpreadSheet1.Sheets(0).Cells(0, 3).HorizontalAlignment = GrapeCity.Windows.SpreadSheet.Data.CellHorizontalAlignment.Center  
GcSpreadSheet1.Sheets(0).Cells(1, 1).Value = 4  
GcSpreadSheet1.Sheets(0).Cells(1, 2).Value = 3  
GcSpreadSheet1.Sheets(0).Cells(2, 1).Value = 2  
GcSpreadSheet1.Sheets(0).Cells(2, 2).Value = 2  
GcSpreadSheet1.Sheets(0).Cells(3, 1).Value = 5  
GcSpreadSheet1.Sheets(0).Cells(3, 2).Value = 7  
GcSpreadSheet1.Sheets(0).Cells(4, 1).Value = 6  
GcSpreadSheet1.Sheets(0).Cells(4, 2).Value = 2  
GcSpreadSheet1.Sheets(0).SetArrayFormula(6, 2, 1, 1, "Sum(IF(B2:B5<5,B2:B5*C2:C5))")  

Users can enter a formula in a cell by starting with an equals sign (=). Type the equal sign and then the formula such as = sum(A1+B1). Use Ctrl + Shift + Enter to create an array formula after entering the formula. The following image was created by typing =B2:B5*C3:C5 in a cell and then using Ctrl + Shift + Enter to change the formula to an array formula. TypeFormula1 Array Formula You can double-click on the formula cell to see the array formula and selection area. TypeFormula Selection Area You can prevent or allow the user to enter formulas with the CanUserEditFormula property. C#

gcSpreadSheet1.CanUserEditFormula = true;

VB

GcSpreadSheet1.CanUserEditFormula = True

MESCIUS inc.

comments powered by Disqus