C # Implementation code that imports Excel data into a database

C # Implementation code that imports Excel data into a database

If the data in Excel is as follows:

The database is built as follows:

Where ID is the self-increment field:

Code:

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 {Protec            Ted void Page_Load (object sender, EventArgs e) {filesvr filesvr = new FileSvr (); System.Data.DataTable dt = filesvr.getexceldatatable ("C:\\users\\newspring\\desktop\\demo\\inexceloutexcel\\in            Exceloutexcel\\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//const string cmdtext = "Provider=Microsoft.Jet.OLEDB.4.0;Data Sou RCE={0};            Extended properties= ' Excel 8.0;imex=1 '; Support for. xls and. xlsx, which includes versions of office2010 such as Hdr=yes, represent the first row is the title, not the data; Const string cmdtext = "Provider=microsoft.ace.oledb.1 2.0;data source={0}; Extended properties= ' Excel 12.0; Hdr=yes;            Imex=1 ' ";            System.Data.DataTable dt = null; Establish the 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); Gets the first sheet name of Excel, String sheetname = schematable.rows[0]["table_name"]. ToString ().                Trim ();  Querying 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 database///</summary>//&lt ;p Aram name= "DT" ></param>///<returns></returns> public int Insetdata (System.Data.Data            Table 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, off                SETLNG, 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; }    }}

Operation Result:

The above is the content of C # to import Excel data into the database implementation code, more relevant content please pay attention to topic.alibabacloud.com (www.php.cn)!

