Four kinds of Excel Access technology implemented in Delphi

first, the introduction of Excel in the processing of Chinese reports is very powerful, Excel report access is also an important aspect of information system development, this article summarizes the previous development of the use of several Excel file access methods, in the actual work has also been very good validation, This article lists four examples of these methods to share with the reader, the program has been debugged in WINDOWS2000 operating system, OFFFICE2000 application software and DELPHI7 environment. Second, ADO access to Excel file ADO way to access Excel files, the Excel file as an ODBC data source equivalent to a database such as Oracle, MS SQL Server This article applies the example main function is to open the Excel file, The editing and modifying function of Excel file is implemented. The implementation process and the main source code are as follows:1. Create a new window class Tfrmadoexcel in the project, define a component conn with a private variable type of tadoconnection in the window, add tadotable component Adotabxls, Tdatasource component Dsxls, Tdbnavigator Components Navxls, Tdbgrid components Gridxls and TButton components Btnopen, use Btnopen to open Excel files and use Navxls to browse and edit Excel file data. 2． Writes the onclick event for the Btnopen component. Note that for two points, the extend properties property of the Conn component is defined as Excel8.0, in addition, the table in Excel file sole name "Personnel information table" As indicated when "[personnel information sheet $]". procedureTfrmadoexcel.btnopenclick (sender:tobject);//Open the Excel file codebeginConn:=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 Information Sheet'+'$]'; Adotabxls.active:=True;dsxls. DataSet:=Adotabxls; Gridxls.datasource:=Dsxls;except; Freeandnil (Conn);End;EndThird, the COM way to dynamically access Excel files when the COM method dynamically accesses Excel files, the basic method is to use component multiplexing technology to invoke the COM service components provided by the Office software platform, taking advantage of the methods provided by COM components to manipulate Excel files. The application example in this article is a simple demonstration of how COM technology can be used to output data from a dataset dataset to an Excel file. The implementation process and the main source code are as follows: Create a new window class Tfrmcomexcel in the project, define a component conn with a private variable type of tadoconnection in the window, add tadotable component Adotabxls and TButton component Btnopen, You can use Btnopen to output data to an Excel file. The onclick event code for writing the Btnopen component is as follows:procedureTfrmcomexcel.btnopenclick (sender:tobject);varxl:variant;//to open a Variant variable for an Excel filesheet:variant;//A Variant variable that points to an Excel formRecno,i:integer;//record the current record number of the data tablebeginTryXL:= Createoleobject ('Excel.Application'); Xl. Visible:=true;ifFileExists (Extractfiledir (application.exename) +'/test.xls') ThenbeginXL. Workbooks.Open (Extractfiledir (application.exename)+'/test.xls');EndElseXL. 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  notAdotabxls.eof Dobegin forI: =0  toAdotabxls.fieldcount-1  Doif  not(Adotabxls.fields[i]. DataTypeinch[Ftblob, Ftgraphic,ftparadoxole, Ftdbaseole, Fttypedbinary,ftreference, Ftdataset, Ftorablob, FtOraClob, Ftinterface,ftidispatch]) ThenbeginSheet.Cells.NumberFormat:='@'; Sheet.cells[recno, I+1] :=Adotabxls.fields[i]. asstring;End; Inc (RecNo); Adotabxls.next;End;TryXL. Workbooks[xl. Workbooks.count]. SaveAs (Extractfiledir (application.exename)+'/test.xls');except ;End;End; Iv. extended Olecontainer way access to Excel files when you access an Excel file by using OLE, the OLE container loses focus to mask the Excel file operation that is being accessed, and when you activate the OLE container by using In_place mode, will open a window, program execution seems a bit confusing, the main cause of these problems is because the OLE container responds to the Cm_uideactivate message, the OLE container cannot always remain active. This message result can be used for this purpose so that the OLE container is always active. The workaround is to overload the OLE container's Cm_uideactivate message. After you temporarily create an overload in your program, extend the OLE container Olecontainerex. The code is as follows:typeTolecontainerex=class(Tolecontainer)PrivateFjh:boolean;//overriding the Cm_uideactivate message responseprocedureCmuideactivate (varMessage:tmessage);messagecm_uideactivate;published PropertyJh:booleanReadFjhWriteFjh;End;//code Implementation of process CmuideactivateprocedureTolecontainerex.cmuideactivate (varmessage:tmessage);beginif  notJh Theninherited;End; When using Tolecontainerex, it can be created in a temporary manner or further encapsulated into an installable build for use in the design period. The example used in this article takes the form of temporary creation. Create a new window class Tfrmexoleexcel in the project and define the public variable Olecon, type: Tolecontainerex; set a Tpanel type component Panel1 and TButton type variable btnopen in the window, Write the Btnopen Click event, the main source code is as follows:procedureTfrmexoleexcel.btnopenclick (sender:tobject);begin//Creating and displaying extended OLE class componentsOlecon:= 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). JH:=True;olecon.setfocus;End; Delphi Standard components Access to Excel files in Delphi encapsulates a set of Microsoft Office Automation objects (Automation servers). It makes it easy for us to take control of applications in Office (Excel, etc.) as a COM application server. Use this set of VCL components to set property settings at design time or to dynamically access excel at run time. Use the methods provided by the VCL component to manipulate Excel files when accessing Excel files dynamically using standard components. The application example in this article demonstrates how to use the VCL component to output data from a dataset dataset to an Excel file. The implementation process and the main source code are as follows: Create a new window class Tfrmstdcntrexcel in the project, add the Tadoconnection component to the window Adoconnxls,tadotable component Adotabxls, TButton component Btnopen, Texcelapplication components Excelapplication1,texcelworkbook class Components ExcelWorkbook1 and TExcelWorksheet1 class components ExcelWorksheet1, You can use Btnopen to output data to an Excel file. The OnClick event code that writes the Btnopen component is as follows. procedureTfrmstdcntrexcel.btnopenclick (sender:tobject);varaworksheet: _worksheet;tmpi,arowindex:integer;astr:string;beginadoconnxls.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 information table $]'; Adotabxls.active:=true;ifAdotabxls.isempty Thenexit; Tryexcelapplication1.connect; Exceptmessagedlg ('you have not installed Microsoft Excel Yet, please install Microsoft excel! First ', Mterror, [Mbok],0); Abort; End; excelapplication1.visible[0]:=True; Excelapplication1.caption:='Create a new Excel file'; Excelworkbook1.connectto (ExcelApplication1.Workbooks.Add (Emptyparam,0) ); Aworksheet:=excelworkbook1.worksheets.add (Emptyparam,emptyparam,emptyparam,emptyparam,0) as_worksheet; Excelworksheet1.connectto (Aworksheet); Arowindex:=1; while  notAdotabxls.eof Dobegin fortmpi:=0  toadotabxls.fieldcount-1  DoExcelworksheet1.cells.item[arowindex,tmpi+1]:=Adotabxls.fieldlist[tmpi]. Asstring;arowindex:=arowindex+1; Adotabxls.next;End;//Save the Excel file and close the Excel applicationTryastr:=extractfiledir (Application.exename) +'/excel file. xls'; Excelworkbook1.saveas (Astr,emptyparam,emptyparam, Emptyparam,emptyparam,emptyparam, XlNoChange, EmptyParam, Emptyparam,emptyparam,emptyparam,emptyparam,0); Excelworksheet1.disconnect; Excelworkbook1.disconnect; Excelapplication1.disconnect; Adotabxls.active:=false; Adoconnxls.connected:=false;exceptExcelworksheet1.disconnect; Excelworkbook1.disconnect; Excelapplication1.disconnect; Adotabxls.active:=false; Adoconnxls.connected:=false; End;EndSix, summing up, this paper discusses several methods of Excel file access, including ADO, COM, extended Olecontainer and Delphi standard component mode respectively. In the practical application process, these methods also have each characteristic. ADO access to Excel files is useful for maintaining Excel files in database access, COM's dynamic access programming design is similar to the VBA provider approach, which is suitable for Excel file data maintenance and complex report output. Extended OLE way to access Excel files can maintain the friendly interface of Excel application, Delphi standard components access way and COM Access way, but Delphi to a variety of interfaces are more friendly encapsulation, in the case of COM is not very long familiar with the situation, Using this method to access and output data to Excel files is very effective. 

Four kinds of Excel Access technology implemented in Delphi