Formulas, the heart of any spreadsheet application, allow you to establish and calculate mathematical relationships among elements in the spreadsheet cells. While numeric entries remain the same until you change them, cells defined by formulas change automatically to reflect changes in referenced cells, even when there are complex interdependencies among cells.

Objective Grid formulas can calculate with numbers, text, logical values, cell references, and other formulas. For example, you can easily calculate the sum of a series of cells, the total of values in a column, a minimum or maximum value within a range, the rounded result of another formula, or the absolute value of a cell entry. Formulas can also express complex interdependencies among cells and they can define calculation constraints, such as limits on acceptable values or specific conditions under which a calculation should take place.

Once you enter a formula into a cell, the formula works behind the scene. The only trace of its existence is the result of the calculation. To view the formula in a cell, select the cell. You can edit the formula or the value in a cell at any time.

Because of the complex interdependencies among cells, Objective Grid treats the formula engine as a separate entity. As a front-end to this engine, Objective Grid loads values from the engine when cells need to be drawn and stores values or formulas in the engine whenever cells are changed. This is very similar to the "virtual" approach, in which you override certain grid methods like GetStyleRowCol() and StoreStyleRowCol() to browse and modify data of any external data source.

The process of feeding the engine with data and loading data from the engine allows the engine to track cell dependencies quickly. Whenever you change a cell, the formula engine updates each cell with formulas that reference the modified cell. The grid that is responsible for drawing the cells asks the engine which cells need to be redrawn after you make a change to the grid. Cells that need to be redrawn are marked with flags so that no unnecessary drawing (flickering) occurs when a cell is changed. Only the cells that change values as a result of direct or indirect interaction are redrawn. Direct interactions are the result of user interaction and indirect interactions are the result of cell references in a formula.

The formula engine is accessed through the CGXFormulaSheet class, which is derived from CGXData and replaces the CGXData object that holds all the cell data. CGXFormulaSheet has special overrides for GetStyleRowCol() and StoreStyleRowCol() so that whenever cells are changed in the grid, the actual change is directly stored in the formula engine. Other operations— like moving, removing, and inserting cells—are also forwarded to the engine by overrides in CGXFormulaSheet.

When you use the engine in your grid, you should only call functions and methods provided by the CGXFormulaSheet class. This ensures that your code is readable and understandable when you interact with a class object. The CGXFormulaSheet class is a "wrapper" class to the engine. Consider the code for the engine itself as a separate entity. The product includes full source code for this engine, but you should not need to change the code of the engine or access functions from the engine. The engine itself is based on C-Code, which we intentionally ported to C++ classes. The engine code is not documented. Only the CGXFormulaSheet class is documented in the Objective Grid Class Reference.

The engine is extensible. Although you can't change the grammar of the formula parser, you can add your own worksheet functions. If you need to add your own worksheet functions, use existing worksheet functions as a template when you need to work with the engine functions directly.

Formula support is enabled if you call EnableFormulaEngine() at the beginning of your OnInitalUpdate() routine or before you call CGXGridWnd::Initialize(). The EnableFormulaEngine() call forces the grid to replace the default CGXData object with the CGXFormulaSheet object, which establishes a connection to the formula engine.

After you add this line, you can fill the grid with SetValueRange(), SetStyleRange(), and other grid functions. To store formulas, numeric, or date values to cells, call SetExpressionRowCol().

Example:

Objective Grid allows you to store data in a cell in several ways. You can call SetValueRange(), SetStyleRange(), or SetExpressionRowCol().

SetExpressionRowCol() differs from SetValueRange() and SetStyleRange() in that it parses the string value. If the string value is a formula, it stores a formula. String values that are formulas are preceded by an equal sign (=). If the string value is a string, a string value is stored. If the string value is a number, a number is stored.

Date values are of special interest. If you pass a date or a time to SetExpressionRowCol() as a string (for example, "11/21/01"), Objective Grid interprets this string, stores a date value in the cell, and then sets the cell format to date. Objective Grid internally stores date and time information using the same convention as other popular spreadsheet programs: Dates are represented as an integer equal to the number of days since December 31, 1899. Time is represented as fractions of a day, starting at midnight. Cell formats are specified through CGXStyle::SetFormat() and the number of digits (for example, for scientific format) is specified via CGXStyle::SetPlaces().

Because the formula engine distinguishes between numeric and string values, if you pass a numeric value as a string via SetValueRange(), SetValueRange (… , "100")for example, the number is stored and treated as a string. If you pass this string via SetExpressionRowCol(), the string is parsed and the number is recognized.

Whenever the user changes data in a cell through an edit cell, the data is stored in the grid by calling SetExpressionRowCol(). This allows the user to enter formulas in the current cell.

Here are some examples of how values can be interpreted:

GetStyleRowCol(), StoreStyleRowCol(), SetStyleRange(), and SetValueRange() support three nType attributes for expression values. You can pass the following values to nType:

SetExpressionRowCol() is a wrapper method to SetValueRange() that passes GX_VALUE_EXPRESSION as nType, which forces SetValueRange() to interpret the value. The method is declared as:

GetExpressionRowCol() is a wrapper method to GetValueRowCol() that passes GX_VALUE_EXPRESSION as nType, thus forcing GetValueRowCol() to return the expression value instead of the calculated value. The method is declared as:

To support the interdependencies among cells, we added new cell attributes to Objective Grid to identify grid cells that need to be refreshed after the user makes a change. When the user changes a cell, this cell is added to a recalculation list, and all affected cells are marked with an update flag. The formula engine uses this list to recalculate each cell that depends on the changed cells. The CGXGridCore::RefreshViews() method loops through all visible cells and refreshes only the cells marked with the update flag.

The following table lists methods that implement the update interface.

The following flag has been added for m_nClipboardFlags to support formulas:

Support for "direct" Copy and Paste or Cut and Paste is specified through:

When you use direct Copy (or Cut) and Paste, the data is not being copied/loaded from the clipboard. Instead, the grid stores a reference to the original cells and marks them to be copied. When the user pastes the cells, Objective Grid calls either MoveCells() or CopyCells() to move or copy the cells directly within the sheet.

When you use direct Copy/Paste, the engine automatically updates any cell references to the moved cells. Direct Copy/Paste only works within one worksheet. If you copy/paste or drag/drop cells to another worksheet, the conventional method of using the clipboard is used instead and no cell references will be updated.

The following methods implement direct cut/copy/paste:

Other hooks are provided via:

Full clipboard support for BIFF 8 (Excel 97) is provided in Objective Grid. The current implementation has a couple of restrictions, however:

Objective Grid supports multi-level Undo/Redo for all operations in the engine. For example, if cell references are updated because of a move operation, Undo takes care of these changes.

The only changes that cannot be undone are changes that are the result of an embedded tool or matrix operation because embedded tools store values in a group of adjacent cells. These adjacent cells are set to constant formulas with explicit dependencies on their neighboring cells. For example, if you have a matrix in A7..C9 and cell D10 contains the matrix function "=TRANSPOSE(A7..C9)", the cells D10..F12 are the result set of the matrix operation.

If the user changes any cell in the range D10..F12, the change is stored as a value in the cell. If the user then changes a value in the range A7..C9, the cells D10..F12 are overwritten and no Undo information for the manually changed cells is generated.

The following methods control the display of formulas or their resulting values in cells:

You can control the behavior of the GetControlText() and SetValue() by changing GetParam()->m_nDisplayExpression. m_nDisplayExpression toggles the display of formula expressions in inactive cells:

The default setting for a default grid without formula support is GX_EXPR_DISPLAYNEVER. When using the formula engine, the default setting is GX_EXPR_DISPLAYACTIVE.

With this setting, CGXEditControl::SetValue() will behave differently and, based on the needs of your subclassed cell types, you will have to override this method and modify its behavior.

When using the formula engine, please note the following limitations: