CGXFormulaSheet Class

Objective Grid : PART II Programmer’s Guide : Chapter 14 The Formula Engine : CGXFormulaSheet Class

The CGXFormulaSheet class acts like 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() that ensure that cell changes are stored directly in the formula engine. In addition, other operations such as moving, removing, and inserting cells are forwarded to the engine by overrides in CGXFormulaSheet.

Cell Values

The tables in this section (Table 10 through Table 18) list and describe the methods provided by the CGXFormulaSheet class.

Table 10 – Cell Value Methods
Method	Purpose
SetFormulaRowCol	Assigns a formula expression to a cell. The formula must start with an equal sign (=).
SetTextRowCol	Assigns a text label to a cell.
GetTextRowCol	Returns the text value for a cell. Numeric values are converted to strings. Formulas are reevaluated if necessary. Cells with error state will return "Error!" as text.
SetNumberRowCol	Assigns a number to a cell.
GetNumberRowCol	Returns the numeric value for a cell. Text label returns 0. Formula is reevaluated when necessary.
SetFormatRowCol	Changes the numeric formatting of a cell.
GetFormatRowCol	Returns the numeric formatting to a cell.
SetExpressionRowCol	Parses the given text and (based on the text content) assigns a formula, a text label, or a number to the specified cell. To force numeric or date values to be stored as string, insert an apostrophe (’) at the beginning of the text. Formulas must start with an equal sign (=).
GetExpressionRowCol	Returns either the text value or formula for the cell. Numeric values will be converted to a string. Text label cells will have a leading apostrophe (’) to identify them as string.
GetUpdateFlag	Checks if the given cell needs to be redrawn (e.g. if formula result has been changed). CGXGridCore::RefreshViews checks GetUpdateFlag() for all visible and refreshes them if GetUpdateFlag() returns TRUE.
SetUpdateFlag	Forces the given cell to be redrawn with the next RefreshViews() call.

NOTE >>	CGXGridCore::SetValueRange and CGXGridCore::SetExpressionRowCol() call CGXFormulaSheet::SetExpressionRow() after they ensure that the cell is not read-only and notify the cell object of the change.

Call the above cell value methods (SetNumberRowCol() and SetTextRowCol()) directly if you want to initialize the grid with a large data-set. It is much faster than calling SetValueRange() or SetExpressionRowCol() for each cell. Be aware that if cells are read-only, they are not checked, cell objects are not notified, and Undo information is not created.

Example:

// Performance tests:

// Using SetNumberRowCol/SetTextRowCol directly

// instead of SetValueRange or SetExpressionRowCol

// will speed up the initialization of the grid

// enormously (just as fast as filling an array).

// Check it out below!

// NOTE: Directly calling these methods will bypass the

// notification of the associated cell type object for a

// cell (CGXControl::StoreStyle will not be called.) and

// the read-only state of the cell will also not be

// checked.

DWORD ti = GetTickCount();

CGXFormulaSheet* pSheet = GetSheetContext();

CGXStyle style;

for (; nRow < 300; nRow++)

{

for (ROWCOL nCol = 1; nCol < 10; nCol++)

{

// CString s;

// s.Format("%d/%d", nRow/100, nCol);

// style.SetValue("Hello");

// StoreStyleRowCol(nRow, nCol, &style, gxOverride, 0);

pSheet->SetNumberRowCol(nRow, nCol, (double) nRow+nCol);

// pSheet->SetTextRowCol(nRow, nCol, _T("Hello"));

}

CString msg;

msg.Format("%d Ticks", GetTickCount()-ti);

AfxMessageBox(msg);

Row and Column Coordinates

The following table lists methods that can be used to convert row and column coordinates.

Table 11 – Row and Column Coordinate Methods
Method	Purpose
GetEngineRow	Converts a grid row coordinate (of type ROWCOL, zero-based) into an engine row coordinate (of type int, zero-based).
GetEngineCol	Converts a grid column coordinate (of type ROWCOL, zero-based) into an engine column coordinate (of type int, one-based).
GetGridRow	Converts an engine row coordinate (of type int, zero-based) into a grid row coordinate (of type ROWCOL, zero-based).
GetGridCol	Converts an engine column coordinate (of type int, one-based) into a grid column coordinate (of type ROWCOL, zero-based).

Sheet Settings

The following table contains methods used to modify sheet settings.

Table 12 – Sheet Setting Methods
Method	Purpose
SetIterationLimit	When the Iteration Limit is set to a non-zero value, iterative calculation is enabled. In this mode, Objective Grid makes multiple recalculation passes and preserves the natural order and minimal recalculation rules until either the iteration limit has been reached or, if constraint checking is enabled, until all constraints are satisfied, whichever comes first.
GetIterationLimit	Returns the Iteration Limit value.
SetBlanksAreZeros	Specified if blank cells should be treated as a numeric 0 in formula expressions.
GetBlanksAreZeros	Returns TRUE if blank cells should be treated as a numeric 0 in formula expressions.
SetRecalcMode	Objective Gridlets you decide whether to recalculate the spreadsheet after you make changes that affect other cells or whenever the sheet is initially loaded.Objective Grid automatically recalculates the spreadsheets in both cases if Recalculation Mode is set to Automatic. If recalculation is too time-consuming, you can switch off automatic recalculation so Objective Grid will only recalculate upon explicit request.
GetRecalcMode	Returns the recalc mode setting.
SetRecalcMethod	Recalculation Method defines how Objective Gridproceeds with recalculation.There are two options: As Needed and Foreground. As Needed indicates that recalculation is highly optimized to recompute the minimum number of cells when updating the sheet. This can result in a significant performance improvement for certain types of sheets, particularly on very large sheets.
GetRecalcMethod	Returns the recalculation method setting.
SetConstraintCheck	Toggles on/off constraint checking. Constraints are limitations or conditions placed on the variables in your spreadsheet. They are expressed as algebraic statements appended to formulas. You can attach a constraint expression to any formula by typing a semicolon (;) and the constraint conditions after the formula.
GetConstraintCheck	Returns TRUE if constraint checking is enabled.

Recalculation

The following table lists methods used for recalculation.

Table 13 – Recalculation Methods
Method	Purpose
AddRecalcRange	Marks a given range of cells so that formula cells that depend on the marked cells can later be reevaluated. The formula engine maintains a recalc range list where all these ranges are added.
Recalc	Recalculates all cells in the worksheet that depend on cells in the recalc range list.
ResetError	Resets the error state of the formula engine.
GetError	Returns the error state of the formula engine. Objective Grid calls this method and when necessary displays a message box with error information based on the return value of this GetError method.

Named Ranges

The following table contains methods used with named ranges.

Table 14 – Named Range Methods
Method	Purpose
SetRangeName	Adds a named range or replaces an existing named range with the new range settings.
GetRangeName	Returns the associated range for a given range name.
DeleteRangeName	Deletes a named range from the range name list.
GetRangeNameArray	Returns a string array with all named ranges. Each entry in the array is in the form 'name=A1..B5'.
SetRangeNameArray	Adds a batch of named ranges from a string array. Each entry in the array is in the form 'name=A1..B5'.

Range Specification

The following methods convert range specifications.

Table 15 – Range Specification Methods
Method	Purpose
TextToRange	Converts a range specification, e.g. 'A1..C5', into a CGXRange object, e.g. CGXRange(1,1,5,3).
RangeToText	Converts a CGXRange object, e.g. CGXRange(1,1,5,3), into a range specification, e.g. 'A1..C5'.

Keep in mind the following points about these range specification methods:

• The range object must not be a row, column, or table range. Don't use SetRows(), SetCols(), or SetTable() for specifying the range. Use only plain cell ranges.

CGXRange rg;

TextToRange((_T("A1..C5"), rg));

• You can use CString functions to transform a string that you get as a result of RangeToText(). For example:

CString s;

CGXFormulaSheet::RangeToText(CGXRange(1,1,10,4),

s.GetBuffer(20), 20);

s.ReleaseBuffer();

NOTE >> You can use string functions to convert absolute cell references in formula cells to the appropriate form.

See “Cell Referencing in Objective Grid” for more information about absolute and relative cell references. See “Ranges” for more information about ranges.

Expression Evaluation

The following methods are used for parsing expressions.

Table 16 – Expression Evaluation Methods
Method	Purpose
EvaluateExpression	Parses and evaluates the given expression and returns the result as a string.
ParseExpression	Parses the given expression and returns the parsed formula expression in binary format.

Clipboard Cut/Copy and Paste

Methods associated with the clipboard include:

Table 17 – Clipboard Methods
Method	Purpose
StoreCopyCells	Copies cells from source range to a destination range. Cell references in formula expressions (unless marked as absolute range) will be adjusted.
StoreMoveCells	Moves cells from source range to a destination range. Cell references in formula expressions that depend on cells in the moved range will be adjusted.

Cell Operations

The following table lists cell operation methods.

Table 18 – Cell Operation Methods
Method	Purpose
GetColCount	Returns the number of columns.
GetRowCount	Returns the number of rows.
StoreColCount	Specifies the number of columns.
StoreRowCount	Specifies the number of rows.
GetStyleRowCol	Looks up a style in the formula engine. If called with nType = GX_VALUE_EXPRESSION the formula expression (if available) is assigned to the style value. Otherwise, the evaluated cell value (the result) is assigned to the cell value.
StoreStyleRowCol	Stores a style into the formula engine. If called with nType = GX_VALUE_EXPRESSION, the style value is parsed by calling SetExpressionRowCol. Otherwise, the style value is assigned to the cell as a text label (by calling SetTextRowCol).
StoreInsertCols	Inserts columns in the formula engine and updates cell references to moved cells.
StoreInsertRows	Inserts rows in the formula engine and updates cell references to moved cells.
StoreMoveCols	Moves columns in the formula engine and updates cell references to moved cells.
StoreMoveRows	Moves rows in the formula engine and updates cell references to moved cells.
StoreRemoveCols	Removes columns in the formula engine and updates cell references to moved cells.
StoreRemoveRows	Removes rows in the formula engine and updates cell references to moved cells.
StoreMoveDataCols	Rearranges the columns in the grid. The new order of columns is specified through the awColIndex array. Cell references to moved cells are updated.
StoreMoveDataRows	Rearranges the rows in the grid. The new order of rows is specified through the awRowIndex array. Cell references to moved cells are updated.