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