C # Implementing code to import Excel data into the database

This article mainly introduces C # to import Excel data into the database implementation code, the need for friends can refer to

if the data in Excel is as follows : database is modeled as follows: where ID is the self -added field:     Code:       Code is as follows: using System; Using System.Collections.Generic; Using System.Linq; Using System.Web; Using System.Web.UI; Using System.Web.UI.WebControls; Using System.Data; Using System.Data.OleDb; Using System.Configuration; Using System.Data.SqlClient;   namespace Inexceloutexcel {    public partial class ExcelToDB:System.Web.UI.Page     {&nbsp ;       protected void Page_Load (object sender, EventArgs e)         {    &NBS P       FILESVR filesvr = new FileSvr ();             System.Data.DataTable dt = filesvr.getexceldatatable ("C: Usersnewspringdesktopdemoinexceloutexcelinexceloutexcelexcelexceltodb.xlsx "," maptable ");             filesvr.insetdata (DT);        }     {    class FileSvr     {       /// <summary>       ///Excel data import datable        ///</summary>        ///<param Nam E= "FileUrl" ></param>        ///<param name= "table" ></param>     & nbsp  ///<returns></returns>         public System.Data.DataTable getexceldatatable ( String fileUrl, String table)         {           //OFFICE2007 only supported. x LS            //const string cmdtext = "Provider=Microsoft.Jet.OLEDB.4.0;Data source={0};" Extended properties= ' Excel 8.0;imex=1 ';            //support. xls and. xlsx, that is, including office2010 and other versions of the   Hdr=yes on behalf of the first row is the title, not data;     &NB Sp       const string cmdtext = "Provider=microsoft.ace.oledb.12.0;data source={0};" Extended properties= ' Excel 12.0; Hdr=yes; Imex=1 ' ";               System.Data.DataTable dt = null;            //Set up connection             OleDbConnection conn = new OleD Bconnection (String. Format (Cmdtext, FILEURL));             try             {        &NBS P      //Open connection                 if (Conn. State = = Connectionstate.broken | | Conn. state = = connectionstate.closed)                 {        &NB Sp           Conn. Open ();                                   & nbsp System.Data.DataTable schematable = conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, NULL);                  //Get Excel's first sheet name           &NB Sp     String sheetname = Schematable.rows[0]["table_name"]. ToString (). Trim ();                  //query sheet Data             &NB Sp   String strSQL = "SELECT * FROM [" + SheetName + "]";                 OleDbDataAdapter da = new OleDbDataAdapter (strSQL, conn);                 DataSet ds = new DataSet ();                 da. Fill (ds, table);                 dt = ds. Tables[0];                   return DT;                         catch (Exception exc)             {                Throw exc       &NBS P    }             finally             {                Conn. Close ();                 Conn. Dispose ();                                ///&L t;summary>        ///import data from System.Data.DataTable to DB        ///</summary& Gt        ///<param name= "DT" ></param>        ///<returns></ returns>         public int insetdata (System.Data.DataTable dt)         {&nbsp ;           int i = 0;             String LNG = "";             String lat = "";             String offsetlng = "";             String offsetlat = "";               foreach (DataRow Dr IN dt. Rows)             {                LNG = dr["LNG"]. ToString (). Trim ();                 lat = dr["lat"]. ToString (). Trim ();                 OFFSETLNG = dr["OFFSET_LNG"]. ToString (). Trim ();                 Offsetlat = dr["Offset_lat"]. ToString (). Trim ();                  //SW = string. IsNullOrEmpty (SW)? "NULL": SW;                //KR = string. IsNullOrEmpty (KR)? "NULL": KR;                   String strSQL = string. Format (Insert into Dbtoexcel (Lng,lat,offset_lng,offset_lat) Values (' {0} ', ' {1} ', {2},{3}) ", LNG, LAT, OFFSETLNG, Offsetlat);                   string strconnection = Configurationmanager.connectionstrin gs["ConnEctionstr "]. ToString ();                 SqlConnection sqlconnection = new SqlConnection (strconnection);                 try                 {&nbs P                  //SqlConnection SqlConnection = new SqlConnection (Strconne ction);                     Sqlconnection.open ();                     SqlCommand SQLCMD = new SqlCommand ();                     Sqlcmd.commandtext = strSQL;                     sqlcmd.connection = SqlConnection;                     SqlDataReader SqlDataReader = Sqlcmd.executereader () ;                     i++;                     sqldatareader.close ();                                 catch (E Xception ex)                 {              &N Bsp     Throw ex;                                 finally                 {                  & nbsp Sqlconnection.close ();                   {               / /if (opdb. Excsql (strSQL))                //   i++;                         return i;        }   &nbsp {}       Run Result: