Using system;
Using system. Data;
Using system. configuration;
Using system. LINQ;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. htmlcontrols;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. xml. LINQ;
Using system. runtime. interopservices;
Using system. Data. oledb;
Namespace myhelper
{
/// Another method is described as follows... In most cases, you need to install office2003.
// If an error is reported, you must register the component. Your component service cannot be started. Other methods cannot be found. We recommend that you use this method now.
Public class excelease
{
[Dllimport ("Kernel32")]
Public static extern void getwindowsdirectory (system. Text. stringbuilder WINDIR, int count );
/// <Summary>
/// Specify the sheet Page name in an Excel file in the specified ComboBox
/// </Summary>
/// <Param name = "frm"> form </param>
/// <Param name = "CB"> ComboBox </param>
/// <Param name = "exelfilename"> full path name of an Excel file </param>
Public static void fillexcelsheettocombo (system. Web. UI. webcontrols. dropdownlist DDL, string exelfilename)
{
Try
{
DDL. Items. Clear ();
String strconn = "provider = Microsoft. Jet. oledb.4.0;" + "Data Source =" + exelfilename + ";" + "extended properties = Excel 8.0 ;";
System. Data. oledb. oledbconnection conn = new system. Data. oledb. oledbconnection (strconn );
Conn. open ();
System. Data. datatable TB = new system. Data. datatable ();
TB = conn. getoledbschematable (oledbschemaguid. Tables, new object [] {null, "table "});
For (int K = 0; k <TB. Rows. Count; k ++)
{
DDL. items. add (TB. rows [k] ["table_name"]. tostring (). replace ("'",""). replace ("$ ",""));
}
// Microsoft. Office. InterOP. Excel = Excel
// Microsoft. Office. InterOP. Excel. Application APP = new Microsoft. Office. InterOP. Excel. Application ();
// App. Visible = false;
// Microsoft. Office. InterOP. Excel. Workbook WKB = app. workbooks. Open (exelfilename,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing
//);
// Foreach (Microsoft. Office. InterOP. Excel. worksheet sheet in WKB. Sheets)
//{
// DDL. Items. Add (sheet. Name );
//}
// App. Quit ();
}
Catch (exception ex)
{
Httpcontext. Current. response. Write (ex. Message + ex. stacktrace );
}
}
/// </Summary>
/// <Param name = "excelfilename"> Excel file </param>
/// <Param name = "sheetname"> Sheet Name </param>
/// <Returns> NULL: error; otherwise, the datatable is returned. </returns>
Public static system. Data. datatable exceltodsstuct (string excelfilename, string sheetname)
{
Microsoft. Office. InterOP. Excel. Application APP = NULL;
Try
{
String strconn = "provider = Microsoft. Jet. oledb.4.0;" + "Data Source =" + excelfilename + ";" + "extended properties = Excel 8.0 ;";
System. Data. oledb. oledbconnection conn = new system. Data. oledb. oledbconnection (strconn );
Conn. open ();
System. Data. datatable TB = new system. Data. datatable ();
String strexcel = "select * from [" + sheetname + "$]";
System. Data. oledb. oledbdataadapter adpt = new system. Data. oledb. oledbdataadapter (strexcel, strconn );
Adpt. Fill (TB );
Conn. Close ();
Return TB;
// APP = new Microsoft. Office. InterOP. Excel. Application ();
// App. Visible = false;
/// Open an Excel file
// Microsoft. Office. InterOP. Excel. Workbook WKB = app. workbooks. Open (excelfilename,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing,
// Type. Missing
//);
// Microsoft. Office. InterOP. Excel. worksheet sht = NULL;
// For (INT I = 1; I <= WKB. Sheets. Count; I ++)
// If (Microsoft. Office. InterOP. Excel. worksheet) WKB. Sheets [I]). Name = sheetname)
// Sht = (Microsoft. Office. InterOP. Excel. worksheet) WKB. Sheets [I];
// If (SHT = NULL)
//{
// App. Quit ();
// Return NULL;
//}
/// Generate a table
// System. Data. datatable TB = new system. Data. datatable ();
//// Generate the table Header
// For (INT I = 1; I <= sht. usedrange. Columns. Count; I ++)
//{
// TB. columns. add (Microsoft. office. interOP. excel. range) sht. cells [1, I]). text. tostring (),"". getType ());
//}
/// Enter data
// For (INT I = 2; I <= sht. usedrange. Rows. Count; I ++)
//{
// System. Data. datarow R = Tb. Rows. Add ();
// For (Int J = 1; j <= sht. usedrange. Columns. Count; j ++)
//{
// Microsoft. Office. InterOP. Excel. Range c = (Microsoft. Office. InterOP. Excel. Range) sht. cells [I, j];
/// C. numberformat = "@";
// R [J-1] = C. Text. tostring ();
//}
//}
// App. Quit ();
// Return TB;
}
Catch
{
If (app! = NULL)
App. Quit ();
Return NULL;
}
}
}
}