class CGXFormulaSheet: public CGXData
The CGXFormulaSheet class provides an interface to the Objective Grid formula engine. CGXFormulaSheet can be attached to a CGXGridCore object and replace 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 will be directly stored in the formula engine. Also other operations like moving, removing and inserting cells will be forwarded to the engine by overrides in CGXFormulaSheet.
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 that 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 has the advantage that the engine can very easily keep track on any cell dependencies. Whenever a cell is changed the formula engine will update all cells with formulas that reference the modified cell. The grid that is responsible for drawing the cells will ask the engine which cells need to be redrawn after a change in the grid. Flags that need to be redrawn are marked with a special flag so that no unnecessary drawing/flickering occurs when a cell is changed. Only cells which value changes (either direct by user interaction or indirect because of cell references in a formula) will be redrawn.
When you interact as programmer with the engine from your grid you should only call functions and methods provided by the CGXFormulaSheet class. This will keep your code more readable and understandable because you can interact with an class object. The CGXFormulaSheet class is a "wrapper" class to the engine. The code for the engine itself should be considered separate. You have full source code for this engine but usually there is no necessarity to change the code of the engine or access functions from the engine. The engine itself is based on C-Code This way we could reuse a lot of existing code and all those many worksheet functions. The engine code is and will not be documented at all. Only the CGXFormulaSheet will be 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 might have to deal with the engine directly and use the engine functions directly instead of Objective Grid functions. If you need to add your own worksheet functions simply use existing worksheet functions as template.
Adding formula support to your application
Formula support will be enabled if you call the function
EnableFormulaEngine();at the beginning of your OnInitalUpdate routine or before you call CGXGridWnd::Initialize. The EnableFormulaEngine call will force the grid to replace the default CGXData object with the CGXFormulaSheet object which then establishes a 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.
Note: If you link your application static with the Objective Grid libraries the engine code will only be linked into your application if you call EnableFormulaEngine();. This makes sure that you don't have unnecessary overhead because of the formula engine if you don't need formula support in your application.
Initializing the grid with data and formulas
After you have added this line 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 value string value. If the string value is a formula (starting with an equal sign), it will store a formula. If the string value is a string, a string value will be stored and if is a number a number will be stored.
Of special interest are date values. If you pass a date or time as a string of numbers, e.g. "01/01/95" 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 number of digits (e.g. for scientific format) is specified via CGXStyle::SetPlaces.
Please note that the formula engine distincts between numeric and string values. So, if you pass a numeric value as string via SetValueRange, e.g. SetValueRange (…, "100") the number will be stored and treated as a string. If you pass this string via SetExpressionRowCol the string will be parsed and the number recognized. If you pass the numeric value directly as number to SetValueRange, e.g. by calling BOOL SetValueRange(const CGXRange& range, double d, GXModifyType mt = gxOverride, int nType = 0, UINT flags = GX_UPDATENOW); the value will also be stored as number.
Changing cell data and formulas at runtime
The user has the ability to change data and formulas at runtime. Whenever the user edits a cell, the data will be stored in the grid by calling SetExpressionRowCol (CGXEditControl::Store will call SetExpressionRowCol). This gives the user the ability to enter formulas in the current cell.
Converting an existing CGXData object (or grid layout file) into a formula sheet
If you want to convert existing designer layout (.OGL) files into new formula sheet layout (.OGF) files you can do this with the Objective Grid designer. After you have started the designer in formula layout mode, you can select "Convert" from the file menu and convert an existing .OGL file into a .OGF file.
You can also do the conversion from CGXData to CGXFormulaSheet in your OnInitialUpdate routine by placing the following code into your grid views OnInitialUpdate routine:
if (GetParam())
{
EnableFormulaEngine();
// Convert CGXData to CGXFormulaSheet
CGXData* pData = GetParam()->GetData();
if (pData && !pData->IsKindOf(RUNTIME_CLASS(CGXFormulaSheet)))
{
CGXFormulaSheet* pSheet = new CGXFormulaSheet;
GXGetAppData()->AttachGridToObject(pSheet, this);
// The copy operator will perform the conversion
*pSheet = *pData;
// Destroy the existing data object and attach the
// CGFormulaSheet (you can ignore the warning in the
// debug window).
GetParam()->SetData(pSheet);
}
}
CGXGridView::OnInitialUpdate();Example
This example shows how you can enable formula support in your grid view:
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);
GetSheetContext()->SetIterationLimit(0); // no iteration when circular references
GetSheetContext()->SetRecalcMode(GX_RECALC_AUTO); // automatic/manual recalculation
GetSheetContext()->SetRecalcMethod(GX_RECALC_AS_NEEDED); // reevaluate cells on demand
GetSheetContext()->SetConstraintCheck(FALSE); // turn off constraint checks
// 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)"));
...
}#include <gxall.h>
See Also
CGXGridCore::EnableFormulaEngine CGXGridCore::SetExpressionRowCol
