Calc: Handling of Outline Groups

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
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Calc: Handling of Outline Groups

Post by Lupp »

Concerning the topic viewtopic.php?f=9&t=106361 I considered how somebody wanting to suppress specific groups of columns and/or rows when printing might do it efficiently.
My conclusions were:
1. The groups must be defined persistently. Necessary to avoid tiime-consuming and error-prone handiwork.
2. Support by macro code should be helpful.
3. The applicable interface XSheetOutline may not exactly be well know to occasional users of the API.

To be clear: I generally advise against the design of sheets for data maintenance/calculations and for printing as well - except in very simple cases. Where spreadsheet documents actually are used for the creation of form-prints, labels, or the like, the data or formula-results should be separated from sheets formatted for printing. Prettyprint sheets should draw in readily calculated results by single-reference-formulas. I therefore most likely won't use the code below myself.

Anyway. The time I used is gone. Why not post the result?
Please note: If you consider to use it, please also download the demo document. This way I get a knid of tally concerning the range of possible/intended usage.
(Sorry: Due to a bug in LibreOffice my incomplete testing idn't detect an obvious error. Just now I tested in AOO 4.1.7 and got the error shown correctly. Code and example updated.)

Code: Select all

REM  *****  BASIC  *****

REM Rev: Not yet
REM V 0.1 Wolfgang Jaeger 2021-10-19

REM AbbrevGlossary
'Dor          = Designator
'ShDor; shDor = Designators of the sheets to pass (a semicolon separated list of designators preferably interpreted as numbers)

REM In most cases it will be preferable to simply run one of the callers:
REM Sub showHardCodedAllGroupsForAllSheetsTD()
REM Sub hideHardCodedAllGroupsForAllSheetsTD()
REM Sub showHardCodedAllGroupsForActiveSheetTD()
REM Sub hideHardCodedAllGroupsForActiveSheetTD()
REM Sub showLevel1GroupsHardCodedALLSheetsColsAndRows()
REM Sub showLevel1GroupsHardCodedActiveSheetColsAndRows()
REM The prefixes to the names added below shall "help" the IDE and the 'Run' menu to show the Sub(s) in the reasonable order.

REM I don't know a way to get explicit information about the groups existing in a sheet.
REM The interface XSheetOutline doesn't provide access. A workaround may be complicated.

REM The concept requires to pass a list of designators as a string if more than one specific sheet shall be treated.
REM Therefore sheet names containing a semicolon or being recognized by IsNumeric() can't be accepted.               !X!X!X!X!X!X!
REM The list elements are treated as 1-based sheet indices if applicable.                    Generally only use CLEAN sheet names!

REM Specials: "-1" for "AllSheets", "0" for "CurrentSheet"
REM The "0" also is eligible as a ShDor list element. "-1" is not.

REM The following callers make assignments for (assumed) frequent use-cases. The names should explicate relevant details.
Sub o0ShowHardCodedAllGroupsForAllSheetsTD()   : shDor = "-1" : doc0 = ThisComponent : o8SetGroupsShowHide(doc0, shDor, True)  : End Sub
Sub o1HideHardCodedAllGroupsForAllSheetsTD()   : shDor = "-1" : doc0 = ThisComponent : o8SetGroupsShowHide(doc0, shDor, False) : End Sub
Sub o2ShowHardCodedAllGroupsForActiveSheetTD() : shDor = "0"  : doc0 = ThisComponent : o8SetGroupsShowHide(doc0, shDor, True)  : End Sub
Sub o3HideHardCodedAllGroupsForActiveSheetTD() : shDor = "0"  : doc0 = ThisComponent : o8SetGroupsShowHide(doc0, shDor, False) : End Sub
REM For the following:: level=1: Top level groups shown, nested groups hidden. colsRowsBoth="0;1": "0" for columns "1" for rows.
Sub o4ShowLevel1GroupsHardCodedALLSheetsColsAndRows() : shDor = "-1" : level = 1     : _
    colsRowsBoth = "0;1" : doc0 = ThisComponent       : o9ShowLevelGroupsColsRowsBothWorker(doc0, shDor, level, colsRowsBoth)  : End Sub
Sub o5ShowLevel1GroupsHardCodedActiveSheetColsAndRows() : shDor = "0" : level = 1    : _
    colsRowsBoth = "0;1" : doc0 = ThisComponent       : o9ShowLevelGroupsColsRowsBothWorker(doc0, shDor, level, colsRowsBoth)  : End Sub

REM Extra: For calls by clicking on a sensitive area in a ToolBar. Modifier regarded where applicable::
Sub o7SheetGroupingHelperToolbarOrientedModifiable(pTbEvent)
If NOT (TypeName(pTBEvent)="Integer") Then Exit Sub
tbEvent = pTBEvent \ 4096 REM REasons for this "multiplicative offset" unknown to the author of this.
REM tbEvent is an additive designator (flags style) of the used Modifier keys.
REM For WinKB:: (=0:None); 1:Shift; 2:Ctrl or rightAlt; 4:leftAlt;; AOO does not pass the value 4.
Select Case tbEvent
  Case 0: REM Show all outline groups for the active sheet.
    o2ShowHardCodedAllGroupsForActiveSheetTD()
  Case 1: REM Shift:Hide;; Hide all outline Groups for te active sheet.
    o3HideHardCodedAllGroupsForActiveSheetTD()
  Case 2: REM Ctrl:All;; Show all outline groups for All sheets.
    o0ShowHardCodedAllGroupsForAllSheetsTD()
  Case 3: REM Shift:Hide; Ctrl:All;; Show all outline groups for All sheets.
    o1HideHardCodedAllGroupsForAllSheetsTD()
  Case 4, 5: REM Alt:LevelMode; Shift ignored;; Not working in AOO.
    o5ShowLevel1GroupsHardCodedActiveSheetColsAndRows()
  Case 6, 7: REM Alt:LevelMode; Ctrl:AllSheets;  Shift ignored;; Not working in AOO.
    o4ShowLevel1GroupsHardCodedALLSheetsColsAndRows()
  Case Else:
    REM Not implemented. (To get a way to modify the level and/or the orientation, you would need a different caller.)
End Select
End Sub

REM Workers to the full extent of reasonable parameters. Can work for a different Calc document made accessible from the calling one:
Sub o8SetGroupsShowHide(pDoc As Object, pShDor As String, pShow as Boolean)
sheets = pDoc.Sheets
Select Case pShDor
  Case "-1"
    For Each oneSheet in sheets
      If pShow Then
        oneSheet.showDetail(oneSheet.RangeAddress)
      Else
        oneSheet.hideDetail(oneSheet.RangeAddress)
      End If
    Next oneSheet
  Case Else
    splitList = Split(pShDor, ";")
    u = Ubound(splitList)
    For j = 0 To u
      Redim j_element As Variant
      j_element = splitList(j)
      If IsNumeric(j_element) Then
        j_element = CDbl(j_element)
        If j_element=0 Then
          j_element = CDbl(pDoc.CurrentController.ActiveSheet.RangeAddress.Sheet)
        Else
          j_element = j_element - 1 REM The API uses 0-based indices.
        End If
        j_sheet = sheets(CInt(j_element))
      Else
        j_sheet = sheets.getByName(j_element)
      End If
      If pShow Then
        j_Sheet.showDetail(j_sheet.RangeAddress)
      Else
        j_sheet.hideDetail(j_sheet.RangeAddress)
      End If
    Next j
End Select
End Sub

Sub o9ShowLevelGroupsColsRowsBothWorker(pDoc As Object, pShDor As String, pLevel, pColsRowsBoth)
sheets = pDoc.Sheets
oriFlags = Split(pColsRowsBoth, ";")
uOri = Ubound(oriFlags) REM No error catching!
Select Case pShDor
  Case "-1"
    For Each oneSheet in sheets
      For k = 0 To uOri
        oneSheet.showLevel(pLevel, 0 + CInt(oriFlags(k)))
      Next k
    Next oneSheet
  Case Else
    splitList = Split(pShDor, ";")
    u = Ubound(splitList)
    For j = 0 To u
      Redim j_element As Variant
      j_element = splitList(j)
      If IsNumeric(j_element) Then
        j_element = CDbl(j_element)
        If j_element=0 Then
          j_element = CDbl(pDoc.CurrentController.ActiveSheet.RangeAddress.Sheet)
        Else
          j_element = j_element - 1 REM The API uses 0-based indices.
        End If
        j_sheet = sheets(CInt(j_element))
      Else
        j_sheet = sheets.getByName(j_element)
      End If
      For k = 0 To uOri
        j_Sheet.showLevel(pLevel, 0 + CInt(oriFlags(k)))
      Next k
    Next j
End Select
End Sub
Concerning the example below: As always, check the contained code for the absence of malign parts before permitting execution.
snippetExampleSheetOutline_AlsoSeeAoo106361.ods
(28.39 KiB) Downloaded 248 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply