Four Excel Access Technologies in Delphi

Four Excel Access Technologies in Delphi

Publisher: Qin fangyu Release Date: 21:26:00Content summary

Excel is a very important tool for making Chinese reports,ArticleThis paper introduces and analyzes four types of access to excel by Delphi, including ADO access to excel files, dynamic access to excel files by COM, extended olecontainer access to excel files, and access to excel files by Delphi standard components. file method and implementationCode.

Body

Text Size: medium or small

Four Excel Access Technologies in Delphi

Abstract Excel is an important tool for making Chinese reports, this article introduces and analyzes four access methods of Delphi, including ADO access to excel files, com access to excel files dynamically, olecontainer access to excel files, and Delphi Standard Component Access to excel files. excel file method and implementation code.

Key words: Excel files, ADO, com

I. Introduction

Excel is very powerful in processing Chinese reports, and access to excel reports is also an important part of information system development. This article summarizes the Excel file access methods used in previous development, it has also been well verified in actual work. This article lists examples of the four methods to share with readers,ProgramIt has been debugged in the Windows2000 operating system, offfice2000 application software, and Delphi7 environment.

Ii. Accessing Excel files through ado

When you access an Excel file through ado, you can view the Excel file as an ODBC Data Source equivalent to Oracle, ms SQL Server, and other databases. The main function of this example is to open an Excel file, edit and modify Excel files. Implementation process and mainSource codeAs follows:

1. create a new window class tfrmadoexcel in the project, and define the private variable type as the conn component of tadoconnection in the window, add the tadotable component adotabxls, tdatasource component dsxls, tdbnavigator component navxls, TDBGrid component gridxls, and tbutton component btnopen. Use btnopen to open an Excel file and use navxls to browse and edit Excel file data.

2. Compile the onclick event of the btnopen component. Note that the extend properties attribute of the conn component must be defined as Excel 8.0. In addition, the form name "personnel info table" in the Excel file must be written as "[personnel info table $]".

Procedure tfrmadoexcel. btnopenclick (Sender: tobject );

// Open the Excel file code

Begin

Conn: = tadoconnection. Create (NiL );

Conn. connectionstring: = 'provider = Microsoft. jet. oledb.4.0; Data Source = '+ extractfiledir (application. exename) + '\ persondata.xls; extended properties = Excel 8.0; persist Security info = false ';

Conn. loginprompt: = false;

Conn. Connected: = true;

Adotabxls. Connection: = conn;

Adotabxls. tablename: = '[' + 'personnel info table '+' $] ';

Adotabxls. Active: = true;

Dsxls. Dataset: = adotabxls;

Gridxls. datasource: = dsxls;

Except;

Freeandnil (conn );

End;

End;

3. Dynamic Access to excel files through COM

When using COM to dynamically access Excel files, the basic method is to use Component Reuse Technology to call the com service components provided by the office software platform, and make full use of the methods provided by the COM component to manipulate Excel files. This example demonstrates how to use COM technology to output data from a dataset to an Excel file. The implementation process and main source code are as follows:

Create a new window class tfrmcomexcel in the project, define the private variable type as tadoconnection component Conn in the window, add the tadotable component adotabxls and tbutton component btnopen, and use btnopen to output data to the Excel file. The onclick Event code of the btnopen component is as follows:

Procedure tfrmcomexcel. btnopenclick (Sender: tobject );

VaR

XL: variant; // open the variant variable in the Excel file

Sheet: variant; // The variant variable pointing to the Excel form.

Recno, I: integer; // record the current record number of the data table

Begin

Try

Conn: = tadoconnection. Create (NiL );

Conn. connectionstring: = 'provider = Microsoft. jet. oledb.4.0; Data Source = '+ extractfiledir (application. exename) + '\ persondata.xls; extended properties = Excel 8.0; persist Security info = false ';

Conn. loginprompt: = false;

Conn. Connected: = true;

Adotabxls. Connection: = conn;

Adotabxls. tablename: = '[personnel info table $]';

Adotabxls. Active: = true;

Except

Freeandnil (conn );

End;

XL: = createoleobject ('excel. application ');

XL. Visible: = true;

If fileexists (extractfiledir (application. exename) + '\ test.xls') then

Begin

XL. workbooks. Open (extractfiledir (application. exename) + '\ test.xls ');

End

Else XL. workbooks. Add;

XL. workbooks [XL. workbooks. Count]. worksheets [1]. Name: = 'test ';

Sheet: = XL. workbooks [XL. workbooks. Count]. worksheets [trim ('test')];

Recno: = 1;

Adotabxls. first;

While not adotabxls. EOF do

Begin

For I: = 0 to adotabxls. fieldcount-1 do

If not (adotabxls. Fields [I]. datatype in [ftblob, ftgraphic,

Ftparadoxole, ftdbaseole, fttypedbinary,

Ftreference, ftdataset, ftorablob, ft1_lob, ftinterface,

Ftidispatch]) then

Begin

Sheet. cells. numberformat: = '@';

Sheet. cells [recno, I + 1]: = adotabxls. Fields [I]. asstring;

End;

INC (recno );

Adotabxls. Next;

End;

Try

XL. workbooks [XL. workbooks. Count]. saveas (extractfiledir (application. exename) + '\ test.xls ');

Except;

End;

End;

Iv. Extended olecontainer access to excel files

When using OLE to access an Excel file, the OLE container will block the Excel file operations being accessed when the focus is lost. In addition, when activating the OLE container using in_place, an additional window will be opened, program execution is a bit messy. The main cause of these problems is that the OLE container responds to the cm_uideactivate message, and the OLE container cannot always be activated. To this end, you can keep the OLE container in the active state. The solution is to overload the cm_uideactivate message of the OLE container. Create a temporary overload in the program and then expand the OLE container olecontainerex. The Code is as follows:

Type

Tolecontainerex = Class (tolecontainer)

Private

Fenders: Boolean;

// Rewrite cm_uideactivate message response

Procedure cmuideactivate (VAR message: tmessage); message cm_uideactivate;

Published

Property permission: Boolean read fjh write fjh;

End;

// Process cmuideactivate code implementation

Procedure tolecontainerex. cmuideactivate (VAR message: tmessage );

Begin

If not then

Inherited;

End;

When tolecontainerex is used, it can be created on a temporary basis, or further encapsulated as installable components for use during the design period. The application example in this article adopts the temporary creation method. Create a new window class tfrmexoleexcel in the project and define the public variable olecon. The type is tolecontainerex. In the window, set a tpanel component Panel1 and tbutton type variables btnopen to compile the click event of btnopen, the main source code is as follows:

Procedure tfrmoleexcel. btnopenclick (Sender: tobject );

Begin

// Create and display extended Ole Class components

Olecon: = tolecontainerex. Create (NiL );

Olecon. Parent: = Panel1;

Olecon. Align: = alclient;

Olecon. allowactivedoc: = true;

Olecon. allowinplace: = true;

Olecon. autoactivate: = aagetfocus;

Olecon. anchors: = [aktop, akleft, akright, akbottom];

Olecon. Visible: = true;

Olecon. sizemode: = smclip;

Olecon. createobjectfromfile (extractfiledir (application. exename) +

'\ Persondata.xls', false );

Tolecontainerex (olecon). Labels: = true;

Olecon. setfocus;

End;

V. Access Excel files using standard delphi components

Delphi encapsulates a set of Microsoft Office automation objects (automation servers ). It makes it easy for us to control office applications (such as Excel) as a com application server. This set of VCL components can be used to set attributes during design, or to dynamically access excel at runtime. When using standard components to dynamically access an Excel file, make full use of the methods provided by the VCL component to manipulate the Excel file. This example demonstrates how to use the VCL component to output data from a dataset to an Excel file. The implementation process and main source code are as follows:

Create a new window class supervisor in the project, and add the tadoconnection component adoconnxls, tadotable component adotabxls, tbutton component btnopen, texcelapplication component excelapplication1, texcelworkbook component excelworkbook1, and connector components in the window, you can use btnopen to output data to an Excel file. The onclick Event code of the btnopen component is as follows.

Procedure tfrmstdcntrexcel. btnopenclick (Sender: tobject );

VaR

Aworksheet: _ worksheet;

Tmpi, arowindex: integer;

Astr: string;

Begin

Adoconnxls. connectionstring: = 'provider = Microsoft. jet. oledb.4.0; Data Source = '+ extractfiledir (application. exename) + '\ persondata.xls; extended properties = Excel 8.0; persist Security info = false ';

Adoconnxls. loginprompt: = false;

Adoconnxls. Connected: = true;

Adotabxls. Connection: = adoconnxls;

Adotabxls. tablename: = '[personnel info table $]';

Adotabxls. Active: = true;

If adotabxls. isempty then exit;

Try

Excelapplication1.connect;

Except

Messagedlg ('you have not installed Microsoft Excel. Please install Microsoft Excel first! ', Mterror, [mbok], 0 );

Abort;

End;

Excelapplication1.visible [0]: = true;

Excelapplication1.caption: = 'create an Excel file ';

Excelworkbook1.connectto (excelapplication1.workbooks. Add (emptyparam, 0 ));

Aworksheet: = excelworkbook1.worksheets. Add (emptyparam, emptyparam, 0) AS _ worksheet;

Excelworksheet1.connectto (aworksheet );

Arowindex: = 1;

While not adotabxls. EOF do

Begin

For tmpi: = 0 to adotabxls. FieldCount-1 do

Excelworksheet1.cells. item [arowindex, tmpi + 1]: = adotabxls. fieldlist [tmpi]. asstring;

Arowindex: = arowindex + 1;

Adotabxls. Next;

End;

// Save the Excel file and close the Excel application

Try

Astr: = extractfiledir (application. exename) + '\ excelfile .xls ';

Excelworkbook1.saveas (astr, emptyparam,

Xlnochange, emptyparam, 0 );

Excelworksheet1.disconnect;

Excelworkbook1.disconnect;

Excelapplication1.disconnect;

Adotabxls. Active: = false;

Adoconnxls. Connected: = false;

Except

Excelworksheet1.disconnect;

Excelworkbook1.disconnect;

Excelapplication1.disconnect;

Adotabxls. Active: = false;

Adoconnxls. Connected: = false;

End;

End;

Vi. Summary

In summary, this article gives an example of several methods for accessing Excel files, including ado, COM, extended olecontainer, and Delphi standard component methods. In practical application, these methods also have their own characteristics. Using ADO to access Excel files is suitable for maintaining Excel files through database access. Using COM to dynamically access programming is similar to using VBA to access interfaces. This method is applicable to excel file data maintenance and complex report output, when using the extended Ole method to access Excel files, the Excel application's friendly interface can be maintained. The Delphi standard component access method is similar to the com access method, But Delphi provides more friendly encapsulation of various interfaces, if you are not very familiar with the com method, it is very effective to access and output data to an Excel file. Based on my work experience, the author simply summarizes the access to several Excel files, and will further study and summarize in future work.