Using system;
Using system. Data;
Using system. configuration;
Using system. collections;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. Web. UI. htmlcontrols;
Using system. Data. oledb;
Using Excel = Microsoft. Office. InterOP. Excel;
Namespace exceltodataset
{
/// <Summary>
/// Currently, the tested COM is office2003.
/// Application COM component: Microsoft Excel 11.0 Object Library
/// </Summary>
Public partial class exceltodataset: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{
}
Protected void btninputexcel_click (Object sender, eventargs E)
{
String filetype = This. fileupexcel. postedfile. contenttype;
// If (filetype = "application/vnd. MS-excel ")
If (filetype = "application/octet-stream ")
{
This. gvexcel. datasource = createdatasource ();
This. gvexcel. databind ();
}
Else
{
This. lbmsg. Text = "<font color = 'red'> enter an Excel file! </Font> ";
}
}
// Create a data source
Private dataset createdatasource ()
{
// Client path
// String Path = This. fileupexcel. postedfile. filename;
String Path = (string) session ["path"];
String strcon = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + path + "; extended properties = Excel 8.0 ;";
Oledbconnection olecon = new oledbconnection (strcon );
// Oledbdataadapter myda = new oledbdataadapter ("select * from [sheet1 $]", strcon );
Oledbdataadapter myda = new oledbdataadapter ("select * from [" + this. ddlexcelsheetname. selectedvalue + "$]", strcon );
Dataset myds = new dataset ();
Myda. Fill (myds );
Return myds;
}
// Load the sheet table name to dropdown
Protected void addsheetnametodrop ()
{
This. ddlexcelsheetname. Items. Clear (); // clear the value in dropdownlist
If (this. fileupexcel. hasfile)
{
String pathname = This. fileupexcel. postedfile. filename;
Session ["path"] = pathname;
Object missingvalue = type. missing;
Excel. Application Ep = new excel. applicationclass ();
Excel. Workbook ew = ep. workbooks. Open (pathname, missingvalue,
Missingvalue,
Missingvalue,
Missingvalue,
Missingvalue,
Missingvalue );
// Excel. worksheet EWS;
Int COUNT = ew. worksheets. count;
// Number of read tables
// Response. Write (count. tostring ());
// Traverse all the table names in the Excel file
For (INT I = 1; I <= count; I ++)
{
// EWS = (Excel. worksheet) Ew. worksheets [I];
String sheetname = (Excel. worksheet) Ew. worksheets [I]). Name;
// Bind to the DDL Control
This. ddlexcelsheetname. Items. Add (sheetname );
}
// Killed the Excel process. I hate it for a long time.
Ew. Close (false, type. Missing, type. Missing );
Ep. workbooks. Close ();
Ep. Quit ();
System. runtime. interopservices. Marshal. releasecomobject (EP );
System. runtime. interopservices. Marshal. releasecomobject (EW );
Ep = NULL;
Ew = NULL;
GC. Collect (); // force the garbage collection of all codes
}
}
Protected void btnaddsheet_click (Object sender, eventargs E)
{
Addsheetnametodrop ();
}
}
}