对DataGridView数据进行查询方法

一、当DataGridView连接数据库时

1.DataGridView转DataTable

2.DataTable转DataView

3.DataView转DataTable

private System.Data.DataTable Get_lcg(DataGridView myDataGridView)
{
System.Data.DataTable mydataTable = new System.Data.DataTable();

System.Data.DataTable dt = ((System.Data.DataTable)myDataGridView.DataSource).Copy();

DataView dv = dt.DefaultView;
dv.RowFilter = string.Format("字段名 = '{0}')","字段查询内容");
mydataTable = dv.ToTable(true, "字段一", "字段二", "字段三");

return mydataTable;
}

二、当DataGridView没有连接数据库，自定义数据时

public System.Data.DataTable GetDgvToTable(DataGridView dgv, string my_sqlzd, string my_sqltext)
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.DataTable dt_new = new System.Data.DataTable();

for (int count = 0; count < dgv.Columns.Count; count++)
{
DataColumn dc = new DataColumn(dgv.Columns[count].Name.ToString());
dt.Columns.Add(dc);
}

for (int count = 0; count < dgv.Rows.Count; count++)
{
DataRow dr = dt.NewRow();
for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
{
dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
}
dt.Rows.Add(dr);
}

DataRow[] mydr = dt.Select(my_sqlzd + " = '" + my_sqltext + "'");
if (mydr.Count() > 0)
{
dt_new = mydr.CopyToDataTable();
dt_new.Columns.Remove(my_sqlzd);//删除用于标记选择的列
}

return dt_new;
}

三、对DataGridView数据进行统计GroupBy

private void tj_data(DataRow[] mydrs, DataGridView myDataGridView, string group_zd, string group_zd_format, string group_num, string group_num_headertext)
{
if (mydrs.Count() > 0)
{
System.Data.DataTable myDataTable_x = new System.Data.DataTable();
myDataTable_x = mydrs.CopyToDataTable();

IEnumerable<IGrouping<string, DataRow>> result;

if (group_zd_format == "")
{
result = myDataTable_x.Rows.Cast<DataRow>().GroupBy<DataRow, string>(dr => dr[group_zd].ToString());
}
else//格式化日期作为统计字段
{
result = myDataTable_x.Rows.Cast<DataRow>().GroupBy<DataRow, string>(dr => dr[group_zd].ToString().Substring(0,10));
}

if (result.Count() > 0)
{
myDataGridView.Columns.Clear();

DataGridViewTextBoxColumn col0 = new DataGridViewTextBoxColumn();
DataGridViewTextBoxColumn col1 = new DataGridViewTextBoxColumn();

col0.HeaderText = group_zd;
col1.HeaderText = group_num_headertext;

col0.Name = col0.HeaderText;
col1.Name = col1.HeaderText;

col0.ReadOnly = false;
col1.ReadOnly = false;

myDataGridView.Columns.Add(col0);
myDataGridView.Columns.Add(col1);

decimal k;
foreach (IGrouping<string, DataRow> ig in result)
{
k = 0;
foreach (var detail in ig)
{
k += Convert.ToDecimal(detail[group_num]);
}

DataGridViewRow row = new DataGridViewRow();
int index = myDataGridView.Rows.Add(row);
myDataGridView.Rows[index].Cells[0].Value = ig.Key;
myDataGridView.Rows[index].Cells[1].Value = k;
}
if (group_zd_format == "")
{
myDataGridView.Sort(col1, ListSortDirection.Descending);
}
else//按照格式化日期字段排序
{
myDataGridView.Sort(col0, ListSortDirection.Ascending);
}
}
}
}

private void button2_Click(object sender, EventArgs e)
{

System.Data.DataTable dt = ((System.Data.DataTable)this.dataGridView1.DataSource).Copy();

DataRow[] mydrs = dt.Select("订单状态 = '已支付' or 订单状态 = '免费'");

this.tj_data(mydrs, this.dataGridView2, "科室", "", "my_num", "咨询+看诊量");//统计科室看诊数量
this.tj_data(mydrs, this.dataGridView3, "下单时间", "1", "my_num", "咨询+看诊量");//统计每天看诊数量
}

private void button1_Click(object sender, EventArgs e)
{
#region [excel导入datagridview中]

OpenFileDialog myOpenFileDialog = new OpenFileDialog();
//myOpenFileDialog.InitialDirectory = "D:\\Patch";
myOpenFileDialog.Filter = "Excel files|*.xls;*.xlsx";
//myOpenFileDialog.FilterIndex = 2;
myOpenFileDialog.RestoreDirectory = true;
if (myOpenFileDialog.ShowDialog() == DialogResult.OK)
{

DataSet myDataSet1 = new DataSet();
myDataSet1 = this.ReadExcel_ds(myOpenFileDialog.FileName);

System.Data.DataTable dt = new System.Data.DataTable();
dt = myDataSet1.Tables[0];
//增加用于统计数量的字段列，默认值为1
DataColumn dc = new DataColumn("my_num", typeof(int));
dc.DefaultValue = 1;
dt.Columns.Add(dc);

this.dataGridView1.DataSource = dt;
}

#endregion
}