OpenOffice.info

Code: Alles auswählen

fstring = "A%s:A%s"

hideranges = {
                        "Hans":     fstring % (5, 10),   # Zeilen 5 bis 10
                        "Heiner":   fstring % (10, 14), #Zeilen 11 bis 15
                        "August":   fstring % (15, 20)  #usw.
                        }

def hide_rows_depend_on_B4( event ):
    """
    blendet die zu dem Namen gehörenden Zeilen
    aus dem Dictionary oben aus -
    es muss mit dem Tabellenereignis "Inhalt geändert"
    verknüpft werden
    """
    sheet = event.getSpreadsheet()
    if event.AbsoluteName == '$%s.$%s' %( sheet.Name, "B$4" ):
        sname = event.getString()
        namerange = sheet.getCellRangeByName
        for key , nrange in hideranges.items():
            rows = namerange( nrange ).Rows
            rows.IsVisible = key != sname

Code: Alles auswählen

context = XSCRIPTCONTEXT

def einstellungen():
    """
    Bezieht die Einstellungen aus Tabelle2.A2:A20
    Spalte A        Spalte B

    von-bis          Name,Name2,etc.
    usw....
    """
    doc = context.getDocument()
    esheet = doc.Sheets.getByIndex(1)  #Tabelle2
    drange = esheet.getCellRangeByName("A2:B20")
    rawdata = drange.getDataArray()
    ddict = {}
    for row in rawdata:
        if row[0]:
            first, last = row[0].split('-')
            key = 'A%s:A%s' % (first, last)
            ddict[ key ] = row[1].split(',')
    return ddict


def hide_rows_depend_on_B4( event ):
    """
    blendet die zu dem Namen gehörenden Zeilen
    aus den Einstellungen aus -
    es muss mit dem Tabellenereignis "Inhalt geändert"
    verknüpft werden
    """
    hideranges = einstellungen()
    sheet = event.getSpreadsheet()
    if event.AbsoluteName == '$%s.$%s' %( sheet.Name, "B$4" ):
        sname = event.getString()
        namerange = sheet.getCellRangeByName
        for key , animals in hideranges.items():
            rows = namerange( key ).Rows
            rows.IsVisible =  sname not in animals

Code: Alles auswählen

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

Sub ein_ausblenden (event)

sitem = event.source.selectedItem

osheet2 = thisComponent.sheets.getbyname("Tabelle2")

for i = 1 to 5
 ocell = osheet2.getcellbyposition(0,i)
 oStartcell1 = osheet2.getcellbyposition(1,i)
 oEndcell1 = osheet2.getcellbyposition(2,i)
 oStartcell2 = osheet2.getcellbyposition(3,i)
 oEndcell2 = osheet2.getcellbyposition(4,i)
 sText = ocell.string
 if sText = sitem then
    nstartrow1 = oStartcell1.value
    nEndrow1 = oEndcell1.value
    nstartrow2 = oStartcell2.value
    nEndrow2 = oEndcell2.value
 endif
next i

osheet1 = thisComponent.sheets.getbyname("Tabelle1")
ocursor = osheet1.createcursor
ocursor.gotoendofusedarea(false)
nEndusedrows = ocursor.rangeaddress.EndRow

for i = 0 to nEndusedrows 
orow = osheet1.Rows(i)
orow.IsVisible = true
next i
if sitem = "alle" then exit sub

for i = nstartrow1-1 to nEndrow1-1
  orow = osheet1.Rows(i)
  orow.IsVisible = false
next i

if not isnull(nstartrow2)  then
 for i = nstartrow2-1 to nEndrow2-1
   orow = osheet1.Rows(i)
   orow.IsVisible = false
 next i
endif


sitem = event.source.selectedItem

osheet2 = thisComponent.sheets.getbyname("Datenblatt")

for i = 1 to 5
 ocell = osheet2.getcellbyposition(5,i)
 oStartcell1 = osheet2.getcellbyposition(6,i)
 oEndcell1 = osheet2.getcellbyposition(7,i)
 oStartcell2 = osheet2.getcellbyposition(8,i)
 oEndcell2 = osheet2.getcellbyposition(9,i)
 sText = ocell.string
 if sText = sitem then
    nstartrow1 = oStartcell1.value
    nEndrow1 = oEndcell1.value
    nstartrow2 = oStartcell2.value
    nEndrow2 = oEndcell2.value
 endif
next i

osheet1 = thisComponent.sheets.getbyname("Tabelle3")
ocursor = osheet1.createcursor
ocursor.gotoendofusedarea(false)
nEndusedrows = ocursor.rangeaddress.EndRow

for i = 0 to nEndusedrows 
orow = osheet1.Rows(i)
orow.IsVisible = true
next i
if sitem = "alle" then exit sub

for i = nstartrow1-1 to nEndrow1-1
  orow = osheet1.Rows(i)
  orow.IsVisible = false
next i

if not isnull(nstartrow2)  then
 for i = nstartrow2-1 to nEndrow2-1
   orow = osheet1.Rows(i)
   orow.IsVisible = false
 next i
endif

End Sub

Code: Alles auswählen

import re
from itertools.chain import from_iterable as flat

context = XSCRIPTCONTEXT

absrex = re.compile( r'([A-Z]+)(\d+)' )

def ausblenden_wenn( event ):
    """
    Muss mit dem Tabellenereignis: 'Inhalt geändert'
    verknüpft werden.
    
    Bezieht die Einstellungen aus der ersten Tabelle
    
    erste Zeile: Tabellennamen
    zweite Zeile: die zu überwachenden Zelladressen

    Spalte A: Die Namen ...
    B3 bis ??: ... für die Zeilen 'n;m'
    
    """
    targetsheet = event.getSpreadsheet()

    
    doc = context.getDocument()
    esheet = doc.Sheets.getByIndex(0)  #Einstellungen
    sheetsrange = esheet.getCellRangeByName("A1:Z1")
    sheetnames = list( flat(sheetsrange.getDataArray()) )
    col_i = sheetnames.index( targetsheet.Name )

    which = esheet.getCellByPosition( col_i, 1).getString()
    which = absrex.sub( r'$\1$\2', which )

    if event.AbsoluteName == '$%s.%s' %( targetsheet.Name, which ):
        targetsheet.Rows.IsVisible = True
        
        patternrange = esheet.getCellRangeByName("A3:A300")
        patterns = flat( patternrange.getDataArray())
        
        hideranges = esheet.getCellRangeByPosition( col_i, 2, col_i, 299 )
        hides = flat( hideranges.getDataArray())
        
        to_hide = [ data.split(";") for data, fname in zip(hides, patterns)
                    if fname == event.getString() and data ]
        for elem in to_hide:
            a, b = elem 
            nrange = 'A%s:A%s' % (a, b)
            rows = targetsheet.getCellRangeByName( nrange ).Rows
            rows.IsVisible = False