Shulou(Shulou.com)06/03 Report--

This article introduces the relevant knowledge of "how to import Excel data into the database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

If the data in Excel is as follows:

The table of the database is as follows:

Where Id is a self-increasing field:

Code:

The copy 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\\ InExcelOutExcel\\ excel\\ ExcelToDB.xlsx", "mapTable")

FileSvr.InsetData (dt)

}

}

Class FileSvr

{

/ / /

/ Import Excel data into Datable

/ / /

/ / /

/ / /

/ / /

Public System.Data.DataTable GetExcelDatatable (string fileUrl, string table)

{

/ / office2007 previously only supported .xls

/ / const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= {0}; Extended Properties='Excel 8.0

/ / supports .xls and .xlsx, that is, versions of HDR=Yes including office2010 represent the first row is the title, not 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 the 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 of 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 ()

}

}

/ / /

/ Import data from System.Data.DataTable to the database

/ / /

/ / /

/ / /

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 ()

ITunes +

SqlDataReader.Close ()

}

Catch (Exception ex)

{

Throw ex

}

Finally

{

SqlConnection.Close ()

}

/ / if (opdb.ExcSQL (strSql))

/ / iTunes +

}

Return i

}

}

}

Running result:

This is the end of the introduction of "how to import Excel data into the database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.