Use oledb to import EXCEL to Dataset

In this method, all the sheet data in the input Excel file is filled in the dataset. This is a simple and quick method. The disadvantage is that it is not suitable for complicated Excel files.

Using System. Data;
Using System. Data. oledb;
Using System. Data. ODBC;

Public Dataset getdatasetfromexcel ( String Filepath)
{
String Strconn =   " Provider = Microsoft. Jet. oledb.4.0; " ;
Strconn + =   " Data Source = "   + Filepath +   " ; " ;
Strconn + =   " Extended properties = Microsoft Excel 8.0; " ;
// Strconn + = "HDR = yes; IMEX = 1 ";

Oledbconnection objconn =   New Oledbconnection (strconn );

Arraylist sheetnamelist =   New Arraylist ();

Try
{
Objconn. open ();

Datatable dtexcelschema = Objconn. getoledbschematable (oledbschemaguid. Tables, New   Object [] {Null,Null,Null,"Table"} );

String Sheetname = "" ;

For ( Int I = 0 ; I < Dtexcelschema. Rows. Count; I ++ )
{
Sheetname=Dtexcelschema. Rows [I] ["Table_name"]. Tostring ();
Sheetnamelist. Add (sheetname );
}
}
Catch (Exception ex)
{
ThrowEx;
}
Finally
{
Objconn. Close ();
}  

Dataset dsexcel =   New Dataset ();

Try
{
String Strsql =   "" ;

For ( Int I = 0 ; I < Sheetnamelist. Count; I ++ )
{
// String strsql = "select * from [sheet1 $ E5: G23]";
// String strsql = "select [F1], [F2], [F3] from [sheet2 $:100]";
Strsql =   " Select * from [ "   + ( String ) Sheetnamelist [I] +   " ] " ;
Oledbdataadapter oleexceldataadapter =   New Oledbdataadapter (strsql, objconn );
Datatable dtexcel =   New Datatable (( String ) Sheetnamelist [I]);
Oleexceldataadapter. Fill (dtexcel );
Dsexcel. Tables. Add (dtexcel );
}
Return Dsexcel;
}
Catch (Exception ex)
{
ThrowEx;
}
}

Reprinted from: http://blog.csdn.net/fangxinggood/archive/2005/06/18/397315.aspx