asp.net中Excel匯入（使用微軟OLEDB驅動）

 

Code
//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>

 

 

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