Private Static string _ filepath = string. empty;
# Region Excel import SQL database
/// <Summary>
/// Obtain the Excel Data Table list
/// </Summary>
/// <Returns> </returns>
Public static arraylist getexceltables ()
{
// Save the Excel schema to the data
System. Data. datatable dt = new system. Data. datatable ();
Arraylist tableslist = new arraylist ();
If (file. exists (filepath ))
{
Using (oledbconnection conn = new oledbconnection ("provider = Microsoft. Jet." +
"Oledb.4.0; extended properties = \" Excel 8.0 \ "; Data Source =" + filepath ))
{
Try
{
Conn. open ();
Dt = conn. getoledbschematable (oledbschemaguid. Tables, new object [] {null, "table "});
}
Catch (exception exp)
{
Log. strfilename = "querying the Excel form name ";
Log. strdepartment = "data stored in Excel architecture ";
Log. strdescription = exp. tostring ();
Log. writelog ();
} // Obtain the number of data tables
Int tablecount = DT. Rows. count;
For (INT I = 0; I <tablecount; I = I + 2)
{
String tablename = DT. Rows[2]. tostring (). Trim (). trimend ('$ ');
If (tableslist. indexof (tablename) <0)
{
Tableslist. Add (tablename );
}
}
}
}
Return tableslist;
}
/// <Summary>
/// Import the Excel data table to the able (the first row serves as the header)
/// </Summary>
/// <Returns> </returns>
Public static system. Data. dataset filldataset ()
{
If (! File. exists (filepath ))
{
Throw new exception ("the Excel file does not exist! ");
}
Arraylist tablelist = new arraylist ();
Tablelist = getexceltables ();
If (tablelist. Count <= 0)
{
Return NULL;
}
System. Data. datatable table;
System. Data. dataset DS = new dataset ();
Oledbconnection dbcon = new oledbconnection (@ "provider = Microsoft. Jet. oledb.4.0; Data Source =" + filepath + "; extended properties = Excel 8.0 ");
Try
{
If (dbcon. State = connectionstate. Closed)
{
Dbcon. open ();
}
For (INT I = 0; I <tablelist. Count; I ++)
{
String dtname = tablelist . Tostring ();
Try
{
Oledbcommand cmd = new oledbcommand ("select * from [" + dtname + "$]", dbcon );
Oledbdataadapter adapter = new oledbdataadapter (CMD );
Table = new datatable (dtname );
Adapter. Fill (table );
DS. Tables. Add (table );
}
Catch (exception exp)
{
Log. strfilename = dtname;
Log. strdepartment = "importing an Excel table to dataset ";
Log. strdescription = exp. tostring ();
Log. writelog ();
}
}
}
Finally
{
If (dbcon. State = connectionstate. open)
{
Dbcon. Close ();
}
}
Return Ds;
}
/// <Summary>
/// Import the database in Excel
/// </Summary>
/// <Returns> </returns>
Public static dataset importfromexcel ()
{
Return filldataset ();
}
# Endregion
