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