Excel数据导入DataGridView存入数据库

 1    private void Form1_Load(object sender, EventArgs e)
 2 {
 3         BindData("select *from pers");
 4    }
 5 
 6 //绑定数据
 7         private void BindData(string sql)
 8         {
 9             SqlConnection conn = new SqlConnection("uid=sa;pwd=123456;server=(local);database=myoffice;max pool size=10000;");
10             conn.Open();
11             SqlDataAdapter da = new SqlDataAdapter(sql, conn);
12             DataSet ds=new DataSet();
13             da.Fill(ds,"pers");
14             dataGridView1.DataSource = ds.Tables["pers"];
15             ds.Dispose();
16             conn.Close();
17         }
18         
19  //把excel数据读入dataset返回l数据集
20         private DataSet xsldata()
21         {
22             OpenFileDialog openFile = new OpenFileDialog();//打开文件对话框。
23             openFile.Filter = ("Excel 文件(*.xls)|*.xls");//后缀名。
24             if (openFile.ShowDialog() == DialogResult.OK)
25             {
26                 string filename = openFile.FileName;
27                 int index = filename.LastIndexOf("\\");//截取文件的名字
28                 filename = filename.Substring(index + 1);
29                 string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFile.FileName + ";Extended Properties='Excel 8.0;IMEX=1'";
30                 System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
31                 string strCom = "SELECT * FROM [sheet1$]";
32                 Conn.Open();
33                 System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
34                 DataSet ds = new DataSet();
35                 myCommand.Fill(ds, "[sheet1$]");
36                 Conn.Close();
37                 return ds;
38             }
39             else
40                 return null;
41 
42         }
43         //excel导入数据到数据库
44         private void button2_Click(object sender, EventArgs e)
45         {
46             SqlConnection conn = new SqlConnection("uid=sa;pwd=123456;server=(local);database=myoffice;max pool size=10000;");
47             conn.Open();
48             SqlCommand cmd = new SqlCommand();
49             cmd.Connection = conn;
50             DataSet ds = xsldata();
51             for (int i = 0; i < ds.Tables[0].Rows.Count;i++ )
52             {
53                 cmd.CommandText = "insert into pers(name,sex,address)values('" + ds.Tables[0].Rows[i][1] + "'," + ds.Tables[0].Rows[i][2] + ",'" + ds.Tables[0].Rows[i][3] + "')";
54                 cmd.ExecuteNonQuery();
55             }
56             conn.Close();
57             BindData("select *from pers");
58         }

 

转载于:https://www.cnblogs.com/zhuwenjuan/archive/2012/08/08/2628172.html