Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
166 views
in Technique[技术] by (71.8m points)

c# - Apply Formula Cell to a DataGridview

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);
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

In many, many cases the data is not actually in the DataGridView, but elsewhere like a DataTable or a collection of some sort (like List<T>). The control simply presents a view of the data to the user.

There are several ways to do something along the lines of what you want. For both of these, the data actually resides in a DataTable.

Expressions

A DataColumn can be assigned an Expression. Consult the link for the types of Expression, keywords, operators and functions supported. The following will create an expression based columns to multiple Quantity * Price for some rows:

dtSample = new DataTable();
dtSample.Columns.Add(new DataColumn("Item", typeof(string)));
dtSample.Columns.Add(new DataColumn("Quantity", typeof(int)));
dtSample.Columns.Add(new DataColumn("Price", typeof(decimal)));
dtSample.Columns.Add(new DataColumn("Sale", typeof(decimal)));

// assign expression using the col names
dtSample.Columns[3].Expression = "(Quantity * Price)";

After some random data is added, as well as an empty row, the DataTable will maintain those columns for you. This works like you probably want it to: if the user (or code) changes the value of a Quantity or Price cell, the Sale column contents is automatically updated. (an image is later after the second method).

Expressions work at the row level. There is not an all-rows/table-wise counter part for something like a TOTALS row - this is because the data would often come from a DataSource. Adding calculated rows could accidentally add new data to that source (like a DB). But it is not hard to do in code:

Event Driven Calculations

Similar to the DGV CellFormatting answer given, you can respond to events from the DataTable such as RowChanged. There you can perform whatever operations and update the table.

...create table and columns
...populate table
// hook up event
dtSample.RowChanged += RowChanged;

Then in the event, the code calculates an over all per unit average to display in the last row. In some cases, you may be able to use the Compute() method of the DataTable. Unlike an Expression, it isnt updated automatically and as shown in this answer it can be clumsy to update.

With typed data in a DataTable, it is fairly easy to perform calculations in response to events:

private void RowChanged(object sender, DataRowChangeEventArgs e)
{
    // number of rows used 
    int Rows = dtSample.Rows.Count-1;
    if (e.Row == dtSample.Rows[Rows]) return;

    // display TotalSales / TotalUnits
    // get the units
    int TotUnits = dtSample
        .AsEnumerable()
        .Where(r => !r.IsNull("Quantity"))
        .Take(Rows)
        .Sum(n => n.Field<int>("Quantity"));
    
    // sum Sales, divide and display in DGV
    dtSample.Rows[Rows]["Price"] = dtSample
        .AsEnumerable()
        .Where(r => !r.IsNull("Sale"))
        .Take(Rows)
        .Sum(n => n.Field<decimal>("Sale")) / TotUnits;
}

enter image description here

The "Sales" column is automatically maintained via an Expression which means you cannot manually do anything to that column.

The overall average price at the bottom is also "automatically" updated, the difference is that we had to write a smattering of code to do so.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...