Refresh of Recuive Formula Cells

[Hong Chen]

If I key in formulas in a range of cells with empty values, the values don't refresh properly.

Say I start to type from A1 to A10, with each cell referring to the next cell, while in A11 there is a value of 1. As soon as I reaches A10, the value starts to appear, however, values in A1-A9 are not displayed properly until I programmatically refresh the grid. Is there any better way to display values?

Also, in virtual grid's QueryCellInfo event, if I type the similar series of formulas, I can only assign formula up to 3 nested levels. Beyond that I will get a crash.

Thanks

[Administrator]

I tried to see what you are describing, but I am not sure that I do. Here is what I did. I dropped a GridControl on form, and in FormLoad I set

this.GridControl1.TableStyle.CellType = "FormulaCell";

I then run the app and type 1 into cell A10. Then I go to cell A1 and type =A2. In cell A2, I type =A3, and so on, until in cell A9 I type =A10.

Is this what you are describing? When I do this, I see nothing until cell I type =A10 into cell A9. Then I see all 1's in the first column. What would you expect different, or are you doing something different? What version of the grid are you using, I am using the latest version, 1.5.2.0.

As far as the virtual grid problem, can you post a sample showing the problem?

[Hong Chen]

The attached project will display the formula refresh problem. If search the following line:
"formula will turn funny"
and make the number slightly bigger, this will happen.

Regarding question 1, your key-in steps are exactly what I did. When I type in A1 which has a formula as "=a2+1", A1 displays "Cell empty:", if I go to a2-a10, type the formula in the similar format, they all display "Cell empty:". Up to now they all display what I expected. However, If I type 10 in A11, only A10 display correctly, all the cells above don't display correctly until I call refresh method.

Thanks for such a quick response.

[Administrator]

Thank you for your sample.

The first problem we will have to correct in our library code. I did not see it because I was just using the formula =An+1 instead =An+1 + 1. We should have this resolved in the upcoming 1.6 release. The problem is that the calculations are being done in the order they are drawn. This leads to a problem with these 'look ahead' formulas.

As far as the virtual grid problem, you are relying on the dynamic generation of the FormulaTag (which holds the parsed formula and claculated value) everytime the cell is needed. This really causes a tremendous number of recursive calls into the engine. Currently, the recursive limit has a max of 10 calls. That is why you see this behavior.

Now just increasing this limit is NOT the solution though. In such a situation, you should actually cache these FormulaTags so they are not dynamically generated with each access of the cell. The attached sample shows how you can go about this. Now with the current max 10 recursive calls limit, there is still another problem with the new sample. If you page down the grid everything goes OK. But if you grab the thumb, and drag to the bottom quickly, you are likely to see an too complex exception being thrown. The reason is this max 10 limit. We will also handle this problem in the 1.6 relase.

[Hong Chen]

Thanks, the fomulas in virtual grid work now in your example.

A few other questions:
1. Do you have a function to save to an Excel File?
2. How to copy a range of cells quickly to an array? If I try to take the value to an array cell by cell, it it is quite slow.

[Administrator]

1) We currently only export delimited text. Direct support of XLS (BIFF) is on our to-do and we hope to have this support in teh next few months.

Here are some snippets that read and write CSV files.

//CSV CODE
//save text
private void button3_Click(object sender, System.EventArgs e)
{
	this.gridControl1.Model.TextDataExchange.ExportTabDelim = ",";

	string outPut;
	int nRows, nCols;
	GridRangeInfoList rangeInfoList = new GridRangeInfoList();

	rangeInfoList.Add(GridRangeInfo.Cells(1, 1, this.gridControl1.RowCount, this.gridControl1.ColCount));

	bool b = this.gridControl1.Model.TextDataExchange.CopyTextToBuffer(out outPut, rangeInfoList, out nRows, out nCols);

	if(b)
	{
		StreamWriter writer = new StreamWriter("test.csv");
		writer.Write(outPut);
		writer.Close();
	}
}

//load text
private void button4_Click(object sender, System.EventArgs e)
{
	this.gridControl1.Model.TextDataExchange.ImportTabDelim = ",";

	StreamReader reader = new StreamReader("test.csv");
	string s = reader.ReadToEnd();
	reader.Close();
	this.gridControl1.BeginUpdate();
	//reset grid
	this.gridControl1.RowCount = 1;
	this.gridControl1.ColCount = 1;
	this.gridControl1.Model.TextDataExchange.PasteTextFromBuffer(s, GridRangeInfo.Cell(1,1), 0);
	this.gridControl1.EndUpdate();
}

2) There is no method to support copying values to an array. But you can spend up looping through the cells by directly accessing the GridData object instead of using a direct indexer on the GridControl. We are talking of speeding things up by 100 to 200 times. If you need direct access to a significant amount of data, then going through the GridData object is worth the effort. Below is a code snippet.

//C#
private void button2_Click(object sender, System.EventArgs e)
{
	//GridData method
	int start = Environment.TickCount;
	int[,] A = new int[this.gridControl1.RowCount, this.gridControl1.ColCount];
	GridData data = this.gridControl1.Model.Data;
	for(int row = 1; row <= this.gridControl1.RowCount; ++ row)
		for(int col = 1; col <= this.gridControl1.ColCount; ++col)
		{
			A[row-1, col-1] = (int) (new GridStyleInfo(data[row, col])).CellValue;
		}
	Console.WriteLine("GridData {0}", Environment.TickCount-start);
}

'VB.NET
Private Sub button2_Click(sender As Object, e As System.EventArgs)
	'GridData method
	Dim start As Integer = Environment.TickCount
	Dim A(Me.gridControl1.RowCount, Me.gridControl1.ColCount) As Integer
	Dim data As GridData = Me.gridControl1.Model.Data
	Dim row As Integer
   
	While row <= Me.gridControl1.RowCount
		Dim col As Integer
      
		While col <= Me.gridControl1.ColCount
			A(row - 1, col - 1) = CInt(New GridStyleInfo(data(row, col)).CellValue)
		End While
	End While
	Console.WriteLine("GridData {0}", Environment.TickCount - start)
End Sub 'button2_Click

[Administrator]

Oops...
The CS to VB tool messed up incrementing col and row in the Whiles. So, instead of the nexted while loops in the VB code, try

For row = 1 to Me.gridControl1.RowCount
	For col = 1 to Me.gridControl1.ColCount
		A(row - 1, col - 1) = CInt(New GridStyleInfo(data(row, col)).CellValue)
	Next
Next