[Calc] Selection of multiple linear items.

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

[Calc] Selection of multiple linear items.

Post by Sébastien C »

 
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...
The API makes it easy to detect differences in type of selection since the services are not necessarily the same. The treatment, therefore, is adapted to each of the three situations.

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!
:mrgreen:

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
Module common :

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
 
Attachments
myTempoCSV.csv
An example of CSV file.
(26.89 KiB) Downloaded 300 times
selectSpreadsheetLines.oxt
A librairy for open CSV file.
(6.99 KiB) Downloaded 265 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Post Reply