Jump to content
Sign in to follow this  

Openoffice calc, all commands

Recommended Posts

Dear Sir/Madam,

I don't have excel so im using OpenOffice.

However since there no functions of OpenOffice i did a Google search and found this

'?do=embed' frameborder='0' data-embedContent>>


However, I tried manipulating the function by replacing Excel with OOoCalc but to no avail. this is my script so far. ( i took the script from the example in the excel helpfile on autoit)

#include <OOoCOM_UDF_v08.au3>
#include <MsgBoxConstants.au3>
Local $oExcel = _OOoCalc_New() ;Create new book, make it visible
For $i = 1 To 5 ;Loop
    _OOoCalc_WriteCell($oExcel, $i, $i, 1) ;Write to the Cell
Local $sCellValue
For $i = 1 To 5 ;Loop
    $sCellValue = _OOoCalc_ReadCell($oExcel, $i, 1)
    MsgBox($MB_SYSTEMMODAL, "", "The Cell Value is: " & @CRLF & $sCellValue, 2)
MsgBox($MB_SYSTEMMODAL, "Exiting", "Press OK to Save File and Exit")
_OOoCalc_BookSaveAs($oExcel, @TempDir & "Temp.xls", "xls", 0, 1) ; Now we save it into the temp directory; overwrite existing file if necessary
_OOoCalc_BookClose($oExcel) ; And finally we close out
It does nothing. ive only been using Autoit for 6 hours so much it feels way over my head.
Any more experienced Autoit programmers help would by very much appreciated.
many regards

Share this post

Link to post
Share on other sites

I don't have Excel either, and use Open Office.

I have never had any success with the existing Open Office UDF's:-

The last link looked like the easiest to debug, and now works for me.

I create an Excel file in Open Office Calc called "testcalc1.xls" containing data, and put it in the script's directory to test the following example script.

This example does not work if "testcalc1.xls" is already opened in Open Office Calc when this example is started.

#include <Array.au3>

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

_OOOpenBook(@ScriptDir & '\testcalc1.xls')

;~ $array = _OOSheetRangetoArray(0, "B3", "F5", "ALL")
$array = _OOSheetRangeToArray(0, "B3", "F5", "")

_ArrayDisplay($array, "Test")

Func _OOInit($pass = "", $readonly = False, $hidden = False)
    $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)
    $OpenPar[2] = _OOSetProp("Hidden", $hidden)
EndFunc   ;==>_OOInit

Func _OOOpenBook($fname)
    $errormodul = "_OOOpenBook"
    $fname = StringReplace($fname, ":", "|")
    $fname = StringReplace($fname, " ", "%20")
    $fname = "file:///" & StringReplace($fname, "\", "/")
    $odoc = $oDesk.loadComponentFromURL($fname, "_blank", 0, $OpenPar)
    $oSheet = $odoc.CurrentController.ActiveSheet
EndFunc   ;==>_OOOpenBook

Func _OOSheetRangeToArray($sheetnameornumber, $startcell, $endcell, $all = "ALL")
    $errormodul = "_OOSheetRangeToArray"
    If IsString($sheetnameornumber) Then
        $oSheet = $odoc.sheets.getbyname($sheetnameornumber)
        $oSheet = $odoc.sheets.getbyindex($sheetnameornumber) ;index starts with 0

    If StringUpper($all) = "ALL" Then ;used data of the whole sheet
        $oCursor = $oSheet.createCursor()
        $start = $oCursor.getrangeaddress()
        $Start_row = $start.startRow
        $Start_col = $start.startColumn
        $end = $oCursor.getrangeaddress()
        $end_row = $end.endRow
        $end_col = $end.endColumn
        If IsString($startcell) Then
            $cell = _OOAdress2Coord($startcell)
            $Start_row = $cell[1]
            $Start_col = $cell[0]
        If IsString($endcell) Then
            $cell = _OOAdress2Coord($endcell)
            $end_row = $cell[1]
            $end_col = $cell[0]
    $ooarray = $oSheet.getCellRangeByPosition($Start_col, $Start_row, $end_col, $end_row).getDataArray()
    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]
    Return $array
EndFunc   ;==>_OOSheetRangeToArray

Func _OOSetProp($cName, $uValue)
    $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 _OOAdress2Coord($cellname)
    $errormodul = "_OOAdress2Coord"
    Local $textchar[3]
    Local $numchar[3]
    $cellname = StringUpper($cellname)
    $numchar = StringRegExp($cellname, '\d+', 1)
    $cellCoord[1] = $numchar[0] - 1
    $textchar = StringRegExp($cellname, '[[:alpha:]]{0,2}', 1)
    $x = (Asc(StringMid($textchar[0], 1, 1)) - 65)
    If StringLen($textchar[0]) = 2 Then
        $x = (($x + 1) * 26) + (Asc(StringMid($textchar[0], 2, 1)) - 65)
    $cellCoord[0] = $x
    Return $cellCoord
EndFunc   ;==>_OOAdress2Coord

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...