Objective Grid: Filling a large grid with a formula

Article ID: 326
Last updated: 05 Jan, 2012
Article ID: 326
Last updated: 05 Jan, 2012
Revision: 1
Views: 1781
Posted: 12 Jan, 2001
by Meltreger B.
Updated: 05 Jan, 2012
by Meltreger B.
Problem

6.40.08 What is the most efficient way to fill a column of a large grid, with thousands of rows, with a formula with automatic translation of relative cells references?


Cause




Action

Working with large formula grids is slow. We have found that using a combination of SetExpressionRowCol() and CopyCells() provides the fastest initialization.

For example, I set up a grid with 10,000 rows.
Placing the formula one row at the time using SetExpressionRowCol() took 161 seconds.
Placing the formula one row at a time into a block of 1000 rows and then using CopyCells() to copy these 1000 rows into the other 9000 rows (9 copies) took 38 seconds.
The value 1000 was chosen arbitrarily, but it was much quicker than 500 and 2000. The optimal block size could be determined by trial and error.

Here is my code.

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
, (t1-t) * 24. * 60. * 60.);
AfxMessageBox(s);

Also, if this is a standard initialization, using an OGF file might be a way to quickly load it.

Entered: 11/12/99


This article was:   Helpful | Not helpful
Report an issue
Article ID: 326
Last updated: 05 Jan, 2012
Revision: 1
Views: 1781
Posted: 12 Jan, 2001 by Meltreger B.
Updated: 05 Jan, 2012 by Meltreger B.

Others in this category