C # Get the Excel schema and how to import SQL Server

usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Windows.Forms;usingSystem.Data.OleDb;usingSystem.Data.SqlClient;usingSystem.IO;namespacewindowsformsapplication1{ Public Partial classForm3:form { PublicForm3 () {InitializeComponent (); }        Private voidButton1_Click (Objectsender, EventArgs e) {            stringConnString ="server=localhost;database=test;uid=sa;pwd=123456"; OpenFileDialog OFD=NewOpenFileDialog (); if(OFD. ShowDialog () = =DialogResult.OK) {transferdata (ofd.            FileName, connstring); }        }         Public voidTransferData (stringExcelfile,stringconnectionString) {            Try            {                stringFileType =path.getextension (Excelfile); //Get all data                stringstrconn=""; if(FileType = =". xls") {strconn="provider=microsoft.jet.oledb.4.0;"+"Data source="+ Excelfile +";"+"Extended Properties=excel 8.0;"; }                Else{strconn="provider=microsoft.ace.oledb.12.0;"+"Data source="+ Excelfile +";"+"; Extended Properties=excel 12.0"; } OleDbConnection Conn=NewOleDbConnection (strconn); Conn.                Open (); DataSet DS=NewDataSet (); DataTable DT= Conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables,New Object[] {NULL,NULL,NULL,"Table" });  for(inti =0; i < dt. Rows.Count; i++)                {                    stringSheetName = dt. rows[i]["table_name"].                    ToString (); if(Sheetname.contains ("$") &&!sheetname.replace ("'",""). EndsWith ("$"))                    {                        Continue; }                    stringStrexcel =string. Format ("SELECT * from [{0}]", SheetName); OleDbDataAdapter mycommand=NewOleDbDataAdapter (STREXCEL, conn); DataSet Dsitem=NewDataSet ();                    Mycommand.fill (Dsitem, sheetname); dsitem.tables[0]. TableName = Sheetname.trimend ('$'). Trim ();//the read-out indicates that the ' $ ' symbol is automatically added and needs to be removed. Ds. Tables.add (dsitem.tables[0].                Copy ()); }                 for(inti =0; I < DS. Tables.count; i++)                {                    stringSheetName =ds. Tables[i].                    TableName; stringstrSQL =string. Format ("if object_id (' {0} ') is null CREATE TABLE {0} (", SheetName); foreach(System.Data.DataColumn CinchDs. tables[0]. Columns) {strSQL+=string. Format ("[{0}] varchar (255),", C.columnname); } strSQL= Strsql.trim (',') +")"; using(SqlConnection sqlconn =NewSqlConnection (connectionString))                        {Sqlconn.open (); using(SqlCommand cmd =Sqlconn.createcommand ()) {Cmd.commandtext=strSQL; Cmd.                        ExecuteNonQuery (); }                    }                    using(SqlBulkCopy bcp =NewSqlBulkCopy (connectionString)) {bcp.} BatchSize= -; Bcp. DestinationTableName=SheetName; //establish a mapping relationship between Excel and SQL Server tablesBcp. Columnmappings.add (0,0); Bcp. Columnmappings.add (1,1); Bcp. Columnmappings.add (2,2); Bcp. WriteToServer (ds.                    Tables[i]); }                }            }            Catch(Exception ex) {MessageBox.Show (ex).            ToString ()); }        }    }}

http://blog.csdn.net/jinjazz/article/details/2650506

Http://www.cnblogs.com/advocate/archive/2010/04/12/1710383.html

Http://developer.51cto.com/art/201302/380622.htm

C # Get the Excel schema and how to import SQL Server