I want to add formula cell in a DataGridView
. Is there any custom DataGridView
to do this?
Example:
grid[4, column].Text = string.Format("=MAX({0}6:{0}{1})", columnAsString, grid.RowCount);
Is there any custom DataGridView to do this?
Off-topic, but if you are looking for a custom control, take a look at Free .NET Spreadsheet Control. Also it supports formula.
If writing code for calculation is an option for you
If writing code for calculation is an option for you, to calculate value of a cell based on values of some other cells you can use CellFormatting
event of DataGridView
and put calculation logic there. Also handle CellEndEdit
and call InvalidateCell
or Invalidate
to force update value of cell after each in reference cells.
Here is an example:
void Form1_Load(object sender, EventArgs e)
{
Random r = new Random();
var dt = new DataTable();
dt.Columns.Add("A", typeof(int));
dt.Columns.Add("B", typeof(int));
for (int i = 0; i < 10; i++)
dt.Rows.Add(r.Next(100));
grid.DataSource = dt;
grid.CellFormatting += grid_CellFormatting;
grid.CellEndEdit += grid_CellEndEdit;
}
void grid_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
grid.Invalidate();
}
void grid_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
var grid = sender as DataGridView;
var parameterColumnName = "A"; //Parameter column name
var start = 0; //Start row index for formula
var end = grid.RowCount - 1; //End row index for formula
var resultRowIndex = 0; //Result row index
var resultColumnName = "B"; //Result column name
if (e.RowIndex == resultRowIndex &&
grid.Columns[e.ColumnIndex].Name == resultColumnName)
{
var list = Enumerable.Range(start, end - start + 1)
.Select(i => grid.Rows[i].Cells[parameterColumnName].Value)
.Where(x => x != null && x != DBNull.Value)
.Cast<int>();
if (list.Any())
e.Value = list.Max();
}
}
Note
The solution is not limited to DataTable
, it will work regardless of the DataSource
which you use for DataGridView
and you can use any kind of data source in this solution.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments