Drive OpenOffice Calc with tcluno

These examples were created to show the use of tcluno for in an article for the German computer magazine http://www.LinuxMagazin.de . Variables are therefore in German, but I hope the example is useful for non-German speakers as well.

It shows the usage of the tcluno extension found at http://tcluno.sourceforge.net using the pure scripting solution tclurtp.

The example creates a spreadsheet with some random numbers and a formula and save the sheet in XLS format.

Besides installing tcluno it requires that you start OpenOffice with network support. This could be done by starting OpenOffice using

ooffice "-accept=socket,host=localhost,port=2002;urp;"

Many thanks to the authors of tcluno, Arnulf Wiedemann and Wolfgang Großer, who supported me when writing the article.

Author Carsten Zerbst

 #!/bin/sh
 #
 # Beispiel für TclUno
 # \
 exec tclsh "$0" "$@"

 lappend auto_path [ file join [ pwd ] tclurtp ]
 lappend auto_path [ file join [ pwd ] tcluno ]

 # damit wird die Skriptlösung angefordert
 # setting the argv to -urtp will require a pure scripted 
 # solution of the network interface
 set ::argv "-urtp"

 # Erweiterung laden
 # load extension
 package require tcluno_scalc

 # Kontakt mit OpenOffice herstellen
 # contact OpenOffice and print error message
 if {[catch {::tcluno_soffice::initDesktop } desktop]} {
    puts stderr "Verbindung mit OpenOffice fehlgeschlagen !"
    puts stderr "Fehler war:"
    puts stderr $desktop
    puts stderr ""
    puts stderr "Bitte OpenOffice mit Netzwerkunterstützung starten:"
    puts stderr "ooffice \"-accept=socket,host=localhost,port=2002;urp;\""
    exit 1
 }

 # Schnittstellen anzeigen
 # Show interfaces of the desktop object
 puts "desktop hat folgende Schnittstellen:"
 puts [ join [$desktop getTypes] "\n" ]

 # show services of desktop object
 puts "\ndesktop ist folgender Service:"
 puts [$desktop getSupportedServiceNames]

 # leere Liste erstellen
 # create empty list of Any type
 set filterSequence [$desktop tcluno::createUnoSequence Any]

 # neues Dokument in Tabellenkalkulation
 # create new document in OO Calc
 set spreadsheet [$desktop loadComponentFromURL  "private:factory/scalc" \
                     "_blank" 0  $filterSequence ]

 # erste Seite finden
 # find the first page
 set sheets [$spreadsheet getSheets]
 set sheet [$sheets getByIndex 0]

 # and set the name to Zufallszahlen (random numbers)
 puts "\nTabellenname ist: [$sheet getName]"
 $sheet setName "Zufallszahlen"

 # Tabelle mit Zufallszahlen füllen
 # fill the page with random numbers
 for { set col 0} { $col < 10} {incr col} {
    for { set row 0} { $row < 10} {incr row} {
        set cell [$sheet getCellByPosition $col $row]
        set value [expr rand() * 100]
        $cell setValue $value
    }
 }

 # Zellenadresse 
 # show the addresse of a cell
 set cell [ $sheet getCellRangeByName "A12" ]
 puts "\nKomplette Adresse: [ $cell getCellAddress ]"

 # Formeln und Texte
 # enter some text and formula 
 set cell [ $sheet getCellRangeByName "A12" ]
 $cell setFormula "Summe:"

 set cell [ $sheet getCellRangeByName "B12" ]
 $cell setFormula "=sum(B1:B10)"

 set cell [ $sheet getCellRangeByName "A13" ]
 $cell setFormula "Mittelwert:"

 set cell [ $sheet getCellRangeByName "B13" ]
 $cell setFormula "=median(B1:B10)"

 # Als Excell speichern
 # and save as excell
 set filterSequence [$::desktop tcluno::createUnoSequence Any]
 set msExcelFilter [$::desktop tcluno::createUnoStructHelper \
                       com.sun.star.beans.PropertyValue \
                       {FilterName -1 {MS Excel 97} 0}
                  ]
 $::desktop tcluno::appendUnoSequence $filterSequence $msExcelFilter

 $spreadsheet storeAsURL file:/tmp/test.xls $filterSequence

Bernd Schmitt Using Windows & TclTk 8.4 & TclUno from ftp.linux-magazin.de & OpenOffice.org2 - I get errors:

after filtersequence

after msexcelfilter

 TCLFCN:AUS:::tclUrtpBridge::sequence_112:::tclUrtpBridge::struct_113::
 TCLFCN:AUS:2:{{SEQUENCE_INFO_INDEX 1} {TYPE_CLASS Any}}::

after appendunosequence

 EXCEPTION!!
 OID:45ddd6c;msci[0];6dc5311993f11daa8eeacef8a3af58:
 EXCEPTION:class_name:com.sun.star.reflection.InvocationTargetException:exception occured during invocation!:

after storeasurl

 TCLFCN:AUS:::tclUrtpBridge::sequence_112:::tclUrtpBridge::struct_113::
 TCLFCN:AUS:2:{{SEQUENCE_INFO_INDEX 1} {TYPE_CLASS Any}} {{STRING FilterName} {LONG -1} {TYPE_CLASS STRING} {STRING {MS Excel 97}} {ENUM 0}}::

2006 Feb 13 I was able to run the above example under linux using tclkit, but I needed the tclvfs package to do so. Is there any way to start openoffice without displaying an empty document? When I start openoffice it begins with a text document, and I'd prefer to start with openoffice empty, iconified or with a spreadsheet document.

Arnulf Wiedemann 2006 Feb 14 Hello Bernd, I will have a look at the problems next weekend, because I am very busy in my job at the moment.

Wolfgang Grosser 2006 Mar 29 Hello Bernd, with Windows XP and OpenOffice.org 2.0 I can reproduce your error. We (Arnulf Wiedemann and me) will investigate on this error. Using itcluno (instead of basic low level tcluno) the following code works on Windows (and also Linux of course):

 #!/bin/sh
 #
 # Beispiel für TclUno
 # \
 exec tclsh "$0" "$@"
 
 lappend auto_path [ file join [ pwd ] tclurtp ]
 lappend auto_path [ file join [ pwd ] tcluno ]
 
 # Erweiterung laden
 # load extension
 package require itcluno
 
 # Kontakt mit OpenOffice herstellen
 # contact OpenOffice and print error message
 # neues Dokument in Tabellenkalkulation
 # create new document in OO Calc
 itcluno::SpreadSheet spreadSheet
 
 spreadSheet renameSheet Tabelle1 Zufallszahlen
 
 # Tabelle mit Zufallszahlen füllen
 # fill the page with random numbers
 set values [list]
 for { set row 0} { $row < 10} {incr row} {
    set line [list]
    for { set col 0} { $col < 10} {incr col} {
         lappend line [expr rand() * 100]
    }
    lappend values $line
 }
 spreadSheet setCellValue Zufallszahlen A1 $values
 
 # Formeln und Texte
 # enter some text and formula
 spreadSheet setCellValue Zufallszahlen A12 Summe:
 spreadSheet setCellValue Zufallszahlen B12 =sum(B1:B10)
 spreadSheet setCellValue Zufallszahlen A13 Mittelwert:
 spreadSheet setCellValue Zufallszahlen B13 =median(B1:B10)
 
 # Als Excel speichern
 # and save as excel on the actual partition

 spreadSheet saveFile /tmp/test.xls

The code does the same (except that the cells are filled row by row instead of column by column in the first example). I hope this will help you.

Marc Ziegenhagen 2006 Apr 7 I have tried to use unospection for looking the methods and the help of the openoffice-api, but I have the problem to find a tclvfs for linux. Did somebody now where I can get al tclvfs for linux? I have also tried with tclkit but there it comes the message the he requires the log package.

Miko the code with tcluno was working (except save as excel) with OO 2.0 on windows XP. With OO 2.3, its broken... apw 2007-12-31 The problem is known here, we are working on a solution.

Category Application