Method 1: import data to a DataSet object. Only the standard format of Excel is supported, that is, cells cannot be merged.
/// <Summary>
/// Import data to the dataset
/// Note: This method only supports the original excel File
/// </Summary>
/// <Param name = "Path"> file Path </param>
/// <Param name = "exceptionMsg"> exception information </param>
/// <Returns> </returns>
Public static System. Data. DataTable InputExcel (string Path, ref string exceptionMsg)
{
System. Data. DataTable dt = null;
Try
{
String strConn = "Provider = Microsoft. Jet. OLEDB.4.0;" + "Data Source =" + Path + ";" + "Extended Properties = Excel 8.0 ;";
Using (OleDbConnection conn = new OleDbConnection (strConn ))
{
Conn. Open ();
System. Data. DataTable sheetDt = conn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, null );
String [] sheet = new string [sheetDt. Rows. Count];
For (INT I = 0; I <sheetdt. Rows. Count; I ++)
{
Sheet [I] = sheetdt. Rows [I] ["table_name"]. tostring ();
}
String strexcel = string. Format ("select * from [{0}]", sheet [0]);
Oledbdataadapter mycommand = new oledbdataadapter (strexcel, strconn );
Dt = new System. Data. DataTable ();
MyCommand. Fill (dt );
Conn. Close ();
}
}
Catch (Exception ex)
{
Predictionmsg = ex. Message;
}
Return dt;
}
Method 2: Read the Excel file and assemble it according to the data information
# Region reads data from an Excel table to a able
Public static system. Data. datatable changeexceltodatetable (string _ path)
{
System. Data. datatable tempdt = new system. Data. datatable ();
Tempdt. tablename = "Excel ";
Application APP = new application ();
Object OBJ = system. reflection. Missing. value;
Try
{
Workbook _ wbook = app. workbooks. open (_ path, OBJ );
Worksheet _ wsheet = (worksheet) _ wbook. worksheets. get_item (1 );
Datarow newrow = NULL;
Datacolumn newcolumn = NULL;
For (INT I = 2; I <= _ wsheet. usedrange. Rows. Count; I ++)
{
Newrow = tempdt. newrow ();
For (Int J = 1; j <= _ wsheet. usedrange. Columns. Count; j ++)
{
If (I = 2 & j = 1)
{
// Header
For (int k = 1; k <= _ wSheet. UsedRange. Columns. Count; k ++)
{
String str = (_ wSheet. UsedRange [1, k] as Range). Value2.ToString ();
NewColumn = new DataColumn (str );
NewRow. Table. Columns. Add (newColumn );
}
}
Range range = _ wSheet. Cells [I, j] as Range;
If (range! = Null &&! "". Equals (range. Text. ToString ()))
{
NewRow [j-1] = range. Value2;
}
}
Tempdt. Rows. Add (newRow );
}
_ WSheet = null;
_ WBook = null;
App. Quit ();
Kill (app );
Int generation = System. GC. GetGeneration (app );
App = null;
System. GC. Collect (generation );
Return tempdt;
}
Catch (Exception ex)
{
App. Quit ();
Kill (app );
Int generation = System. GC. GetGeneration (app );
App = null;
Throw ex;
}
}
# Endregion
# Region Process Termination
[Dllimport ("user32.dll", charset = charset. Auto)]
Private Static extern int getwindowthreadprocessid (intptr hwnd, out int ID );
Private Static void kill (Microsoft. Office. InterOP. Excel. Application Excel)
{
Intptr T = new intptr (Excel. hwnd); // obtain the handle. The specific function is to obtain the memory entry.
Int K = 0;
Getwindowthreadprocessid (T, out k); // obtain the unique identifier K of the process.
System. Diagnostics. PROCESS p = system. Diagnostics. process. getprocpolicyid (k); // get a reference to process K
P. Kill (); // closes process K
}
# Endregion