. NET read Excel return DataTable Instance code

Source: Internet
Author: User
Tags foreach root directory

  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 ;                       {            & nbsp               string fileexception = extension. ToLower ();                             if (fileexception = "x") LSx "| | Fileexception = ". xls")                             {                                #region Reading Excel                                 s Tring Filefolder = Server.MapPath ("~/_layouts/15/images/" + foldertemp + "upfile/");   &nbsp                             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                      //{                               //   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;        }    }}  

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.