asp.net 从Excel表导入数据到数据库中

http://www.cnblogs.com/hfzsjz/archive/2010/12/31/1922901.html

http://hi.baidu.com/ctguyg/item/ebc857e90e436ae1fb42ba01

 

 1   <form action="" method="post" runat ="server">
 2         <div>
 3       <span>请选择文件：</span><asp:FileUpload ID="FileUpload1" runat="server" />
 4         <asp:Button ID="btnExport" runat="server" Text="导入" onclick="btnExport_Click" />
 5     </div>
 6     <div>
 7         <asp:GridView ID="GridView1" runat="server">
 8         </asp:GridView>
 9     </div>
10     </form>
 1         protected void btnExport_Click(object sender, EventArgs e)
 2         {
 3             try
 4             {
 5                 if (this.FileUpload1.HasFile)
 6                 {
 7                     DataTable inputdt = new DataTable();
 8                     int len = this.FileUpload1.FileName.ToString().Trim().Length;
 9                     string path = "~/temp/upfile/" + this.FileUpload1.FileName.ToString().Trim();
10                     path = Server.MapPath(path);
11                     this.FileUpload1.SaveAs(path); //上传文件
12                     inputdt = JDBMS.DBUtility.MDBHelper.InputExcel(path, this.FileUpload1.FileName.ToString().Trim().Substring(0, len - 4));
13                     if (Session["inputdt"] != null)
14                         Session.Remove("inputdt");
15                     Session.Add("inputdt", inputdt);
16                     if (inputdt.Rows.Count > 0)
17                     {
18                         this.GridView1.DataSource = inputdt;
19                         this.GridView1.DataBind();
20                     }
21                 }
22                 else
23                 {
24                     throw new Exception("请选择导入表的路径");
25                 }
26             }
27             catch (Exception ex)
28             {
29                 Response.Write("<script language='javascript'>alert('" + ex.Message + "');</script>");
30             }
31         }
 1      /// <summary>
 2         /// 导入数据到数据集中
 3         /// </summary>
 4         /// <param name="Path"></param>
 5         /// <param name="TableName"></param>
 6         /// <param name="tablename2">如果这个有就以他为表名，没有的话就以TableName</param>
 7         /// <returns></returns>
 8         public static DataTable InputExcel(string Path, string TableName)
 9         {
10             try
11             {
12                 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
13                 OleDbConnection conn = new OleDbConnection(strConn);
14                 conn.Open();
15                 string strExcel = "";
16                 OleDbDataAdapter myCommand = null;
17                 strExcel = "select * from [" + TableName + "$]";
18                 myCommand = new OleDbDataAdapter(strExcel, strConn);
19                 DataTable dt = new DataTable();
20                 myCommand.Fill(dt);
21                 conn.Close();
22                 return dt;
23             }
24             catch (Exception ex)
25             {
26                 throw new Exception(ex.Message);
27             }
28         }

 

 

 

posted on 2014-05-15 00:14  L秦岭  阅读(272)  评论(0)  编辑  收藏  举报

导航