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 }