asp.net code for reading Excel data to DataTable-practical tips

Copy Code code as follows:

<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

Copy Code code as follows:

<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

Copy Code code as follows:

<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 will return a full table, otherwise return the 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 [" + WorksheetName + "$]", con);
Read
Con. Open ();
Adapter. FillSchema (Dtexcel, schematype.mapped);
Adapter. Fill (Dtexcel);
Con. Close ();
Dtexcel.tablename = WorksheetName;
Return
return dtexcel;
}

Code

Copy Code code as follows:

///<summary>
///Get link string
///</summary>
/// ;p Aram 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 ();
}