new SqlDataAdapter("insert into data([id],[number],[bynumber],[begintime],[endtime],[sec],[minute],[money])Select * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="+File1.PostedFile.FileName.ToString()+";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]", con); 拜托写清楚
//将Excel数据读入dataset中并在DataGridView中显示 private void btnOK_Click(object sender, EventArgs e) { OpenFileDialog fileDialog = new OpenFileDialog(); if (fileDialog.ShowDialog() == DialogResult.OK) { string strFileName = fileDialog.FileName; this.txtAddress.Text = strFileName; OleDbConnection myConn = new OleDbConnection(); string connStr=" Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+strFileName+";Extended Properties=Excel 8.0"; myConn.ConnectionString = connStr; myConn.Open(); string sql = " SELECT * FROM [Sheet1$] "; OleDbDataAdapter da = new OleDbDataAdapter(sql, myConn); DataSet ds = new DataSet(); da.Fill(ds); this.dgData.DataSource = ds.Tables[0].DefaultView; myConn.Close(); } }//从DataGridView中将数据导入SQL private void btnImport_Click(object sender, EventArgs e) { if (this.dgData.Rows.Count < 0)//dgData:DatagridView { MessageBox.Show("没有可导入的数据!") return; } this.Cursor = Cursors.WaitCursor; SqlParameter[] paramter = new SqlParameter[2]; paramter[0] = new SqlParameter("@UserID",SqlDbType.NVarChar,20); paramter[1] = new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
for (int i = 0; i < dgData.Rows.Count; i++) { paramter[0].Value = this.dgData.Rows[i].Cells[0].Value.ToString().Trim(); paramter[1].Value = this.dgData.Rows[i].Cells[1].Value.ToString().Trim();
个人感觉可以先导出到dataset里面去然后再倒进sql
看着比较清楚~
而且也容易调整结构~
先把数据从Excel读入DataSet中
然后再把DataSet数据插入数据库中
不过这样可能会比较慢
拜托写清楚
private void btnOK_Click(object sender, EventArgs e)
{
OpenFileDialog fileDialog = new OpenFileDialog();
if (fileDialog.ShowDialog() == DialogResult.OK)
{
string strFileName = fileDialog.FileName; this.txtAddress.Text = strFileName; OleDbConnection myConn = new OleDbConnection();
string connStr=" Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+strFileName+";Extended Properties=Excel 8.0";
myConn.ConnectionString = connStr;
myConn.Open(); string sql = " SELECT * FROM [Sheet1$] ";
OleDbDataAdapter da = new OleDbDataAdapter(sql, myConn); DataSet ds = new DataSet();
da.Fill(ds);
this.dgData.DataSource = ds.Tables[0].DefaultView;
myConn.Close();
}
}//从DataGridView中将数据导入SQL
private void btnImport_Click(object sender, EventArgs e)
{
if (this.dgData.Rows.Count < 0)//dgData:DatagridView
{
MessageBox.Show("没有可导入的数据!")
return;
} this.Cursor = Cursors.WaitCursor;
SqlParameter[] paramter = new SqlParameter[2];
paramter[0] = new SqlParameter("@UserID",SqlDbType.NVarChar,20);
paramter[1] = new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
for (int i = 0; i < dgData.Rows.Count; i++)
{
paramter[0].Value = this.dgData.Rows[i].Cells[0].Value.ToString().Trim();
paramter[1].Value = this.dgData.Rows[i].Cells[1].Value.ToString().Trim();
string spName = "sp_ImportData";//存储过程执行插入 ,也可以是SQL语句
try
{
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, spName, paramter);
}
catch (Exception ex)
{
throw ex;
}
}
MessageBox.Show("导入成功!");
this.Cursor = Cursors.Default;
}