How do I have formula cells in a GridDataBoundGrid?

If you want to use formula cells in a GridDataBoundGrid, then you cannot directly use indexers like grid(row,col).CellType = "FormulaCell" to set style properties for an individual cell.

The reason is that in a GridDataBoundGrid, there is nothing stored in the grid on a cell by cell basis. The only cell specific information that is available is the value (which comes from the datasource and is not stored in the grid). So, if you want to use formulas in a GridDataBoundGrid, then you must provide the formula and also provide the FormulaTag, both on a cell basis. The attached sample uses the Model.QueryCellInfo event handler to provide the formula (generated in the handler dynamically), and to provide the FormulaTag (saved in an arraylist). It also handles the Model.SaveCellInfo event to make sure the FormulaTag information is updated properly.

C#

//Maintain formulaTags for the cells

formulaTags = new ArrayList();

for(int i = 0; i <= dt.Rows.Count + 1; ++i)

{

formulaTags.Add(null);

}

private void GridSaveCellInfo(object sender, GridSaveCellInfoEventArgs e)

{

if(e.ColIndex == 3 && e.RowIndex > 0 && e.RowIndex <= dt.Rows.Count)

{

formulaTags[e.RowIndex - 1] = e.Style.FormulaTag;

e.Handled = true;

}

}

private void GridQueryCellInfo(object sender, GridQueryCellInfoEventArgs e)

{

if(e.ColIndex == 3 && e.RowIndex > 0 && e.RowIndex <= dt.Rows.Count)

{

e.Style.CellType = "FormulaCell";

e.Style.Text = string.Format("=({0}{1} + {2}{1}) / 2", GridRangeInfo.GetAlphaLabel(1), GridRangeInfo.GetNumericLabel(e.RowIndex),GridRangeInfo.GetAlphaLabel(2));

e.Style.FormulaTag = (GridFormulaTag) this.formulaTags[e.RowIndex - 1];

e.Handled = true;

}

}

VB

'Maintain formulaTags for the cells

formulaTags = New ArrayList()

i = 0

Do While i <= dt.Rows.Count + 1

formulaTags.Add(Nothing)

i += 1

Loop

Private Sub GridSaveCellInfo(ByVal sender As Object, ByVal e As GridSaveCellInfoEventArgs)

If e.ColIndex = 3 AndAlso e.RowIndex > 0 AndAlso e.RowIndex <= dt.Rows.Count Then

formulaTags(e.RowIndex - 1) = e.Style.FormulaTag

e.Handled = True

End If

End Sub

Private Sub GridQueryCellInfo(ByVal sender As Object, ByVal e As GridQueryCellInfoEventArgs)

If e.ColIndex = 3 AndAlso e.RowIndex > 0 AndAlso e.RowIndex <= dt.Rows.Count Then

e.Style.CellType = "FormulaCell"

e.Style.Text = String.Format("=({0}{1} + {2}{1}) / 2", GridRangeInfo.GetAlphaLabel(1), GridRangeInfo.GetNumericLabel(e.RowIndex),GridRangeInfo.GetAlphaLabel(2))

e.Style.FormulaTag = CType(Me.formulaTags(e.RowIndex - 1), GridFormulaTag)

e.Handled = True

End If

End Sub

Here is the link with both CS and VB samples: http://websamples.syncfusion.com/samples/kb/grid.windows/GDBGFormula/main.htm