C # The Excel class can be used to implement the Import and Export function. After a long time, the two methods are displayed.

Source: Internet
Author: User

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;
}

}

}
}

 

 

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.