About Infragistics WebCalcManager

Add Excel formula features to your ASP.NET Applications.

Infragistics WebCalcManager adds Microsoft Excel formula support to ASP.NET controls including WebGrid and editors. Infragistics WebCalcManager includes over 100 formulas, a formula builder, custom formulas, etc. Infragistics WebCalcManager adds Microsoft Excel formula support to NetAdvantage ASP.NET controls as well as ordinary ASP.NET controls like TextBox. Infragistics WebCalcManager supports Internet Explorer 4.0 and up, and Mozilla Firefox 1.0 and up (both Windows and Mac).

Infragistics WebCalcManager Features

Using the CalcManager - After placing an WebGrid on a form, the user turns to the WebGrid designer and adds an unbound column. One of the column properties is the new Formula property. The user chooses the Formula property and clicks the ellipsis (...) button which brings up the Formula Designer. The Formula Designer then checks for an instance of WebCalcManager already created on the form. If there are none present, then a new instance is added to the form. You are then prompted before the addition of an WebCalcManager component is made on the form. The WebCalcManager component traverses all the controls on the form, querying each control to see if it has implemented IUltraCalcParticipant and sets the CalcManager property on the interface to itself. In this manner, the WebGrid control on the form obtains a reference to an IUltraCalcManager. This reference, in turn, is serialized, so the WebGrid has a reference to the IUltraCalcManager at run-time.

Once the Formula Designer finishes adding the WebCalcManager component on the form, it displays the dialog for entering a formula. The Formula Designer allows the user to select from a list of pre-defined named references (and other column references) of the WebGrid, as well as a list of functions by category.

When you are finished entering the formula, the Designer compiles the formula to check if there is a problem with its syntax. If there is a problem, then you are made aware that the formula is invalid. So, Once you have correctly entered a valid formula, the Formula property of the column is set to its string representation (which then gets serialized with the form).

Creating User-Defined Functions- User-defined functions are defined by creating one-or-more classes that derive from the UltraCalcUserDefinedFunction class. There are abstract properties and methods that must be overridden to supply a unique name for the function, its category and parameter descriptions, as well as the actual function implementation. These function classes can be registered in one of the following three (3) ways:

  • Each class can be individually registered at run-time by creating an instance of the class and passing it into the AddFunction method of WebCalcManager.
  • All the classes derived from UltraCalcUserDefinedFunction in an assembly can be registered at run-time in a single call to the AddLibrary method of WebCalcManager.
  • All the classes derived from UltraCalcUserDefinedFunction in an assembly can be registered at design-time by adding a reference to the assembly to the solution and then identifying which assembly/assemblies contain user-defined functions. The WebCalcManager's designer will provide a UI for identifying these assemblies. It will then be the WebCalcManager's responsibility to register the user-defined functions in these assemblies at both design and run-time.

What Happens At Run-Time - Let us say that a developer has entered formulas on various columns in an WebGrid during design-time. These formulas are serialized out as statements in the InitializeComponent of the form. There might, however, not always be a statement that assigns an UltraCalcManager to the CalcManager property of the WebGrid in the InitializeComponent. Here are the scenarios that can arise:

When the application is run, the WebGrid waits for the initialization to complete and for the DataSource to be set. Typically, the DataSource is set at design-time; however, if it is set at run-time, then the WebGrid will not compile any formulas or add any references to the WebCalcManager until the DataSource it set. When the DataSource is set and the initialization completeness has been detected (which the WebGrid can synchronously detects using ISupportsInitialize.EndInit and the set of the DataSource property), the WebGrid goes on to compile the formulas and adds them (along with various references) to the WebCalcManger.

Even when the initialization is complete and the DataSource is set on the WebGrid, the CalcManager property of the WebGrid may not have been set. This situation would arise if the developer deletes the WebCalcManager component from the form's component tray. In such a situation, WebGrid simply ignores the formulas on the column and displays the cell values in the cells (with no error messages displayed). When the developer sets the CalcManager to an WebCalcManager, the WebGrid compiles the formulas and adds them (along with various references) to the WebCalcManager's calc network.

Once the formulas and references are added, the WebCalcManager will synchronously (or asynchronously) evaluate the formulas and set the values on the cell references. Until the cell values for formula column cells are calculated, they display a message indicating that the cell values are being calculated. Whenever the UltraCalcEngine calculates a formula for a cell, it sets the result to the cell reference Value property. If there is an error evaluating the formula, then the UltraCalcEngine sets the reference Value property to an instance of UltraCalcValue that represents an error. If the cell reference Value is set to an error value, then it calls RaiseFormulaError from the IUltraCalcManager which, in turn, raises the FormulaCalculationError event of the WebCalcManager. The developer can hook-into this event and display a message box or take some other action. One of the options that the FormulaError event provides is the ability to specify the error message to display in the cell via the ErrorDisplayValue property on the FormulaErrorEventArgs. After the RaiseFormulaError returns, the WebGrid takes the ErrorDisplayValue setting and stores it on the cell reference, and uses that to render the cell on subsequent cell paints.

If the formula evaluation has completed successfully, then the UltraCalcEngine sets the Value property of the cell reference to the result. The cell reference then stores the value and renders the cell using that value upon subsequent paints.
Cells in a column with a formula will be made read-only, since it is not desirable (under any perceivable circumstance) for the values of such cells to be editable. Since the results of the column formula evaluation may not necessarily be the same type as the cell's column, the default embeddable editor (which we base on the column's DataType) may not be able to render the results of formula evaluations. Due to this fact, the WebGrid will always use EditorWithText to render the cells of columns with formulas. This is not a significant restriction as one would initially think, since cells are read-only. Note: that if a user sets the EditorControlID property of a formula column to an editor, then the cells in that column will always use that editor to render cell values.

Assigning Formulas At Run-Time - Under certain circumstances, users may opt for adding formulas at run-time without using the Designer. One example of when you would not make use of the Designer is when there is no data source available at design-time. The following use cases describes how to use formula functionality at run-time:

Assign the Formula property on one (or more) columns. In order for the WebGrid to compile and evaluate this formula, it needs a reference to the IUltraCalcManager instance, which it gets through the CalcManager property it exposes. At this point the CalcManager property is null, so the WebGrid simply stores the formulas into the columns and takes no other action. The next step for the developer is to assign the CalcManager property of the WebGrid to a valid instance of the WebCalcManager component. This, of course, requires the application to have a reference to the assembly that defines WebCalcManager, namely Infragistics.Web.UltraCalcEngine. If you don't set the CalcManager property to an instance of WebCalcManager and run the application, the WebGrid will ignore the Formulas and will display the cell values in the cells of the columns. If the developer assigns the CalcManager property on the WebGrid to an WebCalcManager and runs the application, the WebGrid will compile the formula using the CalcManager, evaluate it and display the results in the cells. If the formula has a syntax error, then the WebGrid raises the FormulaError event on the WebCalcManager by calling the RaiseFormulaError method on IUltraCalcManager and displays the error message in the cells. The developer can also assign the CalcManager before assigning the formulas on the columns. In this situation, the WebGrid compiles the formula as soon as it is assigned. If there is a syntax error, it does not throw an exception. It takes the same steps (as described above) about raising the FormulaError event off of the CalcManager.

UltraCalcEngine evaluates formulas for every cell in the formula columns and sets the cell reference Value property to the result of the formula evaluation. The cell reference then stores the result and the WebGrid uses that to render the cell. If there is an evaluation error, then the cell reference Value property is assigned an instance of UltraCalcValue that represents an error, which will lead to a FormulaError event to be raised on the WebCalcManager.

As previously discussed, you may associate formulas with stand-alone controls via WebCalcManager. One can associate (at most) one formula with a stand-alone control. WebCalcManager serializes-out necessary information that associates formulas with standalone controls via the CalcSettings extender property. At run-time the WebCalcManager waits for the initialization to complete before compiling the formulas and adding them along with the references to its UltraCalcEngine. Once the formulas and the references are added, the UltraCalcEngine synchronously (or asynchronously) starts evaluating the formulas. As they are evaluated, the associated reference Value property is set to the evaluation result. There could be an evaluation error, in which case the WebCalcManager displays an error icon beside the associated control via the IErrorProvider mechanism; it then leaves the existing value of the control. There could have also been formula compilation errors, in which case, the WebCalcManager takes the same actions of displaying an error icon beside the control whose formula failed the compilation.