Formulas in Databound Grids

[Mac]

Is it possible to have a formula in a databound grid?

What I am trying to do is bring in a dataset which has a couple of blank columns.

I am then adding a formula to this column made up some of the other columns in the grid.

When I add the formula and run, all it does is show the formula as text rather than actually running the formula.

Me.GridDataBoundGrid1.Model(rows, col).CellType = "FormulaCell"
Me.GridDataBoundGrid1.Model(rows, col).Text = "=SUM(A1:F1)"

Regards

Mac

[Administrator]

It is possible to have formulas in a GridDataBoundGrid, BUT it might be simpler (depending upon your needs) just to add an Expression column to your DataTable.

If you want to use formula cells, then you cannot 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 somehow and also provide the FormulaTag somehow, both on a cell basis. The attached sample does this in its Option2 commented code. It uses a Model.QueeryCellInfo event handler to proveide 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.

The sample also shows an Option1 set of code, that simply uses an Expression column.

[Alberto del Barrio]

Is there a way to have a totals row under the rest of the rows of a databound grid?

I've managed to insert a formula in the row that's automatically inserted at last position of the grid when EnableAddNew is set to true, and the value is calculated and showed correctly. The problem is that when the grid control loses the focus, a message box is shown saying an exception has been thrown because the data in that cell cannot be converted to the type of the DataColumn it belongs to.

Is there a better way to accomplish the totals row?

Thanks in advance

[Administrator]

You can store the sums in a hashtable, and then provide them for the AddNew row in a Model.QueryCellInfo event handler. Attached is a little sample.