Script crash when read empty cells in LibreOffice Calc

[cssyphus]

Trying to automate reading data from Calc (LibreOffice's spreadsheet app).

Simple script: just uses Ctrl+C to copy cell data, and ClipGet() to read the clipboard.

Problem: On empty cells, the ^c works fine... but the ClipGet() crashes the script and also crashes WinBatch Studio itself. The Debug log also abends at that point.

Note: If you comment out this line `_cell3 = clipGet()` the script will complete correctly.
Note: I updated Calc to latest version (25.2.3.2) but that made no difference

Code Select Expand

;Test of automating LibreOffice Calc
;calc_test.wbt

debugTrace(10, `c:\calc_test_DEBUG.log`)

_xls = `c:\calc_test.xlsx`
_win = `calc_test.xlsx — LibreOffice Calc` ;test.xlsx — LibreOffice Calc

Terminate(!winExist(_win), `Error:`, `Window [%_win%] Not Found. Exiting.`)

sendKeysTo(_win, `^{Home}`)
sendKeysTo(_win, `^c{Right}`)
_cell1 = clipGet()
sendKeysTo(_win, `^c{Right}`)
_cell2 = clipGet()
sendKeysTo(_win, `^c{Right}`)
_cell3 = clipGet() ;<--- COMMENT OUT THIS LINE 
sendKeysTo(_win, `^c{Right}`)
_cell4 = clipGet()


_out = _cell1 :` - `: _cell2 :` - `
if isDefined(_cell3)
	_out :=  _cell3 :` - `: _cell4
else
	_out :=  _cell4 : @CRLF: `(Cell3 clipGet() was skipped)`
endif

message(`Results:`,_out)

Attached are the sample .xlsx file used in testing, and the debug log.
For whatever reason, the zipfile will not upload. Download the zipfile from this link.

[spl]

To be clear, you are not using "com.sun.star.ServiceManager" to automate the OpenOffice calc file?

[EDIT]
If you search OpenOffice on the Tech DB you will see several contributions I made years ago. Won't help with your current issue, but I might be able to assist with a workaround for reading OpenOffice/LibreOffice data.

[cssyphus]

Thanks Stan - that is very helpful information. I wasn't aware of "com.sun.star.ServiceManager" or of your OO contributions in the Tech DB. I will look into those immediately.

Of course, going that direction significantly increases the complexity of the requisite scripting solution.

So for that reason, and others, I am still wondering:

a. what is happening with this script completely crashing WB, and
b. if there is any obvious work-around to keep the script algorithm brain-dead simple...?

No worries if there isn't, I am happy to explore com automation with LO.

But it would be nice to know if there is, for when Quick & Dirty solutions are preferred. And because I'm curious - it is a pretty weird thing to encounter.

[spl]

Thanks Stan - that is very helpful information. I wasn't aware of "com.sun.star.ServiceManager" or of your OO contributions in the Tech DB. I will look into those immediately.

Of course, going that direction significantly increases the complexity of the requisite scripting solution.

So for that reason, and others, I am still wondering:

a. what is happening with this script completely crashing WB, and
b. if there is any obvious work-around to keep the script algorithm brain-dead simple...?

No worries if there isn't, I am happy to explore com automation with LO.

But it would be nice to know if there is, for when Quick & Dirty solutions are preferred. And because I'm curious - it is a pretty weird thing to encounter.

No worries. Most of my work with OpenOffice/LibreOffice was done between 2009-2012 where I helped a company installing multiple PC's for CSR's did not want to license MS Office for those machines so I had to write multiple scripts for transition. If you are just interested in a few cells, automation would be overkill. But, if the original LO was saved as .ods then it could be easily opened in Excel (which I assume you probably have) and the cell range values sent to clipboard with a simple command and hopefully no WB crash.

[td]

But it would be nice to know if there is, for when Quick & Dirty solutions are preferred. And because I'm curious - it is a pretty weird thing to encounter.

The term "Crash" means different things to different WinBatch users, so you need to be more explicit about what happens when you call ClipGet().

Have you tried other clipboard related functions? Like ClipGetEx, ClipHasFormat, and BinaryClipGet. The most likely cause of your problems is that the contents in the clipboard are not plain text but some other data type.

[spl]

This is a bit cheesy, and maybe off topic. I created a test LibreOffice spreadsheet [attached in zip] with 4 cell value A1 = numeric A2 = formula A3= No value/null A4 = Text. It is saved in standard .ods format which Excel can open and work with. Again, maybe your goal requires using sendkeys() but in the script below the data can be easily parsed with WB clipboard functions without [in my tests] crashing or with errors

Code Select Expand

file = Dirscript():"LibreTest.ods"
if ! FileExist(file) Then Terminate(@TRUE,"File Not Found",file)
XL=0
XL = CreateObject("Excel.Application")
If XL == 0 Then Terminate(@TRUE,"Error","Cannot Load Excel")
xlMinimized = -4140 
XL.Visible          = 1  
XL.ScreenUpdating   = 1   
XL.UserControl      = 1
XL.DisplayAlerts    = 0
XL.WorkBooks.Open(file)
var = XL.ActiveSheet.Cells(1,1).Value
ClipPut(var)
Message("LibreOffice cell A1",ClipGet())
var = XL.ActiveSheet.Cells(1,2).Value
ClipPut(var)
Message("LibreOffice cell B1",ClipGet())
var = XL.ActiveSheet.Cells(1,3).Value
ClipPut(var)
Message("LibreOffice cell C1",ClipGet())
var = XL.ActiveSheet.Cells(1,4).Value
ClipPut(var)
Message("LibreOffice cell A4",ClipGet())

XL.ActiveWorkbook.Close()
XL.Quit()
XL=0
Exit

[cssyphus]

Thanks Stan, very kind of you!

Actually, I was looking at your OO contributions in the Tech DB and think I can work with that.  I just have two questions:

1. How does one read a value from a cell?  In your example script "SL_OpenOffice_Example.wbt", I added this line to the bottom of the script:  _tst = oSheet.getCellByPosition(1,1).Value but that was just a guess and did not work. (Note: I looked through all of your examples in the TechDB and did not see an example of reading a cell value, only of writing one. Perhaps, as usual, my vision is overly selective.)

2. Where can I find the document object model (or API or whatever_term_is_correct) to find the correct methods/properties for the OO object model? Is there a better, easier-to-read resource that going from link to link to link in that wiki?

Update: Nevermind the first question - I was doing something boneheaded... And yes, it was as boneheaded as putting the attempted cell read after the oDoc.dispose() method call, completely ignoring your comment "This closes everything"

And for Tony:

The term "Crash" means different things to different WinBatch users, so you need to be more explicit about what happens when you call ClipGet().

WinBatch Studio is gone. Vanishes. Abrupt close, no error message. No longer in Task Manager. All WBS unsaved changes lost (i.e. WBS does not do any of its shutdown procedures - for e.g., if a new document was opened in an editor window, that document is not present when WBS restarted). Tony, may I request that you try it on your side (I included script and link to xlsx) and confirm results? It would be great to know if the problem is just on my side.

[spl]

Thanks Stan, very kind of you!

Actually, I was looking at your OO contributions in the Tech DB and think I can work with that.  I just have two questions:

1. How does one read a value from a cell?  In your example script "SL_OpenOffice_Example.wbt", I added this line to the bottom of the script:  _tst = oSheet.getCellByPosition(1,1).Value but that was just a guess and did not work. (Note: I looked through all of your examples in the TechDB and did not see an example of reading a cell value, only of writing one. Perhaps, as usual, my vision is overly selective.)

2. Where can I find the document object model (or API or whatever_term_is_correct) to find the correct methods/properties for the OO object model? Is there a better, easier-to-read resource that going from link to link to link in that wiki?

Outside issue with Tony, please test my last posted script. If you are looking to work with LO files as automated with .net beans etc... I can forward you a multiple of scripts, mostly dealing with the calc files, and trust me transitioning an Excel Pivot Table to OpenOffice was not a walk in the park.

[td]

WinBatch Studio is gone. Vanishes. Abrupt close, no error message. No longer in Task Manager. All WBS unsaved changes lost (i.e. WBS does not do any of its shutdown procedures - for e.g., if a new document was opened in an editor window, that document is not present when WBS restarted). Tony, may I request that you try it on your side (I included script and link to xlsx) and confirm results? It would be great to know if the problem is just on my side.

Your experience suggests, assuming you are using the WBS 32-bit debugger, a memory corruption problem. It might be a WIL problem or a Windows API problem. Either way, it is very likely a data driven problem, which makes it very difficult to reproduce.

We will keep it in mind. We will also look into it further as time permits.

[cssyphus]

Either way, it is very likely a data driven problem, which makes it very difficult to reproduce.

I would be grateful if someone with LibreOffice Calc installed would try to reproduce the result - it only takes a moment to copy/paste the script example (first post in this thread) and to download the sample xlsx file (or you can reproduce it: it's just an xlsx, created in Calc, with text values in A1, B1 and D1, thassit.) Just confirm that WinBatch Studio crashes on your side also when the script tries to read the empty C1 cell. I would feel better knowing it's not just me.

[td]

It is a LibreOffice Calc coding error. The application places the Unicode text format indicator on the clipboard but does not provide any data. Windows considers this an error. Unfortunately, WinBatch's ClipGet does not catch the Windows error, a WinBatch coding error. Curiously, other WIL clipboard functions catch the error and display an error message. ClipGet will be modified to do the same.

And thanks for reporting the problem.

[td]

Neglected to mention that you can use the BinaryClipGet function with format 13. You would need to use ErrorMode to trap the error that it will generate on your blank cell.

A rough example:

Code Select Expand

mode = ErrorMode(@off)
size = BinaryClipGet(0, 13)
if LastError() || size == 0
   _cell3 = ''
else
   bb=BinaryAlloc(size)
   BinaryClipGet(bb, 13)
   if LastError() then _cell3 = ''
   else _cell3 = BinaryPeekStrW(bb, 0, size)
   BinaryFree(bb)
endif
ErrorMode(mode)   
 

Of course, it makes more sense to run with Stan's COM Automation idea.

You could also likely eliminate the ErrorMode and LateError calls because testing the size variable for a zero value should be sufficient. Think of the above as the paranoid version.

[cssyphus]

Thank you Tony, that's what I was looking for.

I followed Stan's (and your) advice and completed the app using Stan's COM automation code, but I wanted to know how to handle such a case in future. I didn't remember about BinaryClipGet() - sounds like that would solve the crash problem, which is all I need to know. Many thanks, as ever.

[td]

I inadvertently omitted the first two lines in the example script snippet above. I added the missing lines to the example.

[spl]

Glad the 'crash' issue may be resolved. As for using WB with LibreOffice directly... This last post in 2013 looked into using .Net/CLR to work with .ODF files. If the issue here was truly to understand why WB might crash from a clipboard function [and LibreOffice calc was just used as an example] Then read no further. But LibreOffice is widely used and has become more sophisticated, WB can probably handle it.

https://forum.winbatch.com/index.php?topic=217.msg778#msg778

[spl]

For kicks, the script below will create and save a blank calc spreadsheet with latest edition of LibreOffice, using the com.sun.star.ServiceManager. Note: while oDoc.dispose() will close the current window it will not completely close LibreOffice, while the sendkeys appears to.

Code Select Expand

;Winbatch 2025A - Create Blank LibreOffice calc
;Stan Littlefield, 5/5/2025
;Requires LibreOffice to be installed
;========================================================================
IntControl(73,1,0,0,0)
gosub udfs
defaults()

;check for OpenOffice Service Manager, otherwise quit
If ! IsInReg("com.sun.star.ServiceManager") Then Terminate(@TRUE,"Closing...","LibreOffice Services Not Registered")
;create initial objects
oSM = CreateObject("com.sun.star.ServiceManager")
oDesk= oSM.createInstance("com.sun.star.frame.Desktop")
cFile=Dirscript():"LibreCalc"  ;note: no extension
odf = cFile:".ods"
if fileexist(odf) Then filedelete(odf)
;create a new book
cellempty=0
celltext=2
cellvalue=1
cellformula=3
NewWkb()
Message("Success","Blank LibreOffice calc created")
cURL = Cvt2Url(odf)
oDoc.storeAsURL( cURL, aBlank ) 
if fileexist(odf)
   Display(2,"File Saved",odf)
else
   Display(2,"Oh Oh","File Could Not Be Saved")
endif
SendKeysTo("~LibreOffice Calc","^q")
;oDoc.dispose()
defaults()
Exit
;========================================================================

:WBERRORHANDLER
geterror()
defaults()
Terminate(@TRUE,"Error Encountered",errmsg)

;========================================================================
:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine

#DefineSubRoutine NewWkb()
;create 'empty' property value array used with several methods
aBlank = ArrDimension(1)
aBlank[0]=makeprop(oSM,"","")

;create MS Office Excel filter property value, for later use 
aXL = ArrDimension(1)
aXL[0]=makeprop(oSM,"FilterName","MS Excel 97")

;create Date Format
aDate = ArrDimension(1)
aDate[0]=makeprop(oSM,"NumberFormat","mm/dd/yyyy")

;Create a Blank workbook
oDoc = oDesk.loadComponentFromURL( "private:factory/scalc", "_blank", 0, aBlank )
Return(1)
#EndSubRoutine

#DefineFunction Cvt2Url(cInfile)
Return( StrCat("file:///",StrReplace(cInfile,"\","/"))  )
#EndFunction

#DefineFunction isInReg(cProg)
Return( RegExistKey(@RegClasses,cProg) )
#EndFunction

#DefineFunction makeProp(oSM,cName,uValue)
oStruct = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
oStruct.Name = cName
oStruct.Value = uValue
Return(oStruct)
#EndFunction

#DefineSubRoutine defaults()
oCell=0
oCursor=0
oDoc=0
oObj=0
oDesk=0
oSM=0
Return(1)
#EndSubRoutine

Return
;========================================================================

[spl]

just for more kicks... this fills cells with data before saving. There are irritating issues with LibreOffice in terms of date formats, but the .ods sheet works fine as well in Excel.

Code Select Expand

;Winbatch 2025A - Create LibreOffice calc
;Stan Littlefield, 5/5/2025
;Requires LibreOffice to be installed
;========================================================================
IntControl(73,1,0,0,0)
gosub udfs
defaults()

;check for OpenOffice Service Manager, otherwise quit
If ! IsInReg("com.sun.star.ServiceManager") Then Terminate(@TRUE,"Closing...","LibreOffice Services Not Registered")
;create initial objects
oSM = CreateObject("com.sun.star.ServiceManager")
oDesk= oSM.createInstance("com.sun.star.frame.Desktop")
cFile=Dirscript():"LibreCalc"  ;note: no extension
odf = cFile:".ods"
if fileexist(odf) Then filedelete(odf)
;create a new book
cellempty=0
celltext=2
cellvalue=1
cellformula=3
NewWkb()

;reference first worksheet, rename it from Sheet1
nameSheet("Sheet1","Sales_Data")

;Delete other Sheets  (by default Sheet2,Sheet3)
If oSheets.hasByName("Sheet2") Then oSheets.removeByName("Sheet2")
If oSheets.hasByName("Sheet3") Then oSheets.removeByName("Sheet3")

;populate cells with data (columns and rows are 0-based)
;OpenOffice uses c,r designation rather than r,c which Excel uses
;PutSheet is a UDF to work with different input methods
PutSheet(0,0,1,"Month")
PutSheet(1,0,1,"Sales")
PutSheet(2,0,1,"End Date")
PutSheet(0,1,1,"Jan")
PutSheet(0,2,1,"Feb")
PutSheet(0,3,1,"Mar")
PutSheet(0,4,1,"Apr")
PutSheet(0,5,1,"May")
PutSheet(0,6,1,"Jun")
PutSheet(0,7,1,"Jul")
PutSheet(0,8,1,"Aug")
PutSheet(0,9,1,"Sep")
PutSheet(0,10,1,"Oct")
PutSheet(0,11,1,"Nov")
PutSheet(0,12,1,"Dec")
PutSheet(1,1,2,3826.37)
PutSheet(1,2,2,3504.21)
PutSheet(1,3,2,2961.45)
PutSheet(1,4,2,2504.12)
PutSheet(1,5,2,2713.98)
PutSheet(1,6,2,2248.17)
PutSheet(1,7,2,1802.13)
PutSheet(1,8,2,2003.22)
PutSheet(1,9,2,1502.54)
PutSheet(1,10,2,1207.68)
PutSheet(1,11,2,1319.71)
PutSheet(1,12,2,786.03)
PutSheet(1,12,2,786.03)
PutSheet(2,1,"C2","=DATE(2024;01;31)")
PutSheet(2,2,"C3","=DATE(2024;02;28)")
PutSheet(2,3,"C4","=DATE(2024;03;31)")
PutSheet(2,4,"C5","=DATE(2024;04;30)")
PutSheet(2,5,"C6","=DATE(2024;05;31)" )
PutSheet(2,6,"C7","=DATE(2024;06;30)")
PutSheet(2,7,"C8","=DATE(2024;07;31)")
PutSheet(2,8,"C9","=DATE(2024;08;31)")
PutSheet(2,9,"C10","=DATE(2024;09;30)" )
PutSheet(2,10,"C11","=DATE(2024;10;31)")
PutSheet(2,11,"C12","=DATE(2024;11;30)")
PutSheet(2,12,"C13","=DATE(2024;12;31)")

oFormats = oDoc.getNumberFormats() 
oLocale = oSM.Bridge_GetStruct("com.sun.star.lang.Locale")
nFormat = oFormats.getStandardFormat( 2, oLocale ) ;com.sun.star.util.NumberFormat.DATE
oCell = oSheet.getCellRangeByName( "C2:C13" ) 
oCell.NumberFormat = nFormat 

;currency
nFormat = oFormats.getStandardFormat( 8, oLocale ) ;com.sun.star.util.NumberFormat.CURRENCY
oCell = oSheet.getCellRangeByName( "B2:B13" ) 
oCell.NumberFormat = nFormat 

;change the background color for the top row
oCell = oSheet.getCellRangeByName( "A1:C1" ) 
oCell.CellBackColor = OORGB(0, 255, 64)


Message("Success","LibreOffice calc created")
cURL = Cvt2Url(odf)
oDoc.storeAsURL( cURL, aBlank ) 
if fileexist(odf)
   Display(2,"File Saved",odf)
else
   Display(2,"Oh Oh","File Could Not Be Saved")
endif
SendKeysTo("~LibreOffice Calc","^q")
;oDoc.dispose()
defaults()
Exit
;========================================================================

:WBERRORHANDLER
geterror()
defaults()
Terminate(@TRUE,"Error Encountered",errmsg)

;========================================================================
:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine

#DefineSubRoutine NewWkb()
;create 'empty' property value array used with several methods
aBlank = ArrDimension(1)
aBlank[0]=makeprop(oSM,"","")

;create MS Office Excel filter property value, for later use 
aXL = ArrDimension(1)
aXL[0]=makeprop(oSM,"FilterName","MS Excel 97")

;create Date Format
aDate = ArrDimension(1)
aDate[0]=makeprop(oSM,"NumberFormat","MM/DD/YYYY")
;aDate[0]=makeprop(oSM,"NumberFormat",36)

;Create a Blank workbook
oDoc = oDesk.loadComponentFromURL( "private:factory/scalc", "_blank", 0, aBlank )
Return(1)
#EndSubRoutine

#DefineFunction Cvt2Url(cInfile)
Return( StrCat("file:///",StrReplace(cInfile,"\","/"))  )
#EndFunction

#DefineFunction isInReg(cProg)
Return( RegExistKey(@RegClasses,cProg) )
#EndFunction

#DefineFunction makeProp(oSM,cName,uValue)
oStruct = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
oStruct.Name = cName
oStruct.Value = uValue
Return(oStruct)
#EndFunction

#DefineSubRoutine nameSheet(ref,name)
oSheets = oDoc.getSheets()
If Isnumber(ref)
   oSheet = oSheets.getByIndex( ref ) ;Sheet 1
Else
   oSheet = oDoc.getSheets().getByName( ref )
Endif   
oSheet.Name = name
Return(1)
#EndSubRoutine

#DefineSubRoutine PutSheet(c,r,nMode,cData)

If isNumber(nMode)
   If nMode==1 Then oSheet.getCellByPosition( c, r ).setString( cData )
   If nMode==2 Then oSheet.getCellByPosition( c, r ).setValue( cData )
   If nMode==3 Then oSheet.getCellByPosition( c, r ).setFormula( cData ) 
Else
   oSheet.getCellRangeByName( nMode ).setFormula( cData ) 
Endif
Return(1)
#EndSubRoutine

#DefineFunction OORGB(r,g,b)
   Return((r & 255) * 65536) + ((g & 255) * 256) + (b & 255)
#EndFunction

#DefineSubRoutine defaults()
oCell=0
oCursor=0
oDoc=0
oObj=0
oDesk=0
oSM=0
Return(1)
#EndSubRoutine

Return
;========================================================================

[td]

Thank you Tony, that's what I was looking for.

I followed Stan's (and your) advice and completed the app using Stan's COM automation code, but I wanted to know how to handle such a case in future. I didn't remember about BinaryClipGet() - sounds like that would solve the crash problem, which is all I need to know. Many thanks, as ever.

I also neglected to mention that the ClipGet problem will be addressed in an upcoming WinBatch release.

[spl]

I also neglected to mention that the ClipGet problem will be addressed in an upcoming WinBatch release.

Yeah, and I apologize about throwing in the LibreOffice stuff. It is just that the code, written in 2009, with only changes to error level and terminate() function, still worked correctly.

[kdmoyers]

<< the LibreOffice stuff. It is just that the code, written in 2009, with only changes to error level and terminate() function, still worked correctly. >>
Indeed it does work, and was very useful to me.  Thanks for posting Stan.
Hypothetically, for future reference, do you accept commission proposals? I have a project that may veer into creating LibreOffice sheets as output.
-Kirby

[spl]

Indeed it does work, and was very useful to me.  Thanks for posting Stan.
Hypothetically, for future reference, do you accept commission proposals? I have a project that may veer into creating LibreOffice sheets as output.
-Kirby

I can send you a proof of concept script, with more functionality, and if hypothetically you might need assistance in the future, be pleased to discuss.

[kdmoyers]

Thanks Stan I'd appreciate that.  admin@guden.com
Still waiting for the politics to play out here. Complex discussion omitted, some folks want spreadsheet output, some want regular paper reports.  Waiting to see what shakes out.  And it looks like, with your research, I *could* do spreadsheet output.

[spl]

Thanks Stan I'd appreciate that.  admin@guden.com
Still waiting for the politics to play out here. Complex discussion omitted, some folks want spreadsheet output, some want regular paper reports.  Waiting to see what shakes out.  And it looks like, with your research, I *could* do spreadsheet output.

Sent. I think getting data from other sources, and Pivot Tables are what some would want from LibreOffice Calc. As I wrote earlier, it is actually pretty robust, one issue being most responses on AskLibre, StackOverflow, SuperUser usually end up with directions on how to perform the task from the app menus rather than from code. But I feel there is enough in the script/xml recordset I sent to fulfill most requirements.