C # implementation code for importing Excel data to a database

Assume that the data in Excel is as follows:

The database creation table is as follows:

Here, Id is the auto-increment field:

Code:
Copy codeThe 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
{
Protected void Page_Load (object sender, EventArgs e)
{
FileSvr fileSvr = new FileSvr ();
System. data. dataTable dt = fileSvr. getExcelDatatable ("C: \ Users \ NewSpring \ Desktop \ Demo \ InExcelOutExcel \ excel \ ExcelToDB.xlsx", "mapTable ");
FileSvr. InsetData (dt );
}
}
Class FileSvr
{
/// <Summary>
/// Import Excel Data to Datable
/// </Summary>
/// <Param name = "fileUrl"> </param>
/// <Param name = "table"> </param>
/// <Returns> </returns>
Public System. Data. DataTable GetExcelDatatable (string fileUrl, string table)
{
// Only. xls is supported before office2007.
// Const string plain text = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = {0}; Extended Properties = 'excel 8.0; IMEX = 1 ';";
// Supports .xlsand .xlsx, that is, HDR = Yes in versions such as office2010 indicates that the first line is the title, not the data;
Const string shorttext = "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 (plain text, fileUrl ));
Try
{
// Open the connection
If (conn. State = ConnectionState. Broken | conn. State = ConnectionState. Closed)
{
Conn. Open ();
}

System. Data. DataTable schemaTable = conn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, null );

// Obtain the first Sheet name in Excel
String sheetName = schemaTable. Rows [0] ["TABLE_NAME"]. ToString (). Trim ();

// Query 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 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;
}
}
}

Running result: