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; } }