JoeriVanleeuw 0 Posted November 3, 2011 Ok Hi, to allI'm new to AutoIt.And I'am trying to read data from an OOo_calc sheet into an arrayI've found this OOoCOM_UDF_v08.au3(On this great forum ) But I keep getting Blank arrays on the _OOoRead2Array commandWhat 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
Zedna 280 Posted November 3, 2011 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 Resources UDF ResourcesEx UDF AutoIt Forum Search Share this post Link to post Share on other sites
JoeriVanleeuw 0 Posted November 3, 2011 I have got LibreOffice 3.4.1 I tested oocalc_4.au3 with removed allwinwait,WinActivate,WinActive commands because it's not necessary and title is diferentand all works fine for me.Running Autoit 3.3.7.18 on Win7 64bitThx for ur speedy replybut I think i was looking for some kind of example code in which it is usedcause it fails everytime in my code Share this post Link to post Share on other sites
Zedna 280 Posted November 3, 2011 Working example is oocalc_4.au3 from link given by you in first post. Resources UDF ResourcesEx UDF AutoIt Forum Search Share this post Link to post Share on other sites
JoeriVanleeuw 0 Posted November 7, 2011 Ok still getting an error i do not understandError:E:AutoIt3Includeoocalc_4.AU3 (308) : ==> Error in expression.:$oSheet = $odoc.sheets.getbyname($sheetnameornumber)$oSheet = ^ ERRORThis 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_CIfromListThx for replies Share this post Link to post Share on other sites
Zedna 280 Posted November 7, 2011 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 Resources UDF ResourcesEx UDF AutoIt Forum Search Share this post Link to post Share on other sites
JoeriVanleeuw 0 Posted November 9, 2011 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
JoeriVanleeuw 0 Posted December 6, 2011 OK i got it solved Thx for ur help everyone Share this post Link to post Share on other sites
water 2,392 Posted December 6, 2011 Could you please post how you solved the problem for future use? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
JoeriVanleeuw 0 Posted December 6, 2011 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 workbut I'll make a demo script asap Share this post Link to post Share on other sites
water 2,392 Posted December 6, 2011 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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
t0nZ 21 Posted February 2, 2012 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