ASP.NET 导入excel 数据

1,需要给上传文件的目录给予权限

2、

 <asp:FileUpload ID="FileUpload1" runat="server" />  
        <asp:Button ID="btn_ExInto_pass" runat="server" Text="导入" OnClick="btn_ExInto_pass_Click" />

　　

 

 1   protected void btn_ExInto_pass_Click(object sender, EventArgs e)    //导入
 2         {
 3             if (FileUpload1.HasFile == false)
 4             {
 5                 Alert.Show("请您选择Excel文件");
 6                 return;//当无文件时,返回        
 7             }
 8             string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
 9             if (IsXls != ".xls")
10             {
11                 Alert.Show("只可以选择Excel文件");
12                 return;//当选择的不是Excel文件时,返回       
13             }
14             string filename = FileUpload1.FileName;                     //获取Execle文件名  
15             string dirPath = "UpLoadFile\\" + System.DateTime.Now.Year.ToString();
16             if (!Directory.Exists(System.Web.HttpContext.Current.Request.PhysicalApplicationPath + dirPath))
17                 Directory.CreateDirectory(System.Web.HttpContext.Current.Request.PhysicalApplicationPath + dirPath);
18 
19             Random rand = new Random(unchecked((int)DateTime.Now.Ticks));
20             string strpath = dirPath + "\\" + DateTime.Now.ToString("MMddHHmmss") + rand.Next(1000, 9999).ToString() + ".xls";  //获取Execle文件路径 
21             string strError = "", strRepeat = "", asMsg = "";
22             strpath = Server.MapPath(strpath);
23             FileUpload1.PostedFile.SaveAs(strpath);
24 
25             DataSet ds = ExecleDs(strpath, filename);
26             if (ds == null || ds.Tables[0].Rows.Count == 0)
27             {
28                 Alert.Show("Excel表为空表,无数据!");   //当Excel表为空时,对用户进行提示       
29             }
30             else
31             {
32                 //for add
33 
34                 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
35                 {
36                     string sql_str = "INSERT INTO PoInfo VALUES('" + ds.Tables[0].Rows[i][0] + "' ,'" + ds.Tables[0].Rows[i][1] + "'  ,'" + ds.Tables[0].Rows[i][2] + "' ,'" + ds.Tables[0].Rows[i][3] + "' ,'" + ds.Tables[0].Rows[i][4] + "'  ,'" + ds.Tables[0].Rows[i][5] + "'  ,'" + ds.Tables[0].Rows[i][6] + "'  ,'" + ds.Tables[0].Rows[i][7] + "'  ,'" + ds.Tables[0].Rows[i][8] + "'  ,'" + ds.Tables[0].Rows[i][9] + "'  ,'" + ds.Tables[0].Rows[i][10] + "'  ,'" + ds.Tables[0].Rows[i][11] + "'  ,'" + ds.Tables[0].Rows[i][12] + "'  ,'')";
37 
38                    int row = SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, sql_str);
39 
40                 }
41 
42                 Alert.Show("导入数据成功！");
43                 BindGrid();
44 
45             }
46         }
47         public DataSet ExecleDs(string filenameurl, string table)
48         {
49             string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
50             OleDbConnection conn = new OleDbConnection(strConn);
51             conn.Open();
52             OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
53             DataSet ds = new DataSet();
54             try
55             {
56                 odda.Fill(ds, table);
57             }
58             catch (Exception)
59             {
60                 throw;
61             }
62             finally
63             {
64                 odda.Dispose();
65                 conn.Close();
66                 conn.Dispose();
67                 System.IO.File.Delete(filenameurl);    //删除文件
68             }
69             return ds;
70         }

 

posted @ 2015-01-07 13:52  NathanJoy  阅读(287)  评论(0)  编辑  收藏  举报