Excel data import into SQL Server---------work notes

Invoke page:

Using system;using system.collections.generic;using system.linq;using system.web;using System.Web.UI;using System.web.ui.webcontrols;using jumi.bll.business;using system.data;using system.data.oledb;using    System.windows.forms;using jumi.model.business;using Jumi.bll.business;namespace JuMi.Web.Form.Manager.Business{        Public partial class WebForm1:JuMi.Web.UI.BasePage {protected void Page_Load (object sender, EventArgs e) {}///<summary>//Import advertiser information///</summary>//<param name= "Sender"  ></param>//<param name= "E" ></param> protected void Btnadverinfo_click (object sender,            EventArgs e) {DataTable dt = exceldemo.getexceltodatatable ("f:\\demo\\exceldemo.xlsx", "table");            int count = 0; for (int i = 0; i < dt. Rows.Count;                i++) {Model_advertiser Model = new Model_advertiser (); Model. Adver_companyname= dt. Rows[i][0]. ToString ();//name model. adver_distinguish = dt. ROWS[I][1]. ToString ();//Tax number model. Adver_regisphone = dt. ROWS[I][2]. ToString ();//Phone model. Adver_banknumber = dt. ROWS[I][3]. ToString ();//bank account model. adver_bankaddress = dt. ROWS[I][4]. ToString ();//bank address model. adver_regisadress = dt. ROWS[I][5]. ToString ();//Registered address model.                adver_state = 0;                if (Advertiserbll.add (model) > 0) {count++; }} if (Count >= dt. Rows.Count) {jscriptmsgsuccess ("Advertiser information import successful!            "," "," function () {api.reload ();} "); else {Jscriptmsgerror ("Advertiser info import failed! "," "," function () {api.reload ();} ");}}}

　　

Implementation Method page:

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Data;usingSystem.Data.OleDb;namespacejumi.bll.business{ Public classExceldemo {/// <summary>        ///Excel data import datable/// </summary>        /// <param name= "FileUrl" >file path</param>        /// <param name= "table" ></param>        /// <returns></returns>         Public StaticDataTable getexceltodatatable (stringFILEURL,stringtable) {            //support for. xls before office2007//Const string cmdtext = "Provider=Microsoft.Jet.OLEDB.4.0;Data source={0}; Extended properties= ' Excel 8.0;imex=1 ' ;//Link String//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 '"; DataTable DT=NULL; //Establish a connectionOleDbConnection conn =NewOleDbConnection (string.            Format (Cmdtext, FILEURL)); Try            {                //Open Link                if(Conn. State = = Connectionstate.broken | | Conn. state = =connectionstate.closed) {conn.                Open (); } DataTable schematable= Conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables,NULL); //gets the first sheet name of Excel                stringSheetName1 = schematable.rows[0]["table_name"]. ToString ().                Trim (); //Querying sheet data                stringSTRSQL1 ="select * FROM ["+ sheetName1 +"]"; OleDbDataAdapter da=NewOleDbDataAdapter (STRSQL1, conn); DataSet DS=NewDataSet (); da.                Fill (ds, table); DT= ds. tables[""+ table +""]; returnDT; }            Catch(Exception exc) {Throwexc; }            finally{Conn.                Close (); Conn.            Dispose (); }        }    }}

Excel data import into SQL Server---------work notes