Shulou(Shulou.com)06/02 Report--

This article mainly introduces how to use the Excel COM component in C#. It is very detailed and has certain reference value. Friends who are interested must finish reading it.

1 Excel object

Microsoft's Excel object model includes 128 different objects, from simple objects such as rectangles and text boxes to complex objects such as PivotTable and charts. Here's a brief introduction to the four most important and most used objects.

(1) Application object. The Application object is at the top of the Excel object hierarchy, representing the environment in which Excel itself runs. (2) Workbook object. The Workbook object is directly at the lower level of the Application object, representing an Excel workbook file.

(3) Worksheet object. The Worksheet object is contained in the Workbook object and represents an Excel worksheet.

(4) Range object. The Range object is contained in the Worksheet object, which represents one or more cells in the Excel worksheet.

2 managed code and unmanaged code in C#

Programs that run within the .NET common language framework are managed code. All types of managed code in the program are strictly checked, there are no pointers, and the management of memory is completely controlled by the running system. In a controlled state, it is easier to write programs with fewer errors, and we can spend more time solving practical problems rather than computer language problems. Relatively speaking, programs that run outside the .NET Framework are unmanaged code. For example: COM component, ActiveX component, Win32 API function, pointer operation and so on. In C # programming, unmanaged code needs to be used in some specific cases, such as using a mature COM component, calling an API function, or using pointers to write real-time / efficient programs.

3. Call the COM component of Excel in Visual C #

A .NET component is actually a DLL under .NET, which contains not only the running program itself, but also the description information of the DLL (Meta Data, that is, metadata), while a COM component uses its class library (TLB) to store its description information. These COM components are unmanaged code, and to use these COM components of unmanaged code in Visual C #, you must convert them into .NET components of managed code. So before calling the Excel table with Visual C #, you must complete the conversion from the unmanaged code of the COM component to the class library of the managed code.

3.1Transforming COM components of Excel to .NET components

Open the Add Reference dialog box in the project, select the COM bar, then find "Microsoft Excel 9.0 Object Library" (Office 2000) in the COM list, and add it to the References of the project. Visual C#.NET automatically generates the corresponding .NET component files, which can be used later.

This transformation results in that the .NET component cannot be used alone, it is just an outer wrapper of the previous COM component, which can be used to find the original COM component and call its corresponding interface functions. So it must work with the original COM component.

3.2 Visual C# Open the Excel form

In fact, using an Excel COM component (a converted one) in C # is exactly the same as using any other .NET component. You can create a transformed COM component with the new keyword, and then use the component object like any other C # object.

A namespace Excel is defined in the converted .NET component, and a class Application is encapsulated in this namespace. This class has a very important relationship with starting the Excel table. In Visual C #, you only need the following three lines of code to open the Excel table, as follows:

Excel.Application excel = new Excel.Application (); / / reference Excel object excel.Application.Workbooks.Add (true); / / reference Excel workbook excel.Visible = true; / / make Excel visible

But at this time, the Excel table is an empty table with no content, so here's how to enter data into the Excel table.

3.3 enter data into the Excel table

In the namespace "Excel", a class "Cell" is also defined, which represents a cell in the Excel table. Enter the corresponding data into the Excel table by assigning a value to "Cell". The following code function is to open the Excel table and enter some data into the table.

Excel.Application excel = new Excel.Application (); excel.Application.Workbooks.Add (true); excel.Cells [1,1] = "First Row First Column"; excel.Cells [1,2] = "First Row Second Column"; excel.Cells [2,1] = "Second Row First Column"; excel.Cells [2,2] = "Second Row Second Column"; excel.Visible = true

3.4 examples of using C# Excel COM components

The following example connects to the Oracle database (Name) in C #, reads data from the table (TableName), and writes it to Excel.

String cnString= "Provider=msdaora.1;Data source=Name;"; cnString=cnString+ "user id=UserName;password=Password"; try {OleDbConnection cn=new OleDbConnection (cnString); cn.Open (); try {string s = "select * from Name.TableName"; OleDbCommand cmd=new OleDbCommand (Smemcn); OleDbDataReader dr=cmd.ExecuteReader (); Excel.Application xlApp= new Excel.Application (); if (xlApp==null) {MessageBox.Show ("Can't open Excel!"); return;} xlApp.Application .Workbooks .add (true); int row=2,fieldcount; fieldcount=dr.FieldCount For (int col=0;col < fieldcount;col++) xlApp.Cells [1J collocation 1] = dr.GetName (col); while (dr.Read ()) {for (int col=0;col < fieldcount;col++) xlApp.Cells [row,col+1] = dr.GetValue (col). ToString (); row++;} xlApp.Visible = true; xlApp=null;} catch (Exception ex) {MessageBox.Show (ex.Message);} finally {cn.Close ();}} catch (Exception ex) {MessageBox.Show (ex.Message) }}}

3.5 install a .NET program that uses COM components

If you want to install such a program and run it on another machine, there are three things to do in addition to installing the runner.

The first is to install the .NET operating system. Because no .NET program can run independently without the .NET operating system.

Second, the invoked COM component must be installed on the target machine. In this example, most of the target machines have Excel for Microsoft Office, which is generally not a problem. However, if it is another user-defined COM component, the COM component must be installed before running the .NET program.

*. The converted .NET component DLL file should be installed on the target machine. Because .NET components do not need to be registered in Windows Registry, the easiest way is to copy the .NET component DLL file to the runtime directory. If this .NET component is shared by multiple .NET programs, it can be installed in the .NET common component area so that it can be used by any one of the .NET components. You need to register a .NET component as a COM+ component only if it participates in a transaction. Because .NET still uses the traditional COM+ mechanism to handle transaction commit, rollback, and so on.

These are all the contents of the article "how to use Excel COM components in C#". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.