Introduction to Excel
Excel is a software in Microsoft Office Automation suite, which is mainly used to process spreadsheets. Excel is popular with many users for its powerful and user-friendly interface. In the design of application systems, for different users, their requirements for printing is not the same, if you want to make the printing function in the program for each user, can imagine the program design is very complex. Because the Excel table is powerful, and because almost every machine has it installed, if the results of the program processing in the Excel table, so that each user can according to their own needs in Excel to customize their own printing. This not only makes the program design simple, but also meet the requirements of many users, more practical. So how do you call Excel with Visual C #, and then how do you store the data in an Excel table? This paper is to explore the solution to the above problems.
Excel Objects
Microsoft's Excel object model includes 128 different objects, from simple objects such as rectangles and text boxes to complex objects such as pivot tables, graphs, and so on. Here's a brief description of the most important and the four most used objects.
(1) Application object. The Application object is at the top level of the Excel object hierarchy, representing the environment in which Excel itself runs.
(2) Workbook object. The workbook object is directly below the Application object, representing an Excel workbook file.
(3) Worksheet object. The worksheet object is contained in the Workbook object, representing an Excel worksheet.
(4) Range object. A Range object is contained in a Worksheet object that represents one or more cells in an Excel worksheet.
managed code and non-managed code in C #
Programs running within the. NET common language framework are managed code. The managed code is strictly checked for all types in the program, no pointers, and the management of the memory is completely controlled by the running system.
In a controlled state, writing programs is easier and less error-prone, and we can spend more time on solving practical problems rather than on computer language issues. Relatively speaking, those in. NET Framework runs outside of the managed code. For example: COM component, ActiveX component, Win32 API function, pointer operation and so on. In some specific cases, C # programming requires the use of unmanaged code, for example, to take advantage of a mature COM component, to invoke an API function, or to write a real-time/efficient program with pointers.
COM components that call Excel in Visual C #
One. NET component is in fact a. NET DLL that contains not only the running program itself, but more importantly the description of the DLL (meta data, or metadata), while a COM component uses its class library (TLB) to store its descriptive information. These COM components are unmanaged code that must be converted into managed code in order to use the COM components of these unmanaged code in Visual C #. NET component. So before you call an Excel table in Visual C #, you must complete the conversion from the unmanaged code of the COM component to the class library of the managed code.
1. Convert an Excel COM component to a. NET component
Open the Add Reference dialog box in your project, select the COM bar, and then in the COM list, locate the Microsoft Excel 9.0 Object Library ( Office2000), and then add it to the references of the project. Visual c#.netwill automatically generate the corresponding. NET component files, which can be used normally later.
This conversion forms. NET components cannot be used alone, it is nothing more than an outer wrapper of a previous COM component, in. NET, where you can discover the original COM component and invoke its corresponding interface function through this outer wrapper. So it has to work with the original COM component.
2, Visual C # open Excel table
In fact, using a converted COM component in C # is exactly the same as using any of the other. NET components. You can create a converted COM component with the new keyword, and then use the Component object as you would any other C # object.
After the conversion. NET component defines a namespace Excel, which encapsulates a class application, which has a very important relationship to starting Excel tables, and in Visual C # you can complete the work of opening Excel tables with only the following three lines of code. Specifically as follows:
Excel.Application Excel = new Excel.Application ();//referencing an Excel object
Excel. APPLICATION.WORKBOOKS.ADD (TRUE);//referencing an Excel workbook
Excel. Visible = true;//Make Excel visible |
But now that the Excel table is an empty table with nothing, here's how to enter data into an Excel table.
3. Enter data into Excel form
In the namespace "Excel", you also define a class "cell" that represents a cell in an Excel table. By assigning a value to the Cell, which enables you to enter the appropriate data into the Excel table, the following code function opens the Excel table and enters some data into the table.
Excel.Application Excel = new Excel.Application ();
Excel. APPLICATION.WORKBOOKS.ADD (TRUE);
Excel. cells[1, 1] = "A-A-Row-a-Column";
Excel. cells[1, 2] = "Second Column";
Excel. cells[2, 1] = "Second Row-A-Column";
Excel. Cells[2, 2] = "Second Row Second Column";
Excel. Visible = true; |
4. Example
The following example connects an Oracle database (Name) in C #, reads data from a table (tablename), and writes 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 ("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 [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 that uses COM components
If you want to install such a program on another machine, do three things in addition to running the program.
First, the installation. NET running system. Because any one. NET program can not leave the. NET running system to run independently.
Second, the COM component being invoked must be installed on the target machine. In this case, Microsoft Office Excel is installed on most of the target machines, and this 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.
Finally, after the conversion. NET component DLL file to be installed on the target machine. Because. NET components do not need to be registered in Windows registry, so the easiest way to do this is to. NET component DLL file to the running program directory. If this. NET components are shared by multiple. NET programs and can be installed in the. NET common component zone so that they can be either. NET components are used. Only when one. NET component is involved in a transaction, it needs to be registered as a COM + component. Because. NET still uses the traditional COM + mechanism to handle transaction submission, rollback, and so on.
Summary
From what has been discussed above, we know how to use the COM components of Excel in C #. It should be noted that the Excel object contains many of the content we did not introduce, in the use of the process requires us to continue to learn. Also gives us an idea of how to use COM components in C #.
