Introduction
The most valuable feature offered by Aspose.Grid.Web is its support for using formulas or functions in worksheets. Aspose.Grid.Web has its own Formula Engine that calculates all formulas in worksheets. Aspose.Grid.Web supports both built-in and user defined functions or formulas. In this topic, we will discuss in detail about adding formulas to cells using Aspose.Grid.Web API.
Adding Formulas to Cells
How to Add & Calculate a Formula?
Developers can add, access or modify formulas in cells by using the Formula property of a cell. Aspose.Grid.Web supports user defined formulas ranging from simple to complex ones. However, a large number of built-in functions or formulas (similar to MS Excel) are also supplied with Aspose.Grid.Web. To see the full list of these built-in functions, please refer to List of Supported Functions.
NOTE: The formula syntax should be compatible with MS Excel sytanx. For example, all formulas must begin with an Equal (=) sign. If you a developer and want to add a formula programmatically then even if you don't use an (=) sign before your formula, Aspose.Grid.Web will recognize it as a formula but if you are an end user and want to add formula in GUI mode then you must have to use an (=) sign before adding your formula.
Example:
[C#]
//Accessing the worksheet of the Grid that is currently active
WebWorksheet sheet=GridWeb1.WebWorksheets[GridWeb1.ActiveSheetIndex];
//Putting some values to cells
sheet.Cells["A1"].PutValue("1st Value");
sheet.Cells["A2"].PutValue("2nd Value");
sheet.Cells["A3"].PutValue("Sum");
sheet.Cells["B1"].PutValue(125.56);
sheet.Cells["B2"].PutValue(23.93);
//Adding a simple formula to "B3" cell
sheet.Cells["B3"].Formula="=SUM(B1:B2)";
[VB.NET]
'Accessing the worksheet of the Grid that is currently active
Dim sheet As WebWorksheet = GridWeb1.WebWorksheets(GridWeb1.ActiveSheetIndex)
'Putting some values to cells
sheet.Cells("A1").PutValue("1st Value")
sheet.Cells("A2").PutValue("2nd Value")
sheet.Cells("A3").PutValue("Sum")
sheet.Cells("B1").PutValue(125.56)
sheet.Cells("B2").PutValue(23.93)
'Adding a simple formula to "B3" cell
sheet.Cells("B3").Formula="=SUM(B1:B2)"
The output of the above code snippet is shown below in the figure:
|
Figure: Formula added to B3 cell but not calculated by GridWeb
|
In the above screenshot, you can see that formula is added to B3 cell but its not calculated yet. To calculate all formulas, developers would need to call RunAllFormulas method of WebWorksheets collection of GridWeb control after adding formulas to worksheets as shown below:
Example:
[C#]
//Calculating all formulas added in worksheets
GridWeb1.WebWorksheets.RunAllFormulas();
[VB.NET]
'Calculating all formulas added in worksheets
GridWeb1.WebWorksheets.RunAllFormulas()
NOTE: Users can also calculate formulas by themselves by clicking Submit button as shown below in the figure:
|
Figure: Clicking Submit button of GridWeb
|
IMPORTANT: If a user clicks Save, Undo buttons or Sheet Tabs, all formulas are also calculated by GridWeb automatically.
Finally, you would be able to see the cacluated result of the fomula as shown below:
|
Figure: Formula result after calculation
|
Referencing Cells from Other Worksheets
Using Aspose.Grid.Web, developers can also refer values (stored in cells of different worksheets) in their formulas to create more complex formulas as shown below:
Example:
[C#]
//Accessing the worksheet of the Grid that is currently active
WebWorksheet sheet=GridWeb1.WebWorksheets[GridWeb1.ActiveSheetIndex];
//Adding a bit complex formula to "A1" cell
sheet.Cells["A1"].Formula="=SUM(F1:F7)/ AVERAGE (E1:E7)-Sheet1!C6";
[VB.NET]
'Accessing the worksheet of the Grid that is currently active
Dim sheet As WebWorksheet = GridWeb1.WebWorksheets(GridWeb1.ActiveSheetIndex)
'Adding a simple formula to "A1" cell
sheet.Cells("A1").Formula="=SUM(F1:F7)/ AVERAGE (E1:E7)-Sheet1!C6"
From the above code snippet, developers may find that the syntax of referencing a cell value from a different worksheet is SheetName!CellName.
Provides a list of all functions supported by Aspose.Grid.
7/26/2006 7:16:56 PM - -210.56.19.13