2、或者将上述的excel表格嵌入在winform程序窗口里实现在EXcel表格里输入数据及新增按钮和保存按钮来保存的功能。
你干嘛不把excel编辑好了以后再导入数据库呢?这方面比较容易实现,要想在excel中编程和数据库交互比较困难。
winform中可以使用datagrid控件实现excel的编辑功能,这个和数据库交互很容易实现。
private void btnOpen_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "*.xls|";
ofd.CheckFileExists = true;
ofd.CheckPathExists = true;
ofd.ShowDialog();
this.textBox1.Text = ofd.FileName.ToString();
}
private void btnInsert_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; " + "Data Source =" + this.textBox1.Text + ";Extended Properties=Excel 8.0";
OleDbConnection myConn = new OleDbConnection(strCon);
string sql = "Select * FROM [Sheet1$]";
try
{
myConn.Open();
OleDbDataAdapter oda = new OleDbDataAdapter(sql, myConn);
oda.Fill(ds, "[Sheet1$]");
myConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
string server = this.txtServer.Text.Trim();
string db = this.txtDB.Text.Trim();
string username = this.txtUserName.Text.Trim();
string userpwd = this.txtUserPwd.Text.Trim();
//这里的连接用来将数据写入SQLDB
string connectionString = @"server="+server+"; database="+db+"; uid="+username+";pwd="+userpwd+"";
SqlConnection con = new SqlConnection(connectionString);
string sqlGetAllDB = "select * from tb_Lot";
SqlDataAdapter daAllDB = new SqlDataAdapter(sqlGetAllDB, con);
DataSet dsAllDB = new DataSet();
DataGrid mygrid = new DataGrid();
mygrid.BeginInit();
mygrid.Location = new System.Drawing.Point(10, 240);
mygrid.Width = 1020;
mygrid.Height = 300;
this.Controls.Add(mygrid);
mygrid.EndInit();
mygrid.SetDataBinding(ds, "[Sheet1$]");
try
{
int num = ds.Tables[0].Rows.Count;
for (int i = 0; i < num; i++)
{
string ch1 = mygrid[i, 0].ToString();
string ch2 = mygrid[i, 1].ToString();
string ch3 = mygrid[i, 2].ToString();
string strii = "select * into from" + ds.Tables[0].TableName;
string strsql = "insert into tb_Lot values('" + ch1 + "','" + ch2 + "','" + ch3 + "')";
con.Open();
System.Data.DataTable dt = new System.Data.DataTable("tb_Lot");
SqlDataAdapter da = new SqlDataAdapter(strsql, con);
da.Fill(dt);
this.lblmessage.Text = "数据导入成功!";
this.groupBox2.Visible = true;
daAllDB.Fill(dsAllDB, "tb_Lot");
this.dataGridView1.DataSource = dsAllDB.Tables[0];
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("数据库连接失败!");
}
}
还不如这样直接将Excel的数据导入的数据库表中,在Excel里面来操作的话难度比较高。这方法我自己用过,可以将Excel的数据逐条导入的表中,前提是Excel的列数和表中的字段必须对应。楼主可以研究下EXCEL里的VBA,顺便再看看VBA里的ADODB.