C# execl数据导入

1.前台： <asp:FileUpload ID="FileUpload1" runat="server" CssClass="btn btn-default" />

           <asp:Button ID="btnsearch" CssClass="btn btn-default" OnClick="btnsearch_Click" runat="server" Text="导入" />

 

2. 后台操作 

string Extension = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower();
 if (Extension != ".xls" && Extension != ".xlsx")
   {
       Response.Write("<script>alert('文件格式错误，请重新选择！');</script>");
       return;
   }
DataTable dt = GetExcelTable(FileUpload1);

做重要的是：通过fileupload获取选中excel数据，并转换为datatable 类型

/// <summary>
        /// 通过fileupload获取选中excel数据，并转换为datatable 类型
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static DataTable GetExcelTable(FileUpload file)
        {
            try
            {
                string path = "";
                string FilePath = System.Web.HttpContext.Current.Server.MapPath("~/");
                FilePath += "\\excel\\"; //路径
                string Extension = System.IO.Path.GetExtension(file.PostedFile.FileName).ToLower();
                if (Extension == ".xls" || Extension == ".xlsx")
                {
                    Random ran = new Random();
                    string iran = ran.Next(100000000, 1000000000).ToString();
                    string NewFilePath = DateTime.Now.ToString("yyyyMMddHHmmssfff") + iran + Extension;
                    file.SaveAs(FilePath + NewFilePath);
                    path = FilePath + NewFilePath;
                }
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
                //获取excel数据  
                DataTable dt1 = new DataTable("exceltable");
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();

                DataTable dt = conn.GetSchema("TABLES");
                if (dt.Rows.Count > 0)
                {
                    string selSqlStr = string.Format("select * from [{0}]", dt.Rows[0]["TABLE_NAME"]);
                    OleDbDataAdapter oleDa = new OleDbDataAdapter(selSqlStr, conn);
                    oleDa.Fill(dt1);
                }
                conn.Close();
                return dt1;
            }
            catch
            {
                return null;
            }
        }