最近做項目時,顯示查詢結果總需要綁定到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);}}#endregionOleDataBaseHandle.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(@"SOFTWARE\Microsoft\Jet\4.0\Engines\Excel");//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轉換為datatablereturn 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?
?