Asp.net code for reading Excel Data to DataTable

Copy codeThe Code is as follows:
/// <Summary>
/// Obtain the Excel Data of the specified path and workbook name: obtain the data of the first sheet.
/// </Summary>
/// <Param name = "FilePath"> file storage path </param>
/// <Param name = "WorkSheetName"> workbook name </param>
/// <Returns> if you find the data, a complete Table is returned; otherwise, an exception is returned. </returns>
Public DataTable GetExcelData (string astrFileName)
{
String strSheetName = GetExcelWorkSheets (astrFileName) [0]. ToString ();
Return GetExcelData (astrFileName, strSheetName );
}


Code
Copy codeThe Code is as follows:
/// <Summary>
/// Return the list of workbooks contained in the specified file. If there is a WorkSheet, The ArrayList named after the workbook is returned. Otherwise, the returned result is null.
/// </Summary>
/// <Param name = "strFilePath"> obtain the Excel file </param>
/// <Returns> If WorkSheet exists, ArrayList named after the workbook is returned. Otherwise, null is returned. </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 ("the specified Excel architecture cannot be obtained. ");
}
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 the BUG that some tables named Chinese Characters in Excel2003 could not be correctly recognized.
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 codeThe Code is as follows:
/// <Summary>
/// Obtain the Excel Data of the specified path and workbook name
/// </Summary>
/// <Param name = "FilePath"> file storage path </param>
/// <Param name = "WorkSheetName"> workbook name </param>
/// <Returns> if you find the data, a complete Table is returned; otherwise, an exception is returned. </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 codeThe Code is as follows:
/// <Summary>
/// Obtain the 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 ();
}