Friday, August 2, 2013

LibreOffice calc: automatically sort data upon data entry

A common task in LibreOffice (or Apache OpenOffice) calc is to sort a range of cells via Data -> Sort... where the sort criteria (columns, sort order) are defined.
If the spreadsheet is a living document where entries are deleted and new entries entered on a regular basis one would wish for this sorting to be done automatically rather than the user having to do it manually each time. In this article I show how to achieve this with macros in LibreOffice.

The very basic idea of automation is to assign a macro to an event. In case that event happens the macro is being run.

But before I get to that I'd like to outline the steps and how they are connected.

  • First define a range for the cells to be sorted
  • Then create the macro which will be triggered (it will make use of the range)
  • Assign the macro to the 'change content' event Now whenever the content of a cell is changed the macro will be triggered and run.

    Define a range

    Assume the data to be sorted are in two columns A and B. Currently I have only 4 entries but there could be more in the future. The columns also have a header in the first row.
    The range is set by
  • marking columns A and B
  • going to Data -> Define Range... and entering a name for the selection and finally clicking Add and OK. I chose the name 'MyData'.

    Create a macro

    It would suffice to create one macro but I opted to split the macro into two, one being of a more general reusable nature and the other more specific to the event.

    The first macro 'SortRange' sorts a range by its second column in descending order using the first row as header i.e. not part of the actual sort. The macro gets a range object as parameter and thus could be used for any range.

    Sub SortRange( oRange As Variant )     
    rem Sorts range 'sRange' by column 2 in descending order
    rem (assumes that columns have labels in first row)
    
    rem ----------------------------------------------------------------------
    rem define variables
    dim document   as object
    dim dispatcher as object
    rem ----------------------------------------------------------------------
    rem get access to the document
    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    
    rem ----------------------------------------------------------------------
    rem Select the range
    dim args1(0) as new com.sun.star.beans.PropertyValue
    args1(0).Name = "ToPoint"
    args1(0).Value = oRange.AbsoluteName
    
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    
    rem ----------------------------------------------------------------------
    dim args2(7) as new com.sun.star.beans.PropertyValue
    args2(0).Name = "ByRows"
    args2(0).Value = true
    args2(1).Name = "HasHeader"
    args2(1).Value = true
    args2(2).Name = "CaseSensitive"
    args2(2).Value = false
    args2(3).Name = "NaturalSort"
    args2(3).Value = false
    args2(4).Name = "IncludeAttribs"
    args2(4).Value = true
    args2(5).Name = "UserDefIndex"
    args2(5).Value = 0
    
    Rem Sort by the second column of the range !!!
    args2(6).Name = "Col1"
    args2(6).Value = oRange.RangeAddress.StartColumn + 2
    
    args2(7).Name = "Ascending1"
    args2(7).Value = false
    
    dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args2())
    
    End Sub
    
    Note how the Data -> Sort... Options are mapped to these properties and could be adjusted if one wanted different sorting criteria.

    The second macro 'SortRangeFilter' checks whether the change actually happened inside the range and then calls the SortRange macro. The name of the range is hardcoded in this macro and thus makes it inflexible but I couldn't find a better way (yet).
    Macros which are triggered by an event listener get passed a parameter which describes the source object. These can be different things which I need to differentiate in my code:

  • a single cell e.g. when entering a value
  • a range of cells e.g. when deleting a row
  • a set of ranges e.g. when marking and deleting two disjunct cells
    In the last cases my selection might contain cells which are not members of my defined range. In order to make my code the most flexible I apply the following logic:
  • a single cell is just a special case of range of cells thus I only need to differentiate two cases
  • for the remaining cases I determine the intersection of my named range with the selection (= source of the event). If it is empty there is nothing to do, otherwise I need to sort the range.

    Sub SortRangeFilter( oEvent As Variant )
    
    Dim sRange As String
    sRange = "MyData"
    Rem       ^^^^^^
    Rem       Hardcoded range name
    
    Rem Get the range object in order to access its boundaries
    Dim oSheet As Variant
    oSheet = ThisComponent.CurrentController.getActiveSheet()
    
    Rem Define an error handler in case the range name does not exist
    On Error Goto ErrorHandler
    Dim oCellRange As Variant
    oCellRange = oSheet.getCellRangeByName( sRange )
    
    Rem Check the type of 'oEvent'
    Rem Both support the 'queryIntersection' method
    If ( oEvent.supportsService( "com.sun.star.sheet.SheetCellRanges" ) Or _
         oEvent.supportsService( "com.sun.star.sheet.SheetCellRange" )) Then
      If oEvent.queryIntersection( oCellRange.RangeAddress ).count > 0 Then
        SortRange( oCellRange )
      Endif
    Endif
    Exit Sub
    
    ErrorHandler:
      Msgbox "SortRangeFilter: Range does not exist '" + sRange + "'"
      Exit Sub
    End Sub
    
    Note: rather than hardcoding the range name one could check all defined ranges for the current sheet and try to find the right one but there are a number of drawbacks:
  • a cell could be a member of several ranges (since ranges can overlap). Which range should be picked for sorting?
  • if multiple cells are selected (e.g. a row) there is an even higher chance of confusion. Some of the selected cells might be in range 1, others in range 2 etc. so which range is to be picked? And it is unlikely that all ranges need to be sorted.

    Both macros need to be entered in a macro library. I chose My Macros -> Standard -> Module1.

    Set event listener

    Now that the macro is in place I can set up the event trigger.
  • Right click on the sheet name and choose Sheet Events...
  • In the Assign Action window select Content changed and click Macro...
  • In the Macro Selector window navigate to (in my case) My Macros -> Standard -> Module1 and select SortRange in the list of macros and click OK
  • Back again in the Assign Action window click OK too

    Now you have it: any change in the range i.e. columns A or B will trigger a re-sort. Any other change on the sheet won't.
    If you create the range in a different place the sort will still be triggered.



    Below I'll show a couple of scenarios and how the macros behave. Assume there is a defined range 'MyData' somewhere on the spreadsheet, say C5:D10. The column headers have been entered in B5 and C5.

    Then the sheet event trigger needs to be assigned as described above and I can start entering the data.

    I begin with B6=A and C6=20. Nothing happens other than the named range being marked due to the macro.

    Then I enter B7=B and C7=34 and now the sort trigger already fires and sorts my columns and reverses the order.

    I enter another data set B8=C and C8=51
    And finally the last data B9=D and C9=40 which is moved up two rows.

    Always starting with the last setup I show various scenarios to delete cells and how the sort automatically kicks in.

    Mark a single cell and delete it
    Mark a row and delete it
    Mark some disjunct cells and delete them
    If you accidentally delete the range 'MyData' you get an error message

    Since my macro selects the data range it stays this way after the sort has finished. Of course this could be changed but I view it as an indicator that something happened, the viewer is visually attracted to the marked data range.

  • 1 comment:

    1. I feel really happy to have seen your webpage and look forward to so many more entertaining times reading here. Thanks once more for all the details.


      Data Science Training in Bangalore

      ReplyDelete