把Excel数据导入到数据库

引入命名空间

1 using System.IO;
2 using System.Data;
3 using System.Data.OleDb;

引入命名空间

首先要把Excel上传到服务器

 1  //上传Excel文件到服务器
 2         protected void btnUpLoad_Click(object sender, EventArgs e)
 3         {
 4             ddlSelect.Items.Clear();
 5             if (this.fuExcel.HasFile)
 6             {
 7                 string filename = fuExcel.PostedFile.FileName;
 8                 FileInfo info = new FileInfo(filename);
 9                 string name = info.Name;
10                 string type = info.Extension;
11                 if (type == ".xls" || type == ".xlsx")
12                 {
13                     string savepath = Server.MapPath("~/Excel");
14                     this.fuExcel.SaveAs(savepath+"\\"+name);
15                     filename=savepath+"\\"+name;
16                     getSheetName(filename);
17                     ViewState["FileName"] = filename;
18                 }
19                 else
20                 {
21                     Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('文件格式不正确,请重新选择！')</script>");
22                 }
23             }
24             else
25             {
26                 Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('请选择要导入数据的Excel文件!')</script>");
27             }
28         }

上传Excel到服务器

 //获取EXCEL工作簿名称
        private DataSet getSheetName(string filepath)
        {
            DataSet ds = new DataSet();
            string strCon;
            strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= '" + filepath + "'; Extended Properties='Excel 12.0; HDR=YES;IMEX=1';";
            OleDbConnection ODConn = new OleDbConnection(strCon);
            ODConn.Open();
            DataTable dtOle = ODConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dtOle == null) return null;
            String[] excelSheets = new String[dtOle.Rows.Count];
            int i = 0;
            foreach (DataRow row in dtOle.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                ddlSelect.Items.Add(new ListItem(excelSheets[i].Substring(0,excelSheets[i].Length-1),excelSheets[i].Substring(0,excelSheets[i].Length-1)));
                i++;
            }
            ODConn.Close();
            return ds;
        }

获取EXCEL工作簿名称

根据选择的工作簿去导入数据

 1 //把数据导入到数据库中
 2         protected void btnImport_Click(object sender, EventArgs e)
 3         {
 4             if (!string.IsNullOrEmpty(ddlSelect.SelectedValue))
 5             {
 6                 DataTable dt = GetDate(ViewState["FileName"].ToString()).Tables[0];//获取Excel工作薄中的数据
 7                 if (dt.Rows.Count > 0)
 8                 {
 9                     int s=0;
10                     int ss = 0;
11                     for (int i = 0; i < dt.Rows.Count; i++)
12                     {
13                         DataSet j = sqlhelp.ReturnDataTable("select name from students where name=@0", dt.Rows[i][0].ToString());//去重
14                         if (j.Tables[0].Rows.Count>0)
15                         {
16                             s++;
17                         }
18                         else
19                         {
20                             int x = sqlhelp.ExecuteNonQuery("insert into students(Name,Sex,Birthday,PhoneNum,Post) values(@0,@1,@2,@3,@4)", dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString(), dt.Rows[i][2].ToString(), dt.Rows[i][3].ToString(), dt.Rows[i][3].ToString());//把数据导入到数据库
21                             ss += x;
22                         }
23                     }
24                     Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('导入成功有" + ss + "条,数据重复" + s + "条,共有数据" + dt.Rows.Count + "条')</script>");
25                     return;
26                 }
27                 else
28                 {
29                     Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('选中的工作簿内没有数据')</script>");
30                 }
31             }
32             else
33             {
34                 Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('请选择EXCEL的工作簿再进行导入工作')</script>");
35             }
36         }

把数据导入到数据库

 1 private DataSet GetDate(string filepath)
 2         {
 3             DataSet ds = new DataSet();
 4             string strCon, strCmm;
 5             strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= '" + filepath + "'; Extended Properties='Excel 12.0; HDR=YES;IMEX=1';";
 6             strCmm = "select   distinct   *   from   [" +ddlSelect.SelectedValue + "$] ";
 7             OleDbConnection oleConn = new OleDbConnection(strCon);
 8             OleDbCommand oleCmm = new OleDbCommand(strCmm, oleConn);
 9             OleDbDataAdapter oleDa = new OleDbDataAdapter(oleCmm);
10             oleDa.Fill(ds);
11             oleConn.Close();
12             return ds;
13         }

获取选中工作薄的数据