Office Automation/fr

│ Deutsch (de) │ English (en) │ español (es) │ français (fr) │ italiano (it) │ русский (ru) │ 中文（中国大陆）‎ (zh_CN) │

La possibilité d'interagir avec les programmes office et générer des feuilles de calcul, des documents texte et des présentations depuis le code peut être de grande valeur au bureau, et permet de gagner beaucoup de temps pour ceux qui peuvent le faire. Un exemple est la création d'applications qui peuvent lire et écrire des fichiers dans un format arbitraire et générer un fichier Excel, une tâche qu'il est bien plus efficace de faire par code que manuellement.

Utilisation de OpenOffice UNO Bridge

OpenOffice a des bindings de langage pour C++, Java, JavaScript et Python. Sur Windows, OpenOffice peut aussi être manipulé en Pascal via COM Automation (voir ci-dessous), mais il n'y a actuellement aucune façon simple d'utiliser OpenOffice UNO (Universal Network Objects) depuis Pascal sur OS X et Linux. Si vous êtes interessé par le développement d'un "bridge" OO pour Pascal, veuillez vous référer au liens suivants pour plus d'information (attention: ces liens sont relativement techniques à la mode de Sun):

api.openoffice.org

About Bridges

Voir aussi le sujet ci-dessous à propos de Python.

Utilisation de COM Automation pour interagir avec OpenOffice et Microsoft Office

Automation est uniquement valable sous Windows donc les deux exemples suivants ne fonctionneront pas sous OS X ou Linux. Pour ces plateformes, veuillez vous référer à Making do without Windows COM Automation. Si vous avez seulement besoin de créer et/ou visualiser un document de traitement de text depuis votre programme, veuillez jeter un oeil à XDev Toolkit.

OpenOffice sur Windows

Voici un exemple simple sur comment ouvrir un document avec votre programme en utilisant le serveur Automation d'OpenOffice. Notez que ça ne marche que sur 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);

   {Crée un nouveeau document}
  Document := Desktop.LoadComponentFromURL('private:factory/swriter',
                                           '_blank', 0, LoadParams);

  TextCursor := Document.Text.CreateTextCursor;

   {Insère un nouveau document}  //Remplacer avc vos valeur de nom de fichier
  TextCursor.InsertDocumentFromURL('file:///C|/my/path/mydoc.doc',  
                                   LoadParams);
end.

Office sur Windows

Voici un exemple simple de comment ouvrir un document avec votre programme en utilisant le serveur d'automation de Word. Notez que ce serveur ne fonctionne que sous Windows. Ceci marchera avec Delphi et FPC.

program TestMsOffice;

{$IFDEF FPC}
 {$MODE Delphi}
{$ELSE}
 {$APPTYPE CONSOLE}
{$ENDIF} 

uses
  SysUtils, Variants, ComObj;

const
  ServerName = 'Word.Application';
var
  Server     : Variant;
  w:widestring;
begin
  if Assigned(InitProc) then
    TProcedure(InitProc);

  try
    Server := CreateOleObject(ServerName);
  except
    WriteLn('Unable to start Word.');
    Exit;
  end;

   {ouvre un document existant}  //Substituez votre votre chemin et nom de fichier
  w:= UTF8Decode('c:\my\path\mydoc.doc');
  Server.Documents.Open(w); //OLE utilises BSTR (http://msdn.microsoft.com/en-us/library/windows/desktop/ms221069(v=vs.85).aspx). Seule la widestring est compatible avec BSTR dans FPC, donc la conversion est nécessaire pour les caractères non latins.
  Server.Visible := True;  {Make Word visible}

end.

Voici un échantillon de code de comment travailler dans un document Word ouvert, en utilisant le serveur d'automation de Word.

var
  Server: Variant;
begin
  try
    Server := GetActiveOleObject('Word.Application');
  except
    try
      ShowMessage('Word pas encore ouvert, crée un objet');
      // Si aucune instance de Word ne tourne, essayer de créer un nouvel objet Word
      Server := CreateOleObject('Word.Application');
    except
      ShowMessage('Ne peut pas lancer Word/Word pas installé ?');
      Exit;
    end;
  end;
end;

Limitations: Parce que End est unmot réservé de FPC, il doit être utiliser comme un paramètre après le signe &.

Server.ActiveDocument.Application.Selection.start := Server.ActiveDocument.Application.Selection.&end+1;

De nombreux exemples pour Excel sont disponibles dans la page Wiki allemande.

Attempting to use Python to manipulate OpenOffice

Since OpenOffice includes support for Python, it would seem possible to run Python scripts from Pascal to manipulate OO, in lieu of actual Pascal language bindings. Here are the steps for one possible approach to doing this:

1. Test UNO via Python macro run within OO
2. Test UNO via Python standalone script
3. Support for running Python scripts in Pascal
4. Test UNO via Python script run in Pascal
5. Pascal class that wraps Python UNO

Note: The following scripts were tested with OpenOffice 2.3.1 on Windows XP and NeoOffice 2.2.5 Patch 6 on Mac OS X 10.4.11 (PowerPC).

Step 1. Test UNO via Python macro run within OO

OO has tools for creating JavaScript macros, but not Python macros, so use a text editor to save the following script to file test_macro.py and place it in OO's user macro folder. On Windows, this folder is:

C:\Document and Setting\<username>\Application Data\OpenOffice.org2\user\Scripts\python\Library1

On Mac OS X, this folder is:

~/Library/Preferences/NeoOffice-2.2/user/Scripts/python/Library1

On both platforms, you'll need to create the python/Library1 folder.

Here is the code for test_macro.py, adapted from the OO Pascal example above:

# Python macro that tests UNO by creating new document and inserting some text.

import uno

def TestNewDoc():
  ctx = uno.getComponentContext()
  smgr = ctx.ServiceManager
  desktop = smgr.createInstance('com.sun.star.frame.Desktop')
  doc = desktop.loadComponentFromURL('private:factory/swriter', '_blank', 0, ())
  textCursor = doc.Text.createTextCursor()
  doc.Text.insertString(textCursor, 'Hello World', 0)

In OO, choose Tools | Macros | Organize Macros | Python and run the macro to make sure it works.

Step 2. Test UNO via Python standalone script

Here is the same code as a standalone script:

# Python script that tests UNO by creating new document and inserting some text.

import sys

if sys.platform == 'darwin':
  sys.path.append('/Applications/NeoOffice.app/Contents/MacOS')

import officehelper

ctx = officehelper.bootstrap()
smgr = ctx.ServiceManager
desktop = smgr.createInstance('com.sun.star.frame.Desktop')
doc = desktop.loadComponentFromURL('private:factory/swriter', '_blank', 0, ())
textCursor = doc.Text.createTextCursor()
doc.Text.insertString(textCursor, 'Hello World', 0)

Save this to file test.py and run it like this on Windows from a command line. Note: On Windows and Linux, use the version of Python included with OO; on Mac OS X, use the system's Python 2.3.

"\program files\openoffice.org 2.3\program\python" test.py

On Mac OS X, run the script like this from a Terminal window:

#!/bin/sh
export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH":/Applications/NeoOffice.app/Contents/MacOS"
python2.3 test.py

Unfortunately, this script doesn't work either on Windows or Mac OS X. On Windows, it displays an error dialog with no text (!) and then outputs an error message to the console that says "Cannot connect to soffice server". On Mac OS X, it starts NeoOffice and creates the new document, then NeoOffice shuts down abruptly.

UNO Python To-Do

Obviously additional investigation is needed before we can proceed to step 3. You are welcome to work on this. Here are a couple things to try:

• Test on Linux
• Test on more recent versions of OpenOffice

• Jan. 5, 2009: Results of testing OpenOffice 3 on Windows:
  • OO 3.0.0 support for user Python macros is broken (Step 1); fixed with OO 3.0.1 RC1.
  • Step 2 no longer displays the empty error dialog and the console exception message is different, but still doesn't start OO.
  • Note that paths to various parts of OO and to user macros have changed with OO 3.

---

Using the fpXMLXSDExport unit

FPC 2.6 and newer contain the fpXMLXSDExport unit, part of the FCL-DB export components. With that, you can export datasets to various XML formats, including a Microsoft Access-compatible format and a Microsoft Excel-compatible format.

The Access format can output XML with or without an embedded XSD data/table definition. Note that exporting binary/BLOB type data needs additional action at the Access import end, as Access does not support proper binary fields, only OLE fields.

In the Excel format, multiline text fields are not supported at the moment: the line ends are removed during the export.

Lazarus provides a visual component for this: after installing the lazdbexport package, you will see the TXMLXSDExporter component on the Data Export tab

See fpXMLXSDExport for details.

Using the Free Pascal Spreadsheet Library

Another way to automate repetitive work with spreadsheets is to use the FPSpreadsheet library. It can read and write spreadsheets in several formats and it doesn't require having any external application installed on the machine.

The advantages are that fpspreadsheet is 100% ObjectPascal code, and it requires no external libraries or programs.

Writing an Excel file using ADO

please write me.

Reading/Writing an Excel file using OLE

This method needs Excel to be installed on the user's machine because it uses OLE to access it.

Keep in mind that this method starts Excel in the background, which opens the file and works with it like a real user.

• Create a new form with button, stringgrid and edit.
• Create a new Excel file and fill a few cells.

Example - Open/Read Excel file:

uses .....  comobj;

procedure TForm1.Button1Click(Sender: TObject);

Var   XLApp: OLEVariant;
      x,y: byte;
      path: variant;

begin
 XLApp := CreateOleObject('Excel.Application'); // requires comobj in uses
 try
   XLApp.Visible := False;         // Hide Excel
   XLApp.DisplayAlerts := False;
   path := edit1.Text;
   XLApp.Workbooks.Open(Path);     // Open the Workbook
   for x := 1 to 4 do
    begin
     for y := 1 to 6 do
      begin
       SG.Cells[x,y] := XLApp.Cells[y,x].Value;  // fill stringgrid with values
      end;
    end;
 finally
   XLApp.Quit;
   XLAPP := Unassigned;
  end;

If you want to make some changes and you want them to write back into the Excel, file you can use:

XLApp.Cells[x,y].Value := SG.Cells[y,x];

If you want to save:

XLApp.ActiveWorkBook.Save;

Read/Writing an Excel file using the SpreadSheet Interface Component

The component provides a library interface, abstracting the Excel COM and the Calc Open Office UNO interfaces. The component is available here: http://www.tcoq.org/composants

Since Automation is not yet available, but COM is available, the Excel interface component provides a set of Lazarus classes encapsulating calls to the Excel COM interface (the one below the Automation). It hides most of the drudgery of working with low-level code. Be careful, this is a work-in-progress. Use it at your own risk.

Functionality:

• creating and loading excel workbooks,
• saving workbooks,
• creating and accessing sheets,
• getting values and setting values (and formulas) in cells,
• getting and changing color of cells,
• getting and changing column height and row width,
• creating comments,
• creating shapes,
• creating charts.

Inits first.

  IMPLEMENTATION
  USES
    ExcelUtilities,
    SpreadSheetInterfaces ;

  VAR
   aCell    : IRange ;
   aValue   : OleVariant ; // Not sure about this, but it works. ie( Edit.Text := STRING(aValue); )
   ExcelApp : TExcelApplication ;
   ExcelWbs : IWorkBooks ;

  ExcelBook   : IWorkBook ;
  ExcelSheet  : ISheet ;
  ExcelSheets : ISheets ;

Getting a sheet is simple:

  // Initializing the common excel workbook:
  ExcelApp         := TExcelApplication.Create(nil) ;
  ExcelApp.Active  := True ;
  ExcelApp.Visible := True ;

  ExcelWbs    := ExcelApp.WorkBooks ;
  ExcelBook   := ExcelWbs.Add ;
  ExcelSheets := ExcelBook.Sheets ;
  ExcelSheet  := ExcelSheets.Sheet(1) ;

Playing around with cells is simple too:

  // adding a value
  aCell := ExcelSheet.Cells(1, 1) ;
  aCell.Value := 10;

  // adding a formula
  aCell := ExcelSheet.Cells(2,1) ;
  aCell.Formula := '=A1+10' ;

  // getting the value computed in Excel
  aValue := aCell.Value ;

The test case provided has many more examples.

Copy HTML to the clipboard

You can copy HTML to the clipboard which is understood by many applications. This way you can copy formatted text. For those applications that only understand text put plain text too.

uses
  ClipBrd;
...
  // register the mime type for text/html. You can do this once at program start:
  ClipbrdFmtHTML:=RegisterClipboardFormat('text/html');
...
  // put text and html on the clipboard. Other applications will choose the best format automatically.
  ThePlainUTF8Text:='Simple text';
  Clipboard.AsText:=ThePlainUTF8Text; 
  AsHTML:='<b>Formatted</b> text'; // text with formattings
  Clipboard.AddFormat(ClipbrdFmtHTML,AsHTML[1],length(AsHTML));

Voir aussi

• Clipboard

External links

• Excel file format - description on OpenOffice website