As an excellent RAD, the powerful database function is one of its most important features, but the QuickReport control for manipulating difficulties often meet the needs of the database report. If your report is very complicated, or require flexibility to change the format, use Excel as a report server is a nice choice. The Excel component provided by Delphi starts from version 5 greatly simplifies the application of OLE automation technology. However, there are many helper files that have been the most ill-devastated place in Delphi. These new components are no exception. This article tries to introduce this more detail.
Excel's object model is a tree hierarchy. The root is the application itself. Workbook Workbook is the properties object of the root object. The mainly discussed for data exchange is the property object of the workbook. For details, please refer to Msoffice Provision Excel VBA Help file. Control Excel in Delphi first to establish a connection with the server program, open the workbook, and then exchange data with the target worksheet, and finally disconnect.
Open an Excel workbook
Our example starts from a main form with TStringGrid (of course to fill in some data) and two buttons, dragging a TexceLapplication control from the control panel's ServeLication control on the form. First, set ConnectKind to CKRUNNINGORNEW, indicating that if the EXCEL instance that can be detected, the Excel is launched. Also, if you want the program to run contact with the server program, you can set the AutoConnect property to True. Establishing contact with Excel as long as a statement is: Excel. Connect; Maybe you have noticed several other Excel controls on the Servers tab, which can be connected with the previous Excel through the ConnectTo method:
Excelworkbook1.connectto (Excel. ActiveWorkbook);
Excelworksheet1.connectto (Excel. Activeesheet as _Worksheet);
Excelworksheet2.connectto (Excel. Worksheets.Item ['Sheet2'] AS _WORKSHEET
Note that before using the ConnectTo method, you must first open the appropriate workbook or worksheet, and these controls do not bring additional convenience in most cases, so it is best to use only one texcelapplication. Once you connect with the Excel server, you can create a new workbook:
Var wkbook: _workbook;
LCID: Integer;
... lcid: = getUserDefaultlcid ();
WKBOOK: = Excel.Workbooks.add (EmptyParam, LCID);
The first parameter of the Add function is used to define the template used by the New Workbook, you can use XLWBATCHART, LWBATEXCEL4INTLMACROSHEET, XLWBATEXCEL4MACROSHEET, or XLWBATWORKSHEET constant, or the existing XLS file name. The EMPTYPARAM here is the VariantS unit and the defined variable, indicating that the new workbook is created using the default generic template. If you open an existing XLS document, you should pass the file name you want to open as the first parameter to the Open function:
WKBOOK: = Excel.Workbooks.open (edtdesfile.text, emptyparam, emptyparam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, LCID;
To know, all data operations are mainly for active worksheets, the following statement uses a _Worksheet variable representing the current activity cell. If you know the name of the worksheet, the index number can be replaced by the work derived name:
WKSHEET: = wkbook.sheets [1] as _Worksheet;
Save a workbook after completing data exchange:
Excel.activeworkbook.saveas ('Myoutput', EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, LCID;
or:
Excel.Activeworkbook.save (LCID);
Finally, turn off the workbook and disconnect to Excel:
Wkbook.close (True, SaveAsName, EmptyParam, LCID); //Excel.quit; Excel.disconnect;
The close method here includes a saved function, the first parameter illustrates whether the modification is saved before turning off the workbook, the second parameter gives the file name to be saved, the third parameter is used for multiple authors to process documents Case. The second line requires termination of the operation of Excel.
Exchange data with worksheet
The input data is performed on a cell or area of the active worksheet, and the RANGE and Cells are object properties of the worksheet. Cells is a collection of cells. Cell A1 in the upper left corner, Note that Items in VBA is Cells's default attributes can be omitted, but there is no convenience in Delphi. For the cellular value to reference its value attribute, it is self-evident, this property is a Variant variable, for example:
WKSHEET.CELLS.ITEM [1, 1] .value: = 'address book;
Of course, you can also specify a formula for cells:
VAR AFORMULA: STRING; ......
AFORMULA: = '= Rand ()'; wksheet.range ['f3', 'g6']. Value: = AFORMUL;
The above method is very straightforward, but the speed is very slow, not suitable for large reports. So can you pass all the data to Excel? We can use Range, which represents one of the worksheets, like we drag it with a mouse, generally a rectangular area, as long as it gives the position of the upper left corner and the lower right corner cell, such as Range [ 'C3', 'J42']. There is also a small problem here because if the data exceeds 26 columns (such as 100 columns) or if you need to determine the target area range in the run, you can use the character name to mark cells. Recall that since "C3" is a unit mark, then we can of course use Cells, such as Range [Cells.Item [1, 1], Cells.Item [100,100]]. I can imagine that the value of the Range should be an array, but it is absolutely unable to assign it with array in delphi! Remember, in Delphi, the value of the Excel object is always a Variant type.
VAR
Data: Variant; IR, IC: Integer;
......
Data: = VararrayCreate ([1, IR, 1, IC], Varvariant); // Creating 100 * 100 Dynamic arrays
... // Here is an array element assignment
WITH WKSHEET Do Range [Cells.Item [3,1], Cells.Item [IR 2, IC]]. Value: = Data;
Note that both worksheets have a Cells property in order to clearly, use the with statement here. In addition, Range is directional, using the one-dimensional array of VararrayCreate can only assign a single line of Range, and if you want to be a single column definition value, you must use a two-dimensional array, such as:
DataS: = VararrayCreate ([1,100, 1, 1], Varvariant); // Create a dynamic array of 100 * 1.
By the way, Cells.Item [] is actually returned to the RANGE object. Removing data from the worksheet basically is the reverse process of writing data, a slightly need to pay attention to how to determine the data range of the worksheet:
VAR
Ir, IC: integer;
......
WKSHEET.CELLS.SPECIALCELLS (XlcellTypelastcell, EMPTYPARAM) .actiVate;
Ir: = Excel.activecell.Row;
IC: = Excel.Activecell.column;
This is cleverly uses the special cell function Specialcells to obtain the last cell containing the data.
Data editing
Below is two examples of data editing.
VAR DESTRANGE: Olevariant;
Begin
Destrange: = Excel.Range ['C1', 'D4'];
Excel.Range ['A1', 'B4']. Copy (DESTRANGE);
The above example copies the content of 8 cells. If an empty parameter is transmitted to the COPY function, the data of the area is copied to the clipboard, and it can be pasted to the other position with the PASTE method.
Var ws: _Worksheet;
......
Excel.Range ['A1', 'B4']. Copy (EmptyParam); // Copy data to the clipboard in a worksheet
WS: = Excel.activeesheet as _Worksheet; // Change Activity Worksheet
WS.RANGE ['C1', 'D4']. SELECT;
WS.PASTE (EmptyParam, EmptyParam, LCID); // Paste the content in the clipboard into a new worksheet
Format setting
Choosing Excel is mainly because of its powerful formatting capabilities.
Let's take the title "Address Book" to merge the title "Address", and then modify the font to 18 pounds "Lishu", bold:
WITH WKSHEET.RANGE ['A1', 'D1'], FONT DO BEGIN
MERGE (TRUE); // Merge unit
Horizontalaright: = XlCenter;
SIZE: = 18;
Name: = 'Lishu';
FontStyle: = bold;
END;
If the cell content is longer, some content cannot be displayed, and the usual approach is to double-click the edge of the right side of the selected area to automatically adapt to the length of the content of each column. The adaptive column width is high in Delphi, and it is necessary to note that the method can only be used only for the entire row, otherwise the OLE method refuses to perform an error: wksheet.columns.entirecolumn.autofit;
Chinese reports typically need to get up and down tables, you can use the BORDERS collection attribute. It is to be noted that the collection objects in VBA usually have a default ITEM property, and it is not possible in Delphi. The Weight property is used to define the thickness of the table line:
With aname.referStorage, Borders Do Begin
Horizontalalight: = XLRight;
Item [xledgebottom] .weight: = xlmedium;
Item [xledgetop] .weight: = xlmedium;
Item [xlinsidehorizontal] .weight: = xlthin
Item [xlinsidevertage] .weight: = xlthin
END;
Page Setup and Print
The page setting is set by the Pagesetup object property of the worksheet. More than 40 kinds of paper is preset in Excel VBA, which requires that some printers only support some of the paper types. The attribute orientation is used to control the direction of the print, and the constant landscape = 2 represents lateral printing. Boolean property centerhorizontally and centervertage are used to determine if the printed content is in the horizontal and vertical direction.
With WKSheet.pagesetup do begin
PAPERSIZE: = xlpapera4; //paper type a4
PRINTTITLEROWS: = 'a1: d1'; // review this row / page
Leftmargin: = 18; //0.25 "Left margin
Rightmargin: = 18; //0.25 "Will Vary Between Printers
TopMargin: = 36; //0.5 "
Bottommargin: = 36; //0.5 "
Centerhorizontally: = TRUE;
Orientation: = 1; // Landscape = 2, portrait = 1
END;
Print reports can call the printout method of the worksheet, the method defined by the VBA has 8 optional parameters, the first two parts for specify the start page, the third format print, but in Delphi, it has finally added one LCID parameters, and this parameter cannot use EMPTYPARAM. Similarly, print preview method printpreView does not have parameters in VBA, and two parameters are required in Delphi.
// wkbook.printpreview (True, LCID); // for previewing
WKSHEET.PRINTOUT (EmptyParam, EmptyParam, 1, EmptyParam, EmptyParam, EmptyParam,
EMPTYPARAM, EMPTYPARAM, LCID);
Named area and macro
If the format of the report is more complicated, naming for a specific table area and then reference is a better way. Names is a collection object attribute of Workbook, which has an Add method to complete this work.
VAR Aname: Excel2000.name;
......
Aname: = wkbook.names.add ('Address Book ",' = Sheet1! $ A $ 3: $ D $ 7 ', EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EMPTYPARAM, EMPTYPARAM, EMPTYPARAM;
The first parameter of the ADD function is the name defined, and the second parameter is the cell area represented by the name. Note that the type of the area name must use a qualifier, if the type library (D4) is used, the qualifier is Excel_TLB. In addition, the named area should use an absolute reference method, add "$" symbol. Once a zone is named, you can use this name to reference it. The following line of code makes the address book content in bold:
Aname.ReferStorage.Font.Bold: = true;
But the most surprisingly, you can modify the Excel macro in Delphi! The following code creates a macro for our workbook, and records the last visit when turning off the workbook:
VAR LINENO: Integer;
CM: CODEMODULE;
SDATE: STRING;
Begin
CM: = Wkbook.vbProject.vbComponents.Item ('thisworkbook'). CodeModule;
Lineno: = cm.createEventProc ('BeforeClose', 'Workbook');
SDATE: = 'Lasted Date:' DateTostr (Date ());
CM.Insertlines (LINENO 1, 'Range ("B2"). Value = "' sdate '");
END;
Modifying the macro requires a unit: VBIDE2000, if the type library is used, the corresponding unit is VBIDE_TLB. The key to this code is the CodeModule object, unfortunately, in the Excel VBA Help text, can only retrieve MSDN. Delphi4 and previous version Delphi4 did not provide TexceLapplication objects, requiring import type libraries using OLE automation technology, Excel97's type library is Excel8.olb. The main difference between these two methods is to establish a connection with the server program, which is the program framework for control Excel through the type library:
Uses Windows, Comobj, ActiveX, Excel_TLB;
VAR
Excel: _Application;
LCID: Integer;
UNKNOWN: IUNKNOWN;
Result: HRESULT;
Begin
LCID: = locale_user_default;
Result: = getActiveObject (class_application, nil, unknown; // Try capturing program instances in running
IF (result = mk_e_unavailable) THEN Excel: = COAPPLICATION.CREATE / / Start a new program instance
Else Begin {Check the error during the getActiveObject method call.
OLECHECK (Result);
OLECHECK (Unknown.Queryinterface);
END;
... // Data processing
Excel.visible [LCID]: = True;
// Excel.displayAlerts [LCID]: = false; // Display Tips dialog
Excel.quit;
END;
There is no usual Try ... ExcePT structure, because the exception processing mechanism should be complex OLE check, which reduces the execution speed of the ExcePt section. It is to be noted that different Delphi version generated companion functions COAPPLICATION and some constant names may vary, and should look at the corresponding type library. Before calling the Quit method, you must release all workbooks and workfinder variables created in the program, otherwise Excel may reside in memory (you can press CTRL Alt Del). Calling the getActiveObject capture program instance There is also a small problem. If Excel minimizes the running status, only the program owner framework is displayed and the user area is not visible. In addition, if you do not want to introduce type libraries, you can use a method of lagging binding, but speed is much slow. The following example declares a Variant variable to represent the Excel application:
Var Excel: Variant;
......
Try
EXCEL: = getActiveoleObject ('Excel.Application');
Except
Excel: = CreateoleObject ('Excel.Application');
END;
Excel.visible: = true;
When using a lag binding, the compiler does not check the calling Excel object method, and the payment of these work is completed when executed, so that the large amount of default parameters set (often more than a dozen) set by VBA will have The role, so this method has an unexpected benefit - the code is simple:
Var WBK, WS, Sheetname: Olevariant;
......
WBK: = Excel.Workbooks.Open ('c: /test.xls');
WS: = wbk.worksheets.Item ['SheetName'];
Ws.activate;
......
WBK.Close (SaveChanges: = true);
Excel.quit;
In addition to running slowly, if you want to use the constant defined in the type library, you can only do it yourself:
Const xlwbatworksheet = -4167;
......
XLapp.Workbooks.Add (XLWBATWORKSHEET);
Finally don't forget to turn off the Excel release variables:
Excel: = unassigned;
The following is the source code used in this example, passed in Delphi6 MsOffice2000.
Unit unit1;
Interface
Uses Windows, Messages, Sysutils, Variants, Classes, Graphics, Controls, Forms, Dialogs,
Oleserver, Excel2000, Grids, stdctrls;
Type
TFORM1 = Class (TFORM)
Button1: tbutton;
StringGrid1: tstringgrid;
TEXCELAPPLICATION;
Procedure formactivate (Sender: TOBJECT);
Procedure Button1Click (Sender: TOBJECT);
Private {private declarations}
Procedure Write2xls;
Procedure OpenExL;
Procedure CloseExl; Procedure Addformula;
PROCEDURE NAMESHEET;
PROCEDURE FORMATS;
PROCEDURE ADDMACRO;
Procedure Retrieve;
Procedure printit;
PUBLIC {public declarations}
END;
Var Form1: TFORM1;
Implementation {$ r * .dfm}
Uses vbide2000;
VAR
Ir, IC: integer;
WKSHEET: _WORKSHEET;
LCID: Integer;
WKBOOK: _WORKBOOK;
Aname: Excel2000.name;
Procedure TFORM1.MMACACTIVATE (Sender: TOBJECT);
Begin
With stringgrid1 do begin
Rows [0] .commatext: = 'Name, Gender, age, phone';
Rows [1] .commatext: = 'Zhang San, Male, 25, 010-33775566';
Rows [2] .commatext: = 'Li Si, male, 47, 012-6574906;
Rows [3] .commatext: = 'Friday, female, 18,061-7557381';
Rows [4] .commatext: = 'Sun Tao, female, 31, 3324559;
END;
END;
Procedure TFORM1.OPENEXL;
Begin
With Excel Do Begin
Connect; LCID: = GetUserDefaultlcid ();
WKBOOK: = Workbooks.Add (EmptyParam, LCID);
WKSHEET: = wkbook.sheets [1] as _Worksheet;
END;
END;
Procedure TFORM1.WRITE2XLS;
VAR
Data: Variant;
I, J: Integer;
Begin
Ir: = stringgrid1.rowcount;
IC: = StringGrid1.colcount;
Data: = VararrayCreate ([1, IR, 1, IC], Varvariant;
For i: = 1 to Ir do
For J: = 1 to IC DO
Datas [I, J]: = StringGrid1.cells [J-1, I-1];
With WKSheet Do Begin
Activate (LCID);
Cells.Item [1,1] .value: = 'address book;
Range [Cells.Item [3, 1], Cells.Item [IR 2, IC]]. Value: = Data;
END;
// Excel.visible [LCID]: = true;
Data: = UNASSIGNED;
END;
Procedure TFORM1.RETRIEVE;
VAR
Data: Variant;
I, J: Integer;
Begin
With WKSheet Do Begin
Cells.Specialcells (xlcelltypelastcell, emptyparam) .activate;
Ir: = Excel.activecell.Row;
IC: = Excel.Activecell.column;
Data: = Range [Cells.Item [1,1], cells.Item [IR, IC]]. Value;
With stringgrid1 do begin
COLCOUNT: = IC;
Rowcount: = IR;
Scrollbars: = SSBOTH; for i: = 0 to IR-1 DO
For j: = 0 to IC-1 DO
Cells [J, I]: = DATAS [i 1, J 1];
END;
Data: = UNASSIGNED;
END;
END;
Procedure TFORM1.CLOSEEXL;
Const
SaveASName = 'Test.xls';
Begin
Wkbook.close (True, SaveAsName, EmptyParam, LCID);
Excel.quit;
Excel.disconnect;
END;
PROCEDURE TFORM1.NAMESHEET;
Begin
Aname: = wkbook.names.add ('Address Book ",' = Sheet1! $ A $ 3: $ D $ 7 ', EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EMPTYPARAM, EMPTYPARAM, EMPTYPARAM;
END;
Procedure TFORM1.AddFormula;
VAR
AFORMULA: STRING;
Begin
AFORMULA: = '= rand ()';
WKSHEET.RANGE ['F3', 'G6']. Value: = AFORMULA;
END;
PROCEDURE TFORM1.FORMATS;
Begin
With wksheet.range ['A1', 'D1'], FONT DO
Begin
MERGE (TRUE); // Merge unit
Horizontalaright: = XlCenter;
Size: = 18; Name: = 'Lishu';
FontStyle: = bold;
END;
WKSHEET.COLUMNS.ENTIRECOLUMN.AUTOFIT;
With aname.referStorage, Borders Do Begin
Horizontalalight: = XLRight;
Item [xledgebottom] .weight: = xlmedium;
Item [xledgetop] .weight: = xlmedium;
Item [xlinsidehorizontal] .weight: = xlthin
Item [xlinsidevertage] .weight: = xlthin
END;
END;
Procedure TFORM1.ADDMACRO;
VAR
LINENO: Integer;
CM: CODEMODULE;
SDATE: STRING;
Begin
CM: = Wkbook.vbProject.vbComponents.Item ('thisworkbook'). CodeModule;
Lineno: = cm.createEventProc ('BeforeClose', 'Workbook');
SDATE: = 'Lasted Date:' DateTostr (Date ());
CM.Insertlines (LINENO 1, 'Range ("B2"). Value = "' sdate '");
END;
Procedure TFORM1.PRINTITIT;
Begin
With WKSheet.pagesetup do begin
PAPERSIZE: = XLPapera4; // Paper Type A4PrinTtitlerows: = 'A1: D1'; // Repeat this Row / Page
Leftmargin: = 18; //0.25 "Left margin
Rightmargin: = 18; //0.25 "Will Vary Between Printers
TopMargin: = 36; //0.5 "
Bottommargin: = 36; //0.5 "
Centerhorizontally: = TRUE;
Orientation: = 1; // Landscape = 2, portrait = 1
END;
WKSHEET.PRINTOUT (EmptyParam, EmptyParam, 1, EmptyParam, EmptyParam, EmptyParam,
EMPTYPARAM, EMPTYPARAM, LCID);
END;
Procedure TFORM1.BUTTON1CLICK (Sender: TOBJECT);
Begin
Try
Openexl;
Write2xls;
Addformula; Namesheet;
Formats; printit;
Addmacro;
Retrieve;
Finally
Closeexl;
END;
END;
End.
