Because of the complex interdependencies among cells, Objective Grid treats the formula engine as a separate entity. You might consider Objective Grid as a front end to this engine. It loads values from the engine when cells need to be drawn and that stores values or formulas back into 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. Feeding the engine with data and loading data from the engine allows the engine to easily track any cell dependencies. Whenever a cell is changed, the formula engine will update all cells with formulas that reference the modified cell. The grid object responsible for drawing the cells will query the engine to determine which cells need to be redrawn. Cells that need to be redrawn are marked with a special flag so that unnecessary drawing/flickering does not occur when a cell is changed. Only cells with changes in value are redrawn.
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 forwarded to the engine by overrides in CGXFormulaSheet.
As the programmer, you should only call functions and methods provided by the CGXFormulaSheet class when you interact with the engine from your grid object. This will keep your code more readable and more understandable, as you will be interacting with a class object. The CGXFormulaSheet class is a "wrapper" class to the engine. The code for the engine itself should be considered separate. Objective Grid ships with full source code for the engine but should be used for reference only. There is rarely a reason to access any of the engine functions directly with the exception of adding new worksheet functions. The engine itself is based on legacy C code and 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 will be able to add your own worksheet functions. When you add your own worksheet functions, you may have to deal with the engine directly and use the low-level engine functions directly instead of Objective Grid functions. If you need to add your own worksheet functions, use existing worksheet functions as template.
Formula support is enabled by calling the function EnableFormulaEngine() at the beginning of your OnInitialUpdate() routine or before you call CGXGridWnd::Initialize(). The EnableFormulaEngine() call will force Objective Grid to replace the default CGXData object with the CGXFormulaSheet object. This establishes the connection to the formula engine.
Furthermore, you might want to enable all the built-in worksheet functions that come with the formula engine. To do this, call GXEnableWorksheetFunctions() from your InitInstance() method.
If you link your application statically with the Objective Grid libraries, the engine code will not be linked into your application unless you call EnableFormulaEngine(). This avoids unnecessary overhead induced by the formula engine when you don't need formula support in your application.
After you have called EnableFormulaEngine(), you can fill the grid with SetValueRange(), SetStyleRange(), and other grid functions. In order to store formulas, numeric, or date values to cells you should call SetExpressionRowCol().
SetExpressionRowCol() differs from SetValueRange() and SetStyleRange() as it will parse the string value. If the string value is a formula, a formula is stored. If the string value is a string, a string value is stored, and if it is a number, a number is stored.
Date values are of special interest. If you pass a date or time as a string of numbers (e.g. "01/01/99") to SetExpressionRowCol(), Objective Grid will interpret this string, store a date value in the cell and set the cell format to date. Cell formats are specified through CGXStyle::SetFormat() and the precision is specified via CGXStyle::SetPlaces().
Please note that the formula engine distinguishes between numeric and string values. If you pass a numeric value as a string via SetValueRange(), as in
SetValueRange (..., "100"); |
the number will be stored and treated as a string. If you pass this string via SetExpressionRowCol(), the string is parsed and recognized and stored as a number. If you pass the numeric value directly as a number to SetValueRange(), as in
SetValueRange (..., 100.00); |
the value will also be stored as number.
The user has the ability to change data and formulas at run time. Whenever the user edits a cell, the data will be stored in the grid by a call to SetExpressionRowCol(). This gives the user the ability to enter formulas in the current cell.
This following example shows how you can enable formula support in your grid view:
void CGridSampleView::OnInitialUpdate()
{
EnableFormulaEngine();
BOOL bNew = ConnectParam();
// Create all objects and link them to the grid
CMyGridView::OnInitialUpdate();
// ... and now 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 for 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 1000 rows and 40 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)"));
...
}
...
}
|
See Chapter 14, "The Formula Engine," for more detailed information about the formula engine and a reference of worksheet functions.
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.