C # get data from Excel to DataTable

Source: Internet
Author: User

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 ();

Related Article

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.