[Calc] Selection of multiple linear items.

Creating Extension - 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 forum is not for asking questions about writing your own macros.

[Calc] Selection of multiple linear items.

Postby Sébastien C » Sun Feb 24, 2019 3:18 pm

 
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   Expand viewCollapse view
' ╔══════════════════════════════════════════════════════════════════════════════════════════════════════════╗
' ║ 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   Expand viewCollapse view
' ╔════════════════════════════════════════════════════════════════════════════════════════╗
' ║ 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 8 times
selectSpreadsheetLines.oxt
A librairy for open CSV file.
(6.99 KiB) Downloaded 8 times
LibreOffice 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 81
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 0 guests