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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.