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