This article mainly introduces. NET reads Excel returns the datatable instance code, has the need friend to refer to the
The code is 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 &N Bsp { string url = common.rooturl; private string _strconn;//import Excel Connection string pmurl = Common.prourl; private USERBLL BLL = new USERBLL (); protected void Page_Load (object sender, EventArgs e) { &NBS P Hidproid.value = request.querystring["Proid"]; } protected void Btnok_click (object sender, EventArgs e) &N Bsp {&nbsP DataTable exceltable = null; Spsecurity.runwithelevatedprivileges (delegate &NBSP ; { , if (baseinfotemplatefile.hasfile) &N Bsp { list<string& Gt Noinput = new list<string> (); string strloginname = HttpContext.Current.User.Ident ity. Name; Get user name String foldertemp = Strloginname.substring (s Trloginname.lastindexof (") + 1); try { STRing extension = path.getextension (baseinfotemplatefile.filename); Get file suffix if (extension!= null)   ; { & nbsp string fileexception = extension. ToLower (); if (fileexception = "x") LSx "| | Fileexception = ". xls") { #region Reading Excel s Tring Filefolder = Server.MapPath ("~/_layouts/15/images/" + foldertemp + "upfile/");   if (! Directory.Exists (Filefolder))//root directory &NB Sp & nbsp directory.createdirectory (Filefolder); Determine if the upload directory exists Auto create } & nbsp Baseinfotemplatefile.saveas (Server.MapPath ("~/_layouts/15/images/" + foldertemp + "upfile/" + Baseinfotemplatefile.filename)); String str Filepathnmae = Server.MapPath ("~/_layouts/15/images/" + FOldertemp + "upfile/" + baseinfotemplatefile.filename); String str Excel = Excelsheetname (Strfilepathnmae) [0]. ToString (); exceltable = Exceldatasource (Strfilepathnmae, Strexcel). Tables[0]; #endregion //data is ex Cel data Dat aTable data = Exceldatasource (Strfilepathnmae, Strexcel). Tables[0]; Try &NBS P //{ &NBSP if (data!= null) { &NB Sp &NB Sp foreach (DataRow row in data. Rows) &NBS P { & nbsp /read &NBSP ; { &NBsp } & nbsp //I //catch (Exception) &NB Sp //{ // &NBSP;PAGE.CLIENTSCRIPT.REGISTERSTARTUPSC Ript (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 { &NB Sp Page.ClientScript.Regist Erstartupscript (Page.ClientScript.GetType), "MyScript", "<script type= ' Text/javascript ' >$". Ligerdialog.closewaitting (); alert (' The file you selected is not in Excel format! ');</script> "); return; } & nbsp &NBSP ; finally end up with temporary storage files deleted { String strfilefolder = Server.MapPath ("~/_layouts/15/images/" + Folderte MP + "upfile/"); if (directory.exists (Strfilefolder))/ /root { &NBS P //directory.createdirectory (Strfilefolder); Determine if the upload directory exists Auto create &N Bsp Directory.delete (Strfilefolder, true); &NBSp else &NB Sp { & nbsp Page.ClientScript.RegisterStartupScript (Page.ClientScript.GetType (), "MyScript", "<script type= ' Text/javascript ' >returnpagevalue ();</script> "); & nbsp } Else { &N Bsp 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.CLIENTSCRI Pt. GetType (), "MyScript", "<script type= ' Text/javascript ' >returnpagevalue ();</script>"); } ///<summary> ///connect to Excel &NB Sp ///</summary> ///<param name= "filepath" > File path </param> ///<param name= "sheetname" >sheet name </param> ///<r eturns></returns> Public DataSet Exceldatasource (String filepath, string sheetname) { &NBS P _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 S in Excel Heetname ///</summary> ///<param name= "filepath" > File path Dia </param> ///<returns></returns> public ArrayList excelsheetname (string filepath) &N Bsp { _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, n ull, NULL, "TABLE"}); Conn. Close (); if (sheetnames!= null) foreach (DataRow Dr in Sheetnames.rows) { &NBS P AL. ADD (dr[2]); return al; } }}