把Excel数据导入到数据库

引入命名空间

using System.IO;
using System.Data;
using System.Data.OleDb;

首先要把Excel上传到服务器

 //上传Excel文件到服务器
        protected void btnUpLoad_Click(object sender, EventArgs e)
        {
            ddlSelect.Items.Clear();
            if (this.fuExcel.HasFile)
            {
                string filename = fuExcel.PostedFile.FileName;
                FileInfo info = new FileInfo(filename);
                string name = info.Name;
                string type = info.Extension;
                if (type == ".xls" || type == ".xlsx")
                {
                    string savepath = Server.MapPath("~/Excel");
                    this.fuExcel.SaveAs(savepath+"\\"+name);
                    filename=savepath+"\\"+name;
                    getSheetName(filename);
                    ViewState["FileName"] = filename;
                }
                else
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('文件格式不正确,请重新选择！')</script>");
                }
            }
            else
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('请选择要导入数据的Excel文件!')</script>");
            }
        }

 //获取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;
        }

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

//把数据导入到数据库中
        protected void btnImport_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(ddlSelect.SelectedValue))
            {
                DataTable dt = GetDate(ViewState["FileName"].ToString()).Tables[0];//获取Excel工作薄中的数据
                if (dt.Rows.Count > 0)
                {
                    int s=0;
                    int ss = 0;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataSet j = sqlhelp.ReturnDataTable("select name from students where name=@0", dt.Rows[i][0].ToString());//去重
                        if (j.Tables[0].Rows.Count>0)
                        {
                            s++;
                        }
                        else
                        {
                            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());//把数据导入到数据库
                            ss += x;
                        }
                    }
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('导入成功有" + ss + "条,数据重复" + s + "条,共有数据" + dt.Rows.Count + "条')</script>");
                    return;
                }
                else
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('选中的工作簿内没有数据')</script>");
                }
            }
            else
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('请选择EXCEL的工作簿再进行导入工作')</script>");
            }
        }

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