Embed and operate an Excel table in Form

The following section describes how to operate an Excel worksheet on the Internet:ArticleBut they all start the Excel window to open the Excel data file. Sometimes you need to embed an Excel table into yourself.ProgramIn form, it seems better to 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 an Excel file. new Application Process
excel. application APP = new excel. application ();
If (APP = NULL)
{< br> statusbar1.text = "error: Excel couldn't be started! ";
return;
}< br>
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 = app. workbooks;
_ workbook = workbooks. add (xlwbatemplate. xlwbatworksheet); // generate a new workbook Based on the template
// _ workbook = workbooks. add ("C: \ a.xls"); // you must open the.xls file.

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. When the form containing the webbrowser exits, how can I ensure that the Excel process also 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");
}< br> private void nar (Object O)
{< br> try {system. runtime. interopservices. marshal. releasecomobject (o) ;}< br> catch {}< br> finally {o = NULL ;}< BR >}< br> after testing, I found that apart from releasing these variables, the axwebbroswer1 must 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 it is still caused by other problems and cannot exit normally, you only need to call garbage collection.
GC. Collect ();