I wrote a PHP script (in CLI mode) that does a job that other languages could do as well: parsing small XML files in mass witch containing bibliographic records of printed works, and make a perfectly readable CSV file by Calc. I chose PHP (rather than Python or Perl) only because I am personally more comfortable with this language to get to know it a bit. In any case, that is not the problem; unlike the LibreOffice/OpenOffice Basic, reading XML is extremely easy to do (with Xpath) even if it is understood that the thing is possible with the DOM that knows the Basic. Exporting CSV to PHP is also very easy, without having to worry about encoding quotation marks or preceeding backslash characters. Everything is automatic and you can worry about less boring things. PHP, finally, is able to open, read and close hundreds of small XML files at a speed of execution that the Basic is not able to offer us.
Why export CSV to Calc? To use the spreadsheet as I like: a very big dialog box... I understand that some people will find it vÊrŶ bÂdly; but it is like eating snails: I find that very well... And I do not think that I must to apologise for that! We start by importing CSV into Calc. Note that this import respects perfectly the date (this is normal because it is defined as such), but also the time too, which is not explicit in the documentation.
We insert a first blank line and we add some very funny titles. As we like hypertext links, we also make sure that they can use macros at this point, for example to cause a sort of increasing or decreasing line sorting depending on whether it is on a date or a title. Similarly, we insert a first column receiving as many checkboxes that the CSV has lines. The automation of all this comes from the childhood of Art. As the CSV is the fruit of a nice bazaar of XML files, we call a sort's macro, just to give a little hold all this at the first display...
Where it starts to get funny is when creating a custom style of cells. It is extremely simple to do with the API and it is just as easy to set up in conditional formatting. Result: clicking on the checkbox radically changes the appearance of the spreadsheet line, which is naturally the most beautiful effect...
The goal of the game is not only to be able to very clearly visualize the lines of items, but also to select them as quickly as possible, via the mouse, or better: the keyboard.
I have already shown that it is quite possible to program check boxes with the HYPERLINK function. However, when you have dozens of selections to make with the mouse, it is particularly long and, again, very boring. So I thought about whether it would not be possible to intercept a key on the keyboard (for example, the spacebar) to check (or uncheck) checkboxes in the first column. So I had to add to my little joke a listener who does the work. This thing is very easy for a simple reason: in this case, we do not use Calc to edit cells, but just to select items, much like in a very large drop-down list. I chose the spacebar because it is a huge and very common key so famous for this use. But it no longer allows to be used in editing, which, as we have seen, is not the proposed purpose.
The lines of the CSV import are naturally scrolled with the arrow keys. It does not matter which column the cursor is on; this precise point has no effect on the line selection action.
The selection can be of three types:
- Only one cell is selected: this is the most common case. Pressing the spacebar will switch the checkbox in the first column, which, thanks to the conditional formatting created by programming, will still have the effect of changing the appearance of the line. I think we can call this as "enriched CSV"...
- A block of cells is selected: it is very easy to do by adding the [Shift] key with the arrow keys when moving. The switchover is the same as for the individual lines: what is not checked becomes it; and vice versa.
- Several non-contiguous cell blocks are selected: in this case, it is necessary to use the mouse, playing with the [Shift] key (contiguous selection) or the [Ctrl] key (discontinuous selection). The use of this kind of thing is obviously not unknown to people who have had the patience to read me so far...
It is obviously up to everyone to process the checked lines at their convenience. I have, in the example published here, only put an example that counts the number of checkboxes actually checked since the treatment in itself goes far beyond the only subject of the selection. The analysis of checked cells is very easy to do with the API and can, of course, be followed by all imaginable applications.
Because it is obvious that a CSV file can not, by its nature, contain any macro, I leave on this thread of forum two files: the first is an example of CSV file and the second contains a library of two modules. The "common" module contains procedures that may be useful, some of which are from this forum. The "selectionLines" module includes, among other things, the "main" procedure that should be launched to open the CSV file whose URL has been previously noted in the "myCSVFile" constant at the beginning of the module.
In the same way, I am the first to recognize that what is proposed here is not necessarily original in these parts, often coming from examples of code already published. But it is their assembly that seems interesting to me, especially if the purpose of this large selection of items is given to inexperienced users of the spreadsheet. However, it is still possible to click on the hyperlink checkboxes individually, as well as to select ranges of checkboxes of the same value through the fill down. It is all of these selection’s techniques that allow as much to act very globally as very individually.
Have fun!
Module selectionLines :
Code: Select all
' ╔══════════════════════════════════════════════════════════════════════════════════════════════════════════╗
' ║ Select spreadsheet lines ║█
' ║ ║█
' ║ Procedures written by S.M.C.J. Licence : WTFPL. ║█
' ╚══════════════════════════════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Option Explicit
Global myKeyHandler As Object
Const myCSVFile = "/here/you/have/to/set/the/path/of/the/file/named/myTempoCSV.csv"
' ╔══════════════════════════════════════════════════════════════════════════════════════════════════════════╗
' ║ Open and transform a CSV file for select items. ║█
' ╚══════════════════════════════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Sub main
Dim myCSVdocument As Object, mySheet As Object
Dim myConditionalFormat As Object, myStyle As Object, myCells As Object
Dim myFormula(1) As String
Dim myLastLine As Long
Dim myOptions(2) As New com.sun.star.beans.PropertyValue
Dim myShadow As New com.sun.star.table.ShadowFormat
Dim myBorder As New com.sun.star.table.BorderLine
If Not fileExists(myCSVFile) then
msgBox "The file" & chr(13) & chr(13) & myCSVFile & chr(13) & chr(13) & "do not exists on your system." & chr(13) & chr(13) & _
"Please set the ""myCSVFile"" Constant to the begining of the code," & chr(13) & "and try again.", 16, "File do not exists"
exit Sub
End If
' Open the CSV file.
myOptions(0).Name = "FilterName" : myOptions(0).value = "Text - txt - csv (StarCalc)"
myOptions(1).Name = "FilterOptions" : myOptions(1).value = "44,34,76,1,1/2/2/4/3/2"
' 44 = Field separator : comma.
' 34 = Text field delimiter : quote character ".
' 76 = Encoding : Unicode (UTF-8).
' 1 = First line to be treated : line 1.
' 1/2 = Column format : column 1 is formating in TEXT (2).
' 2/4 = Column format : column 2 is formating in DATE (4) JJ/MM/AA (french disposition) ; the import of TIME what is spewed by PHP is still...
' 3/2 = Column format : column 3 is formating in TEXT (2).
myCSVdocument = starDesktop.loadComponentFromURL(convertToURL(myCSVFile), "_blank", 0, myOptions())
mySheet = myCSVdocument.sheets(0)
myFormula(0) = "=HYPERLINK(""vnd.sun.star.script:selectSpreadSheetLines." ' Left part of the formula's spreadsheet.
myFormula(1) = "?language=Basic&location=application" ' Right part of the formula's spreadsheet.
With mySheet
.rows.insertByIndex (0, 1)
.rows(0).horiJustify = com.sun.star.table.CellHoriJustify.CENTER
.rows(0).charWeight = 150
.rows(0).cellBackColor = RGB(255, 200, 200) ' With pink flesh.
.columns.insertByIndex(0, 1)
.columns(0).horiJustify = com.sun.star.table.CellHoriJustify.CENTER
.columns(0).width = 1600
.columns(1).width = 3600
.columns(1).horiJustify = com.sun.star.table.CellHoriJustify.CENTER
.columns(2).horiJustify = com.sun.star.table.CellHoriJustify.CENTER
.columns(3).width = 8000 ' Title column.
.columns(5).width = 600 ' Not very iumportant...
.getCellByPosition(1, 0).string = "References"
.getCellByPosition(2, 0).formula = myFormula(0) & "selectionLines.callSort" & myFormula(1) & "&mySort=inputDates"" ; ""Input dates"")"
.getCellByPosition(2, 0).charColor = RGB(0 , 0 , 255) ' Blue hyperlink.
.getCellByPosition(2, 0).charUnderline = 1
.getCellByPosition(3, 0).formula = myFormula(0) & "selectionLines.callSort" & myFormula(1) & "&mySort=titles"" ; ""Titles"")"
.getCellByPosition(3, 0).charColor = RGB(0 , 0 , 255) ' Blue hyperlink.
.getCellByPosition(3, 0).charUnderline = 1
.getCellByPosition(4, 0).string = "Files of:"
.getCellByPosition(4, 0).horiJustify = com.sun.star.table.CellHoriJustify.RIGHT
.getCellByPosition(5, 0).value = 12 ' Need in a specific case (not important in this demonstration).
.getCellByPosition(5, 0).charColor = RGB(255, 0, 0) ' Red.
.getCellByPosition(6, 0).string = "Ex Libris." ' Yes, latin word; but no matter in this demonstration.
.getCellByPosition(6, 0).horiJustify = com.sun.star.table.CellHoriJustify.LEFT
.getCellByPosition(6, 0).charPosture = com.sun.star.awt.FontSlant.ITALIC
' At start, ☐ or ☒ (myType = 0) or ☑ (myType = 1) ; to choice...
.getCellByPosition(0, 1).formula = myFormula(0) & "common.checkBox" & myFormula(1) & "&myCell="" & ADDRESS(ROW(A2);COLUMN(A2);1) & ""&myType=0""; ""☐"")"
.getCellByPosition(0, 1).charHeight = 14
myLastLine = knowLastCell(.name, 1, 0)
.getCellRangeByPosition(0, 1, 0, myLastLine).fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)
End With
With myShadow
.location = com.sun.star.table.ShadowLocation.BOTTOM_RIGHT
.shadowWidth = 50
.color = RGB(128, 128, 128)
End With
myBorder.outerLineWidth = 50
With mySheet.getCellByPosition(0, 0) ' Cell call of the counting macro.
.formula = myFormula(0) & "selectionLines.countCkecks" & myFormula(1) & """ ; ""Count"")"
.charColor = RGB(0 , 0 , 255) ' Blue hyperlink.
.cellBackColor = RGB(220, 220, 220) ' Grey button.
.shadowFormat = myShadow
.bottomBorder = myBorder
.rightBorder = myBorder
.leftBorder = myBorder
.topBorder = myBorder
End With
thisComponent.currentController.freezeAtPosition(1, 1)
moveToCell(0, 1)
' Call of sorting.
myCells = mySheet.getCellRangeByPosition(0, 1, 3, myLastLine)
sortingCSVlines(myCells, 2)
' Creating a custom style ...
myStyle = thisComponent.createInstance("com.sun.star.style.CellStyle")
thisComponent.getStyleFamilies().getByName("CellStyles").insertByName("Selected line", myStyle)
With myStyle
.charColor = RGB(255, 255, 255) ' White.
.cellBackColor = RGB(255, 80, 80) ' Soft red.
.charWeight = 150 ' Bold.
End With
' ... and applying it as CONDITIONAL FORMAT.
myCells = mySheet.getCellRangeByPosition(0, 1, 25, myLastLine)
myConditionalFormat = myCells.conditionalFormat
myOptions(0).Name = "Operator" : myOptions(0).value = com.sun.star.sheet.ConditionOperator.FORMULA
myOptions(1).Name = "Formula1" : myOptions(1).Value = "IF($A1 <> ""☐"")"
myOptions(2).Name = "StyleName" : myOptions(2).Value = "Selected line"
myConditionalFormat.clear()
myConditionalFormat.addNew(myOptions())
myCells.conditionalFormat = myConditionalFormat
addKeyHandler ' Call the listener that handles the checkbox with the spacebar.
myCSVdocument.setModified(False) ' No saving request when closing the file.
End Sub
' ╔══════════════════════════════════════════════════════════════════════════════════════════════════════════╗
' ║ Sort the lines of the imported CSV. ║█
' ╚══════════════════════════════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Sub sortingCSVlines(myCells As Object, myColumn As Integer)
Dim mySortDescriptor As Variant
Dim sortConfig(0) As New com.sun.star.table.TableSortField
With sortConfig(0)
.Field = myColumn
Select Case myColumn
case 2 : .isAscending = False ' Sorting did on the "Titles".
case 3 : .isAscending = True ' Sorting did on the "Input dates".
End Select
End With
mySortDescriptor = myCells.createSortDescriptor
setPropVal(mySortDescriptor, "SortFields" , sortConfig())
setPropVal(mySortDescriptor, "IsSortColumns" , False )
setPropVal(mySortDescriptor, "CopyOutputData" , False )
setPropVal(mySortDescriptor, "IsUserListEnabled" , False )
setPropVal(mySortDescriptor, "BindFormatsToContent", False )
setPropVal(mySortDescriptor, "ContainsHeader" , False )
myCells.sort(mySortDescriptor())
End Sub
' ╔══════════════════════════════════════════════════════════════════════════════════════════════════════════╗
' ║ Call sorting from the HYPERTEXT() function of the spreadsheet. ║█
' ╚══════════════════════════════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Sub callSort(myURL As String)
Dim myCells As Object
With thisComponent.currentController.activeSheet
myCells = .getCellRangeByPosition(0, 1, 3, knowLastCell(.name, 1, 0))
End With
Select Case getArgumentFromURL(myURL, "mySort")
Case "inputDates" : sortingCSVlines(myCells, 2)
Case "titles" : sortingCSVlines(myCells, 3)
End Select
End Sub
' ╔══════════════════════════════════════════════════════════════════════════════════════════════════════════╗
' ║ keyHandler management. ║█
' ╚══════════════════════════════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Sub addKeyHandler
myKeyHandler = createUnoListener("callKeyHandler_","com.sun.star.awt.XKeyHandler")
thisComponent.currentController.addKeyHandler(myKeyHandler)
End Sub
Sub removeKeyHandler
On Error Resume Next
thisComponent.currentController.removeKeyHandler(myKeyHandler)
End Sub
Sub callKeyHandler_disposing : End Sub
Function callKeyHandler_keyPressed(myEvent) As Boolean
Dim mySelection As Object
Dim myColumn As Long, myLine As Long
Dim i As Integer, j As Integer
callKeyHandler_keyPressed = False
mySelection = thisComponent.currentSelection
If mySelection.supportsService("com.sun.star.table.CellProperties") And _
myEvent.keyCode = 1284 Then ' 1284 = spacebar.
callKeyHandler_keyPressed = True
If mySelection.supportsService("com.sun.star.table.Cell" ) Then ' Only one cell is selected.
myLine = mySelection.cellAddress.row
checkUnCheck(myLine)
myColumn = mySelection.cellAddress.column
End If
If Not mySelection.supportsService("com.sun.star.table.Cell" ) And _
mySelection.supportsService("com.sun.star.table.CellRange") Then ' A block of cells is selected.
With mySelection.rangeAddress
For i = .startRow to .endRow : checkUnCheck(i) : Next i
myColumn = .startColumn : myLine = .endRow
End With
End If
If Not mySelection.supportsService("com.sun.star.table.CellRange") Then ' Multiple blocks of non-contiguous cells are selected.
For j = 0 to uBound(mySelection.rangeAddresses)
With mySelection.rangeAddresses(j)
For i = .startRow to .endRow : checkUnCheck(i) : Next i
End With
Next j
With mySelection.rangeAddresses(uBound(mySelection.rangeAddresses))
myColumn = .startColumn : myLine = .endRow
End With
End If
moveToCell(myColumn, myLine + 1)
End If
End Function
Sub checkUnCheck(myLine As Long)
Dim myDocument As Object, mySheet As Object
Dim myFormula As String, myCheck As String
Dim myCheckPlace As Integer
myDocument = thisComponent.currentController
mySheet = myDocument.activeSheet
' Obviously does not touch the first line of title and after the last not empty line.
If ((myLine > 0) And (myLine <= knowLastCell(mySheet.name, 1, 0))) Then
myFormula = mySheet.getCellByPosition(0, myLine).formula
myCheck = mid(myFormula, len(myFormula) - 2, 1)
myCheckPlace = len(myFormula) - 2
Select Case myCheck
Case "☒" : mid(myFormula, myCheckPlace, 1) = "☐"
Case "☐" : mid(myFormula, myCheckPlace, 1) = "☒"
End Select
mySheet.getCellByPosition(0, myLine).formula = myFormula
End If
End Sub
Function callKeyHandler_keyReleased(myEvent) As Boolean
callKeyHandler_KeyReleased = True
End Function
' ╔══════════════════════════════════════════════════════════════════════════════════════════════════════════╗
' ║ Count how much lines are checked. ║█
' ╚══════════════════════════════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Sub countCkecks
Dim mySheet As Object, myFunction As Object
Dim myArguments(1) As Variant
mySheet = thisComponent.sheets(0)
myFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
myArguments(0) = mySheet.getCellRangeByName( "$A2:$A" & (knowLastCell(mySheet.name, 1, 0) + 1) )
myArguments(1) = "☒"
msgbox myFunction.callFunction("COUNTIF", myArguments) & " lines are checked.", 64, "Checked lines."
End Sub
Code: Select all
' ╔════════════════════════════════════════════════════════════════════════════════════════╗
' ║ Know the last nonempty cell of a row or a column. ║█
' ║ * If myType = 0, the search is do on the column. ║█
' ║ * If myType = 1, the search is do on the line. ║█
' ║ Source : https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=96475. ║█
' ╚════════════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Function knowLastCell(mySheetName As String, myColumnOrMyLine As Long, myType As Integer) As Long
Dim mySheet As Object, myBlock As Object, myFound As Object
On Error Goto myError
mySheet = thisComponent.sheets.getByName(mySheetName)
Select Case myType
Case 0 : myBlock = mySheet.columns(myColumnOrMyLine)
Case 1 : myBlock = mySheet.rows (myColumnOrMyLine)
End Select
myFound = myBlock.queryEmptyCells.rangeAddresses
With myFound(uBound(myFound))
Select Case myType
Case 0 : knowLastCell = .startRow - 1
Case 1 : knowLastCell = .startColumn - 1
End Select
End With
Exit Function
myError: ' If the column or the line is empty.
knowLastCell = -1
End Function
' ╔════════════════════════════════════════════════════════════════════════════════════════╗
' ║ Checkoxes management. ║█
' ║ Source : https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=93771. ║█
' ╚════════════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Sub checkBox(myURL As String)
Dim mySheet As Object, myCell As Object
Dim myCheck(1) As String, myFormula As String
Select Case cInt(getArgumentFromURL(myURL, "myType"))
Case 0 : myCheck(0) = "☐" : myCheck(1) = "☒"
Case 1 : myCheck(0) = "☐" : myCheck(1) = "☑"
End Select
mySheet = thisComponent.currentController.activeSheet
myCell = mySheet.getCellRangeByName(getArgumentFromURL(myURL, "myCell"))
myFormula = myCell.formula
If mid(myFormula, (len(myFormula) - 2), 1) = myCheck(0) Then ' Mid is here a FUNCTION.
mid(myFormula, (len(myFormula) - 2), 1) = myCheck(1) ' Mid is here an INSTRUCTION.
Else
mid(myFormula, (len(myFormula) - 2), 1) = myCheck(0) ' Mid is here an INSTRUCTION.
End If
myCell.formula = myFormula
End Sub
' ╔════════════════════════════════════════════════════════════════════════════════════════╗
' ║ Assign the value valProp to the property nomProp. ║█
' ║ Source : « Programmation OpenOffice.org : macros OOoBasic et API / Bernard Marcelly ». ║█
' ╚════════════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Sub setPropVal(descr As Variant, nomProp As String, valProp As Variant)
Dim p As Object
For each p in descr
If p.name = nomProp Then
p.Value = valProp
Exit Sub
End If
Next
' The property nomProp does not exist!
err = 423 ' Trigger error: property or method not found.
End Sub
' ╔════════════════════════════════════════════════════════════════════════════════════════╗
' ║ Move the spreadsheet cursor with the DISPATCHER to avoid selection. ║█
' ╚════════════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Sub moveToCell(x As Long, y As Long)
Dim myDispatcher As Object
Dim myArgument(0) As New com.sun.star.beans.PropertyValue
myDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
myArgument(0).Name = "ToPoint"
myArgument(0).Value = "$" & thisComponent.sheets(0).getCellByPosition(x, 0).columns.elementNames(0) & _
"$" & (y + 1)
myDispatcher.executeDispatch(thisComponent.currentController.Frame, ".uno:GoToCell", "", 0, myArgument())
End Sub
' ╔════════════════════════════════════════════════════════════════════════════════════════╗
' ║ Extracts the values of variables (text) passed in argument with the URL's call. ║█
' ║ Sources : https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=44153 ║█
' ╚════════════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Function getArgumentFromURL(sURL$, sName$) As String
On Error Goto exitErr:
Dim iStart%, i%, l%, sArgs$, a()
iStart = instr(sURL, "?")
l = len(sName)
If (iStart = 0) or (l = 0) Then Exit Function
sArgs = mid(sURL, iStart + 1) ' sArgs behind "?".
a() = split(sArgs, "&")
For i = 0 To uBound(a())
If instr(1, a(i), sName & "=", 1) = 1 Then ' Not case sensitive.
getArgumentFromURL = mid(a(i), l + 2)
Exit for
Endif
Next i
exitErr: ' Return "".
End Function