. NET read Excel return DataTable Instance code _ Practical Tips

Copy Code code as follows:

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; connections when you import Excel
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<string> noinput = new list<string> ();
string strloginname = HttpContext.Current.User.Identity.Name; Get user Name
String foldertemp = strloginname.substring (strloginname.lastindexof (' \ \) + 1);
Try
{
string extension = Path.getextension (baseinfotemplatefile.filename); Get the suffix of a file
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); To determine whether an uploaded directory exists 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 in Excel
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 system, check 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//eventually delete temporarily stored files
{
String strfilefolder = Server.MapPath ("~/_layouts/15/images/" + foldertemp + "upfile/");
if (directory.exists (Strfilefolder))//root directory
{
Directory.CreateDirectory (Strfilefolder)//To determine whether the upload directory exists 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 (' Please select 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> ");
}
<summary>
Connect to Excel
</summary>
<param name= "filepath" > File path </param>
<param name= "SheetName" >sheet name </param>
<returns></returns>
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;
}
<summary>
Get all the SheetName in Excel
</summary>
<param name= "filepath" > File path </param>
<returns></returns>
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, NULL, NULL, "TABLE"});
Conn. Close ();
if (sheetnames!= null)
foreach (DataRow Dr in Sheetnames.rows)
{
Al. ADD (dr[2]);
}
Return al;
}
}
}