OPen office calc & AutoIt

[JoeriVanleeuw]

Ok Hi, to all

I'm new to AutoIt.

And I'am trying to read data from an OOo_calc sheet into an array

I've found this OOoCOM_UDF_v08.au3

(On this great forum )

But I keep getting Blank arrays on the _OOoRead2Array command

What would be the correct syntax to use this command So it creates my array of all the data provided on the sheet

Thx in advance

[Zedna]

I have got LibreOffice 3.4.1

I tested oocalc_4.au3 with removed all

winwait,WinActivate,WinActive commands because it's not necessary and title is diferent

and all works fine for me.

Running Autoit 3.3.7.18 on Win7 64bit

[JoeriVanleeuw]

I have got LibreOffice 3.4.1

I tested oocalc_4.au3 with removed all

winwait,WinActivate,WinActive commands because it's not necessary and title is diferent

and all works fine for me.

Running Autoit 3.3.7.18 on Win7 64bit

Thx for ur speedy reply

but I think i was looking for some kind of example code in which it is used

cause it fails everytime in my code

[Zedna]

Working example is oocalc_4.au3 from link given by you in first post.

[JoeriVanleeuw]

Ok still getting an error i do not understand

Error:

E:AutoIt3Includeoocalc_4.AU3 (308) : ==> Error in expression.:

$oSheet = $odoc.sheets.getbyname($sheetnameornumber)

$oSheet = ^ ERROR

This is the only part of code i'm using Containing oOcalc

#include <oocalc_4.AU3>
Func _Read_CIfromList()
ShellExecute('E:Script xcl to cpentryxcl filesci.xls')
WinActive("OpenOffice", "")
Sleep(500)
$array = _OOSheetRangetoArray("Ci", "A1", "A1", "ALL")
_ArrayDisplay($array, "Test")
EndFunc   ;==>_Read_CIfromList

Thx for replies

[Zedna]

Here is functional example without any include files

I just copied needed functions from oocalc_4.au3 directly to this script,

Error checking was removed too.

So just change XLS filename and range in sheet

expandcollapse popup

#include <Array.au3>

Global $setOOoProp, $oSheet, $odoc, $oDesk, $osm, $oCell, $errormodul
Global $OpenPar[3],$cellkoord[2]

_OOInit() ;verbindung zu openoffice
_OOOpenBook(@ScriptDir & 'testcalc1.xls')
;~ $array = _OOSheetRangetoArray(0, "B3", "F5", "ALL")
$array = _OOSheetRangetoArray(0, "B3", "F5", "")
_ArrayDisplay($array, "Test")

Func _OOSetProp($cName, $uValue) ;Eigenschaften in struct übergeben
    $errormodul = "_OOSetProp"
    $osm = ObjCreate("com.sun.star.ServiceManager")
    $oPropertyValue = $osm.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
    $oPropertyValue.Name = $cName
    $oPropertyValue.Value = $uValue
    $setOOoProp = $oPropertyValue
    Return $setOOoProp
EndFunc   ;==>_OOSetProp

Func _OOInit($pass = "", $readonly = False, $hidden = False) ;verbindung zu OO herstellen
    $errormodul = "_OOINIT"
    $osm = ObjCreate("com.sun.star.ServiceManager")
    $oDesk = $osm.createInstance("com.sun.star.frame.Desktop")
    $OpenPar[0] = _OOSetProp("ReadOnly", $readonly)
    $OpenPar[1] = _OOsetProp("Password", $pass) ;setzt das passwort des dokuments
    $OpenPar[2] = _OOsetProp("Hidden", $hidden)
EndFunc   ;==>_OOInit

Func _OOOpenBook($fname) ; bestehende Tabellendatei öffnen
    $errormodul = "_OOOpenBook"
    $fname = StringReplace($fname, ":", "|")
    $fname = StringReplace($fname, " ", "%20")
    $fname = "file:///" & StringReplace($fname, "", "/")
    $odoc = $oDesk.loadComponentFromURL($fname, "_blank", 0, $OpenPar)
    $oSheet = $odoc.CurrentController.ActiveSheet ;auskommentieren, um alle importierbaren Formate zu öffnen
EndFunc   ;==>_OOOpenBook

Func _OOSheetRangeToArray($sheetnameornumber, $startcell, $endcell, $all = "ALL") ;gibt ein Array der Daten aus dem Bereich des Tabellenblatts
    $errormodul = "_OOSheetRangeToArray"
    If IsString($sheetnameornumber) Then
        $oSheet = $odoc.sheets.getbyname($sheetnameornumber)
    Else
        $oSheet = $odoc.sheets.getbyindex($sheetnameornumber) ;index starts with 0
    EndIf

    If StringUpper($all) = "ALL" Then ;used data of the whole sheet
        $oCursor = $oSheet.createCursor()
        $oCursor.GotoStartOfUsedArea(0) ;von der ersten ausgefüllten Zelle
        $start = $oCursor.getrangeaddress()
        $Start_row = $start.startRow
        $Start_col = $start.startColumn
        $oCursor.GotoEndOfUsedArea(1);bis zur letzten ausgefüllten Zelle
        $end = $oCursor.getrangeaddress()
        $end_row = $end.endRow
        $end_col = $end.endColumn
    Else
        If IsString($startcell) Then
            $cell = _OOAdress2Koord($startcell)
            $Start_row = $cell[1]
            $Start_col = $cell[0]
        EndIf
        If IsString($endcell) Then
            $cell = _OOAdress2Koord($endcell)
            $end_row = $cell[1]
            $end_col = $cell[0]
        EndIf
    EndIf

    $ooarray = $oSheet.getCellRangeByPosition($Start_col, $Start_row, $end_col, $end_row).getDataArray() ;verschachteltes array, in [0] ist die erste zeile, in [2] die 2. usw

    Dim $array[UBound($ooarray)][UBound($ooarray[0])]
    For $rows = 0 To UBound($ooarray) - 1
        $row = $ooarray[$rows]
        For $cols = 0 To UBound($row) - 1
            $array[$rows][$cols] = $row[$cols]
        Next
    Next
    Return $array
    ;ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $GetUsedRangeAddress = ' & $GetUsedRangeAddress & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console
;~   ; return $Range
EndFunc   ;==>_OOSheetRangeToArray

Func _OOAdress2Koord($cellname) ;wandelt "C1" in $cellkoord[0]=2 und $cellkoord[1]=0
    $errormodul = "_OOAdress2Koord"
    Local $textchar[3]
    Local $numchar[3]
    $cellname = StringUpper($cellname)
    $numchar = StringRegExp($cellname, 'd+', 1) ;y-Koordinate der Zelle, findet Zahlen im Zellennamen;
    ;    msgbox (0,$cellname,$textchar[0]&" "&$numchar[0])
    $cellkoord[1] = $numchar[0] - 1
    $textchar = StringRegExp($cellname, '[[:alpha:]]{0,2}', 1) ;findet A oder AA im Zellennamen
    $x = (Asc(StringMid($textchar[0], 1, 1)) - 65) ;ascii erster Buchstabe
    If StringLen($textchar[0]) = 2 Then
        $x = (($x + 1) * 26) + (Asc(StringMid($textchar[0], 2, 1)) - 65)
    EndIf
    $cellkoord[0] = $x
    Return $cellkoord
EndFunc   ;==>_OOAdress2Koord

[JoeriVanleeuw]

THX zedna for the reply

You helped me tremendously

I think I will stick around to this forum

[JoeriVanleeuw]

OK i got it solved

Thx for ur help everyone

[water]

Could you please post how you solved the problem for future use?

[JoeriVanleeuw]

Could you please post how you solved the problem for future use?

Can't hand in the code i made cause it contains info about my work

but I'll make a demo script asap

[water]

That's fine. Just show how you solved the poblem so any user with the same problem can find the solution.

[t0nZ]

So what is the state of the art for UDF's to interact with LibreOffice ?

I searched the forums but the best effort with LibreOffice is this work, after the good OOoCOM_UDF_v08.au3.

I created some stuff with OOoCOM_UDF_v08.au3 to work with OpenOffice (last time I used 3.2) but now with LibreOffice (now 3.4.5) is all broken.

When I have some time I'll work too on the stuff from this "new" thread .