Excel is imported into the DataTable and then written to the database using the general method

What's the first thing? To see the Office version of your PC, my is Office 2013. In order to use the OLE DB program, you need to install an engine. The name is AccessDatabaseEngine.exe. It's not too much to introduce here. Its database connection string is "Provider=microsoft.ace.oledb.12.0;data source={0}; Extended properties= ' Excel 12.0; Hdr=yes; Imex=1 ' "

So, we are using OLE to read Excel.

1 File contents of Excel:

2 testing the page design diagram

3 Webconfig Setting database-related strings

4 test the Code of the page:

Simple layout, mainly look at the function:

<%@ Page language="C #"autoeventwireup="true"Codebehind="ExcelTodatabase.aspx.cs"inherits="Exceltodatabase.exceltodatabase"%><! DOCTYPE html>"http://www.w3.org/1999/xhtml">"Server"><meta http-equiv="Content-type"Content="text/html; Charset=utf-8"/> <title></title>"Form1"runat="Server"> <div> <asp:fileupload id="Excelfile"runat="Server"/> <asp:button id="Btnfileup"runat="Server"text="Submit"onclick="Btnfileup_click"/> <br/> <br/> <br/> <asp:gridview id="Gvexcel"runat="Server"> </asp:GridView> <br/> <br/> <asp:label id="Debug"runat="Server"text="Label"></asp:Label> <br/> </div> </form></body>
5 Main functions:
5.1 Importing files
5.2 Importing Excel into a DataTable
5.3 Writing a DataTable to the database
The complete code:
usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingsystem.web;usingSystem.Web.UI;usingSystem.Web.UI.WebControls;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Data.OleDb;usingSystem.IO;usingSystem.Configuration;namespaceexceltodatabase{ Public Partial classExcelTodatabase:System.Web.UI.Page {//Connection String        Private Static stringconnstring = configurationmanager.connectionstrings["ConnectionString"].        ConnectionString; protected voidPage_Load (Objectsender, EventArgs e) {        }        protected voidBtnfileup_click (Objectsender, EventArgs e) {            if(excelfile.hasfile) {//Check file name extensions                stringfilename =Path.getfilename (Excelfile.PostedFile.FileName); //Debug. Text = filename;                stringExtension =path.getextension (Excelfile.PostedFile.FileName); //Debug. Text = extension;                if(Extension! =". xlsx") {Response.Write ("<script>alert (' Sorry! You are not uploading a valid Excel file ') </script>"); }                Else                {                    //save to server first                    stringNewFileName ="Excel"+datetime.now.tostring ("yyyymmddhhmmssfff"); stringSavepath ="excelfiles/"+NewFileName; Excelfile.PostedFile.SaveAs (Server.MapPath (Savepath)+extension); //Debug.                    Text = Server.MapPath (savepath) + extension; //You can import the data here.                    stringFILEURL = Server.MapPath (Savepath) +extension; DataTable DT=Getexcel (FILEURL); Gvexcel.datasource=DT;                    Gvexcel.databind (); //Write to Database                    BOOLAddtodb =insertdb (DT); if(ADDTODB) {Response.Write ("<script>alert (' Write database Success ') </script>"); }                    Else{Response.Write ("<script>alert (' Sorry! Write Failed ') </script>"); }                }            }            Else{Response.Write ("<script>alert (' Sorry! You have not uploaded the file ') </script>"); }        }        /// <summary>        ///Import Excel to DataTable by file name/// </summary>        /// <param name= "FileUrl" ></param>        /// <returns></returns>         PublicDataTable Getexcel (stringfileUrl) {            Const stringCmdtext ="Provider=microsoft.ace.oledb.12.0;data source={0}; Extended properties= ' Excel 12.0; Hdr=yes; Imex=1 '"; DataTable DT=NULL; OleDbConnection Conn=NewOleDbConnection (string.            Format (Cmdtext, FILEURL)); Try            {                //Open Connection                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                stringSheetName = schematable.rows[0]["table_name"]. ToString ().                Trim (); //querying for data in sheet                stringstrSQL ="SELECT * FROM ["+ SheetName +"]";//SQL statement, you can modifyOleDbDataAdapter da =NewOleDbDataAdapter (strSQL, conn); DataSet DS=NewDataSet (); Da.                Fill (DS); DT= ds. tables[0]; returnDT; }            Catch(Exception) {Throw; }        }         Public BOOLinsertdb (DataTable dt) {intresult=0; //1 First to define a good field            stringName =""; stringMoney =""; //iterate through the existing table            foreach(DataRow Drinchdt. Rows) {Name= dr["name"]. ToString ().                Trim (); Money= dr[" Money"]. ToString ().                Trim (); stringsql = String.Format ("INSERT INTO Test (Name,money) values (' {0} ', ' {1} ')", Name,money);//SQL statements, test-used                using(SqlConnection conn=NewSqlConnection (connstring)) {Conn.                    Open (); SqlCommand cmd=NewSqlCommand (Sql,conn); Result=cmd.                ExecuteNonQuery (); }            }            if(result>0)            {                return true; }            Else            {                return false; }        }    }}
The actual running page
Contents of the database:
This is the simple process.
The difficulty lies in a few mistakes
1 is not registered with the local computer ...... Solution: Look at the opening introduction
2 other errors were not found to be related to the program for the moment. Can be resolved by itself.
Full item: Http://files.cnblogs.com/files/fanling521/ExcelToDatabase.rar
Contains the Excel file
Excel is imported into the DataTable and then written to the database using the general method