Using C # How to import EXCEL to SQL Server

Author:De thief stolen

The import of Excel must be a uniform template for the customer to facilitate business processing.

I did this.

First, read all sheet pages in Excel. Please select the sheet to be processed. The code for obtaining the sheet page is as follows:

Code
///
/// Obtain the Excel table name [sheet1 $]
///
/// Excel path
/// Datatable
Public static datatable getexceltablename (string path)
{

VaR conn = getconnection (PATH );
VaR dt = conn. getoledbschematable (oledbschemaguid. Tables, null );


Conn. Close ();
Return DT;
}

The returned datatable can be bound to The ListBox control,
After you select the sheet page to be processed, enter the selected sheet Page name in the following method to get the content of the selected sheet page:

 

Code

 ///
/// Obtain the content of the corresponding table based on the Excel table name
///
/// Excel table name
/// Excel path
///
Public static datatable getexceltable (string tablename, string path)
{

VaR SQL = string. Format ("select * from [{0}]", tablename );
VaR conn = getconnection (PATH );
VaR mycommand = new oledbdataadapter (SQL, Conn );
VaR mydataset = new dataset ();
Mycommand. Fill (mydataset );
Return mydataset. Tables [0];
}

 

Return a datatable so that you can process the data in the datatable based on your business.
The complete code is as follows:

 

Code

 Using system. Data;
Using system. Data. oledb;
Using system. Windows. forms;
/*
* Program function: Excel Import
* Creation date:
* Creator: robber
* Development Environment: winxp_sp3 + vs2008
* URL: http://robber.cnblogs.com/
*/
Namespace exceltodb
{
///
/// Summary in Excel.
///
Public class Excel
{
Public Excel ()
{
//
// Todo: add the constructor logic here
//
}
Static oledbconnection getconnection (string path)
{
VaR strconn = string. Format ("provider = Microsoft. Jet. oledb.4.0; Data Source = {0}; extended properties = Excel 8.0;", PATH );
VaR conn = new oledbconnection (strconn );
Conn. open ();
Return conn;

}
///
/// Obtain the Excel table name [sheet1 $]
///
/// Excel path
/// Datatable
Public static datatable getexceltablename (string path)
{

VaR conn = getconnection (PATH );
VaR dt = conn. getoledbschematable (oledbschemaguid. Tables, null );


Conn. Close ();
Return DT;
}
///
/// Obtain the Excel file path
///
/// Path
Public static string getexcelpath ()
{
VaR DLG = new openfiledialog {filter = "Microsoft Excel | *. xls "};
Return DLG. showdialog () = dialogresult. OK? DLG. filename: String. empty;
}
///
/// Obtain the content of the corresponding table based on the Excel table name
///
/// Excel table name
/// Excel path
///
Public static datatable getexceltable (string tablename, string path)
{

VaR SQL = string. Format ("select * from [{0}]", tablename );
VaR conn = getconnection (PATH );
VaR mycommand = new oledbdataadapter (SQL, Conn );
VaR mydataset = new dataset ();
Mycommand. Fill (mydataset );
Return mydataset. Tables [0];
}
}
}