Sign in to follow this  
Followers 0
malkerian

Openoffice calc, all commands

2 posts in this topic

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
 

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]

_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

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