C # get data from Excel to DataTable

Example 1

The code is as follows

Copy Code

#region get data from a specified workbook in Excel to a DataTable <SUMMARY> Gets data from the specified workbook in Excel to the DataTable </SUMMARY> <param name= "Excelpath"/>excel Path <param name= "SheetName"/> Workbook name <RETURNS> return result is datatable</returns> Private DataTable exceltodatatable (string excelpath, String sheetname) { String strconnexcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Excelpath + "; Extended properties= ' Excel 8.0; Hdr=yes;imex=1 ' "; OleDbConnection connexcel = new OleDbConnection (Strconnexcel); OleDbDataAdapter daexcel = null; DataTable dtexcel = new DataTable (); String strsqlexcel = String. Format ("select * from [{0}$]", sheetname); Try { Connexcel.open (); Daexcel = new OleDbDataAdapter (Strsqlexcel, Connexcel); Daexcel.fill (Dtexcel); return dtexcel; } catch (Exception ex) { Write error log return dtexcel; } Finally { Connexcel.close (); Daexcel.dispose (); Connexcel.dispose (); } } #endregion

Example 2

Code

The code is as follows

Copy Code

<summary> Gets the Excel data for the specified path, the name of the specified workbook: Data from the first sheet </summary> <param name= "FilePath" > File storage path </param> <param name= "WorksheetName" > Workbook name </param> <returns> If you try to find the data will return a full table, otherwise return the exception </returns> Public DataTable Getexceldata (string astrfilename) { String strsheetname = Getexcelworksheets (Astrfilename) [0]. ToString (); Return Getexceldata (Astrfilename, strSheetName); }

Code

The code is as follows

Copy Code

<summary> Returns a list of workbooks contained in the specified file, or, if there is a worksheet, returns a ArrayList named after the workbook, otherwise null </summary> <param name= "strFilePath" > to get excel</param> <returns> If there is worksheet, return the ArrayList named after the workbook, otherwise return null </returns> Public ArrayList getexcelworksheets (string strFilePath) { ArrayList altables = new ArrayList (); OleDbConnection odn = new OleDbConnection (Getexcelconnection (strFilePath)); Odn. Open (); DataTable dt = new DataTable (); DT = Odn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, NULL); if (dt = null) { throw new Exception ("Cannot get the schema for the specified Excel. "); } foreach (DataRow dr in Dt. Rows) { String tempname = dr["table_name"]. ToString (); int idolarindex = Tempname.indexof (' $ '); if (Idolarindex > 0) { Tempname = tempname.substring (0, Idolarindex); } Fixed a bug in Excel2003 that a table with the names of Chinese characters is not recognized correctly. if (tempname[0] = = "") { if (tempname[tempname.length-1] = = "") { Tempname = tempname.substring (1, tempname.length-2); } Else { Tempname = tempname.substring (1, tempname.length-1); } } if (!altables.contains (tempname)) { Altables.add (Tempname); } } Odn. Close (); if (altables.count = 0) { return null; } return altables; }

Code

code is as follows

copy code

///<summary>    ///Gets the Excel data for the specified path, the name of the specified workbook    // /</summary>    ///<param name= "FilePath" > File storage path </param>     ///<param name= "WorksheetName" > Workbook name </param>    ///<returns> If you try to find the data, it returns a full table, otherwise it returns an exception </returns>     public DataTable getexceldata (String FilePath, String worksheetname)     {         DataTable dtexcel = new DataTable ();         OleDbConnection con = new OleDbConnection (Getexcelconnection ( FilePath));         OleDbDataAdapter adapter = new OleDbDataAdapter ("SELECT * FROM [" + Works Heetname + "$]", con); Read Con. Open (); Adapter. FillSchema (Dtexcel, schematype.mapped); Adapter. Fill (Dtexcel); Con. Close (); Dtexcel.tablename = WorksheetName; Return return dtexcel; }

Code

The code is as follows

Copy Code

///<summary>    ///Get link string     /// </summary>    ///<param name= "strFilePath" ></PARAM>    // /<returns></returns>     public string getexcelconnection (string strFilePath)     {         if (! File.exists (strFilePath))         {              throw new Exception ("The specified Excel file does not exist!") ");        }         return "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + strFilePath + "; Extended properties= "Excel 8.0;imex=1; Hdr=yes; ""; //@ "provider=microsoft.jet.oledb.4.0;" + //@ "Data source=" + strFilePath + ";" + //@ "Extended properties=" + Convert.tochar (34). ToString () + //@ "Excel 8.0;" + "imex=1; Hdr=yes; "+ Convert.tochar (34).    ToString ();