Initializing Formula Grids
Using Formula Sheet Methods
Speed can be a problem in initializing large formula grids. If you are using the grid with the formula engine, the fastest way to fill the grid is to use CGXFormulaSheet methods instead of CGXGridCore methods to do the assignments. If you call SetNumberRowCol() and SetTextRowCol() directly, the speed improvements will be enormous compared to calling SetValueRange() or SetExpressionRowCol() for each cell. Be aware that cells are not checked for read-only status, cell objects are not notified, and no Undo information is created. The code for this sample is located at <stingray-installdir>\Samples\Grid\General\Formula.
SetFormulaRowCol()
void CMyView::OnTestTestusingformulasheet()
{
// Performance tests: Using the formulasheet methods instead of the
// gridcore level calls will speed things up. The code below calls
// the formulasheet method SetFormulaRowCol directly.
// NOTE: Directly calling this method 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.
GetParam()->EnableUndo(FALSE); //Turn off Undo, if it’s on.
LockUpdate(TRUE);
DWORD ti = GetTickCount();
CGXFormulaSheet* pSheet = GetSheetContext();
for (ROWCOL nRow = 2; nRow < 300; nRow++)
{ for (ROWCOL nCol = 1; nCol < 10; nCol++)
{ pSheet->SetFormulaRowCol(nRow, nCol, "=A1 + B1"); }
}
LockUpdate(FALSE);
GetParam()->EnableUndo(TRUE); //Turn Undo back on.
Redraw();
CString msg;
msg.Format("%d Ticks", GetTickCount()-ti);
AfxMessageBox(msg);
}
void CMyView::OnTestTestusinggridmethods()
{
//The code below uses the call to the GridCore SetExpressionRowCol.
GetParam()->EnableUndo(FALSE); //Turn off Undo, if it’s on.
LockUpdate(TRUE);
DWORD ti = GetTickCount();
for (ROWCOL nRow = 2; nRow < 300; nRow++)
{ for (ROWCOL nCol = 1; nCol < 10; nCol++)
{ SetExpressionRowCol(nRow,nCol, "=A1 + B1"); }
}
LockUpdate(FALSE);
GetParam()->EnableUndo(TRUE); //Turn Undo back on.
Redraw();
CString msg;
msg.Format("%d Ticks", GetTickCount()-ti);
AfxMessageBox(msg);
}
SetNumberRowCol() and SetTextRowCol()
// 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 readonly
// 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);
Copying Cells
Using a combination of SetExpressionRowCol() and CopyCells() also provides fast initialization. Here is an example of this technique:
SetRowCount(10000);
SetColCount(50);
COleDateTime t, t1;
t = COleDateTime::GetCurrentTime();
CString s;
for (ROWCOL i = 1; i >=1000 ; ++i)
{
s.Format(=A%d-B%d, i, i);
SetExpressionRowCol( i, 3, s );
}
CGXRange range( 1, 3, 1000,3);
for ( i = 1001; i <=9001 ; i+=1000 )
{
CopyCells(range, i, 3);
}
t1 = COleDateTime::GetCurrentTime();
s.Format(%f seconds\n, (t1-t) * 24. * 60. * 60.);
AfxMessageBox(s);
NOTE >> If this is a standard initialization, using an OGF file might be a way to quickly load it. (OGF is the file format for formula grids, while OGL is the file format for a standard grid.)