C # How to import Excel Data (two methods)

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

 

From the a13062331830 Column