C # how to embed and operate an Excel table (from hahawk's blog) in Form)

There are many articles on the Internet about how to operate Excel tables, but they all start Excel windows to open Excel data files. Sometimes it is better to embed an Excel table into the form of your own program and give the customer an operation interface without switching the window. This is easy to implement Using OLE technology in VC, but the methods in C # are different. The following will be explained.

I. First, let's briefly review how to operate an Excel worksheet.

Add a reference to excel. Select project> Add reference> COM> to add Microsoft Excel 9.0. (Different office versions have different DLL files ).
Using Excel;
Using system. reflection;

// Generate a new process for excel. Application
Excel. Application APP = new excel. Application ();
If (APP = NULL)
{
Statusbar1.text = "error: Excel couldn't be started! ";
Return;
}

App. Visible = true; // if you only want to use a program to control the Excel file and do not want the user to operate it, you can set it to false.
App. usercontrol = true;

Workbooks workbooks = app. workbooks;

_ Workbook = workbooks. Add (xlwbatemplate. xlwbatworksheet); // generate a new workbook Based on the template
/// _ Workbook = workbooks. Add ("C: // a.xls"); // The actual workbook a.xls

Sheets sheets = Workbook. worksheets;
_ Worksheet = (_ worksheet) sheets. get_item (1 );
If (worksheet = NULL)
{
Statusbar1.text = "error: Worksheet = NULL ";
Return;
}

// This paragraph puts the value 5 to the cell G1
Range range1 = worksheet. get_range ("A1", missing. value );
If (range1 = NULL)
{
Statusbar1.text = "error: range = NULL ";
Return;
}
Const int ncells = 2345;
Range1.value2 = ncells;

2. embed the Excel user interface into your own windows form

Currently, C # And VB.net do not support OLE technology (for details, see Microsoft support center info: 304562). Therefore, you only need to use the webbrowser control to complete this function. (For the following methods, refer to the Microsoft support center HOWTO: 304662)
1. Right-click the toolbox, select the custom toolbox, add the COM component, select "Microsoft Web Browser" (the corresponding file is/winnt/system32/shdocvw. dll), and click OK. The webbroser control icon with the text as explorer appears in the toolbox.
2. Add the webbrowser control to form1. (The object name is saved as axwebbrowser1)
3. Assume that the Excel file to be opened is C:/a.xls.
String strfilename = @ "C:/a.xls ";
Object refmissing = system. reflection. Missing. value;
Axwebbrowser1.navigate (strfilename, ref refmissing );
It is worth noting that the webbrowser control does not support menu merging, that is, the menu of the Excel table cannot be brought into our program. This is a major disadvantage compared with the OLE implementation method. Fortunately, the tool bar can be added, and many Excel-specific operations can be performed through the tool bar.
// Add the Excel tool in the following sentence
Axwebbrowser1.execwb (shdocvw. olestmid. olecmdid_hidetoolbars, shdocvw. olecmdexecopt. olecmdexecopt_dontpromptuser, ref refmissing, ref refmissing );

Iii. Back to the question raised in this article, how to operate the embedded Excel file?

First of all, you need to understand that when using webbrowser to "LOAD" an Excel "table, excel.exe is still running in the new process space. You can use the task manager to observe. Therefore, as long as we can obtain the Excel. Application object, we can operate the Excel Data as mentioned in the previous section.
Fortunately, you can access excel. application through event parameter E provided by navigatecomplete in webbrowser.
Public void axwebbrowser1_navigatecomplete2 (Object sender, axshdocvw. dwebbrowserevents2_navigatecomplete2event E)
{
Object o = E. Pdisp;
Object odocument = O. GetType (). invokemember ("document", bindingflags. getproperty, null, O, null );
Object oapplication = O. GetType (). invokemember ("application", bindingflags. getproperty, null, odocument, null );
// Object oname = O. GetType (). invokemember ("name", bindingflags. getproperty, null, oapplication, null );

// Because the Excel file is opened, the oapplication here is actually excel. Application
Excel. Application eapp = (Excel. Application) oapplication; // you can operate Excel as described above.
}

4. How to ensure that the Excel process also exits when the form containing the webbrowser exits? (See Microsoft Help Center kb317109)

Because webbrowser only views Excel tables, Excel runs in a separate process. Therefore, to ensure that the eapp of the Excel Object and all its corresponding member variables are released, the Excel process can exit when the form exits. This is especially important when a program needs to open and close an Excel table multiple times.
Excel. Application oapp;
Excel. workbooks obooks;
Excel. Workbook obook;
Excel. worksheet osheet;
...........
Private void excelexit ()
{
Nar (osheet );
Obook. Close (false );
Nar (obook );
Nar (obooks );
Oapp. Quit ();
Nar (oapp );

Debug. writeline ("sleeping ...");
System. Threading. thread. Sleep (5000 );
Debug. writeline ("End excel ");
}
Private void nar (Object O)
{
Try {system. runtime. interopservices. Marshal. releasecomobject (o );}
Catch {}
Finally {o = NULL ;}
}
After testing, I found that in addition to releasing these variables, the axwebbroswer1 must also be destroyed before the Excel process exits. Otherwise, the Excel process will not exit even if axwebbroser1 goes to navigate the blank content "about: blank. Therefore, you should disable the form in which axwebbroser1 is located, or directly call axwebbrowser1.dispose ();
If you still cannot exit normally due to other problems, you only need to call garbage collection.
GC. Collect ();