Jump to content

OOo/LibO Calc UDF


GMK
 Share

Recommended Posts

You say 'set filter'.

Do you mean create new one or use already existing.

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

Link to comment
Share on other sites

The AutoFilter function. But I figured it out:

Local $oSheet = __OOoCalc_GetSheet($oWorkbook, -1)
$oRange = $oSheet.getCellRangeByPosition(0, 0, $Columns, UBound($aArray)).getRangeAddress()
$oWorkbook.DatabaseRanges.addNewByName("Filters", $oRange)
$oWorkbook.DatabaseRanges.getByName("Filters").AutoFilter = True

This enables the AutoFilter for the selected Range.

Link to comment
Share on other sites

  • 9 months later...
  • 1 month later...
On 4/26/2017 at 11:51 PM, keafter said:

......................

Side note,

It would appear that there is some code out of place. Either that or I'm not understanding the _OOoCalc_BookAttach($sFileName) function properly.

In the  _OOoCalc_BookAttach function around line 230ish I commented out

If Not FileExists($sFileName) Then Return SetError($_OOoCalcStatus_NoMatch, 1, 0)

$sFileName = "2017 Notary.ods" which will always fail in FileExists() because it's not the entire file path. However If I give it the full file path it will fail a few lines further down at:

If StringInStr($sWinTitle, $sFileName) <> 1 Then Return SetError($_OOoCalcStatus_NoMatch, 0, 0)

Because $sWinTitle = "2017 Notary.ods - Open Office Calc" and the entire filepath is not contained within that. So it seems like with BookAttach the file is already open, it is looking for the window title and therefore never the path. That's my thought process anyway. I could very easily be looking at it the wrong way though, lemme know. Back to testing the sort function.

Hi.

Sorry for going back so much with the quoted post, but I was looking for some inspiration to make _OOoCalc_BookAttach() function working in my code.

I thought I was coding something wrong, but I believe that @keafter was right when indicating the problem in the FileExists verification.

If Not FileExists($sFileName) Then Return SetError($_OOoCalcStatus_NoMatch, 1, 0)

I'm using UDF OOoCalc.au3 last modification dated 2016/11/16.

For my purpose the FileExists() verification is not needed, so my personal workaround is just skip the verification and everything works perfectly.

Link to comment
Share on other sites

  • 6 months later...

Hi.

I've made a modification to _OOoCalc_BookAttach()  in case you have 2 or more document open with same name ( in different directories ).

I use the URL to determine if the document exist.

Func _OOoCalc_BookAttach($sFileName)
    Local $oOOoCalc_COM_ErrorHandler = ObjEvent("AutoIt.Error", __OOoCalc_ComErrorHandler_InternalFunction)
    #forceref $oOOoCalc_COM_ErrorHandler
    If Not IsString($sFileName) Then Return SetError($_OOoCalcStatus_InvalidDataType, 1, 0)
    If Not FileExists($sFileName) Then Return SetError($_OOoCalcStatus_NoMatch, 1, 0)
    Local $oSM = ObjCreate("com.sun.star.ServiceManager")
    If Not IsObj($oSM) Then Return SetError($_OOoCalcStatus_GeneralError, 0, 0)
    Local $oDesktop = $oSM.createInstance("com.sun.star.frame.Desktop")
    If Not IsObj($oDesktop) Then Return SetError($_OOoCalcStatus_GeneralError, 0, 0)
    
    Local $bDocExist = False, $oReturn
    Local $oComponents = $oDesktop.getComponents().createEnumeration()
    Local $sFileURL = __OOoCalc_FileToURL($sFileName)
    While $oComponents.hasMoreElements()
        $oReturn = $oComponents.nextElement()
        If $oReturn.getURL() = $sFileURL Then
            $bDocExist = True
            ExitLoop
        EndIf
    WEnd
    If Not $bDocExist Then Return SetError($_OOoCalcStatus_NoMatch, 0, 0)
    Return SetError($_OOoCalcStatus_Success, 0, $oReturn)
EndFunc   ;==>_OOoCalc_BookAttach

Func _OOoCalc_BookGetHwnd(ByRef $oObj)
    Local $oOOoCalc_COM_ErrorHandler = ObjEvent("AutoIt.Error", __OOoCalc_ComErrorHandler_InternalFunction)
    #forceref $oOOoCalc_COM_ErrorHandler
    If Not IsObj($oObj) Then Return SetError($_OOoCalcStatus_InvalidDataType, 1, 0)
    Local $array[0]
    Local $oWindow = $oObj.CurrentController.Frame.getContainerWindow()
    If Not IsObj($oWindow) Then Return SetError($_OOoCalcStatus_GeneralError, 0, 0)
    Local $handle = HWnd($oWindow.getWindowHandle($array, 1))
    Return SetError($_OOoCalcStatus_Success, 0, $handle)
EndFunc

 

Link to comment
Share on other sites

  • 1 year later...

Dear all,

I am using the latest "OOoCalc.au3" and this works beautifully when saving the files to xls (Both OpenOffice and LibreOffice)

_OOoCalc_BookSaveAs($oCalc, "C:\test.xls",'MS Excel 97',False)

Lately, LibreOffice 7.1.3.2  comes with the option to save files to xlsx which OpenOffice do not offer so I just uninstall OpenOffice and use LibreOffice instead.

The problem is that if I do the following modifications to the codes below, nothing happens and the test.xlsx is not created. Do anyone have any idea that I did wrong?

_OOoCalc_BookSaveAs($oCalc, "C:\test.xlsx",'Excel 2007-365',False)

or 

_OOoCalc_BookSaveAs($oCalc, "C:\test.xlsx",'MS Excel 2007',False)

Thank you all!

Link to comment
Share on other sites

16 minutes ago, PnD said:

Dear all,

I am using the latest "OOoCalc.au3" and this works beautifully when saving the files to xls (Both OpenOffice and LibreOffice)

_OOoCalc_BookSaveAs($oCalc, "C:\test.xls",'MS Excel 97',False)

Lately, LibreOffice 7.1.3.2  comes with the option to save files to xlsx which OpenOffice do not offer so I just uninstall OpenOffice and use LibreOffice instead.

The problem is that if I do the following modifications to the codes below, nothing happens and the test.xlsx is not created. Do anyone have any idea that I did wrong?

_OOoCalc_BookSaveAs($oCalc, "C:\test.xlsx",'Excel 2007-365',False)

or 

_OOoCalc_BookSaveAs($oCalc, "C:\test.xlsx",'MS Excel 2007',False)

Thank you all!

Never mind all. I just figured it out and this should work excellently

_OOoCalc_BookSaveAs($oCalc, "C:\test.xlsx",'Calc MS Excel 2007 XML',False)

I hope this will help anyone who needs it.

Link to comment
Share on other sites

  • 2 months later...

@GMK

Excellent work.

There is a bug in setFormula when you're writing a HYPERLINK. Possibly other formulas but I was attempting _OOoCalc_WriteFormula and realized it seemed to be cleaning the variable before setting it.  For example:

 

Local $sFormula = '=HYPERLINK("https://www.google.com","My Link")'
_OOoCalc_WriteFormula($oCalc, $sFormula, "A1")

would look fine in the Calc input line but show an Err:508 in the cell. I then realized if I go into Calc and add a space to the end of the input line the error would disappear and the link would work.

Until the bug is fixed, here is the trick to put a HYPERLINK in your calc spreadsheet:

Local $sFormula = '=HYPELINK("https://www.google.com","My Link")'
_OOoCalc_WriteFormula($oCalc, $sFormula, "A1")
_OOoCalc_ReplaceInRange($oCalc, "HYPELINK", "HYPERLINK")

It's all about the 'hype' :dance:

OK you got me it's not about the hype but it turns out after you write your formula into the cell, the replace function seems to undo whatever damage it does to the formula.

 

Edited by NassauSky
Link to comment
Share on other sites

@NassauSky, thanks for the bug report!  I'll have to look into it.

Edit, does this work any better?

Local $sFormula = '=HYPERLINK("https://www.google.com";"My Link")'
_OOoCalc_WriteFormula($oCalc, $sFormula, "A1")

https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_HYPERLINK_function

Edited by GMK
Possible solution added
Link to comment
Share on other sites

  • 11 months later...
39 minutes ago, lapoelkan12 said:

hi  , if i write  a  calc file  and  after i reopen  how can  restart at point that i left ?  thanks

From your not entirely clear statement, I conclude that you must to store position, and then restore position to the same cell.

 

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

Link to comment
Share on other sites

Something like this? You "simply" need to translate it to autoIt ;) 

https://ask.libreoffice.org/t/basic-calc-how-to-get-address-of-last-cell-used-with-content/46656

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Don't know the oO UDF but usually functions return a value and/or set @error and @extended.
Please check this values.

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 11 months later...

Hi all,

How can I open OpenOffice Calc hidden so I can then reposition the window with WinMove and then show the OpenOffice calc window? 

The following doesn't work:

#include ".\Required-AU\OOoCalc.au3"    ; For Open Office / Libre Office Spreadsheet
#include <MsgBoxConstants.au3>

Global $oCalc
Local $fExcelTemplate = @ScriptDir & "\Required\PO.xls"

MsgBox($MB_OK, "Open Office", "Starting OO Hidden")
Local $oCalc = _OOoCalc_BookOpen($fExcelTemplate, True)
    If @error Then Exit MsgBox($MB_TOPMOST, "Notice", "Unable to open Book: " & $fExcelTemplate)

Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase
SplashTextOn("Wait","Waiting for OO To Start")
    WinWait("Office Calc")
    WinActivate("Office Calc")
SplashOff()

SplashTextOn("Wait","Moving Hidden Window")
    WinMove("Office Calc", "", 1000, 0, 800, 800) ;x,y,w,h
SplashOff()

SplashTextOn("Wait","In 3 secs will show OO window")
    Sleep(3000)
SplashOff()
WinSetState("Office Calc", "", @SW_SHOW) ;Now shows Office Calc but it's all grey inside with no cells or icons

MsgBox($MB_OK, "Open Office", "Click OK to close the sheet")
_OOoCalc_BookClose($oCalc)

 

 

Edited by NassauSky
Clarifying Code and added #include
Link to comment
Share on other sites

  • 4 weeks later...
On 7/14/2023 at 12:28 PM, NassauSky said:

How can I open OpenOffice Calc hidden so I can then reposition the window with WinMove and then show the OpenOffice calc window?

Can't test right now, but try this:

#include ".\Required-AU\OOoCalc.au3"    ; For Open Office / Libre Office Spreadsheet
#include <MsgBoxConstants.au3>

Global $fExcelTemplate = @ScriptDir & "\Required\PO.xls"

MsgBox($MB_OK, "Open Office", "Starting OO Hidden")
Global $oCalc = _OOoCalc_BookOpen($fExcelTemplate, True)
If @error Then Exit MsgBox($MB_TOPMOST, "Notice", "Unable to open Book: " & $fExcelTemplate)
Global $oCalcWin = $oCalc.CurrentController.Frame.ContainerWindow

Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase
SplashTextOn("Wait","Waiting for OO To Start")
WinWait("Office Calc")
WinActivate("Office Calc")
SplashOff()

SplashTextOn("Wait","Moving Hidden Window")
$oCalcWin.setPosSize(1000, 0, 800, 800, 15) ;x,y,w,h,flag=15
SplashOff()

SplashTextOn("Wait","In 3 secs will show OO window")
Sleep(3000)
SplashOff()
$oCalcWin.Visible = True

MsgBox($MB_OK, "Open Office", "Click OK to close the sheet")
_OOoCalc_BookClose($oCalc)
Edited by GMK
ContainerWindow object
Link to comment
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
 Share

×
×
  • Create New...