最近做项目时，显示查询结果总需要绑定到datagridview控件上显示，总结了给datagridview绑定数据的方式，以及导出datagridview数据到excel表格，如有错误请多指教
1.直接绑定数据源，可以绑定的数据格式有List<T>,DataTable,DataSet等，
this.dataGridView1.DataSource = list;
this.dataGridView1.DataSource = table;
this.dataGridView1.DataSource =ds.Tables["表名"];
2.手动绑定datagridview指定列的数据，datagridview列如下图：
绑定数据代码如下：
DataTable dt = cdh.checkDB_typeValue(list_rd); if (dt.Rows.Count>0) { //dataGridView2.DataSource = dt; for (int i = 0; i < dt.Rows.Count; i++) { DataGridViewRow dr = new DataGridViewRow(); dataGridView2.Rows.Add(dr); dataGridView2.Rows[i].Cells["档案类型"].Value = dt.Rows[i]["档案类型"].ToString(); dataGridView2.Rows[i].Cells["字段"].Value = dt.Rows[i]["字段"].ToString(); dataGridView2.Rows[i].Cells["错误类型"].Value = dt.Rows[i]["错误类型"].ToString(); dataGridView2.Rows[i].Cells["错误数据"].Value = dt.Rows[i]["错误数据"].ToString(); dataGridView2.Rows[i].Cells["档案号"].Value = dt.Rows[i]["档案号"].ToString(); } btn_export.Enabled = true; }
3.自动生成datagridview控件并绑定数据和单元格点击事件
foreach (KeyValuePair<string, List<string>> item in ckImageMethod.errorFiles) { //实例化tabpage对象 TabPage tpage = new TabPage(); tpage.Text = item.Key; tpage.AutoScroll = true; //实例化datagridview对象 DataGridView dgv = new DataGridView(); dgv.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.AllCells; dgv.AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.AllCells; dgv.Location = new System.Drawing.Point(3, 3); dgv.Size = new System.Drawing.Size(tabControl2.Width - 15, tabControl2.Height - 35); //给datagridview添加单元格点击事件 dgv.CellContentClick += Dgv_CellContentClick; //给datagridview添加文本列 DataGridViewTextBoxColumn col1 = new DataGridViewTextBoxColumn(); col1.HeaderText = "文件名"; DataGridViewTextBoxColumn col2 = new DataGridViewTextBoxColumn(); col2.HeaderText = "文件路径"; //添加按钮列 DataGridViewButtonColumn col3 = new DataGridViewButtonColumn(); col3.HeaderText = "操作"; col3.Name = "btnModify"; col3.DefaultCellStyle.NullValue = "打开文件"; dgv.Columns.Add(col1); dgv.Columns.Add(col2); dgv.Columns.Add(col3); foreach (string filePath in item.Value) { string path = filePath; //给datagridview添加一行数据，因为第三列时按钮，所以不添加值也会默认添加按钮 dgv.Rows.Add(path.Substring(path.LastIndexOf('\') + 1), path); } //添加datagridview控件到tabpage中 tpage.Controls.Add(dgv); //添加tabpage到tabControl中 tabControl2.TabPages.Add(tpage); }
单元格点击事件：
private void Dgv_CellContentClick(object sender, DataGridViewCellEventArgs e) { DataGridView dgv = sender as DataGridView; //点击button按钮事件 if (dgv.Columns[e.ColumnIndex].Name == "btnModify" && e.RowIndex >= 0) { if (e.RowIndex == (dgv.RowCount - 1)) { return; } //说明点击的列是DataGridViewButtonColumn列 string path = dgv.Rows[e.RowIndex].Cells[1].Value.ToString(); if (Directory.Exists(path)) { //打开文件夹 System.Diagnostics.Process.Start(path); } else { //打开文件所在文件夹 DirectoryInfo fi = new DirectoryInfo(path); System.Diagnostics.Process.Start(fi.Parent.FullName); } } }
4.导出datagridview数据到excel中，我做的比较麻烦，而且我导出的是datagridview绑定的数据源datatable，如果要导出datagridview中显示的数据，要先把datagridview的数据存到datatable中，再导出。下面是导出方法
private void btn_export_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtPath.Text)) { MessageBox.Show("保存路径不能为空！", "提示"); return; } Task tk = new Task(Way); //MessageBox.Show("正在导出请稍后..."); lb1.Visible = true; lb1.Text = "数据导出中，请稍后..."; txtPath.Visible = false; btn_export.Enabled = false; tk.Start(); } private void Way() { dataexcel.DataExcelExport(dt, txtPath.Text, "错误数据导出", "EXCEL"); MessageBox.Show("数据导出完成"); btn_export.Enabled = true; txtPath.Visible = true; lb1.Visible = false; }
调用的类的方法：
public class DataExcelExportHandle { public delegate void Del_error(string StrError); public static event Del_error aa; public void DataExcelExport(DataTable QueryTable, string FilePath, string TableName, string DbType) { List<string> ErrorList = new List<string>(); string Query = string.Empty;//数据导出语句 try { //判断Excel版本 string StrVerSion = FilePath.Split('.')[1].ToString(); string DataConnect = string.Empty; if (DbType == "EXCEL") { if (StrVerSion.ToLower() == "xls") { DataConnect = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + FilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'"; } if (StrVerSion.ToLower() == "xlsx") { //DataConnect = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + FilePath + ";Extended Properties='Excel 12.0 Macro; HDR = NO'"; DataConnect = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + FilePath + ";Extended Properties='Excel 12.0 Xml; HDR = NO'"; } } //else //{ // if (StrVerSion.ToLower() == "mdb") // { // DataConnect = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + FilePath; // //判断ACCESS数据文件是否存在 不存在则创建 // if (!File.Exists(FilePath)) // { // Catalog catalog = new Catalog(); // catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Jet OLEDB:Engine Type=5"); // } // } // if (StrVerSion.ToLower() == "accdb") // { // DataConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath; // //判断ACCESS数据文件是否存在 不存在则创建 // if (!File.Exists(FilePath)) // { // ADOX.Catalog catalog = new ADOX.Catalog(); // catalog.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";"); // } // } //} //string ConnectStr = CurrencyHandle.GetXmlNumber("DataBaseDeploy", "SqlConnect"); //DataTable dt = SqlDataBaseHandle.GetTable(StrDataQuery, ConnectStr); //根据表名字段动态创建表字段 string CreateColumn = "create table " + TableName + " ("; string QueryNumberStr = "insert into " + TableName + " ("; for (int i = 0; i < QueryTable.Columns.Count; i++) { CreateColumn = CreateColumn + QueryTable.Columns[i].ColumnName + " text,"; QueryNumberStr = QueryNumberStr + QueryTable.Columns[i].ColumnName + ","; } QueryNumberStr = QueryNumberStr.TrimEnd(','); CreateColumn = CreateColumn.TrimEnd(','); CreateColumn = CreateColumn + ")"; //创建表 OleDataBaseHandle.OleConnectOpen(DataConnect); OleDataBaseHandle.OleImplement(CreateColumn); Query = QueryNumberStr + ") values("; for (int i = 0; i < QueryTable.Columns.Count; i++) { Query = Query + "@" + QueryTable.Columns[i].ColumnName + ","; } Query = Query.Substring(0, Query.Length - 1); Query = Query + ")"; int CountIndex = 1;//记录数据条数 string SysTable = TableName; #region 数据导出 for (int i = 0; i < QueryTable.Rows.Count; i++) { try { //当导出数据超过限制时将自动创建新的表 if (DbType == "EXCEL") { if (StrVerSion.ToLower() == "xlsx") { if (i > CountIndex * 1000000) { CreateColumn = CreateColumn.Replace(TableName, SysTable + CountIndex); OleDataBaseHandle.OleImplement(CreateColumn); Query = Query.Replace(TableName, SysTable + CountIndex); TableName = TableName + CountIndex; CountIndex++; } } else { if (i > CountIndex * 65000) { CreateColumn = CreateColumn.Replace(TableName, SysTable + CountIndex); OleDataBaseHandle.OleImplement(CreateColumn); Query = Query.Replace(TableName, SysTable + CountIndex); TableName = TableName + CountIndex; CountIndex++; } } } List<OleDbParameter> oleList = new List<OleDbParameter>(); for (int k = 0; k < QueryTable.Columns.Count; k++) { OleDbParameter prop = null; try { if (QueryTable.Rows[i][k] == null) { prop = new OleDbParameter("@" + QueryTable.Columns[k].ColumnName, ""); } else { prop = new OleDbParameter("@" + QueryTable.Columns[k].ColumnName, QueryTable.Rows[i][k].ToString()); } } catch (Exception excc) { aa("导出失败" + Query); ErrorList.Add(excc.Message); } oleList.Add(prop); } if (!OleDataBaseHandle.OleImplement(Query, oleList)) { aa("导出失败" + Query); ErrorList.Add("导出失败" + Query); } } catch (Exception exx) { aa(exx.Message + Query); ErrorList.Add(exx.Message + Query); } } #endregion OleDataBaseHandle.OleConnectColse(); } catch (Exception ex) { aa(ex.Message + Query); ErrorList.Add(ex.Message + Query); } finally { if (ErrorList != null && ErrorList.Count > 0) { ToolHelper.ErrorInfoSave(ErrorList); } } } }
上面方法调用的ole操作类：
https://www.cnblogs.com/Li232/p/10813918.html
/// <summary> /// Ole操作类 /// </summary> public class OleDataBaseHandle { private static OleDbConnection con = null; private static OleDbCommand cmd = null; /// <summary> /// 通过文件路径 获取文件的所有表名 /// </summary> /// <param name="Filename">文件路径</param> /// <returns></returns> public static DataTable GetFileTableName(string Filename) { string DbConnect = string.Empty; try { DbConnect = GetConnect(Filename);//通过文件获取连接字符串 con = new OleDbConnection(DbConnect); con.Open(); DataTable dt = new DataTable(); dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" }); return dt; } catch (Exception) { return null; } finally { if (cmd != null) { cmd.Dispose(); } if (con != null) { if (con.State == ConnectionState.Open) { con.Close(); } } } } /// <summary> /// 通过文件路径和表名获取所有数据(现支持EXCEL2003和ACCESS2003) /// </summary> /// <param name="Filename">文件路径</param> /// <param name="TableName">表名</param> /// <returns></returns> public static DataTable GetFileTableData(string Filename, string TableName) { string DbConnect = string.Empty; try { DbConnect = GetConnect(Filename);//通过文件获取连接字符串 con = new OleDbConnection(DbConnect); con.Open(); DataTable dt = new DataTable(); cmd = new OleDbCommand("select * from [" + TableName + "]", con); OleDbDataAdapter dapter = new OleDbDataAdapter(cmd); dapter.Fill(dt); return dt; } catch { return null; } finally { if (cmd != null) { cmd.Dispose(); } if (con != null) { if (con.State == ConnectionState.Open) { con.Close(); } } } } /// <summary> /// 通过数据文件和表名获取该表的所有列(现支持EXCEL2003和ACCESS2003) /// </summary> /// <param name="Filename">文件路径</param> /// <param name="TableName">文件表名</param> /// <returns></returns> public static DataTable GetFileTableName(string Filename, string TableName) { List<string> ErrorList = new List<string>(); OleDbCommand cmd = null; OleDbConnection con = null; string DbConnect = string.Empty; try { DbConnect = GetConnect(Filename);//通过文件获取连接字符串 con = new OleDbConnection(DbConnect); con.Open(); DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null }); if (dt == null || dt.Rows.Count < 1) { return null; } else { return dt; } } catch (Exception ex) { ErrorList.Add(ex.Message); return null; } finally { if (cmd != null) { cmd.Dispose(); } if (con != null) { if (con.State == ConnectionState.Open) { con.Close(); } } } } /// <summary> /// 通过语句执行返回结果true false /// </summary> /// <param name="StrQuery">执行的SQL语句</param> /// <returns></returns> public static bool OleImplement(string StrQuery, string ConnectStr) { try { con = new OleDbConnection(ConnectStr); con.Open(); cmd = new OleDbCommand(StrQuery, con); int a = cmd.ExecuteNonQuery(); if (a != 0) { return true; } else { return false; } } catch { return false; } finally { if (cmd != null) { cmd.Dispose(); } if (con != null) { if (con.State == ConnectionState.Open) { con.Close(); } } } } /// <summary> /// 通过数据文件和表名获取该表的所有列 /// </summary> /// <param name="StrQuery">所执行的SQL语句</param> /// <returns></returns> public static bool OleImplement(string StrQuery) { try { cmd = new OleDbCommand(StrQuery, con); int a = cmd.ExecuteNonQuery(); if (a != 0) { return true; } else { return false; } } catch (Exception exxx) { return false; } } /// <summary> /// 通过连接字符串建立长链接 /// </summary> /// <param name="StrConnect">连接数据库字符串</param> public static void OleConnectOpen(string StrConnect) { con = new OleDbConnection(StrConnect); con.Open(); } /// <summary> /// 关闭长链接并释放资源 /// </summary> public static void OleConnectColse() { if (cmd != null) { cmd.Dispose(); } if (con != null) { if (con.State == ConnectionState.Open) { con.Close(); con.Dispose(); } } } /// <summary> /// 执行参数化SQL语句 /// </summary> /// <param name="StrQuery">所执行的SQL语句</param> /// <param name="para">参数化集合</param> /// <returns></returns> public static bool OleImplement(string StrQuery, List<OleDbParameter> para) { try { cmd = new OleDbCommand(StrQuery, con); foreach (var item in para) { cmd.Parameters.Add(item); } cmd.ExecuteNonQuery(); return true; } catch { return false; } } /// <summary> /// 通过文件路径返回ADO连接字符串 /// </summary> /// <param name="Filename">文件路径</param> /// <returns></returns> public static string GetConnect(string Filename) { string DbConnect = string.Empty; if (Filename.Split('.')[1].ToLower() == "xls") { DbConnect = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Filename + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'"; } else if (Filename.Split('.')[1].ToLower() == "xlsx") { DbConnect = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + Filename + ";Extended Properties='Excel 12.0 Macro; HDR = NO'"; } else if (Filename.Split('.')[1].ToLower() == "mdb") { DbConnect = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + Filename; } else if (Filename.Split('.')[1].ToLower() == "accdb") { DbConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Filename; } else { DbConnect = null; } return DbConnect; } /// <summary> /// 执行参数化SQL语句 /// </summary> /// <param name="StrQuery">所执行的SQL语句</param> /// <returns></returns> public static DataTable OleGetTable(string FilePath, string StrQuery) { OleDbConnection objConn = null; DataTable dt = new DataTable(); try { string StrVerSion = FilePath.Split('.')[1].ToString(); string DataConnect = string.Empty; if (StrVerSion.ToLower() == "xls") { DataConnect = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + FilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; } if (StrVerSion.ToLower() == "xlsx") { DataConnect = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + FilePath + ";Extended Properties='Excel 12.0 Xml; HDR = NO'"; } objConn = new OleDbConnection(DataConnect); objConn.Open();//打开连接 //RegistryKey reg_TypeGuessRows = Registry.LocalMachine.CreateSubKey(@"SOFTWAREMicrosoftJet4.0EnginesExcel"); //reg_TypeGuessRows.SetValue("TypeGuessRows", 65000); cmd = new OleDbCommand(StrQuery, objConn); OleDbDataAdapter adr = new OleDbDataAdapter(); adr.SelectCommand = cmd; adr.Fill(dt); objConn.Close(); //OleDbDataReader reader = cmd.ExecuteReader(); //dt.Load(reader); //直接把reader转换为datatable return dt; } catch(Exception ex) { ToolHelper.ErrorInfoSave(ex.Message); return null; } } }
推荐两个百度到的导出datagridview数据的博客地址，这两个写的很简单：
https://www.cnblogs.com/chaowang/p/6265698.html
https://www.cnblogs.com/kongxiaoshuang/p/6062368.html