Openoffice calc, all commands

[malkerian]

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

Huzah!

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

Next

 

Local $sCellValue

For $i = 1 To 5 ;Loop

    $sCellValue = _OOoCalc_ReadCell($oExcel, $i, 1)

    MsgBox($MB_SYSTEMMODAL, "", "The Cell Value is: " & @CRLF & $sCellValue, 2)

Next

 

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

 

Malkerian

 

[Malkey]

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.

expandcollapse popup

#include <Array.au3>

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

_OOInit()
_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)
    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)
        $start = $oCursor.getrangeaddress()
        $Start_row = $start.startRow
        $Start_col = $start.startColumn
        $oCursor.GotoEndOfUsedArea(1)
        $end = $oCursor.getrangeaddress()
        $end_row = $end.endRow
        $end_col = $end.endColumn
    Else
        If IsString($startcell) Then
            $cell = _OOAdress2Coord($startcell)
            $Start_row = $cell[1]
            $Start_col = $cell[0]
        EndIf
        If IsString($endcell) Then
            $cell = _OOAdress2Coord($endcell)
            $end_row = $cell[1]
            $end_col = $cell[0]
        EndIf
    EndIf
    $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]
        Next
    Next
    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)
    EndIf
    $cellCoord[0] = $x
    Return $cellCoord
EndFunc   ;==>_OOAdress2Coord