最近做项目时，显示查询结果总需要绑定到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>)
{
//dataGridView2.DataSource = dt;
for (int i = ; 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;
}
绑定datagridview数据
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(, );
dgv.Size = new System.Drawing.Size(tabControl2.Width - , tabControl2.Height - );
//给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('\\') + ), path);
}
//添加datagridview控件到tabpage中
tpage.Controls.Add(dgv);
//添加tabpage到tabControl中
tabControl2.TabPages.Add(tpage);
}
后台添加datagridview控件并绑定数据
单元格点击事件：
private void Dgv_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
DataGridView dgv = sender as DataGridView;
//点击button按钮事件
if (dgv.Columns[e.ColumnIndex].Name == "btnModify" && e.RowIndex >= )
{
if (e.RowIndex == (dgv.RowCount - ))
{
return;
}
//说明点击的列是DataGridViewButtonColumn列
string path = dgv.Rows[e.RowIndex].Cells[].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;
}
导出datagridview数据
调用的类的方法：
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('.')[].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 = ; 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 = ; i < QueryTable.Columns.Count; i++)
{
Query = Query + "@" + QueryTable.Columns[i].ColumnName + ",";
}
Query = Query.Substring(, Query.Length - );
Query = Query + ")";
int CountIndex = ;//记录数据条数
string SysTable = TableName;
#region 数据导出
for (int i = ; i < QueryTable.Rows.Count; i++)
{
try
{
//当导出数据超过限制时将自动创建新的表
if (DbType == "EXCEL")
{
if (StrVerSion.ToLower() == "xlsx")
{
if (i > CountIndex * )
{
CreateColumn = CreateColumn.Replace(TableName, SysTable + CountIndex);
OleDataBaseHandle.OleImplement(CreateColumn);
Query = Query.Replace(TableName, SysTable + CountIndex);
TableName = TableName + CountIndex;
CountIndex++;
}
}
else
{
if (i > CountIndex * )
{
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 = ; 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 > )
{
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 < )
{
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 != )
{
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 != )
{
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('.')[].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('.')[].ToLower() == "xlsx")
{
DbConnect = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + Filename + ";Extended Properties='Excel 12.0 Macro; HDR = NO'";
}
else if (Filename.Split('.')[].ToLower() == "mdb")
{
DbConnect = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + Filename;
}
else if (Filename.Split('.')[].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('.')[].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转换为datatable
return dt;
}
catch(Exception ex)
{
ToolHelper.ErrorInfoSave(ex.Message);
return null;
}
} }
上述方法中用到的ole操作类
推荐两个百度到的导出datagridview数据的博客地址，这两个写的很简单：
https://www.cnblogs.com/chaowang/p/6265698.html
https://www.cnblogs.com/kongxiaoshuang/p/6062368.html