Visual C # programming operation Excel2004-08-20 Author: Shao back to the ancestral Source: ahcithttp: //www.yesky.com/SoftChannel/72342380468109312/20040819/1844435.shtml Abstract: This article describes the Excel object, managed code in C # and Non-managed code and introduce the use of COM components in the .NET environment.
Keywords: managed code; non-managed code; Excel object; dynamic connection library
introduction
Excel is a software in Microsoft's Office Automation Suite, which is mainly used to process spreadsheets. Excel is welcomed by many users with powerful, friendly interface. When designing an application system, for different users, they are different for printing, and if they want to make the print function in the program, they can imagine that programming is very complicated. Due to the power of the Excel form, it is installed in almost every machine. If the result of the program processing is placed in the Excel form, each user can customize their own prints in Excel according to their own needs. This not only makes the program design, but also meets the requirements of many users, more practical. So how to call Excel with Visual C #, how do you store the data into an Excel form? This article explores the solutions to the above problems.
Excel object
Microsoft's Excel object model includes 128 different objects, from rectangular, text boxes, etc. Simple objects to perspective tables, charts, and more complex objects. Below we briefly introduce one of these most important and use the most used objects.
(1) Application object. The Application object is on the top floor of the Excel object hierarchy, indicating the operating environment of Excel itself.
(2) Workbook object. The Workbook object is directly under the next layer of the Application object, indicating an Excel work thin file.
(3) Worksheet object. The Worksheet object is included in the Workbook object, indicating an Excel worksheet.
(4) Range object. The Range object is included in the Worksheet object, indicating one or more cells in the Excel worksheet.
Channel in C # and non-accomplished code
The program running within the .NET public language frame is the managed code. The managed code is strictly inspected in all types in the program, no pointer, and the management of memory is fully controlled by the running system. In a controlled state, the writing program is easier and less error, we can spend more time on the actual problem instead of computer language issues. Relatively speaking, those procedures running outside the .NET frame are non-compatible. For example: COM components, ActiveX components, Win32 API functions, pointer operations, etc. C # Programming In some specific cases, you need to use non-accommodating code, for example, to use a mature COM component, or call an API function, or write a real-time / efficient program with a pointer.
Visual C # Middle Call Excel COM components
One .NET component is actually a .NET DLL, which contains not only the runtime, but more importantly, the description information of this DLL is included (Meta Data, ie metadata), and a COM component is used Library (TLB) stores its description information. These COM components are non-managed code. To use these non-managed COM components in Visual C #, they must convert them into the .NET component of the managed code. So before the Excel form is called with Visual C #, you must complete the conversion of non-managed code from the COM component to the class library of the category library.
1. Convert the Excel's COM component to the .NET component Open the Add Reference dialog box, select the COM column, then find "Microsoft Excel 9.0 Object Library" (Office 2000) in the COM list, then add it to the project You can in References. Visual C # .NET automatically generates the corresponding .NET component file, you can use it normally.
This conversion forms the .NET component cannot be used alone, it is just an outer packaging of the previous COM component, and can discover the original COM components through this outer package in .NET and call its corresponding interface function. So it must work with the original COM components.
2, Visual C # Open Excel form
In fact, use a converted COM component in C # and is exactly the same as any other .NET component. You can create a converted COM component with the New keyword, and then use this component object like any other C # object.
A named space Excel is defined in the converted .NET component, encapsulates a class Application in this namespace, which is very important in this class and startup Excel form, in Visual C #, only need the following three lines of code You can complete the work of opening the Excel form, as follows:
Excel.Application Excel = new Excel.Application (); // Reference Excel object Excel.Application.Workbooks.add (true); // Reference Excel workbook Excel.visible = true; // Make Excel visual
But at this time, the Excel form is an empty form. There is no content, here you will introduce how to enter data into the Excel form.
3, enter data into the Excel form
In the namespace "Excel", a class "cell" is also defined, which represents a cell in the Excel form. By assigning the "Cell", the corresponding data is entered into the Excel form, the following code features are to open the Excel table and enter 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, instance
The following example is connected to the Oracle Database (Name) in the C #, read data from the table (TableName) and writes 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 ("Can't Open Excel!"); Return;} xlapp.application .Workbooks .add (true); int = 2, FieldCount; FieldCount = Dr.fieldCount; for (int COL = 0; Col If you want to install such a program on another machine, then do three things in addition to the installation of running programs. First, it is installed .NET running system. Because any .NET program cannot leave the .NET running system to run independently. Second, the COM component called must be installed on the target machine. Most of this example is equipped with Microsoft Office Excel, which generally does not have this problem. But if it is another user-defined COM component, then this COM component must be installed before running the .NET program. Finally, the converted .NET component DLL file is installed on the target machine. Since the .NET component does not need to register in Windows Registry, the easiest way is to copy the .NET component DLL file to the running program directory. If this .NET component is shared by multiple .NET programs, it can be installed in the .NET public component area, so that it can be used by any .NET component. Only when a .NET component is involved in transaction processing, it is necessary to register it as a COM component. Because .NET still uses traditional COM mechanisms to handle transactions, rollback, etc. summary Through the above discussion, we know how to use Excel's COM components in C #. It should be noted that many of the contents of Excel objects we have not introduced, and we need to learn from during use. Also let us know how to use COM components in C #. references:
