Close contact between Delphi and Excel

Close contact between Delphi and Excel [Wang Anpeng ([email protected]) 2002/4/14] As an excellent RAD, Delphi's powerful database function is one of its most important features, but the difficult-to-operate QuickReport control often cannot satisfy Database reporting needs. If your report is very complex, or requires flexibility in format changes, then using Excel as the report server is a good choice. The Excel component provided by Delphi starting from version 5 greatly simplifies the application of OLE automation technology. However, the missing help files have always been the most criticized aspect of Delphi, and these new components are no exception. This article attempts to introduce this in more detail. The object model of Excel is a tree-like hierarchical structure. The root is the application itself. WorkBook is the attribute object of the root object. The WorkSheet used for data exchange mainly discussed in this article is the attribute object of the workbook. For details, please refer to MSOffice. Excel VBA Help file. To control Excel in Delphi, you must first establish a connection with the server program, open the workbook, then exchange data with the target worksheet, and finally disconnect. Open the Excel workbook Our example starts with a main form with a TStringGrid (of course some data needs to be filled in) and two buttons. Drag a TExcelapplication control from the Servers tab of the control panel and place it on the form. First, set ConnectKind to ckRunningOrNew, which means that if the running Excel instance can be detected, establish contact with it, otherwise start Excel. In addition, if you want the program to establish contact with the server program as soon as it is run, you can set the AutoConnect property to True. All it takes to establish contact with Excel is one statement: Excel . Connect; Maybe you have noticed that there are several other Excel controls on the Servers tab. These controls can be linked to the previous Excel through the ConnectTo method: ExcelWorkbook1.ConnectTo( Excel . ActiveWorkbook); ExcelWorksheet1.ConnectTo(Excel . ActiveSheet as _Worksheet); ExcelWorksheet2.ConnectTo(Excel . Worksheets.Item['Sheet2'] as _Worksheet); It should be noted that the corresponding workbook or worksheet must be opened before using the ConnectTo method. In addition, these controls will not bring additional convenience in most cases, so it is best to use only one TExcelApplication. Once contact is established with the Excel server, a new workbook can be created: var wkBook : _WorkBook; LCID : Integer; ... LCID := GetUserDefaultLCID(); wkBook := Excel.Workbooks.Add(EmptyParam, LCID); Add function The first parameter is used to define the template used for the new workbook. You can use xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet or xlWBATWorksheet constant, or it can be the name of an existing xls file. The EmptyParam here is the Variants unit and defined variable, which means using the default universal template to create a new workbook. If you open an existing xls document, you should pass the file name to be opened 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); You must know that all data operations are mainly for the active worksheet. The following statement uses a _WorkSheet variable to represent the current active cell. If you know the name of the worksheet, the index number can be replaced by the worksheet name: wkSheet:=wkBook.Sheets[1] as _WorkSheet; After completing the data exchange, you need to save the workbook: Excel.ActiveWorkBook.SaveAs ('MyOutput', EmptyParam ,EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, LCID); or: Excel.ActiveWorkBook.Save(LCID); Finally, close the workbook and disconnect from Excel: wkBook.Close(True, SaveAsName, EmptyParam, LCID); //Excel.Quit; Excel.Disconnect; The Close method here contains the save function. The first parameter indicates whether to save the changes before closing the workbook. The second parameter gives the file name to be saved. The third parameter is used for multiple authors to process the document. situation. The second line asks to terminate Excel. Exchanging data with the worksheet The input data is performed on a certain cell or range of the active worksheet. Range and cells are both object properties of the worksheet. Cells is a collection of cells. If no specific location is specified, it can represent all cells in the entire worksheet. However, it is generally used to refer to a specific cell. For example, WS.Cells.Item[1,1] represents the most recent cell. Cell A1 in the upper left corner. Note that Item is the default property of Cells in VBA and can be omitted, but there is no such convenience in Delphi. To assign a value to a cell, you must refer to its Value property. It goes without saying that this property is a Variant variable, for example: wkSheet.Cells.Item[1, 1].Value := 'Address Book'; Of course, you can also assign a value to a cell. Specify the formula: var AFormula:String; …… AFormula:='=Rand()'; wkSheet.Range['F3','G6'].Value:=AFormula; The above method is very direct and simple, but it is very slow and not suitable for large reports. So can all the data be transferred to Excel in sequence? We can use Range. This object represents an area in the worksheet. As we drag out with the mouse, it is usually a rectangular area. Just give the positions of its upper left corner and lower right corner cells, such as Range[ 'C3','J42']. There is a small problem here, because if the data exceeds 26 columns (for example, there are 100 columns) or if the target area range needs to be determined on the fly, it is more troublesome to use character names to mark cells. Recall that since "C3" is the label of the cell, of course we can also use Cells, such as Range[Cells.Item[1,1], Cells.Item[100,100]]. It is conceivable that the value of Range should be an array, but you must not use Array in Delphi to assign a value to it! Remember that in Delphi, the value of an Excel object is always of type Variant. var Datas: Variant; Ir, ic: Integer; …… Datas:= varArrayCreate([1,ir,1,ic],varVariant); //Create a 100*100 dynamic array here… //Assign values ​​to the array elements here with wkSheet do Range[cells.Item[3,1],cells.Item[ir+2,ic]].Value:=Datas; It should be noted that both the worksheet and the Range have the Cells property. For clarity, the with statement is used here. In addition, Range is directional. A one-dimensional array created with VarArrayCreate can only be assigned to a single-row Range. If you want to define a value for a single-column Range, you must use a two-dimensional array, for example: Datas:=VarArrayCreate([1,100,1 ,1], varVariant); //Create a 100*1 dynamic array. By the way, Cells.Item[] actually returns a Range object. Retrieving data from the worksheet is basically the reverse process of writing data. What needs to be paid attention to is 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; The special cell function SpecialCells is cleverly used here to obtain the last cell containing data. Data Editing Below are two examples of data editing. var DestRange: OleVariant; begin DestRange := Excel.Range['C1', 'D4']; Excel.Range['A1', 'B4'].Copy(DestRange); The above example copies 8 cells content. If you pass an empty parameter to the Copy function, the data in this area is copied to the clipboard, and can be pasted to other locations using the Paste method later. var WS: _Worksheet; …… Excel.Range['A1', 'B4'].Copy(EmptyParam); //Copy data in a worksheet to the clipboard WS := Excel.Activesheet as _Worksheet; //Change activity Worksheet WS.Range['C1', 'D4'].Select; WS.Paste(EmptyParam, EmptyParam, lcid); //Paste the contents of the clipboard into a new worksheet. Format settings Choose Excel as the report server mainly because of its powerful formatting capabilities. We first merge the cells with the title "Address Book" and display it in the center, and then change the font to 18-point "official script" in bold: with wkSheet.Range['A1','D1'],Font do begin Merge(True ); //Merge cells HorizontalAlignment:= xlCenter; Size:=18; Name:='official script'; FontStyle:=Bold; end; If the cell content is long, part of the content will not be displayed. The usual approach is to double-click the right edge of the selected area to automatically adapt the width of each column to the length of the content. In Delphi, adaptive column width and row height can also be achieved through the AutoFit method. It should be noted that this method can only be used for the entire row and entire column, otherwise an error of OLE method refusal to execute will be prompted: wkSheet.Columns.EntireColumn.AutoFit; Chinese style Reports usually require upper and lower capped table lines, and you can use the Borders collection property. It should be noted that collection objects in VBA usually have a default Item property, which cannot be omitted in Delphi. The Weight property is used to define the thickness of table lines: with Aname.RefersToRange,Borders do begin HorizontalAlignment:= xlRight; Item[xlEdgeBottom].Weight:=xlMedium; Item[xlEdgeTop].Weight:=xlMedium; Item[xlInsideHorizontal].Weight: =xlThin; item[xlInsideVertical].Weight:=xlThin; end; Page setup and print page setup are set through the PageSetUp object property of the worksheet. There are more than 40 paper constants preset in Excel VBA. It should be noted that some printers only support some of the paper types. The attribute Orientation is used to control the direction of printing, and the constant landscape = 2 indicates horizontal printing. The Boolean properties CenterHorizontally and CenterVertically are used to determine whether the printed content is centered horizontally and vertically. with wkSheet.PageSetUp do begin PaperSize:=xlPaperA4; //Paper type A4 PRintTitleRows := '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; //Horizontal printing (landscape)=2, portrait=1 end; To print a report, you can call the PrintOut method of the worksheet. This method defined by VBA has a total of 8 optional parameters. , the first two are used to specify the starting and ending pages, and the number of copies to be printed in the third format. However, in Delphi, an LCID parameter is added at the end, and EmptyParam cannot be used for this parameter. Similarly, the print preview method PrintPreview has no parameters in VBA, but requires two parameters when called in Delphi. // wkBook.PrintPreview(True,LCID); //for previewing wkSheet.PrintOut(EmptyParam,EmptyParam,1, EmptyParam,EmptyParam,EmptyParam, EmptyParam,EmptyParam,LCID); Named ranges and macros. If the format of the report is more complex, A better approach is to name specific table ranges and then reference them by name. Names is a collection object property of WorkBook, which has an Add method that can do this job. 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 defined name, and the second parameter is the cell range represented by the name. It should be noted that the type of the range name must use a qualifier. If a type library (D4) is used, the qualifier is Excel_TLB. In addition, the named range should use absolute reference, that is, add the "$" symbol. Once you name a range, you can reference it using that name. The following line of code makes the address book contents appear in bold: AName.RefersToRange.Font.Bold:=True; But perhaps the most surprising thing is that you can Dynamically modify Excel macro programs in Delphi! The following code creates a macro for our workbook that records the last access time when closing the workbook: var LineNo: integer; CM: CodeModule; sDate:String; begin CM := WkBook.VBProject.VBComponents.Item( 'ThisWorkbook').Codemodule; LineNo := CM.CreateEventProc('BeforeClose', 'Workbook'); SDate:='Last access date:'+DateToStr(Date()); CM.InsertLines(LineNo + 1, ' Range("B2").Value = "'+sDate+''"'); End; Required to modify the macro Add a unit to the previous uses section: VBIDE2000. If a type library is used, the corresponding unit is VBIDE_TLB. The key to this code is the CodeModule object. Unfortunately, there is no trace of this object in the Excel VBA help document, so we can only search MSDN. Delphi4 and previous versions Delphi4 does not provide the TExcelApplication object, and a type library needs to be introduced to use OLE automation technology. The type library of Excel97 is Excel8.olb. The main difference between these two methods is the method of establishing a connection with the server program. The following is the program framework for controlling 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 to capture the running program instance if (Result = MK_E_UNAVAILABLE) then Excel := CoApplication.Create //Start a new program instance else begin {Check for errors during the GetActiveObject method call} OleCheck(Result); OleCheck (Unknown.QueryInterface(_Application, Excel)); end; …… //Perform data processing Excel.Visible[LCID] := True; // Excel.DisplayAlerts[LCID] := False; //Display prompt dialog box Excel.Quit; End; The usual try...except structure is not used here because the exception handling mechanism requires complex OLE checks, which reduces the cost The execution speed of the except part. It should be noted that the accompanying function CoApplication and some constant names generated by different Delphi versions may be different, so you should check the corresponding type library. Before calling the Quit method, be sure to release all workbook and worksheet variables created in the program, otherwise Excel may reside in memory and run (you can press Ctrl+Alt+Del to view). There is a small problem with calling GetActiveObject to capture the program instance. If Excel is in a minimized running state, only the main frame of the program may be displayed and the user area is not visible. In addition, if you do not want to introduce a type library, you can also use lagging binding, but it is much slower. 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 lagging binding, the compiler does not check the Excel object method called, but leaves these tasks to the server program to complete during execution. In this way, a large number of default parameters set by VBA (often more than a dozen) are used as they should. function, so this method has an unexpected benefit - the code is concise: 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 constants defined in the type library, just You can only do it yourself: const xlWBATWorksheet = -4167; …… XLApp.Workbooks.Add(xlWBatWorkSheet); Finally, don't forget to release the variables after closing Excel: Excel := Unassigned; The following is the source code used in the example of this article, passed under 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; Excel: 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; ; 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 Datas:Variant; i,j:Integer; begin ir:=StringGrid1.RowCount; ic:=StringGrid1.ColCount; Datas:=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:=Datas; end; // Excel.Visible[LCID]:=True; Datas:=Unassigned; end; procedure TForm1.Retrieve; var Datas:Variant; i,j:Integer; begin with wkSheet do begin Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate; ir:=Excel.ActiveCell.Row; ic: =Excel.ActiveCell.Column; Datas:=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; Datas:=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 cells HorizontalAlignment:= xlCenter; Size:=18; Name:='official script'; FontStyle:=Bold; end; wkSheet.Columns.EntireColumn.AutoFit; with Aname.RefersToRange,Borders do begin HorizontalAlignment:= xlRight; Item[xlEdgeBottom].Weight:=xlMedium; Item[xlEdgeTop].Weight:=xlMedium; Item[xlInsideHorizontal].Weight:=xlThin; item[xlInsideVertical].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:='Last access date:'+DateToStr(Date()); CM.InsertLines(LineNo + 1, ' Range("B2").Value = "'+sDate+'"'); end; procedure TForm1.Printit; begin with wkSheet.PageSetUp do begin PaperSize:=xlPaperA4; //Paper type A4 PrintTitleRows := '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. Delphi's close contact with Excel [Wang Anpeng ([email protected]) 2002/4/14] Delphi as an excellent RAD, powerful database Function is one of its most important features, but the difficult-to-operate QuickReport control often cannot meet the needs of database reports. If your report is very complex, or requires flexibility in format changes, then using Excel as the report server is a good choice. The Excel component provided by Delphi starting from version 5 greatly simplifies the application of OLE automation technology. However, the missing help files have always been the most criticized aspect of Delphi, and these new components are no exception. This article attempts to introduce this in more detail. The object model of Excel is a tree-like hierarchical structure. The root is the application itself. WorkBook is the attribute object of the root object. The WorkSheet used for data exchange mainly discussed in this article is the attribute object of the workbook. For details, please refer to MSOffice. Excel VBA Help file. To control Excel in Delphi, you must first establish a connection with the server program, open the workbook, then exchange data with the target worksheet, and finally disconnect. Open the Excel workbook Our example starts with a main form with a TStringGrid (of course some data needs to be filled in) and two buttons. Drag a TExcelApplication control from the Servers tab of the control panel and place it on the form. First, set ConnectKind to ckRunningOrNew, which means that if the running Excel instance can be detected, establish contact with it, otherwise start Excel. In addition, if you want the program to establish contact with the server program as soon as it is run, you can set the AutoConnect property to True. All it takes to establish contact with Excel is one statement: Excel . Connect; Maybe you have noticed that there are several other Excel controls on the Servers tab. These controls can be linked to the previous Excel through the ConnectTo method: ExcelWorkbook1.ConnectTo( Excel . ActiveWorkbook); ExcelWorksheet1.ConnectTo(Excel . ActiveSheet as _Worksheet); ExcelWorksheet2.ConnectTo(Excel . Worksheets.Item['Sheet2'] as _Worksheet); It should be noted that the corresponding workbook or worksheet must be opened before using the ConnectTo method. In addition, these controls will not bring additional convenience in most cases, so it is best to use only one TExcelApplication. Once contact is established with the Excel server, a new workbook can be created: var wkBook : _WorkBook; LCID : Integer; ... LCID := GetUserDefaultLCID(); wkBook := Excel.Workbooks.Add(EmptyParam, LCID); Add function The first parameter is used to define the template used for the new workbook. You can use xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet or xlWBATWorksheet constant, or it can be the name of an existing xls file. The EmptyParam here is the Variants unit and defined variable, which means using the default universal template to create a new workbook. If you open an existing xls document, you should pass the file name to be opened 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); You must know that all data operations are mainly for the active worksheet. The following statement uses a _WorkSheet variable to represent the current active cell. If you know the name of the worksheet, the index number can be replaced by the worksheet name: wkSheet:=wkBook.Sheets[1] as _WorkSheet; After completing the data exchange, you need to save the workbook: Excel.ActiveWorkBook.SaveAs ('MyOutput', EmptyParam ,EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, LCID); or: Excel.ActiveWorkBook.Save(LCID); Finally, close the workbook and disconnect from Excel: wkBook.Close(True, SaveAsName, EmptyParam, LCID); //Excel.Quit; Excel.Disconnect; The Close method here contains the save function. The first parameter indicates whether to save the modifications before closing the workbook. The second parameter gives the file name to be saved. The third parameter is used for multiple authors to process the document. situation. The second line asks to terminate Excel. Exchanging data with the worksheet The input data is performed on a certain cell or range of the active worksheet. Range and cells are both object properties of the worksheet. Cells is a collection of cells. If no specific location is specified, it can represent all cells in the entire worksheet. However, it is generally used to refer to a specific cell. For example, WS.Cells.Item[1,1] represents the most recent cell. Cell A1 in the upper left corner. Note that Item is the default property of Cells in VBA and can be omitted, but there is no such convenience in Delphi. To assign a value to a cell, you must refer to its Value property. It goes without saying that this property is a Variant variable, for example: wkSheet.Cells.Item[1, 1].Value := 'Address Book'; Of course, you can also assign a value to a cell. Specify the formula: var AFormula:String; …… AFormula:='=Rand()'; wkSheet.Range['F3','G6'].Value:=AFormula; The above method is very direct and simple, but it is very slow and not suitable for large reports. So can all the data be transferred to Excel in sequence? We can use Range. This object represents an area in the worksheet. As we drag out with the mouse, it is usually a rectangular area. Just give the positions of its upper left corner and lower right corner cells, such as Range[ 'C3','J42']. There is a small problem here, because if the data exceeds 26 columns (for example, there are 100 columns) or if the target area range needs to be determined on the fly, it is more troublesome to use character names to mark cells. Recall that since "C3" is the label of the cell, of course we can also use Cells, such as Range[Cells.Item[1,1], Cells.Item[100,100]]. It is conceivable that the value of Range should be an array, but you must not use Array in Delphi to assign a value to it! Remember that in Delphi, the value of an Excel object is always of type Variant. var Datas: Variant; Ir, ic: Integer; …… Datas:= varArrayCreate([1,ir,1,ic],varVariant); //Create a 100*100 dynamic array here… //Assign values ​​to the array elements here with wkSheet do Range[cells.Item[3,1],cells.Item[ir+2,ic]].Value:=Datas; It should be noted that both the worksheet and the Range have the Cells property. For clarity, the with statement is used here. In addition, Range is directional. A one-dimensional array created with VarArrayCreate can only be assigned to a single-row Range. If you want to define a value for a single-column Range, you must use a two-dimensional array, for example: Datas:=VarArrayCreate([1,100,1 ,1], varVariant); //Create a 100*1 dynamic array. By the way, Cells.Item[] actually returns a Range object. Retrieving data from the worksheet is basically the reverse process of writing data. What needs to be paid attention to is 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; The special cell function SpecialCells is cleverly used here to obtain the last cell containing data. Data Editing Below are two examples of data editing. var DestRange: OleVariant; begin DestRange := Excel.Range['C1', 'D4']; Excel.Range['A1', 'B4'].Copy(DestRange); The above example copies 8 cells content. If you pass an empty parameter to the Copy function, the data in this area is copied to the clipboard, and can be pasted to other locations using the Paste method later. var WS: _Worksheet; …… Excel.Range['A1', 'B4'].Copy(EmptyParam); //Copy data in a worksheet to the clipboard WS := Excel.Activesheet as _Worksheet; //Change activity Worksheet WS.Range['C1', 'D4'].Select; WS.Paste(EmptyParam, EmptyParam, lcid); //Paste the contents of the clipboard into a new worksheet. Format settings Choose Excel as the report server mainly because of its powerful formatting capabilities. We first merge the cells with the title "Address Book" and display it in the center, and then change the font to 18-point "official script" in bold: with wkSheet.Range['A1','D1'],Font do begin Merge(True ); //Merge cells HorizontalAlignment:= xlCenter; Size:=18; Name:='official script'; FontStyle:=Bold; end; If the cell content is long, part of the content will not be displayed. The usual approach is to double-click the right edge of the selected area so that the width of each column automatically adapts to the length of the content. In Delphi, adaptive column width and row height can also be achieved through the AutoFit method. It should be noted that this method can only be used for the entire row and entire column, otherwise an error of OLE method refusal to execute will be prompted: wkSheet.Columns.EntireColumn.AutoFit; Chinese style Reports usually require upper and lower capped table lines, and you can use the Borders collection property. It should be noted that collection objects in VBA usually have a default Item property, which cannot be omitted in Delphi. The Weight property is used to define the thickness of table lines: with Aname.RefersToRange,Borders do begin HorizontalAlignment:= xlRight; Item[xlEdgeBottom].Weight:=xlMedium; Item[xlEdgeTop].Weight:=xlMedium; Item[xlInsideHorizontal].Weight: =xlThin; item[xlInsideVertical].Weight:=xlThin; end; Page setup and print page setup are set through the PageSetUp object property of the worksheet. There are more than 40 paper constants preset in Excel VBA. It should be noted that some printers only support some of the paper types. The attribute Orientation is used to control the direction of printing, and the constant landscape = 2 indicates horizontal printing. The Boolean properties CenterHorizontally and CenterVertically are used to determine whether the printed content is centered horizontally and vertically. with wkSheet.PageSetUp do begin PaperSize:=xlPaperA4; //Paper type A4 PrintTitleRows := '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; //Horizontal printing (landscape)=2, portrait=1 end; To print a report, you can call the PrintOut method of the worksheet. This method defined by VBA has a total of 8 optional parameters, the first two It is used to specify the starting and ending pages, and the number of copies printed in the third format. However, in Delphi, an LCID parameter is added at the end, and EmptyParam cannot be used for this parameter. Similarly, the print preview method PrintPreview has no parameters in VBA, but requires two parameters when called in Delphi. // wkBook.PrintPreview(True,LCID); //for previewing wkSheet.PrintOut(EmptyParam,EmptyParam,1, EmptyParam,EmptyParam,EmptyParam, EmptyParam,EmptyParam,LCID); Named ranges and macros. If the format of the report is more complex, A better approach is to name specific table ranges and then reference them by name. Names is a collection object property of WorkBook, which has an Add method that can do this job. 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 defined name, and the second parameter is the cell range represented by the name. It should be noted that the type of the range name must use a qualifier. If a type library (D4) is used, the qualifier is Excel_TLB. In addition, the named range should use absolute reference, that is, add the "$" symbol. Once you name a range, you can reference it by that name. The following line of code makes the address book contents appear in bold: AName.RefersToRange.Font.Bold:=True; But perhaps the most surprising thing is that you can Dynamically modify Excel macro programs in Delphi! The following code creates a macro for our workbook that records the last access time when closing the workbook: var LineNo: integer; CM: CodeModule; sDate:String; begin CM := WkBook.VBProject.VBComponents.Item( 'ThisWorkbook').Codemodule; LineNo := CM.CreateEventProc('BeforeClose', 'Workbook'); SDate:='Last access date:'+DateToStr(Date()); CM.InsertLines(LineNo + 1, ' Range("B2").Value = "'+sDate+''"'); End; Required to modify the macro Add a unit to the previous uses section: VBIDE2000. If a type library is used, the corresponding unit is VBIDE_TLB. The key to this code is the CodeModule object. Unfortunately, there is no trace of this object in the Excel VBA help document, so we can only search MSDN. Delphi4 and previous versions Delphi4 does not provide the TExcelApplication object, and a type library needs to be introduced to use OLE automation technology. The type library of Excel97 is Excel8.olb. The main difference between these two methods is the method of establishing a connection with the server program. The following is the program framework for controlling 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 to capture the running program instance if (Result = MK_E_UNAVAILABLE) then Excel := CoApplication.Create //Start a new program instance else begin {Check for errors during the GetActiveObject method call} OleCheck(Result); OleCheck (Unknown.QueryInterface(_Application, Excel)); end; …… //Perform data processing Excel.Visible[LCID] := True; // Excel.DisplayAlerts[LCID] := False; //Display prompt dialog box Excel.Quit; End; The usual try...except structure is not used here because the exception handling mechanism requires complex OLE checks, which reduces the cost The execution speed of the except part. It should be noted that the accompanying function CoApplication and some constant names generated by different Delphi versions may be different, so you should check the corresponding type library. Before calling the Quit method, be sure to release all workbook and worksheet variables created in the program, otherwise Excel may reside in memory and run (you can press Ctrl+Alt+Del to view). There is still a small problem to call the GetActiveObject capture program instance. If EXCEL is in a minimized run, there may be only the case where the programs are the main framework and the user area is invisible. In addition, if you do not want to introduce the type library, you can also use the method of lagging binding, but the speed is much slower. The following example shows a variant variable to represent Excel applications: varXcel: variant; ... Try Excel: = getActiveoleObject ('Excel.application'); Except Excel: = CreateOLOBject ('Excel .Application '); end; Excel .Visible: = true; When using lagging binding, the compiler does not check the calling Excel object method, and hand over these tasks to the server program and complete it during execution, so that a large number of default parameters (more than a dozen) set by VBA will be played. 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; I can only do it by myself: const xlwbatworksheet = -4167; ... xlapp.Workbooks.add (xlwbatworksheet); Finally, do not forget to release the variable after turning off Excel: Excel: = Unassigned; the following is the source code used in this example, passed under Delphi6+MSoffice2000. Unit Unit1; Interface Uses Windows, MESSAGES, SYSUTILS, Variants, Classes, Graphics, Controls, Forms, Dialogs, OLESERVER, EXCEL2000, GRIDS, Stdctrlll s; type tform1 = class (tForm) Button1: TBUTTON; Stringgrid1: TSTRINGRID; Excel: Texcelapplication; Procedure formalTivate (Sender: TOBject); Procedure Button1clCK (Sender: TOBject); Private {Private Declarations} Procedure Write2xls; Procedure OpenExl; Procedure CLOSEEXL; Procedure nameSheet; Procedure formats; processure addmacro; procedure Retrieve; procedure propitit; public derications} End; VAR Form1: T. Form1 ; Implementation {$ R *.dfm} Uses vbide2000; var IR, IC: Integer; wksheet: _Worksheet; LCID: Integer; wkbook: _Workbook; Aname: Excel2000.Name; Procedure TFORMACTIVATE (Sender: Tobject); id1 do begin rows [0] .commatext: = 'Name, gender, age, telephone' ; ROWS [1] .Commatext: = 'Zhang San, Male, 25,010-33775566'; ROWS [2] .Commatext: Li Si, male, 47,012-6574906 '; Female, 18,061-7557381 '; ROWS [4] .Commatext: = 'Sun Tao, female, 31,3324559'; end; end; process; begin with excel do begin connect; ; wkBook: = Workbooks.add (Emptyparam, Lcid); wksheet: = wkbook.sheets [1] as _Worksheet; End; END; Procedure TFORM1.WRITE2XLS; VAR Datas: Variant; I, J: Integer; Begin IR: = Stringgrid1.Rowcount; IC: = Stringgrid1.Colcount; , 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: = 'Lange [Cells [Cells [Cells [Cells [Cells [Cells .Item [3,1], cells.item [IR+2, IC]]. Value: = datas; end; // excel.visible [lcid]: = true; Datas: = Unassigned; End; Procedure TFORIM1.Retrieve; VAR Datas: Variant; I, J: Integer; Begin with wksheet do bet Cell, emptyparam) .Activate; IR: = Excel.activeCell.Row; IC: = Excel.activecell.column; Datas: = Range [cells.item [1,1], cells.item [IR, IC]]. Value; with Stringgrid1 Do Begin Colcount: = IC; Rowcount: = IR; I: = 0 to IR-1 for J: = 0 to IC-1 do cells [j, i]: = datas [i+1, j+1]; Datas: = unassigned; end; end; procedure tForm1.closexl; const saveasname = 'test.xls'; , 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, empt YPARAM, EMPTYPARAM, EMPTYPARAM); End; Procedure TFORM1 .Addformula; Var AFormula: String; Begin AFormula: = '= Rand ()'; wksheet.range ['f3', 'g6']. Value: = AFormula; End; Procedure TFORMATS; D1 '], FONT do Begin Merge (TRUE); // Horizontalalicnment: = xlcenter; size: = 18; name: = 'Lishu'; FontStyle: = Bold; End; wksheet.columns.entirecolumn.autOfit; with Aname.refestorange, borders do begin horizontalally: = xlright; Gebottom] .weight: = xlmedium; item [xledgetop] .weight: = xlmedium; Item [XLINSIDEHORIZTAL] .weight: = xlthin; item [xlinsideVertical] .weight: = xlthin; end; proced; Ger; cm: codemodule; sdate: string; begin cm: = wkBook.vbproject. Vbcomponents.item ('Thisworkbook'). CodeModule; Lineno : = Cm.createEventProc ('BeForeClose', 'Workbook'); SDATE: = 'Last visit date:' + datetostr (date ())); = "'+sdate+'" '); End; Procedure TFORM1.printit; Begin with WKSheet.pageSetup Do Begin Papersize: = xlpapera4; // Paper Type A4 Printtitlerows: = '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 (Landscape) = 2, Portrait = 1 End; WKSHEET.PRINTOUT (EMPTYPARAM, EMPTYPARAM, EMPTYPARAM, EMPTYPARAM, EMPTYP ARAM, Emptyparam, LCID); End; Procedure TFORM1.Button1Click ( Sender: TOBject); Begin Try OpenExl; Write2xls; addformula; NameSheet; Formats; Printit; addmacro; Retrieve; Finally Closeexl; End; End.