riskores_macro.py
riskores_macro.py
—
Python Source,
17 KB (17867 bytes)
Dateiinhalt
import uno
from com.sun.star.table.CellHoriJustify import LEFT as HORIZONTAL_LEFT
from com.sun.star.table.CellVertJustify import CENTER as VERTICAL_CENTER
from com.sun.star.awt.FontWeight import NORMAL as FONT_NORMAL
import time
# Farbcodes (Hex)
COLORS = {
"risk_light": 0xFCE4D6,
"risk_dark": 0xF8CBAD,
"res_light": 0xE2EFDA,
"res_dark": 0xC6E0B4,
}
# -------------------------
# Ereignis: Änderung im Blatt Anleitung
# -------------------------
def on_instruction_change(event=None):
"""Wird ausgelöst bei Änderung im Blatt 'Anleitung'."""
try:
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.CurrentController.ActiveSheet
if sheet.getName() != "Anleitung":
return
sel = doc.CurrentController.getSelection()
if not hasattr(sel, "RangeAddress"):
return
col, row = sel.RangeAddress.StartColumn, sel.RangeAddress.StartRow
# --- Übertragung von C7–C9 auf Risikobewertung & Ressourcen ---
if row in (6, 7, 8) and col == 2: # C7=2,6 / C8=2,7 / C9=2,8
date_val = sheet.getCellByPosition(2, 6).String.strip() # C7
person_val = sheet.getCellByPosition(2, 7).String.strip() # C8
activity_val = sheet.getCellByPosition(2, 8).String.strip() # C9
for target_name in ("Risikobewertung", "Ressourcen"):
try:
target = doc.Sheets.getByName(target_name)
target.getCellByPosition(0, 1).String = date_val
target.getCellByPosition(1, 1).String = activity_val
target.getCellByPosition(2, 1).String = person_val
except Exception as e:
show_message(f"Fehler beim Aktualisieren von {target_name}: {e}")
# --- Gerätefilter bei Änderung in C10 ---
elif row == 9 and col == 2: # C10
device = sheet.getCellByPosition(2, 9).String.strip()
show_message(f"Gerät ausgewählt: '{device}'")
filter_device_rows(doc, device)
except Exception as e:
show_message(f"Fehler in on_instruction_change: {e}")
# -------------------------
# Ereignis: on_document_change
# -------------------------
def on_document_change(event=None):
doc = XSCRIPTCONTEXT.getDocument()
try:
doc.addActionLock()
controller = doc.CurrentController
sheet = controller.ActiveSheet
sel = controller.getSelection()
if not hasattr(sel, "RangeAddress"):
return
ra = sel.RangeAddress
col, row = ra.StartColumn, ra.StartRow
sheet_name = sheet.getName()
allowed = (
"Soziale Beziehungen",
"Arbeitsorganisation",
"Arbeitsumgebung",
"Arbeitsmittel",
"Arbeitsaufgabe",
)
# nur relevante Blätter und Spalten D(3)–G(6)
if sheet_name not in allowed or col < 3 or col > 6:
return
cell = sheet.getCellByPosition(col, row)
val = cell.String.strip().lower()
# --- FALL A: falscher Inhalt ---
if val not in ("", "x"):
show_message("In dieser Zelle kann nur 'x' eingetragen werden.")
cell.String = "x"
val = "x"
# max. 1 x pro Zeile
if count_x_in_row(sheet, row) > 1:
show_message("Pro Zeile kann nur ein 'x' eingetragen werden.")
cell.String = ""
return
item_text = sheet.getCellByPosition(1, row).String.strip()
if not item_text:
return
ws_risk = doc.Sheets.getByName("Risikobewertung")
ws_res = doc.Sheets.getByName("Ressourcen")
# Risikobewertung (F=5, G=6)
if col in (5, 6):
if val == "x":
rating = "traf eher nicht zu" if col == 5 else "traf gar nicht zu"
color = COLORS["risk_light"] if col == 5 else COLORS["risk_dark"]
add_risk_entry(ws_risk, sheet_name, item_text, rating, color, row, col)
else:
remove_entry(ws_risk, sheet_name, item_text)
# Ressourcen (D=3, E=4)
elif col in (3, 4):
if val == "x":
rating = "traf vollkommen zu" if col == 3 else "traf eher zu"
color = COLORS["res_dark"] if col == 3 else COLORS["res_light"]
add_resource_entry(ws_res, sheet_name, item_text, rating, color, row, col)
else:
remove_entry(ws_res, sheet_name, item_text)
except Exception as e:
try:
show_message(f"Fehler: {e}")
except Exception:
print(f"Fehler: {e}")
finally:
try:
doc.removeActionLock()
except Exception:
pass
# -------------------------
# Hilfsfunktionen: on_instruction_change
# -------------------------
def get_active_doc():
return XSCRIPTCONTEXT.getDocument()
def show_message(msg, title="Hinweis"):
"""Zeigt Nachricht in Statusleiste und Konsole."""
try:
doc = get_active_doc()
frame = doc.CurrentController.Frame
if frame:
frame.StatusIndicator.start(title, 0)
frame.StatusIndicator.setText(str(msg))
time.sleep(2)
frame.StatusIndicator.end()
print(f"[{title}] {msg}")
except Exception:
print(f"[{title}] {msg}")
def count_x_in_row(sheet, row):
"""Zählt X in D..G in einer Zeile."""
cnt = 0
for c in range(3, 7):
try:
if sheet.getCellByPosition(c, row).String.strip().lower() == "x":
cnt += 1
except Exception:
pass
return cnt
def get_next_free_row_limited(sheet, max_scan=1000):
"""Sucht nächste freie Zeile ab Zeile 4, limitiert auf max_scan Zeilen."""
start = 3
end = min(sheet.Rows.Count - 1, start + max_scan - 1)
rng = sheet.getCellRangeByPosition(1, start, 1, end)
vals = rng.getDataArray()
for i in range(len(vals) - 1, -1, -1):
if vals[i][0] != "":
return start + i + 1
return start
def find_existing_row_limited(sheet, sheet_name, item_text, max_scan=1000):
"""Suche nach Eintrag in Spalte B/C, begrenzt auf max_scan Zeilen."""
start = 3
end = min(sheet.Rows.Count - 1, start + max_scan - 1)
rng = sheet.getCellRangeByPosition(1, start, 2, end) # B..C
vals = rng.getDataArray()
for idx, row in enumerate(vals, start=start):
if row[0] == sheet_name and row[1] == item_text:
return idx
return None
def get_last_used_row(sheet, col=0):
"""Gibt die letzte belegte Zeile in einer Spalte zurück."""
used_range = sheet.getCellRangeByPosition(col, 0, col, sheet.Rows.Count-1)
data = used_range.getDataArray()
for i in reversed(range(len(data))):
if str(data[i][0]).strip() != "":
return i
return -1
def remove_entry(sheet, sheet_name, item_text):
idx = find_existing_row_limited(sheet, sheet_name, item_text)
if idx is not None:
sheet.Rows.removeByIndex(idx, 1)
def set_row_values(sheet, row, sheet_name, item_text, rating, color, src_row):
"""Setzt Werte und Formatierung für eingefügte Zeile."""
sheet.getCellByPosition(0, row).String = f"A{src_row + 1}"
sheet.getCellByPosition(1, row).String = sheet_name
sheet.getCellByPosition(2, row).String = item_text
sheet.getCellByPosition(3, row).String = rating
for col in range(0, 5):
cell = sheet.getCellByPosition(col, row)
try:
cell.CellBackColor = 0xFFFFFF
cell.CharHeight = 12
cell.CharWeight = FONT_NORMAL
cell.IsTextWrapped = True
except Exception:
pass
cell_c = sheet.getCellByPosition(2, row)
try:
cell_c.CellBackColor = color
cell_c.HoriJustify = HORIZONTAL_LEFT
cell_c.VertJustify = VERTICAL_CENTER
except Exception:
pass
adjust_row_height_heuristic(sheet, row, extra_points=6)
def adjust_row_height_heuristic(sheet, row_index, extra_points=6):
"""Heuristische Zeilenhöhenanpassung."""
try:
base = 400
line_height = 400
text = sheet.getCellByPosition(2, row_index).String
if not text:
new_height = base + int(extra_points * 20)
else:
line_breaks = text.count("\n")
approx_lines = max(1, (len(text) // 60) + 1)
total_lines = max(1, line_breaks + approx_lines)
new_height = base + int(total_lines * line_height) + int(extra_points * 20)
sheet.Rows.getByIndex(row_index).Height = int(new_height)
except Exception:
pass
# -------------------------
# Risikobewertung
# -------------------------
def add_risk_entry(sheet, sheet_name, item_text, rating, color, src_row, col):
if find_existing_row_limited(sheet, sheet_name, item_text) is not None:
return
start = 3
max_scan = 1000
end = min(sheet.Rows.Count - 1, start + max_scan - 1)
rng = sheet.getCellRangeByPosition(1, start, 1, end)
vals = rng.getDataArray()
first = last = None
for idx, r in enumerate(vals, start=start):
if r[0] == sheet_name:
if first is None:
first = idx
last = idx
if first is None:
insert_row = get_next_free_row_limited(sheet)
elif col == 6:
insert_row = first
else:
insert_row = last + 1
sheet.Rows.insertByIndex(insert_row, 1)
set_row_values(sheet, insert_row, sheet_name, item_text, rating, color, src_row)
# -------------------------
# Ressourcen
# -------------------------
def add_resource_entry(sheet, sheet_name, item_text, rating, color, src_row, col):
if find_existing_row_limited(sheet, sheet_name, item_text) is not None:
return
start = 3
max_scan = 1000
end = min(sheet.Rows.Count - 1, start + max_scan - 1)
rng = sheet.getCellRangeByPosition(1, start, 1, end)
vals = rng.getDataArray()
first = last = None
for idx, r in enumerate(vals, start=start):
if r[0] == sheet_name:
if first is None:
first = idx
last = idx
if first is None:
insert_row = get_next_free_row_limited(sheet)
elif col == 3:
insert_row = first
else:
insert_row = last + 1
sheet.Rows.insertByIndex(insert_row, 1)
set_row_values(sheet, insert_row, sheet_name, item_text, rating, color, src_row)
# -------------------------
# Neues: Zeilen nach Gerät filtern (nur belegte Zeilen)
# -------------------------
filter_lock = False
def filter_device_rows(doc, device_value):
"""Blätter filtern basierend auf Dropdown-Auswahl C10, Kopfzeilen bleiben sichtbar, Teilstring-Matching."""
global filter_lock
if filter_lock:
return
filter_lock = True
try:
target_sheets = [
"Soziale Beziehungen",
"Arbeitsorganisation",
"Arbeitsumgebung",
"Arbeitsmittel",
"Arbeitsaufgabe",
]
for sheet_name in target_sheets:
sheet = doc.Sheets[sheet_name]
last_row = get_last_used_row(sheet, col=0)
if last_row == -1:
continue # keine Daten
for row_idx in range(last_row + 1):
cell_val = sheet.getCellByPosition(0, row_idx).String.strip()
# Kopfzeile immer sichtbar
if row_idx == 0:
sheet.Rows.getByIndex(row_idx).IsVisible = True
continue
if device_value.lower() in ("gerät auswählen", ""):
sheet.Rows.getByIndex(row_idx).IsVisible = True
else:
# Mehrfachgeräte erkennen
devices_in_cell = [d.strip().lower() for d in cell_val.split(",")]
sheet.Rows.getByIndex(row_idx).IsVisible = device_value.lower() in devices_in_cell
finally:
filter_lock = False
# -------------------------
# Hilfsfunktionen: on_document_change
# -------------------------
def show_message(msg, title="Hinweis"):
"""Zeigt Nachricht in Statusleiste und Konsole."""
try:
doc = XSCRIPTCONTEXT.getDocument()
frame = doc.CurrentController.Frame
if frame:
frame.StatusIndicator.start(title, 0)
frame.StatusIndicator.setText(str(msg))
time.sleep(2)
frame.StatusIndicator.end()
print(f"[{title}] {msg}")
except Exception:
print(f"[{title}] {msg}")
def count_x_in_row(sheet, row):
"""Zählt X in D..G in einer Zeile."""
cnt = 0
for c in range(3, 7):
try:
if sheet.getCellByPosition(c, row).String.strip().lower() == "x":
cnt += 1
except Exception:
pass
return cnt
def get_next_free_row_limited(sheet, max_scan=1000):
"""Sucht nächste freie Zeile ab Zeile 4, limitiert auf max_scan Zeilen."""
start = 3
end = min(sheet.Rows.Count - 1, start + max_scan - 1)
rng = sheet.getCellRangeByPosition(1, start, 1, end)
vals = rng.getDataArray()
for i in range(len(vals) - 1, -1, -1):
if vals[i][0] != "":
return start + i + 1
return start
def find_existing_row_limited(sheet, sheet_name, item_text, max_scan=1000):
"""Suche nach Eintrag in Spalte B/C, begrenzt auf max_scan Zeilen."""
start = 3
end = min(sheet.Rows.Count - 1, start + max_scan - 1)
rng = sheet.getCellRangeByPosition(1, start, 2, end) # B..C
vals = rng.getDataArray()
for idx, row in enumerate(vals, start=start):
if row[0] == sheet_name and row[1] == item_text:
return idx
return None
def remove_entry(sheet, sheet_name, item_text):
idx = find_existing_row_limited(sheet, sheet_name, item_text)
if idx is not None:
sheet.Rows.removeByIndex(idx, 1)
# -------------------------
# Risikobewertung
# -------------------------
def add_risk_entry(sheet, sheet_name, item_text, rating, color, src_row, col):
if find_existing_row_limited(sheet, sheet_name, item_text) is not None:
return
start = 3
max_scan = 1000
end = min(sheet.Rows.Count - 1, start + max_scan - 1)
rng = sheet.getCellRangeByPosition(1, start, 1, end) # Spalte B
vals = rng.getDataArray()
first = last = None
for idx, r in enumerate(vals, start=start):
if r[0] == sheet_name:
if first is None:
first = idx
last = idx
# Einfügeposition bestimmen
if first is None:
insert_row = get_next_free_row_limited(sheet)
elif col == 6:
insert_row = first
else:
insert_row = last + 1
sheet.Rows.insertByIndex(insert_row, 1)
set_row_values(sheet, insert_row, sheet_name, item_text, rating, color, src_row)
# -------------------------
# Ressourcen
# -------------------------
def add_resource_entry(sheet, sheet_name, item_text, rating, color, src_row, col):
if find_existing_row_limited(sheet, sheet_name, item_text) is not None:
return
start = 3
max_scan = 1000
end = min(sheet.Rows.Count - 1, start + max_scan - 1)
rng = sheet.getCellRangeByPosition(1, start, 1, end)
vals = rng.getDataArray()
first = last = None
for idx, r in enumerate(vals, start=start):
if r[0] == sheet_name:
if first is None:
first = idx
last = idx
if first is None:
insert_row = get_next_free_row_limited(sheet)
elif col == 3:
insert_row = first
else:
insert_row = last + 1
sheet.Rows.insertByIndex(insert_row, 1)
set_row_values(sheet, insert_row, sheet_name, item_text, rating, color, src_row)
# -------------------------
# Formatierung & Höhe setzen
# -------------------------
def set_row_values(sheet, row, sheet_name, item_text, rating, color, src_row):
sheet.getCellByPosition(0, row).String = f"A{src_row + 1}"
sheet.getCellByPosition(1, row).String = sheet_name
sheet.getCellByPosition(2, row).String = item_text
sheet.getCellByPosition(3, row).String = rating
for col in range(0, 11):
cell = sheet.getCellByPosition(col, row)
try:
cell.CellBackColor = 0xFFFFFF
cell.CharHeight = 12
cell.CharWeight = FONT_NORMAL
cell.IsTextWrapped = True
except Exception:
pass
cell_c = sheet.getCellByPosition(2, row)
try:
cell_c.CellBackColor = color
cell_c.HoriJustify = HORIZONTAL_LEFT
cell_c.VertJustify = VERTICAL_CENTER
except Exception:
pass
adjust_row_height_heuristic(sheet, row, extra_points=6)
def adjust_row_height_heuristic(sheet, row_index, extra_points=6):
try:
base = 400
line_height = 400
text = sheet.getCellByPosition(2, row_index).String
if not text:
new_height = base + int(extra_points * 20)
else:
line_breaks = text.count("\n")
approx_lines = max(1, (len(text) // 60) + 1)
total_lines = max(1, line_breaks + approx_lines)
new_height = base + int(total_lines * line_height) + int(extra_points * 20)
sheet.Rows.getByIndex(row_index).Height = int(new_height)
except Exception:
pass