Net reads Excel and returns DataTable

Using System;
Using Microsoft. SharePoint;
Using Microsoft. SharePoint. WebControls;
Using System. Data;
Using System. IO;
Using System. Linq;
Using System. Web;
Using System. Collections;
Using System. Data. OleDb;
Using NuctechProject. DTO. Bll;
Using System. Collections. Generic;

Namespace NuctechProject. Layouts. Project
{
Public partial class IntroductionPlan: LayoutsPageBase
{
String url = Common. rootUrl;
Private string _ strConn; // connection when importing an excel file
String pmurl = Common. proUrl;
Private UserBLL bll = new UserBLL ();
Protected void Page_Load (object sender, EventArgs e)
{
HidProid. Value = Request. QueryString ["proid"];
}

Protected void BtnOK_Click (object sender, EventArgs e)
{
DataTable excelTable = null;

SPSecurity. RunWithElevatedPrivileges (delegate
{
If (BaseInfoTemplateFile. HasFile)
{

List NoInput = new List ();
String strLoginName = HttpContext. Current. User. Identity. Name; // obtain the User Name.
String folderTemp = strLoginName. Substring (strLoginName. LastIndexOf ('\') + 1 );
Try
{
String extension = Path. GetExtension (BaseInfoTemplateFile. FileName); // get the file suffix
If (extension! = Null)
{
String fileException = extension. ToLower ();
If (fileException = ". xlsx" | fileException = ". xls ")
{
# Region reading Excel
String fileFolder = Server. MapPath ("~ /_ Layouts/15/images/"+ folderTemp +" Upfile /");
If (! Directory. Exists (fileFolder) // root Directory
{
Directory. CreateDirectory (fileFolder); // determines whether the upload Directory is automatically created.
}
BaseInfoTemplateFile. SaveAs (Server. MapPath ("~ /_ Layouts/15/images/"+ folderTemp +" Upfile/"+ BaseInfoTemplateFile. FileName ));
String strFilepathNmae = Server. MapPath ("~ /_ Layouts/15/images/"+ folderTemp +" Upfile/"+ BaseInfoTemplateFile. FileName );
String strExcel = ExcelSheetName (strFilepathNmae) [0]. ToString ();
ExcelTable = ExcelDataSource (strFilepathNmae, strExcel). Tables [0];
# Endregion
// Data is excel data
DataTable data = ExcelDataSource (strFilepathNmae, strExcel). Tables [0];

// Try
//{
If (data! = Null)
{

Foreach (DataRow row in data. Rows)
{
// Read
}

}
//}
// Catch (Exception)
//{
// Page. clientScript. registerStartupScript (Page. clientScript. getType (), "myscript", "<script type = 'text/javascript '> $. ligerDialog. closeWaitting (); alert ('excel table column name does not match the system. Please check the Excel table column name! '); </Script> ");
// Return;
//}
}

Else
{
Page. clientScript. registerStartupScript (Page. clientScript. getType (), "myscript", "<script type = 'text/javascript '> $. ligerDialog. closeWaitting (); alert ('the file you selected is not in Excel format! '); </Script> ");
Return;
}
}

}
Finally // finally Delete the temporary storage file
{
String strFileFolder = Server. MapPath ("~ /_ Layouts/15/images/"+ folderTemp +" Upfile /");
If (Directory. Exists (strFileFolder) // root Directory
{
// Directory. CreateDirectory (strFileFolder); // determines whether the upload Directory is automatically created.
Directory. Delete (strFileFolder, true );
}

Else
{
Page. ClientScript. RegisterStartupScript (Page. ClientScript. GetType (), "myscript", "<script type = 'text/javascript '> ReturnPageValue (); </script> ");
}
}
}
Else
{
Page. clientScript. registerStartupScript (Page. clientScript. getType (), "myscript", "<script type = 'text/javascript '> $. ligerDialog. closeWaitting (); alert ('select the import file! '); </Script> ");
Return;
}
});
}

Protected void BtnClose_Click (object sender, EventArgs e)
{
Page. ClientScript. RegisterStartupScript (Page. ClientScript. GetType (), "myscript", "<script type = 'text/javascript '> ReturnPageValue (); </script> ");

}
///

/// Connect to Excel
///
/// File Path
/// Sheet Name
///
Public DataSet ExcelDataSource (string filepath, string sheetname)
{
_ StrConn = "Provider = Microsoft. ACE. OLEDB.12.0; Data Source =" + filepath +
"; Extended Properties = 'excel 12.0; HDR = YES '";
New OleDbConnection (_ strConn );
Var oada = new OleDbDataAdapter ("select * from [" + sheetname + "]", _ strConn );
Var ds = new DataSet ();
Oada. Fill (ds );
Return ds;
}
///
/// Obtain all sheetnames in Excel
///
/// File Path
///
Public ArrayList ExcelSheetName (string filepath)
{
_ StrConn = "Provider = Microsoft. ACE. OLEDB.12.0; Data Source =" + filepath +
"; Extended Properties = 'excel 12.0; HDR = YES '";
Var al = new ArrayList ();
Var conn = new OleDbConnection (_ strConn );
Conn. Open ();
DataTable sheetNames = conn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables,
New object [] {null, "TABLE "});
Conn. Close ();
If (sheetNames! = Null)
Foreach (DataRow dr in sheetNames. Rows)
{
Al. Add (dr [2]);
}
Return al;
}

}
}