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.
If you link your application statically with the Objective Grid libraries, the engine code is only linked into your application when you call EnableFormulaEngine(). This gives you the flexibility not to include the formula engine code in your executable, which reduces its footprint size.
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:
void CGridSampleView::OnInitialUpdate()
{
EnableFormulaEngine();
BOOL bNew = ConnectParam();
CMyGridView::OnInitialUpdate(); // Creates all objects and
// links them to the grid
// ... and you can execute commands on the grid
if (bNew)
{
EnableHints(FALSE);
// Lock any drawing
BOOL bOld = LockUpdate();
// initialize the grid data
// disable Undo mechanism for the following commands
GetParam()->EnableUndo(FALSE);
// no iteration when circular references
GetSheetContext()->SetIterationLimit(0);
// automatic/manual recalculation
GetSheetContext()->SetRecalcMode(GX_RECALC_AUTO);
//reevaluate cells on demand
GetSheetContext()->SetRecalcMethod(GX_RECALC_AS_NEEDED);
// turn off constraint checks
GetSheetContext()->SetConstraintCheck(FALSE);
// Initialize grid with 30 rows and 5 columns
SetRowCount(1000);
SetColCount(40);
// Insert an array with Numeric data
ROWCOL nRow, nCol;
double d = 0.0;
for (nRow = 7; nRow <= 12; nRow++)
{
d *= 2.0;
for (nCol = 1; nCol <= 4; nCol++)
{
d += 1.0;
SetStyleRange(CGXRange(nRow, nCol),
CGXStyle()
.SetValue(d)
.SetHorizontalAlignment(DT_RIGHT)
);
}
}
// Some string data
SetValueRange(CGXRange(7, 6), _T("Hello "));
SetValueRange(CGXRange(7, 7), _T("world "));
SetValueRange(CGXRange(8, 6), _T("Stingray "));
SetValueRange(CGXRange(8, 7), _T("Software "));
SetValueRange(CGXRange(9, 6), _T("Objective "));
SetValueRange(CGXRange(9, 7), _T("Grid "));
nRow++;
nRow++;
SetStyleRange(CGXRange(nRow, 1),
CGXStyle()
.SetValue(_T("String Functions"))
.SetEnabled(FALSE)
.SetFont(CGXFont().SetBold(TRUE))
);
nRow++;
SetExpressionRowCol(nRow, 1, _T("STRCAT"));
SetExpressionRowCol(nRow+1, 1, _T("=STRCAT(F7, G7)"));
SetExpressionRowCol(nRow, 2, _T("LENGTH"));
SetExpressionRowCol(nRow+1, 2, _T("=LENGTH(F7)"));
SetExpressionRowCol(nRow, 3, _T("FIND"));
SetExpressionRowCol(nRow+1, 3, _T("=FIND(\"l\", F7, 0)"));
SetExpressionRowCol(nRow, 4, _T("MID"));
SetExpressionRowCol(nRow+1, 4, _T("=MID(F9&G9, 3, 5)"));
SetExpressionRowCol(nRow, 5, _T("LOWER"));
SetExpressionRowCol(nRow+1, 5, _T("=LOWER(F9&G9)"));
SetExpressionRowCol(nRow, 6, _T("REPEAT"));
SetExpressionRowCol(nRow+1, 6, _T("=REPEAT(\"=\", 10)"));
...
|
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:
SetValueRange(CGXRange(nRow, nCol), 122.22);
// this will set value type to GX_VT_NUMERIC (numeric)
SetValueRange(CGXRange(nRow, nCol), "122.22");
// this will set value type to GX_VT_STRING (string)
SetStyleRange(CGXRange(nRow, nCol), CGXStyle()
.SetValue ("122.22"));
// this will set value type to GX_VT_STRING (string)
SetStyleRange(CGXRange(nRow, nCol), CGXStyle().SetValue (929.2));
// this will set value type to GX_VT_NUMERIC (numeric)
SetExpressionRowCol (nRow, nCol, "1222.22");
// SetExpressionRowCol parses the string and
// will set value type to GX_VT_NUMERIC (numeric)
SetExpressionRowCol (nRow, nCol, "ABDGDDG1222.22");
// SetExpressionRowCol parses the string and
// will set value type to GX_VT_STRING (string)
SetExpressionRowCol (nRow, nCol, "12/21/02");
// SetExpressionRowCol parses the string and
// will store a date value and set the cell format to date.
SetExpressionRowCol (nRow, nCol, "=ABS(A5) ");
// SetExpressionRowCol parses the string and
// will store a formula expression.
|
EnableFormulaEngine() — Call this method in the beginning of your OnInitialUpdate() routine or before calling CGXGridWnd::Initialize() to enable formula support.
GetSheetContext() — Call this method to get a pointer to the CGXFormulaSheet.
GetStyleRowCol(), StoreStyleRowCol(), SetStyleRange(), and SetValueRange() support three nType attributes for expression values. You can pass the following values to nType:
const int GX_VALUE_BASESTYLE = -1;
Use for row, column, and table style.
const int GX_VALUE_TEXT = 0;
Use to store or retrieve plain style information in cell.
When you use GX_VALUE_TEXT, GetStyleRowCol() returns the cell text in CGXStyle::GetValue(). That is the computed value of a formula cell.
On the other hand, StoreStyleRowCol(), SetStyleRange(), and SetValueRange() assume the CGXStyle::GetValue() as plain text or a number and do not try to interpret the value when GX_VALUE_TEXT is specified.
const int GX_VALUE_EXPRESSION = 1;
Use when calling SetValueRange(), StoreStyleRowCol(), etc. and the value should be parsed (date, time, number, formula, etc.)
When you use GX_VALUE_EXPRESSION, GetStyleRowCol() returns the formula itself in CGXStyle::SetValue(). When you use GX_VALUE_EXPRESSION, CGXStyle::GetValue() is interpreted and, if necessary, stored as formula.
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:
BOOL SetExpressionRowCol(ROWCOL nRow, ROWCOL nCol, const CString& s, UINT flags = GX_UPDATENOW); |
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:
const CString& GetExpressionRowCol(ROWCOL nRow, ROWCOL nCol); |
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.
| Method | Purpose | Example |
| GetUpdateFlag | Returns update flag. | virtual BOOL GetUpdateFlag(ROWCOL nRow, ROWCOL nCol); |
| SetUpdateFlag | Set update flag. (TRUE if cell shall be updated later.) | virtual void SetUpdateFlag(ROWCOL nRow, ROWCOL nCol, BOOL bToggle); |
| AddRecalcRange | Add cell to recalc list. | virtual void AddRecalcRange(const CGXRange& range); |
| RefreshViews | Loop through visible cells and redraw cells with update flag. | virtual BOOL RefreshViews(BOOL bRecalc = TRUE, CString* pErrorStr =NULL, BOOL bCreateHint = TRUE); |
| Recalc | Force recalculation either of all formula cells or only of cells depending on cells in the recalc list. | virtual void Recalc(BOOL bForceRecalc = FALSE); |
| ResetError | Resets error state of the engine. | virtual void ResetError(); |
| GetError | Checks if there is an error. (OG calls this method and will display a message box if GetError returned TRUE.) | virtual BOOL GetError(CString& strError); |
Section 14.8.2, "Error Messages Reference," contains an alphabetized summary of error messages.
The following flag has been added for m_nClipboardFlags to support formulas:
const DWORD GX_DNDEXPRESSION = 0x800; // Copy Expression instead of
// value (when GX_DNDSTYLES
// is set)
|
This setting has no effect on direct copy/paste in sheet or CF_TEXT clipboard format.
Support for "direct" Copy and Paste or Cut and Paste is specified through:
BOOL m_bDirectCopyPaste;
BOOL m_bDirectCutPaste;
BOOL m_bDirectDragDrop;
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:
virtual BOOL OnPasteDirect(const CGXRange& range);
virtual BOOL StoreCopyCells(CGXRange rg, ROWCOL nRow, ROWCOL nCol);
virtual BOOL StoreMoveCells(CGXRange rg, ROWCOL nRow, ROWCOL nCol, CObject*& pUndoInfo, BOOL bCreateUndoInfo, GXCmdType ctCmd);
virtual BOOL CopyCells(CGXRange rg, ROWCOL nRow, ROWCOL nCol, UINT flags = GX_UPDATENOW);
virtual BOOL MoveCells(CGXRange rg, ROWCOL nRow, ROWCOL nCol, CObject* pUndoInfo = NULL, UINT flags = GX_UPDATENOW, GXCmdType ctCmd = gxDo);
Other hooks are provided via:
virtual BOOL GetClipboardStyleRowCol(ROWCOL nRow, ROWCOL nCol, CGXStyle* pStyle, BOOL bLoadBaseStyles);
virtual CString GetCopyTextRowCol(ROWCOL nRow, ROWCOL nCol);
virtual BOOL PasteTextRowCol(ROWCOL nRow, ROWCOL nCol, const CString& str, UINT nFlags, const CGXStyle* pOldStyle);
These methods let you hook into the process of supplying data for a specific cell or pasting data into a specific cell. The methods are called from within PasteTextFromBuffer(), CopyTextToFile(), and PasteCellsFromArchive().
Full clipboard support for BIFF 8 (Excel 97) is provided in Objective Grid. The current implementation has a couple of restrictions, however:
When formulas are copied from Excel to Objective Grid only the values that are the result of the Excel formulas are copied over to Grid; the formulas themselves are not copied over.
Copying formulas from Grid to Excel is supported; formulas are copied, but because of syntax differences, Excel might not support all of the Objective Grid formulas.
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:
CGXControl::GetControlText() — This method checks the CGXStyle::GetFormat() setting and will try to format the value accordingly.
CGXControl::SetControlText() — This method will call SetExpressionRowCol() instead of SetValueRange(), thus allowing you to paste formulas into cells from a text file. (SetControlText() is called from PasteTextFromBuffer().)
CGXEditControl::SetValue() — CGXEditControl will display the expression of the cell when in edit mode and the calculated value when the cell is inactive. When the user modifies the cell, SetExpressionRowCol() will be called to store the cell contents. This allows the user to enter formulas 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:
GX_EXPR_DISPLAYALWAYS will display the formula expression in inactive cells.
GX_EXPR_DISPLAYACTIVE will display the formula expression only in the active current cell.
GX_EXPR_DISPLAYNEVER will display no formula expressions at all.
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:
The maximum number of columns is 4096.
The maximum number of rows is one million rows.
The maximal text size for a cell is 512 bytes.
The engine has to be filled with data. You cannot override GetStyleRowCol() and return values on demand. Of course, you can override GetStyleRowCol() and return a value, but then this value is unknown to the engine and cannot be used in formula calculations. Overriding GetStyleRowCol() and setting other style settings like color and font is no problem.
The engine can't be used with ODBC or ADO grids.
Formulas can only refer to cells within the same sheet. There is no support for getting values from other sheets.
Copyright © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and Stingray, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.