OOo/LibO Calc UDF

[Wombat]

I don't believe that option is possible with OOo because OOo's interface doesn't support it.

 

Ah, thats a disappointment...

[GMK]

Thanks, BrewManNH.  I believe you are correct.

Edited December 19, 2013 by GMK

[Mehomic]

 

BrewManNH, on 18 Dec 2013 - 8:11 PM, said:

 I don't believe that option is possible with OOo because OOo's interface doesn't support it.

 

If you had studied the AOO API documentation you will find that there are many 'properties' that can be utilised.

The code below originally by LEAGNUS, has been adapted by me, works just how I need.

expandcollapse popup

#cs
  FileName: RunOOoCalc.au3
  Description: Run a Calc file 'hidden'
    
#ce

#AutoIt3Wrapper_UseX64=n
#AutoIt3Wrapper_Add_Constants=y
Opt ("WinTitleMatchMode",4)
Opt("TrayIconHide", 1) ;0=show 1=hide
Opt('TrayAutoPause', 0) ;0=no pause 1=pause
#Include <Array.au3>

_Run_OOoCalc()

;===============================================================================
;
; Function Name:    _Run_OOoCalc()
; Description:      Opens a Scalc workbook and returns its object identifier.
; Parameter(s):     $fn   = full UNC File Name of the workbook file
; Requirement(s):   AutoIt3 with COM support (post 3.1.1)
;                   On Success - Returns an object variable pointing to
;                           active com.sun.star.frame.Desktop Component object
;                   On Failure  - Returns 0 and sets @ERROR = 1
; Author(s):        Leagnus
; Adapted by:       Code Mehomic (6 Jan 2011)
; Added:            The AOO API 'hidden' properties to the open spreadsheet (display 'hidden')
;===============================================================================
;
Func _Run_OOoCalc()
   Local $oDesktop, $f_num
   Local $OpenPar[2] ;<< set array
   $OpenPar[0] = _setProp("ReadOnly", False) ;<< set to true for read only
   $OpenPar[1] = _setProp("Hidden", True) ; <<-- set to false to make visible
   $oSM = Objcreate("com.sun.star.ServiceManager") ;<< create the service manager
   $oDesktop = $oSM.createInstance("com.sun.star.frame.Desktop")   ;<< create a desktop object:
   $oCurCom = $oDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, $OpenPar) ;<< create blank document
   if @error or Not IsObj($oCurCom) then
      SetError(1, 0, 0)
   else
      Return
 EndIf
EndFunc

;------------------------------------------------------------------------------------------
; Function Name:    setProp()
; Description:      Function to set properties of the OOo service Manager
; Requirement(s):   AutoIt3 with COM support (post 3.1.1)
;                   On Success - Returns an object variable pointing to
;                                     active com.sun.star.frame.Desktop Component object
;                   On Failure  - Returns 0 and sets @ERROR = 1
; Author(s):        Leagnus
; ----------------------------------------------------------------------------------------
 Func _setProp($cName, $uValue)
 Local $oPropertyValue
 Local $oSM, $setOOoProp
  $oSM = Objcreate("com.sun.star.ServiceManager") ;<< create the service manager
  $oPropertyValue = $oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue") ;<< create the property value
  $oPropertyValue.Name = $cName 
  $oPropertyValue.Value = $uValue
  $setOOoProp = $oPropertyValue ;<< create 'name=value' pairs
  Return $setOOoProp
EndFunc
;
; --------------------------------------- end of file ----------------------------------------------------
;

Enjoy.......

Merry Christmas and a Happy New Year to all.

Code Mehomic

Edited December 21, 2013 by Mehomic

[BrewManNH]

Sorry, but I did say "I don't believe" which would imply that I wasn't sure from what I read. If there's a way to do, that's nice to know.

[Wombat]

I tried simply replacing "Password" with "Hidden" in the OOoCalc UDF and that failed

This is what i tried and it failed:

expandcollapse popup

Func _OOoCalcBookOpen($sFileName, $fReadOnly = False, $sHidden = True)
    Local $sFuncName = "_OOoCalcBookOpen"
    If Not IsString($sFileName) Then
        __OOoCalcErrorNotify("Error", $sFuncName, "", "$_OOoCalcStatus_InvalidDataType - File name must be a string")
        Return SetError($_OOoCalcStatus_InvalidDataType, 1, 0)
    EndIf
    If Not FileExists($sFileName) Then
        __OOoCalcErrorNotify("Error", $sFuncName, "", "$_OOoCalcStatus_NoMatch - File could not be found")
        Return SetError($_OOoCalcStatus_NoMatch, 1, 0)
    EndIf
    If Not IsBool($fReadOnly) Then
        __OOoCalcErrorNotify("Error", $sFuncName, "", "$_OOoCalcStatus_InvalidDataType - Read-only flag must be True or False")
        Return SetError($_OOoCalcStatus_InvalidDataType, 2, 0)
    EndIf
    If Not IsBool($sHidden) Then
        __OOoCalcErrorNotify("Error", $sFuncName, "", "$_OOoCalcStatus_InvalidDataType - Password must be a string")
        Return SetError($_OOoCalcStatus_InvalidDataType, 3, 0)
    EndIf
    Local $oSM = ObjCreate("com.sun.star.ServiceManager")
    If Not IsObj($oSM) Then
        __OOoCalcErrorNotify("Error", $sFuncName, "", "$_OOoCalcStatus_GeneralError - Unable to create service manager object")
        Return SetError($_OOoCalcStatus_GeneralError, 0, 0)
    EndIf
    Local $oDesktop = $oSM.createInstance("com.sun.star.frame.Desktop")
    If Not IsObj($oDesktop) Then
        __OOoCalcErrorNotify("Error", $sFuncName, "", "$_OOoCalcStatus_GeneralError - Unable to create desktop instance")
        Return SetError($_OOoCalcStatus_GeneralError, 0, 0)
    EndIf
    Local $sURL = __OOoCalcFileToURL($sFileName)
    Local $aoProperties[1] = [__OOoCalcSetPropertyValue("ReadOnly", $fReadOnly)]
    If Not IsObj($aoProperties[0]) Then
        __OOoCalcErrorNotify("Error", $sFuncName, "", "$_OOoCalcStatus_GeneralError - Unable to set read-only properties")
        Return SetError($_OOoCalcStatus_GeneralError, 0, 0)
    EndIf
        ReDim $aoProperties[2]
        Local $aoProperties[1] = [__OOoCalcSetPropertyValue("Hidden", $sHidden)]
        If Not IsObj($aoProperties[1]) Then
            __OOoCalcErrorNotify("Error", $sFuncName, "", "$_OOoCalcStatus_GeneralError - Unable to set hidden properties")
            Return SetError($_OOoCalcStatus_GeneralError, 0, 0)
        EndIf
    Local $oReturn = $oDesktop.loadComponentFromURL($sURL, "_default", 0, $aoProperties)
    If Not IsObj($oReturn) Then
        __OOoCalcErrorNotify("Error", $sFuncName, "", "$_OOoCalcStatus_GeneralError - Unable to load compoment from URL")
        Return SetError($_OOoCalcStatus_GeneralError, 0, 0)
    Else
        Return SetError($_OOoCalcStatus_Success, 0, $oReturn)
    EndIf
EndFunc   ;==>_OOoCalcBookOpen

Edited December 23, 2013 by Wombat

[pat4005]

BrewManNH, there is a way to instantly hide OOoOffice/LibreOffice window before executing script by adding a string "-invisible" to the shortcut's properties as parameter. But this is not a solution, isn't it?

Edited December 24, 2013 by pat4005

[xekon]

so far I have this UDF working easily, however when I use: _OOoCalcReadCell it is copying the formulas from the cells instead of the calculated values...

if I manually/physically (without the script) hit copy on a cell and then paste into notepad, it correctly copies the calculated value, how can i read/copy the value instead of the formula?

Edited December 21, 2015 by xekon

[GMK]

Sorry for the delay--I don't get on here as much anymore.  There are multiple ways we could get around this.  We could change line 1380 to read ".getValue" instead of ".getFormula".  (Another option there would be to add a parameter to the function asking whether to read formula or value.)  We could also use _OOoCalcReadSheetToArray to read the value(s).

Additional feedback would help to decide how to move forward if I'm to update any functions.

Edited January 4, 2016 by GMK
clarification

[argumentum]

... Additional feedback would help to decide how to move forward if I'm to update any functions.

What is the need for Func _OOoCalcBookNew() to create a default sheet as ReadOnly ?
I had to change that to have a functional demo.
A small change like Func _OOoCalcBookNew( $fReadOnly = False ) would be better

Thanks for putting this together and do continue if you have the time 

 

[GMK]

Before I upload any of the above changes, are there any other feature requests to research and implement?

[mLipok]

rather script breaking change

just FunctionNames renaming 

expandcollapse popup

_OOoCalc_BookNew
_OOoCalc_BookOpen
_OOoCalc_BookAttach
_OOoCalc_BookSaveAs
_OOoCalc_BookClose
_OOoCalc_WriteCell
_OOoCalc_WriteFormula
_OOoCalc_WriteFromArray
_OOoCalc_HyperlinkInsert
_OOoCalc_RangeMoveOrCopy
_OOoCalc_CreateBorders
_OOoCalc_NumberFormat
_OOoCalc_ReadCell
_OOoCalc_ReadSheetToArray
_OOoCalc_RowDelete
_OOoCalc_ColumnDelete
_OOoCalc_RowInsert
_OOoCalc_ColumnInsert
_OOoCalc_SheetAddNew
_OOoCalc_SheetDelete
_OOoCalc_SheetNameGet
_OOoCalc_SheetNameSet
_OOoCalc_SheetList
_OOoCalc_SheetActivate
_OOoCalc_SheetSetVisibility
_OOoCalc_SheetMove
_OOoCalc_SheetPrint
_OOoCalc_HorizontalAlignSet
_OOoCalc_FontSetProperties
_OOoCalc_CellSetColors
_OOoCalc_RowSetColors
_OOoCalc_ColumnSetColors
_OOoCalc_RowSetProperties
_OOoCalc_ColumnSetProperties
_OOoCalc_FindInRange
_OOoCalc_ReplaceInRange
_OOoCalc_ErrorHandlerRegister
_OOoCalc_ErrorHandlerDeRegister

 

EDIT: But this should wait for any other bigger changes like rewrite.

 

Edited January 8, 2016 by mLipok

[Stan2k]

Does this work for anybody with 64-bit LibreOffice5?

I have 64-bit Win10 + 64-bit LO and connection doesnt work. 64-bit Win10 + 32-bit LO works perfectly.

Problem may be in com.sun.star.ServiceManager DCOM, which is apparently run by 64bit soffice.exe and, as I understand, cant be reached by 32bit AutoIt, even if I use 64bit Script Runner.

I experimented with adding com.sun.star.ServiceManager into WOW6432Node parts of registy, but with no luck.

Any comments?

 

 

 

[GMK]

I installed 64-bit LO, but had the same problem.

I suspect that it has something to do with this.  Unfortunately, there seems to be no fix for it yet.

[mLipok]

Here is answer:
https://forum.openoffice.org/en/forum/viewtopic.php?f=44&t=75056
 

EDIT: also here:
https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=63881

Edited July 24, 2016 by mLipok

[keafter]

Dates don't play nicely. It adds a ' in front of the date rendering it useless.

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Date.au3>
#include <OOoCalc.au3>

$Form1_1 = GUICreate("Notary Client Verification Form", 667, 273, 192, 132)
$Date1 = GUICtrlCreateDate(_DateToDayValue, 72, 40, 257, 25,$WS_TABSTOP)
$Button1 = GUICtrlCreateButton("OK", 8, 206, 649, 57)
GUISetState(@SW_SHOW)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            $TheDate = GUICtrlRead($Date1)
            MsgBox(0,"",$TheDate)
            $OOspreadsheet = _OOoCalcBookNew()
            _OOoCalcBookSaveAs($OOspreadsheet,"C:\new.ods")
            _OOoCalcBookClose($OOspreadsheet)
            $OOspreadsheet = _OOoCalcBookOpen("C:\new.ods")
            Sleep(1000)
            _OOoCalcWriteCell($OOspreadsheet,$TheDate,"A1")
    EndSwitch
WEnd

Run and click ok to see error.

[GMK]

64-bit update: I just upgraded to LibreOffice 5.2.0.4 x64  and it seems to work fine better. I'm still trying to get SaveAs to work properly.

@keafter, I'll check out your error and see what I can find. What version of OO/LO are you running?

Edited August 4, 2016 by GMK
Additional clarification needed; OK, maybe not fine, but better.

[keafter]

Apache Open Office 4.1.2

AOO412m3(Build:9782) - Rev. 1709696

[GMK]

I'm working on updating the _OOoCalcWriteCell functions, as well as the constants for _OOoCalcNumberFormat, which don't seem to match the documentation.

In the meantime, you can use _OOoCalcWriteFormula, then use _OOoCalcNumberFormat to format the cell as a date.

Edited August 5, 2016 by GMK
Mistyped function name.

[GMK]

OK, the latest version is available here.

[GMK]

On 8/4/2016 at 1:56 AM, keafter said:

Dates don't play nicely. It adds a ' in front of the date rendering it useless.

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Date.au3>
#include <OOoCalc.au3>

$Form1_1 = GUICreate("Notary Client Verification Form", 667, 273, 192, 132)
$Date1 = GUICtrlCreateDate(_DateToDayValue, 72, 40, 257, 25,$WS_TABSTOP)
$Button1 = GUICtrlCreateButton("OK", 8, 206, 649, 57)
GUISetState(@SW_SHOW)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            $TheDate = GUICtrlRead($Date1)
            MsgBox(0,"",$TheDate)
            $OOspreadsheet = _OOoCalcBookNew()
            _OOoCalcBookSaveAs($OOspreadsheet,"C:\new.ods")
            _OOoCalcBookClose($OOspreadsheet)
            $OOspreadsheet = _OOoCalcBookOpen("C:\new.ods")
            Sleep(1000)
            _OOoCalcWriteCell($OOspreadsheet,$TheDate,"A1")
    EndSwitch
WEnd

Run and click ok to see error.

Update (see first post) and try this:

expandcollapse popup

#include "OOoCalc.au3"
#include <Date.au3>
#include <WindowsConstants.au3>
#include <GUIConstantsEx.au3>

_OOoCalc_ComErrorHandler_UserFunction(_ErrFunc)

$Form1_1 = GUICreate("Notary Client Verification Form", 667, 273, 192, 132)
$Date1 = GUICtrlCreateDate(_DateToDayValue, 72, 40, 257, 25, $WS_TABSTOP)
$Button1 = GUICtrlCreateButton("OK", 8, 206, 649, 57)
GUISetState(@SW_SHOW)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            $TheDate = GUICtrlRead($Date1)
            MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, "Date", $TheDate)
            $sFileName = @MyDocumentsDir & "\new.ods"
            If FileExists($sFileName) Then FileDelete($sFileName)
            $OOspreadsheet = _OOoCalc_BookNew()
            Sleep(1000)
            _OOoCalc_WriteCell($OOspreadsheet, $TheDate, "A1")
            _OOoCalc_NumberFormat($OOspreadsheet, 36, "A1")
            _OOoCalc_BookSaveAs($OOspreadsheet, $sFileName)
            _OOoCalc_BookClose($OOspreadsheet)
            $OOspreadsheet = _OOoCalc_BookOpen($sFileName)
    EndSwitch
WEnd

; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc