【原创】asp.net中Excel导入（使用微软OLEDB驱动）

 

//web.config<configuration>中配置节点
<appSettings>
  <add key="SqlString" value="uid=sa;PWD=sa;DATA SOURCE=(local);INITIAL CATALOG=chinasuntv" />
  <add key="ExcelStr" value="Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="/>
 
  <add key="DataBase" value="/program/UploadFiles/Program.xls"></add>
 </appSettings>

 

 1protected void btnUpLoad_Click(object   sender,   EventArgs   e) 
 2        {   //若此处未加逻辑判断，如果excel文件不存在则程序将会报异常
 3            if(System.IO.File.Exists(MapPath(DataBase)))
 4            {
 5                
 6                OleDbConnection   OleCon   =   new   OleDbConnection( ExcelStr+MapPath(DataBase)); 
 7                OleDbDataAdapter   OleDAp   =   new   OleDbDataAdapter( "SELECT  prgName,PlayTime,prgColumn FROM  [Sheet1$] ", OleCon); 
 8                DataSet   ds   =   new   DataSet(); 
 9                OleDAp.Fill(ds);     
10                
11                string    prgName,playTime,prgColumn,sSQL;
12                SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["SqlString"]);
13                conn.Open();
14                SqlCommand cmd;
15                try 
16                {
17                    
18                    foreach   (DataRow   dr   in   ds.Tables[0].Rows) 
19                    { 
20                        
21                        prgName   =   dr["prgName"].ToString(); 
22                        playTime   =   dr["PlayTime"].ToString(); 
23                        prgColumn   =   dr["prgColumn"].ToString(); 
24                        
25                        sSQL   =   "Insert Into Ax_Program (prgName,PlayTime,prgColumn,prgComment) Values ('"+prgName +"','" + playTime+ "','"+prgColumn+"','"+DateTime.Now.ToString("yyyy-MM-dd HH:mm")+"Excel导入')";                                
26                        cmd = new SqlCommand(sSQL,conn);
27                        cmd.CommandType = CommandType.Text;
28                        cmd.ExecuteNonQuery();
29                        cmd.Dispose();
30
31                    }
32                } 
33                catch   (Exception) 
34                { 
35                    Response.Write("<script language='javascript'>window.alert('导入失败')</script>;"); 
36                    return; 
37                } 
38                finally
39                {
40                    
41                    conn.Close();
42                    conn.Dispose();
43                }
44                Response.Write("<script language='javascript'>window.alert('导入成功')</script>;");
45                ExlDataGrid.Visible=false;
46            
47                OpenAndBindNew();
48                
49                lbWarning.Visible=false;
50                lbWarningS.Visible=true;
51                lbWarningS.Text="本此操作导入的节目信息";
52            }
53            else
54            {
55                Response.Write("<script language='javascript'>window.alert('Excel文件不存在!')</script>;");
56            }
57        }