Excel import in Asp.net (using Microsoft oledb driver)

 

Code
// Configure nodes in 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
1 Protected   Void Btnupload_click ( Object Sender, eventargs E)
  2 { // If no logic is added here, if the Excel file does not existProgramException will be reported
  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. receivettings [ " 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 import ') " ;
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 ('import failed') </SCRIPT>; " );
36 Return ;
37 }
38 Finally
39 {
40
41 Conn. Close ();
42 Conn. Dispose ();
43 }
44 Response. Write ( " <Script language = 'javascript '> window. Alert ('imported successfully') </SCRIPT>; " );
45 Exldatagrid. Visible = False ;
46
47 Openandbindnew ();
48
49 Lbwarning. Visible = False ;
50 Lbwarnings. Visible = True ;
51 Lbwarnings. Text = " Program Information imported in this tutorial " ;
52 }
53 Else
54 {
55 Response. Write ( " <Script language = 'javascript '> window. Alert ('excel file does not exist! ') </SCRIPT>; " );
56 }
57 }