C # Implementation code for importing Excel data into a database-practical tips

If the data in Excel is as follows:

The database is built as follows:

Where ID is a self-added field:

Code:

Copy Code code 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
{
protected void Page_Load (object sender, EventArgs e)
{
FileSvr filesvr = new FileSvr ();
System.Data.DataTable dt = filesvr.getexceldatatable ("c:\\users\\newspring\\desktop\\demo\\inexceloutexcel\\ Inexceloutexcel\\excel\\exceltodb.xlsx "," maptable ");
Filesvr.insetdata (DT);
}
}
Class FileSvr
{
<summary>
Excel data import datable
</summary>
<param name= "FileUrl" ></param>
<param name= "Table" ></param>
<returns></returns>
Public System.Data.DataTable getexceldatatable (string fileUrl, String table)
{
Office2007 only supports. xls before
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 represent the first line is the title, not the data;
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;
Establish a connection
OleDbConnection conn = new OleDbConnection (string. Format (Cmdtext, FILEURL));
Try
{
Open connection
IF (Conn. State = = Connectionstate.broken | | Conn. state = = connectionstate.closed)
{
Conn. Open ();
}

System.Data.DataTable schematable = conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, NULL);

Get the first sheet name for Excel
String sheetname = schematable.rows[0]["table_name"]. ToString (). Trim ();

               //query for data in sheet
                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;
           }
            finally
             {
                 Conn. Close ();
                Conn. Dispose ();
           }

       }

       ///<summary>
       / Import data from System.Data.DataTable to the database
       ///</summary>
        ///<param name= "DT" ></PARAM>
        ///<returns></returns>
        public int Insetdata (System.Data.DataTable DT)
        {
             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.connectionstrings["ConnectionStr"]. ToString ();
SqlConnection SqlConnection = new SqlConnection (strconnection);
Try
{
SqlConnection SqlConnection = new SqlConnection (strconnection);
Sqlconnection.open ();
SqlCommand SQLCMD = new SqlCommand ();
Sqlcmd.commandtext = strSQL;
Sqlcmd.connection = SqlConnection;
SqlDataReader SqlDataReader = Sqlcmd.executereader ();
i++;
Sqldatareader.close ();
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Sqlconnection.close ();

}
if (opdb. Excsql (strSQL))
i++;
}
return i;
}
}
}

Run Result: