Importing excle data into a database

If the data in Excel is as follows:

The database is built as follows:

Where ID is the self-increment field:

Code:

  1 using System;  2 using System.Collections.Generic;  3 using System.Linq;  4 using System.Web;  5 using System.Web.UI;  6 using System.Web.UI.WebControls;  7 using System.Data;  8 using System.Data.OleDb; 9 using System.Configuration; Ten using System.Data.SqlClient; One-namespace Inexceloutexcel-{+-public partial class ExcelToDB:System.Web.UI.Page-Prote             CTED void Page_Load (object sender, EventArgs e): {filesvr filesvr = new FileSvr (); 19 System.Data.DataTable dt = filesvr.getexceldatatable ("c:\\users\\newspring\\desktop\\demo\\inexceloutexcel\\ Inexceloutexcel\\excel\\exceltodb.xlsx "," maptable "); Filesvr.insetdata (DT);         +/-<summary>//Excel data import datable 27 </summary>//<param name= "FILEURL" ></param>//<param name= "table" &G t;</param>//<retUrns></returns> Public System.Data.DataTable getexceldatatable (string fileUrl, string table) 32 {//office2007 only supports. xls//const string cmdtext = "Provider=Microsoft.Jet.OLEDB.4.0;Data S OURCE={0}; Extended properties= ' Excel 8.0;imex=1 '; 35//Support. xls and. xlsx, which includes versions of office2010 such as Hdr=yes for the first line is the title, not the data; the const string cmdtext = "Provider=mi Crosoft. Ace.oledb.12.0;data source={0}; Extended properties= ' Excel 12.0; Hdr=yes; Imex=1 ' "; Notoginseng System.Data.DataTable dt = null; 39//Establish connection OleDbConnection conn = new OleDbConnection (string. Format (Cmdtext, FILEURL)); Conn Try 42 {43//Open connection if (*. State = = Connectionstate.broken | | Conn. state = = connectionstate.closed) {Conn. Open (); System.Data.DataTable schematable = conn. GetoledbschemataBLE (oledbschemaguid.tables, NULL); 51 52//Gets the first sheet name of Excel in the string sheetname = schematable.rows[0]["table_name"]. ToString (). Trim (); 54 55//query data in sheet. String strSQL = "SELECT * FROM [" + SheetName + "]"; OleDbDataAdapter da = new OleDbDataAdapter (strSQL, conn); The DataSet ds = new DataSet (); Of the DA. Fill (ds, table); DT = ds. Tables[0]; The return DT;             (Exception exc) (67} 68) Finally, {Conn. Close (); Conn. Dispose ();         *//<summary> 77//Import data from System.Data.DataTable to database 78 </summary>//<param name= "DT" ></param>//&LT;RETURNS&GT;&LT;/RETURNS&G T Bayi PUBlic int insetdata (System.Data.DataTable dt) (+ 0) ($) A string of lat = ""; OFFSETLNG string = ""; Offsetlat string = ""; The (DataRow Dr in DT). Rows) (dr[LNG = "LNG"]. ToString (). Trim (); dr[lat = "lat"]. ToString (). Trim (); OFFSETLNG = dr["OFFSET_LNG"]. ToString (). Trim (); 94 Offsetlat = dr["Offset_lat"]. ToString (). Trim (); //SW = string. IsNullOrEmpty (SW)? "NULL": SW; //KR = string. IsNullOrEmpty (KR)? "NULL": KR; 98 strSQL String = String. Format ("Insert into Dbtoexcel (Lng,lat,offset_lng,offset_lat) Values (' {0} ', ' {1} ', {2},{3})", LNG, LAT, OFFSETLNG, Offsetlat); 101 String strconnection = configurationmanager.connectionstrings["ConnectionStr"]. ToString (); 102 SqlConnection Sqlconnection = new SqlConnection (strconnection); 103 try104 {//SqlConnection SqlConnection = new SqlConnection (strconnection); 106 Sqlconnection.open (); 107 Sql Command SQLCMD = new SqlCommand (); 108 Sqlcmd.commandtext = strsql;109 Sqlcmd.conne                     ction = sqlconnection;110 SqlDataReader SqlDataReader = Sqlcmd.executereader (); 111                 i++;112 Sqldatareader.close (); 113}114 catch (Exception ex) 115                     {ex;117 throw}118 finally119 {120 Sqlconnection.close (); 121 122}123//if (opdb. Excsql (strSQL)) 124//i++;125}126 return i;127}128}129}

Operation Result:

Importing excle data into a database