Excel 导入 Gridview

  Excel 导入 Gridview

   /// <summary>

    /// 导入EXCEL
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void BtnIN_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile == false) //HasFile用来检查FileUpload是否有指定文件
        {
            Response.Write("<script>alert('请您选择Excel文件')</script> ");
            return; //当无文件时,返回
        }
        //
        string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();/ /System.IO.Path.GetExtension获得文件的扩展名
        if (IsXls != ".xls")
        {
            Response.Write("<script>alert('只可以选择Excel文件')</script>");
            return; //当选择的不是Excel文件时,返回
        }
        string filename = DateTime.Now.ToString("yyyymmdd") + FileUpload1.FileName;              //获取Execle文件名  DateTime日期函数
        string savePath = Server.MapPath((@"~\Source\UpLoadFile\Excel\") + filename); //Server.MapPath 获得虚拟服务器相对路径
        FileUpload1.SaveAs(savePath);    
        DataSet ds = new DataSet();
        string tablename = "Exstudent";
        ds = ExcelSqlConnection(savePath, tablename); / /关键方法,返回DataSet
        GridViewStudent.DataSource = ds;
        GridViewStudent.DataBind();
    }
    /// <summary>
    /// 读取到EXCEL表，返回数据集
    /// </summary>
    /// <param name="filepath"></param>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
    {
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
        //string strCon = "provider = microsoft.ace.oledb.12.0;data source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
        OleDbConnection ExcelConn = new OleDbConnection(strCon);
        try
        {
            string strCom = string.Format("SELECT * FROM [Sheet1$]");
            ExcelConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds, "[" + tableName + "$]");
            ExcelConn.Close();
            return ds;
        }
        catch
        {


            ExcelConn.Close();
            return null;
        }
    }