110,537
社区成员
发帖
与我相关
我的任务
分享
public void AddData(string filePath)
{
string strConn = @"Provider = Microsoft.Ace.OLEDB.12.0; Data Source = " + filePath + ";Persist Security Info=False";
OleDbConnection conn = new OleDbConnection(strConn);
string strSql = "select * from EKInfo";
OleDbDataAdapter oda = new OleDbDataAdapter(strSql, conn);
DataSet ds = new DataSet();
conn.Open();
oda.Fill(ds, "EKInfo");
DataRow dr = ds.Tables["EKInfo"].NewRow();
dr["Url"] = "http://www.baidu.com";
dr["AddTime"] = DateTime.Now.ToShortDateString();
OleDbCommandBuilder cb = new OleDbCommandBuilder(oda);
oda.Update(ds, "EKInfo");
ds.AcceptChanges();
oda.Dispose();
conn.Dispose();
conn.Close();
}
public void EcxelToDataGridView(string filePath, DataGridView dgv)
{
dgv.Columns.Clear();
dgv.DataSource = null;
//根据路径打开一个excel文件并将数据填充到dataset中
dgv.Columns.Add("Url", "网址");
string strConn = @"Provider = Microsoft.Ace.OLEDB.12.0; Data Source = " + filePath + "; Extended Properties = 'Excel 12.0;HDR = Yes; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim();
strExcel = "select * from ["+tableName+"]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();
ds.Clear();
myCommand.Fill(ds, "table1");
//根据DataGridView的列构造一个DataTable
DataTable td = ds.Tables[0];
try
{
#region 小问题
//if (td.Columns.Count > 1)
//{
dgv.Rows.Clear();
dgv.Columns.Clear();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell))
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
dc.DataType = dgvc.ValueType;
td.Columns.Add(dc);
}
}
//根据excel的行逐一对上面的构造的datatable的列进行赋值
DataRow dr = td.NewRow();
for (int i = 0; i < ds.Tables["table1"].Rows.Count; i++)
{
DataRow excelRow = ds.Tables["table1"].Rows[i];
for (int j = 0; j < td.Columns.Count; j++)
{
dr[j] = excelRow[j];
i++;
}
}
td.Rows.Add(dr);
dgv.DataSource = td;
#endregion
}
catch (Exception)
{
MessageBox.Show("该表已存在你即将导入的excel文件...,请点击清空按钮重新导入...");
return;
}
finally
{
conn.Close();
}
}