Import Excel data into a database

Excel as follows, this sheet is called "line"

The database table is as follows

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingsystem.web;5 usingSystem.Data;6 usingSystem.Data.OleDb;7 usingSystem.Configuration;8 usingSystem.Data.SqlClient;9 Ten namespaceInsertData One { A     classFileSvr -     { -         /// <summary> the         ///get the true length of a DataRow -         /// </summary> -         /// <param name= "Dr" >DataRow</param> -         /// <returns>true Length</returns> +          Public intGetrowlen (DataRow DR) -         { +             inti =0; A              for(; I < Dr. Itemarray.length; i++) at                 if(Dr[i]. ToString (). Trim () = ="") Break; -             returni; -         } -         /// <summary> -         /////excel The specified sheet data import datable -         /// </summary> in         /// <param name= "FileUrl" >Excel Path</param> -         /// <param name= "SheetName" >Worksheet name</param> to         /// <returns></returns> +          PublicDataTable getexceldatatable (stringFILEURL,stringSheetName)//Excel specifies the worksheet data import datable -         { the             //support for. xls and. xlsx, that is, include office2010 and other versions of Hdr=yes that represent the first line is the title, not the data; *             Const stringCmdtext ="Provider=microsoft.ace.oledb.12.0;data source={0}; Extended properties= ' Excel 12.0; Hdr=yes; Imex=1 '"; $ Panax NotoginsengDataTable dt =NULL; -             //Establish a connection theOleDbConnection conn =NewOleDbConnection (string. Format (Cmdtext, FILEURL)); +             Try A             { the                 //Open Connection +                 if(Conn. State = = Connectionstate.broken | | Conn. state = =connectionstate.closed) -                 { $ Conn. Open (); $                 } -  -                 //querying for data in sheet the                 stringstrSQL ="select * FROM ["+ SheetName +"$]"; -OleDbDataAdapter da =NewOleDbDataAdapter (strSQL, conn);WuyiDataSet ds =NewDataSet (); the da. Fill (DS); -DT = ds. tables[0]; Wu                 returnDT; -             } About             Catch(Exception exc) $             { -                 Throwexc; -             } -             finally A             { + Conn. Close (); the Conn. Dispose (); -             } $         } the  Public voidInsertroute (DataTable DT,stringStrconnection,intDir//importing data from a DataTable to a database the         { the             intRetVal =0; the             intCount0 =0; -             intCount =0; in             intRouteno; theSqlConnection conn =NewSqlConnection (strconnection); the Conn. Open (); About             stringstrSQL; theSqlCommand SQLCMD =NewSqlCommand (); theSqlcmd.connection =Conn; theSQLCMD.PARAMETERS.ADD ("@ number", System.Data.SqlDbType.Int,4); +SQLCMD.PARAMETERS.ADD ("@ line number", System.Data.SqlDbType.Int,4); -SQLCMD.PARAMETERS.ADD ("@ line to", System.Data.SqlDbType.Int,4); theSQLCMD.PARAMETERS.ADD ("@ Line Name", System.Data.SqlDbType.NVarChar, -);BayiSQLCMD.PARAMETERS.ADD ("@ Originating Site", System.Data.SqlDbType.NVarChar, -); theSQLCMD.PARAMETERS.ADD ("@ bound to direction", System.Data.SqlDbType.NVarChar, -); theSQLCMD.PARAMETERS.ADD ("@ Update Time", System.Data.SqlDbType.DateTime,4); -             foreach(DataRow DrinchDt. Rows)//traversing row Data -             { theStrsql="Update line set line number [email protected] line number, line to [email protected] line, line name [email protected] Line name, origin site [email protected] originating site, open direction [email protected] to direction, update time [email protected] Update time where number [email protected] number"; theSqlcmd.commandtext =strSQL; theRouteno =convert.toint32 (dr[0]. ToString (). Trim ()); thesqlcmd.parameters["@ number"]. Value = routeno+dir*100000; -sqlcmd.parameters["@ line number"]. Value=Routeno; thesqlcmd.parameters["@ line to"]. Value=dir; thesqlcmd.parameters["@ Line Name"]. value=routeno+"Road"; thesqlcmd.parameters["@ Originating Site"]. value=dr[1]. ToString ();94sqlcmd.parameters["@ bound to direction"]. Value=dr[getrowlen (DR)-1]. ToString (); thesqlcmd.parameters["@ Update Time"]. Value =DateTime.Now; theRetVal =sqlcmd.executenonquery (); the                 if(RetVal = =0)98                 { AboutstrSQL =@"INSERT into line (number, line number, line, line name, originating site, bound direction, update time) VALUES (@ number, @ line number, @ line, @ Line name, @ Originating site, @ to direction, @ Update time)"; -Sqlcmd.commandtext =strSQL;101RetVal =sqlcmd.executenonquery ();102count++;103Console.WriteLine ("insert Line {0} succeeded! ", Routeno);104                 } the                 Else106                 {107count0++;108Console.WriteLine ("update line {0} succeeded! ", Routeno);109                 } the             }111 Conn. Close (); theConsole.WriteLine ("Insert {0} bar, update {1} route", Count, count0);113         } the}

View Code

The results are as follows

Import Excel data into a database