Asp.net code for reading Excel Data to datatable

CopyCode The 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 );
}

CodeCopy code The 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 Code the code is as follows: ///

// obtain the Excel Data of the specified path and workbook name
///
// file storage path
// workbook name
// if you try to find the data, a complete table is returned, otherwise, an exception is returned.
Public datatable getexceldata (string filepath, string worksheetname)
{< br> 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 the code is as follows: ///

// obtain the link string
///
///
//
Public String getexcelconnection (string strfilepath)
{< br> If (! File. exists (strfilepath)
{< br> throw new exception ("the specified Excel file does not exist! ");
}< br> 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 ();
}