Office Automation
The ability to interact with office software and generate spreadsheets, text documents and presentations from code can be invaluable in the office, and win a lot of time for those that can do it. One example of this is the creation of applications that can read files in an arbitrary format and output an Excel file, a task much more efficient to be done with code then manually.
Using the Open Office UNO Bridge
please write me.
Using COM Automation to interact with OpenOffice and Microsoft Office
Automation is unique to Windows so the following two examples won't work on OS X or Linux. For those platforms, please refer to Making do without Windows COM Automation.
Here's a simple example of how to open a document with your program using the OpenOffice Automation server. Note that this works only on Windows.
program TestOO; {$IFDEF FPC} {$MODE Delphi} {$ELSE} {$APPTYPE CONSOLE} {$ENDIF} uses SysUtils, Variants, ComObj; const ServerName = 'com.sun.star.ServiceManager'; var Server : Variant; Desktop : Variant; LoadParams : Variant; Document : Variant; TextCursor : Variant; begin if Assigned(InitProc) then TProcedure(InitProc); try Server := CreateOleObject(ServerName); except WriteLn('Unable to start OO.'); Exit; end; Desktop := Server.CreateInstance('com.sun.star.frame.Desktop'); LoadParams := VarArrayCreate([0, -1], varVariant); {Create new document} Document := Desktop.LoadComponentFromURL('private:factory/swriter', '_blank', 0, LoadParams); TextCursor := Document.Text.CreateTextCursor; {Insert existing document} //Substitute your path and doc TextCursor.InsertDocumentFromURL('file:///C|/my/path/mydoc.doc', LoadParams); end.
Here's a simple example of how to open a document with your program using the Word Automation server. Note that this works only on Windows and currently can't be compiled with Free Pascal 2.2, only Delphi. Please check back later or test with a future version of FPC.
program TestMsOffice; {$IFDEF FPC} {$MODE Delphi} {$ELSE} {$APPTYPE CONSOLE} {$ENDIF} uses SysUtils, Variants, ComObj; const ServerName = 'Word.Application'; var Server : Variant; begin if Assigned(InitProc) then TProcedure(InitProc); try Server := CreateOleObject(ServerName); except WriteLn('Unable to start Word.'); Exit; end; {Open existing document} //Substitute your path and doc Server.Documents.Open('c:\my\path\mydoc.doc'); Server.Visible := True; {Make Word visible} end.
Generating Excel 2 files
Because of it's very simple format, it's easy to generate Excel 2.1 files from code. New Excel versions are still capable of reading those files, so this can be enough to automate the generation of simple Excel files. Unfortunately Excel 4 and older formats don't support multiple sheets in the same file, so this isn't supported by this method.
Here is a unit which can write Excel 2.1 files:
{ excel2utils.pas Writes an Excel 2.1 file To write the file, always start calling StartExcel2File to create the Excel file header, then call WriteExcel2Cell once for each cell value and finally call EndExcel2File to write the end of the file. Public Domain AUTHORS: Felipe Monteiro de Carvalho } unit excel2utils; {$ifdef fpc} {$mode delphi}{$H+} {$endif} interface uses Classes, SysUtils; const STR_EXCEL_EXTENSION = '.xls'; procedure WriteExcel2Cell(AStream: TStream; const ACol, ARow: Word; const AValue: string); procedure StartExcel2File(AStream: TStream); procedure EndExcel2File(AStream: TStream); implementation procedure WriteExcel2Cell(AStream: TStream; const ACol, ARow: Word; const AValue: string); var L: Word; CXlsLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0); begin L := Length(AValue); CXlsLabel[1] := 8 + L; CXlsLabel[2] := ARow; CXlsLabel[3] := ACol; CXlsLabel[5] := L; AStream.WriteBuffer(CXlsLabel, SizeOf(CXlsLabel)); AStream.WriteBuffer(Pointer(AValue)^, L); end; procedure StartExcel2File(AStream: TStream); var CXlsBof: array[0..5] of Word = ($809, 8, 0, $10, 0, 0); begin AStream.WriteBuffer(CXlsBof, SizeOf(CXlsBof)); end; procedure EndExcel2File(AStream: TStream); var CXlsEof: array[0..1] of Word = ($0A, 00); begin AStream.WriteBuffer(CXlsEof, SizeOf(CXlsEof)); end; end.
And here is an example of how to use it:
{ excel2demo.dpr Demonstrates how to write an Excel 2.1 file using the excel2utils unit Public Domain AUTHORS: Felipe Monteiro de Carvalho } program excel2demo; {$mode delphi}{$H+} uses Classes, SysUtils; var OutputFile: TFileStream; MyDir: string; begin // Open the output file MyDir := ExtractFilePath(ParamStr(0)); OutputFile := TFileStream.Create(MyDir + 'test' + STR_EXCEL_EXTENSION, fmCreate or fmOpenWrite); StartExcel2File(OutputFile); // Write some cells WriteExcel2Cell(OutputFile, 0, 0, '1'); WriteExcel2Cell(OutputFile, 1, 0, '2'); WriteExcel2Cell(OutputFile, 2, 0, '3'); WriteExcel2Cell(OutputFile, 3, 0, '4'); // Close the files EndExcel2File(OutputFile); OutputFile.Free; end.
Writing an Excel file using ADO
please write me.
External links
- Excel file format description on the OpenOffice website