Sign in to follow this  
Followers 0
JoeriVanleeuw

OPen office calc & AutoIt

12 posts in this topic

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 :D)

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

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

#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

Share this post


Link to post
Share on other sites

THX zedna for the reply

You helped me tremendously

I think I will stick around to this forum

Share this post


Link to post
Share on other sites

OK i got it solved

Thx for ur help everyone

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 :D

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 .

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0