Visual C # programming Excel

Abstract:This section describes the Excel objects, managed code and unmanaged code in C #, and the use of COM components in the. NET environment.

　　Keywords:Managed code; unmanaged code; Excel object; Dynamic Connection Library
　　Introduction

Excel is a software in Microsoft Office Automation suite. It is mainly used to process workbooks. Excel has been welcomed by many users for its powerful functions and friendly interfaces. When designing an application system, different users have different requirements for printing. If you want to make the printing function in the program suitable for every user, you can imagine that the program design is very complicated. Because the Excel table is powerful, and almost every machine has installed it, if you put the results of program processing in the Excel table, in this way, each user can customize their own printing in Excel according to their own needs. This not only makes the program design simple, but also meets the requirements of many users and is more practical. So how can I use Visual C # To Call Excel and store data in an Excel table? This article will discuss the solutions to the above problems.

　　Excel objects

Microsoft's Excel object model contains 128 different objects, from simple objects such as Rectangles and text boxes to complex objects such as pivot tables and charts. next, we will briefly introduce the most important and most commonly used four objects.

(1) Application object. The Application object is at the top level of the Excel object hierarchy, indicating the Excel running environment.

(2) Workbook object. The Workbook object is directly in the lower layer of the Application object, indicating an Excel worksheet file.

(3) Worksheet object. The Worksheet object is included in the Workbook object, indicating an Excel Worksheet.

(4) Range object. The Range object contains the Worksheet object, which indicates one or more cells in an Excel Worksheet.



　　Managed code and unmanaged code in C #

Programs running in the. NET public language framework are managed code. All types of managed code in the program are strictly checked, with no pointers. The management of memory is completely controlled by the operating system. Under control, programming is easier and less error-prone, so we can spend more time solving practical problems rather than computer language problems. Relatively speaking, programs running outside the. NET Framework are unmanaged code. For example, COM components, ActiveX components, Win32 API functions, and pointer operations. C # in programming, you must use unmanaged code in certain situations. For example, you must use a mature COM component or call an API function, or use pointers to write real-time/efficient programs.

　　Call the Excel COM component in Visual C #

One. NET component is actually.. net dll, which contains not only the running program itself, but also the description of the DLL (Meta Data, that is, metadata ), a com component uses its class library (TLB) to store its description information. These COM components are not managed code. To use these COM components in Visual C #, you must convert them into managed code. NET components. Therefore, before using Visual C # to call an Excel table, you must complete the conversion from the unmanaged code of the COM component to the class library of the managed code.

1. Convert the COM component of Excel to the. NET component.

Open the Add Reference dialog box in the project, select the COM column, and find "Microsoft Excel 9.0 Object Library" (Office 2000) in the COM list ), add it to the project References. Visual C #. NET automatically generates the corresponding. NET component file.

This conversion form. the. NET component cannot be used independently. It is only an outer package of the previous COM component. NET, you can use this outer package to find the original COM component and call its corresponding interface functions. Therefore, it must work with the original COM component.

2. Visual C # open an Excel table

In fact, using a converted COM component in C # is exactly the same as using any other. NET component. You can use the new keyword to create a converted COM component and then use the component object like any other C # object.

After conversion. the. NET Component defines a namespace Excel, which encapsulates a class Application. This class has a very important relationship with starting an Excel table. in Visual C, you only need the following three lines of code to open an Excel Worksheet:

Excel. Application excel = new Excel. Application (); // reference an Excel Object Excel. Application. Workbooks. Add (true); // reference an Excel Workbook Excel. Visible = true; // make Excel Visible

However, the Excel table is an empty table with no content. The following describes how to input data into the Excel table.

3. input data to an Excel table

In the namespace "Excel", a class "Cell" is also defined, which represents a Cell in an Excel table. By assigning values to "Cell", you can input data to an Excel table. The following code is used to open an Excel table and input some data to 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;

4. Instances

The following example connects to the Oracle database (Name) in C #, reads data from the table (TableName), and writes data 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 (s, cn ); OleDbDataReader dr = cmd. ExecuteReader (); Excel. Application xlApp = new Excel. Application (); If (xlApp = null) {MessageBox. Show ("Cant open Excel! "); Return ;} XlApp. Application. Workbooks. Add (true ); Int row = 2, fieldcount; Fieldcount = dr. FieldCount; For (int col = 0; col <fieldcount; col ++) xlApp. Cells [1, col + 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 );} } }

5. Install A. NET program using the COM component.

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

First, install the. NET operating system. Because no. NET program can be run independently from the. NET running system.

Second, the called COM component must be installed on the target machine. In this example, Microsoft Office Excel is installed on most target machines. However, if it is another custom COM component, the COM component must be installed before running the. NET program.

Finally, the converted. NET Component DLL file must be installed on the target machine. Because the. NET component does not need to be registered in Windows Registry, the simplest method is to copy the. NET Component DLL file to the running program directory. If the. NET component is shared by multiple. NET programs, you can install it in the. NET public component area to be used by any. NET component. Only when a. NET component is involved in transaction processing, it must be registered as a COM + component. Because. NET still uses the traditional COM + mechanism to handle transaction commit and rollback.

Summary

Through the above discussion, we know how to use the Excel COM component in C. It should be noted that the Excel object contains a lot of content that we have not introduced, and we need to continue to learn during use. We also learned how to use COM components in C.

References:

[1] Liu Hongcheng C #2003.7 jobs of Tsinghua University Press for advanced programming 187 ~ 200