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