EXCEL导入

 protected void Button1_Click(object sender, EventArgs e)
    {
        String strConn;
        strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection Conn = new SqlConnection(strConn);//定义新的数据连接控件并初始化
       

        Conn.Open();//打开连接
        try
        {
            string fileurl = typename(FileUpload1);//调用typename方法取得excel文件路径
            DataSet ds = new DataSet();//取得数据集
            //GetDataFromExcelWithAppointSheetName(fileurl);
            ds = xsldata(fileurl);
            int errorcount = 0;//记录错误信息条数
            int insertcount = 0;//记录插入成功条数
            int updatecount = 0;//记录更新信息条数
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
               
                string stuname = ds.Tables[0].Rows[i][0].ToString();
                string stusex = ds.Tables[0].Rows[i][1].ToString();
              
                if (stuname != "" && stusex != "" )
                {
                    //SqlCommand selectcmd = new SqlCommand("select count(*) from zy_class   where zhuanye='" + zhuanye + "'and classname='" + classname + "'", Conn);
                    //int count = Convert.ToInt32(selectcmd.ExecuteScalar());
                    //if (count > 0)
                    //{
                    //    SqlCommand selectcmd2 = new SqlCommand("select count(*) from stud where   stuid='" + stuid + "'", Conn);
                    //    int count2 = Convert.ToInt32(selectcmd2.ExecuteScalar());
                    //    if (count2 > 0)
                    //    {
                    //        SqlCommand updatecmd = new SqlCommand("update stud set   stuname='" + stuname + "',stusex='" + stusex + "',zhuanye='" + zhuanyei + "',classname='" + classname + "'   where stuid='" + stuid + "'", Conn);
                    //        updatecmd.ExecuteNonQuery();
                    //        updatecount++;
                    //    }
                    //    else
                    //    {
                            SqlCommand insertcmd = new SqlCommand("insert into TestName    values('" + stuname + "','" + stusex + "')", Conn);
                            insertcmd.ExecuteNonQuery();
                            insertcount++;
                        //}
                    //}
                    //else
                    //{
                    //    Response.Write("<script language='javascript'>alert('专业或班级信息有错！导入失败！请检查！');</script>");
                    //    break;

                    //}
                }
                else
                {
                    errorcount++;
                }
            }
            Response.Write("<script language='javascript'>alert('" + insertcount + "条数据导入成功！" + updatecount + "条数据更新成功！" + errorcount + "条数据部分信息为空没有导入！');</script>");
        }
        catch (Exception ex)
        {
            Response.Write("<script language='javascript'>alert('导入失败！');</script>");
        }
        finally
        {
            Conn.Close();//关闭连接
        }    


    }
    //把EXCEL文件上传到服务器并返回文件路径

    private String typename(FileUpload fileloads)
    {
        string fullfilename = fileloads.PostedFile.FileName;
        string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1);
        string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1);
        string murl = "";
        if (type == "xls")
        {
            fileloads.PostedFile.SaveAs(Server.MapPath("excel") + "\\" + filename);
            murl = (Server.MapPath("excel") + "\\" + filename).ToString();
        }
        else
        {
            Response.Write("<script language='javascript'>alert('导入文件格式不对!');</script>");

        }
        return murl;
    }



    //把excel数据读入dataset返回l数据集
    private DataSet xsldata(string filepath)
    {
        //string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'";
        string strCon = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filepath + ";Extended Properties='Excel 12.0; HDR=No; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
        
        System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
        string strCom = "SELECT * FROM [teset$]";
        Conn.Open();
        System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
        DataSet ds = new DataSet();

        myCommand.Fill(ds, "[teset$]");
        Conn.Close();
        return ds;
    }
    /// <summary>
    /// 根据excel的文件的路径提取其中表的数据
    /// </summary>
    /// <param name="Path">Excel文件的路径</param>
    private void GetDataFromExcelWithAppointSheetName(string Path)
    {
        //连接串
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);

        conn.Open();

        //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等  
        DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

        //包含excel中表名的字符串数组
        string[] strTableNames = new string[dtSheetName.Rows.Count];
        for (int k = 0; k < dtSheetName.Rows.Count; k++)
        {
            strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
        }

        OleDbDataAdapter myCommand = null;
        DataTable dt = new DataTable();

        //从指定的表明查询数据,可先把所有表明列出来供用户选择
        string strExcel = "select * from [" + strTableNames[0] + "]";
        myCommand = new OleDbDataAdapter(strExcel, strConn);
        dt = new DataTable();
        myCommand.Fill(dt);

       
    }