1. Manipulate Excel with OLE technology
---- OLE (Object Links and Embedded) is the standard proposed by Microsoft. It is a way to exchange data between applications, interoperability, MS Office provides a strong OLE service, VB, Delphi, and C Builder Wait language can be submitted to the Excel to access the Excel to access its data, where VB and Delphi are the most simpler. In the syntax, the statement of VBA is used in the grammar, and the "48th" of this newspaper in Delphi "Excel "One article has introduced Delphi practice. We originally thought that C Builder moved Delphi, and the results of the syntax completion cannot be passed, and the information specifically described in this regard cannot be found. After repeated test square: The original C Builder uses an indirect approach to submit an operation command to the OLE service procedure using the four "methods" provided by variant Variant:
Variant Variant :: OLEPROPERTYGET (attribute name, parameter ...);
// Take Object Properties
Void Variant :: OlePropertySet (property name, parameter ...);
// Set the object properties
Variant Variant :: OLEFUNCTION (function name, parameter ...);
/ / Run the function of the object
Void variant :: oleprocedure; process name, parameter ....
// process of running an object
Its header file "vcl / utilcls.h" must be embedded in the user program,
For these four long method names, you can redefine in the program in the program:
#define pg olepropertyget
#define ps olepropertyset
#define fn olefunction
#define pr OLPROCEDURE
For example, for VB's submission statement:
EX.Activeworkbook.activesheet.cells (1, 2) .value = 3
To facilitate understanding of the statement corresponding to C Builder to decompose as the following four sentences:
Variant T1 = EX.OLEPROPERTYGET ("ActiveWorkbook");
Variant T2 = T1.OLEPROPERTYGET ("ActiveSheet");
Variant T3 = T2.OLEPROPERTYGET ("Cells, 1, 2);
T3.OLEPROPERTYSET ("Value", 3);
Remove the intermediate variables to combine these four sentences, that is:
EX.pg ("ActiveWorkbook"). PG ("ActiveSheet").
PG ("Cells", 1, 2) .ps ("Value", 3);
We will change the following VB program fragments to the corresponding C Builder program for reference:
Private submmand1_click () 'Visual Basic program fragment
DIM EX As Object, WB As Object, SH1 As Object
SET EX = CREATEOBJECT ("Excel.Application")
EX.Workbooks.open ("c: /book1.xls")
SET WB = EX.ActiveWorkbook
Set sh1 = wb.activeesheet
Text1.text = sh1.cells (1, 1) .value
For i = 1 to 10: for j = 1 TO 10
Sh1.cells (i, j) .value = i * 100 j
Next J: Next I
Wb.save: wb.close: ex.quit
End Sub '- - - - - - - - - - - -
#include "unit1.h" // C Builder segment
#include "vcl / utilcls.h" // util classes Utility Class Description
// ... save this original code
/ / Insert the four macro definition statements mentioned above here
Void __fastcall tform1 :: button1click (Tobject * Sender)
{Variant EX, WB, SH1;
EX = variant :: CreateObject ("excel.application");
EX.pg ("Workbooks") .pr ("open", "c: //book1.xls");
WB = EX.pg ("ActiveWorkbook"); SH1 = WB.PG ("ActiveSheet");
Edit1-> Text = sh1.pg ("cells", 1, 1) .pg ("value");
For (INT i = 1; i <= 10; i )
For (int J = 1; j <= 10; J )
Sh1.pg ("Cells", I, J) .ps ("Value", i * 100 J);
Wb.pr ("save"); WB.PR ("close");
}
---- Using this method running, you must ensure that there must be MS Office at the same time; then we will introduce another way to get the MS Office can also access the Excel form.
---- II. Access Excel with ODBC Database Technology
---- ODBC is an open database link standard. Different kinds of databases can be manipulated by the same command, and Microsoft provides an ODBC driver for Excel, which is the same as the ODBC driver. Access the Excel form. After defining the ODBC data source, there is no simplicity as an imagination. In the property table, it is always retrieved in TableName, and there is no relevant information. Through the analysis of Excel, the key to the problem finally discovered: ODBC's table name is not an Excel's work table name (such as Sheet1), which must define a "name" as a database in the Excel form. Table name, the first columns of the region must be a field name (otherwise the header data will be a field name), which can define multiple table names. The specific steps are as follows:
---- 1. Define "Table Name" on Excel:
---- Run an Excel program, open or create a table, press the left mouse button to select a zone (first line first fill in the field name), then point the mouse location to the address bar of the upper left corner, enter a table name such as ABC Or select: "Insert (i)" - "Name (N)" - "Definition (D)", then enter the table name (if defined, you can delete it here), save the disk exit (assume the file) Named C: /book.xls), if you are excessive, you can hide before the market;
---- 2. Define ODBC data sources:
---- From the Windows Desktop "My Computer" into the Control Panel, double-click the "32-bit ODBC" icon, run "Add (D)" Excel Driver (*. Xls) " , Then "Finish" will pop up the dialog box, fill in the "Data Source Name (N)" on the right side, such as: Excel01, select "Excel97" on "EXCEL97", click "Option" Cancel "only Read ", in the" Selected Work Directory ", select the Excel file name (this example c: /book1.xls), then" OK "until exit; ---- 3. Set the Database control to avoid login check:
---- Run C Builder, plus three controls in Form1: Database1, DataSource1, Table1, the purpose of joining Database1 is to avoid the login box when you open the database, to double-click this control to pop up a conversation In the box, select the ODBC data source in Alias Name (this example is Excel01), fill in a new alias in the Name (this example takes excel02), then "defaults" has a batch of parameters defaults, and finally cancel the options in Options Two "Login Prompt" with "Keep Inactive Connect", point OK exits;
---- 4. Set other control properties:
---- Select the property DatabaseName of Table1 to the new alias Excel02 in step 3, and then select another property tablename to step 1 (this example is ABC); select the property Dataset of the control DataSource1 to table1; double click FORM1, add a Table1-> open () in the formcreate event subroutine;
---- 5. View the database content:
---- In order to see Excel data directly, add Data Controls in Form1 and DBNAVigator1, select the attribute Datasource to DataSource1, double-click Table1 attribute Active to change to true, wait a few seconds You can see the data in DBGRID1, and finally restore Tabel1-> Active for False;
---- Note: Before the program is compiled, the just opened table must be turned off, ie: Make Table1-> Active to false, otherwise the program is running error, because the Excel table is always opened by ODBC in "exclusive" mode;
---- We use C Builder 4.0 Professional, spreadsheets for Excel 97.
