C # Excel operations

Copy excel.exe to the. \ Microsoft Visual Studio. NET 2003 \ SDK \ v1.1 \ Bin directory.
Exploitation. net tool in the command prompt to execute tlbimp excel.exe. in this way, you will not find a different * because your Excel is xp or 2000 *. the olb file is also because the file excel9.olb is not available in Versions later than 2000.

After you execute tlbimp excel.exe, we will get the excel. dll file.

With this Excel. dll, we can now use various Excel operation functions.
Next let's take a look at how C # uses these things.
1. Create a New Excel Application:

Application exc = new Application ();
If (exc = null ){
Console. WriteLine ("ERROR: EXCEL couldn't be started ");
Return 0;
}

2. Make this project visible:
Exc. set_Visible (0, true );
3. Get the WorkBooks set:
Workbooks workbooks = exc. Workbooks;
4. Add a new WorkBook:
_ Workbook workbook = workbooks. Add (XlWBATemplate. xlWBATWorksheet, 0 );
5. Get the WorkSheets set:

_ Worksheet worksheet = (_ Worksheet) sheets. get_Item (1 );
If (worksheet = null ){
Console. WriteLine ("ERROR in worksheet = null ");
}
6. Set variables for cells:

 

Range range1 = worksheet. get_Range ("C1", Missing. Value );
If (range1 = null)
{
Console. WriteLine ("ERROR: range = null ");
}
Const int nCells = 1;
Object [] args1 = new Object [1];
Args1 [0] = nCells;
Range1.GetType (). InvokeMember ("Value", BindingFlags. SetProperty, null, range1, args1 );

 

Routine:

 

Using System;
Using System. Reflection;
Using System. Runtime. InteropServices;
Using Excel;
Class Excel {
Public static int Main (){
Application exc = new Application ();
If (exc = null ){
Console. WriteLine ("ERROR: EXCEL couldn't be started! ");
Return 0;
}
Exc. set_Visible (0, true );
Workbooks workbooks = exc. Workbooks;
_ Workbook workbook = workbooks. Add (XlWBATemplate. xlWBATWorksheet, 0 );
Sheets sheets = workbook. Worksheets;
_ Worksheet worksheet = (_ Worksheet) sheets. get_Item (1 );
If (worksheet = null ){
Console. WriteLine ("ERROR: worksheet = null ");
}
Range range1 = worksheet. get_Range ("C1", Missing. Value );
If (range1 = null ){
Console. WriteLine ("ERROR: range = null ");
}
Const int nCells = 1;
Object [] args1 = new Object [1];
Args1 [0] = nCells;
Range1.GetType (). InvokeMember ("Value", BindingFlags. SetProperty, null, range1, args1 );
Return 100;
}
}

Now let's take a look at how to use arrays, which are similar to setting cells. The only change required is args2 [0] = array2;

Const int nCell = 5;
Range range2 = worksheet. get_Range ("A1", "E1 ");
Int [] array2 = new int [nCell];
For (int I = 0; I <array2.GetLength (0); I ++)
{
Array2 [I] = I + 1;
}
Object [] args2 = new Object [1];
Args2 [0] = array2;
Range2.GetType (). InvokeMember ("Value", BindingFlags. SetProperty, null, range2, args2 );

You need to know how to use the Tlbimp tool. :) this tool is very useful. You can port a common Win32 program to. Net :)
If the excel file format is very simple, it is the structure of a general table, the method for operating an EXCEL file is almost the same as that for operating an ACCESS database file.
(Note: 1. The program uses the first row record in the EXCLE table as the column name. 2. When using the EXCLE table, add the symbol $ after the table name)

Next, I will post you a piece of code for connecting to and reading EXCEL files:

 

DataSet ds = new DataSet ();
OleDbDataAdapter ad;


String strDbPath = "./code.xls ";
String strConn = "Provider = Microsoft. Jet. OleDb.4.0; Data Source =" + Server. MapPath (strDbPath) + "; Extended Properties = Excel 8.0 ;";

OleDbConnection Conn = new OleDbConnection (strConn );

Conn. Open ();

String strSQL = "select * from [Stock Code $]";


Ad = new OleDbDataAdapter (strSQL, Conn );
Ad. Fill (ds );

Dg1.DataSource = ds. Tables [0]. DefaultView; // dg1 is a DataGrid Control.
Dg1.DataBind (); // set the record in the stock code in excle to the DataGrid Control

If it is used in asp.net, remember to add <identity impersonate = "true"/> in <system. web>
Otherwise, "exception details: System. UnauthorizedAccessException: Access Denied" appears.