360 lines
16 KiB
Python
360 lines
16 KiB
Python
import os
|
||
from datetime import datetime
|
||
from openpyxl import Workbook
|
||
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
|
||
from openpyxl.utils import get_column_letter
|
||
from openpyxl.drawing.image import Image as XlImage
|
||
from collections import defaultdict
|
||
|
||
def _val(v):
|
||
if v is None or v == 0 or v == '':
|
||
return None
|
||
return v
|
||
|
||
def _ist_trenner(pos):
|
||
return (not pos.pos_nr or pos.pos_nr == '') and (pos.faktor == 0 or pos.faktor is None) and (pos.laenge == 0 or pos.laenge is None) and (pos.breite == 0 or pos.breite is None) and (pos.tiefe == 0 or pos.tiefe is None) and (pos.menge == 0 or pos.menge is None) and (pos.einzelpreis == 0 or pos.einzelpreis is None) and (pos.gesamtpreis == 0 or pos.gesamtpreis is None)
|
||
|
||
def _tb():
|
||
return Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
|
||
|
||
def _fmt_date(d):
|
||
if d is None:
|
||
return None
|
||
if isinstance(d, str):
|
||
d = d[:10]
|
||
for fmt in ('%Y-%m-%d', '%d.%m.%Y'):
|
||
try:
|
||
return datetime.strptime(d, fmt).strftime('%d.%m.%Y')
|
||
except ValueError:
|
||
continue
|
||
return d
|
||
return d.strftime('%d.%m.%Y')
|
||
|
||
def export_project_to_excel(project, aufmass, positionen, output_path, company=None):
|
||
wb = Workbook()
|
||
ws = wb.active
|
||
ws.title = "Aufmaß"
|
||
|
||
ws.page_setup.paperSize = ws.PAPERSIZE_A4
|
||
ws.page_setup.orientation = 'landscape'
|
||
ws.page_setup.fitToWidth = 1
|
||
ws.page_setup.fitToHeight = 0
|
||
ws.sheet_properties.pageSetUpPr.fitToPage = True
|
||
ws.page_margins.left = 0.3
|
||
ws.page_margins.right = 0.3
|
||
ws.page_margins.top = 0.4
|
||
ws.page_margins.bottom = 0.7
|
||
|
||
ws.oddFooter.center.text = ""
|
||
ws.oddFooter.right.text = "&P/&N"
|
||
ws.oddFooter.right.font = "Calibri,11"
|
||
|
||
header_fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
|
||
header_font = Font(name='Calibri', size=11, bold=True, color='FFFFFF')
|
||
label_font = Font(name='Calibri', size=10, bold=True)
|
||
value_font = Font(name='Calibri', size=10)
|
||
value_font_bold = Font(name='Calibri', size=10, bold=True)
|
||
title_font = Font(name='Calibri', size=16, bold=True)
|
||
header_box_fill = PatternFill(start_color='F2F2F2', end_color='F2F2F2', fill_type='solid')
|
||
trenner_fill = PatternFill(start_color='F5F5F5', end_color='F5F5F5', fill_type='solid')
|
||
|
||
row = 1
|
||
|
||
firmen_name = company.name if company else 'Aufmaß'
|
||
logo_placed = False
|
||
if company and company.logo:
|
||
logo_path = company.logo
|
||
if not os.path.isabs(logo_path):
|
||
logo_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), logo_path)
|
||
if os.path.exists(logo_path):
|
||
try:
|
||
img = XlImage(logo_path)
|
||
img.width = 120
|
||
img.height = 60
|
||
ws.add_image(img, 'A1')
|
||
ws.row_dimensions[1].height = 60
|
||
ws.merge_cells(start_row=1, start_column=4, end_row=1, end_column=13)
|
||
ws.cell(row=1, column=4, value='Aufmaß').font = title_font
|
||
ws.cell(row=1, column=4).alignment = Alignment(horizontal='center', vertical='center')
|
||
logo_placed = True
|
||
except Exception:
|
||
pass
|
||
if not logo_placed:
|
||
ws.cell(row=1, column=1, value=firmen_name).font = title_font
|
||
row = 2
|
||
|
||
row += 1
|
||
|
||
label_align = Alignment(horizontal='left', vertical='center')
|
||
shrink_val = Alignment(horizontal='left', vertical='center', shrink_to_fit=True)
|
||
|
||
line = row
|
||
for ci in range(1, 14):
|
||
ws.cell(row=line, column=ci).border = _tb()
|
||
pairs = [
|
||
('Vertrag:', _val(project.vertrag), (1, 2, 4)),
|
||
('LV-Name:', _val(project.lv_name), (5, 6, 9)),
|
||
('Aufmaß-Datum:', _fmt_date(project.datum), (10, 11, 13)),
|
||
]
|
||
for label, val, (ls, vs, ve) in pairs:
|
||
c = ws.cell(row=line, column=ls, value=label)
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=vs, value=val)
|
||
c.font = value_font; c.alignment = shrink_val
|
||
if ve > vs:
|
||
ws.merge_cells(start_row=line, start_column=vs, end_row=line, end_column=ve)
|
||
row += 1
|
||
|
||
line = row
|
||
for ci in range(1, 14):
|
||
ws.cell(row=line, column=ci).border = _tb()
|
||
c = ws.cell(row=line, column=1, value='Projekt:')
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=2, value=_val(project.bezeichnung))
|
||
c.font = value_font_bold; c.alignment = shrink_val
|
||
ws.merge_cells(start_row=line, start_column=2, end_row=line, end_column=4)
|
||
c = ws.cell(row=line, column=5, value='Baustelle:')
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=6, value=_val(project.baustelle))
|
||
c.font = value_font; c.alignment = shrink_val
|
||
ws.merge_cells(start_row=line, start_column=6, end_row=line, end_column=13)
|
||
row += 1
|
||
|
||
line = row
|
||
for ci in range(1, 14):
|
||
ws.cell(row=line, column=ci).border = _tb()
|
||
c = ws.cell(row=line, column=1, value='Typ:')
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=2, value=_val(aufmass.typ if aufmass else None))
|
||
c.font = value_font; c.alignment = shrink_val
|
||
ws.merge_cells(start_row=line, start_column=2, end_row=line, end_column=4)
|
||
c = ws.cell(row=line, column=5, value='Bauabschnitt:')
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=6, value=_val(project.bauabschnitt))
|
||
c.font = value_font; c.alignment = shrink_val
|
||
ws.merge_cells(start_row=line, start_column=6, end_row=line, end_column=13)
|
||
row += 1
|
||
|
||
ap_name = f'{_val(project.ansprechpartner_vorname)} {_val(project.ansprechpartner_nachname)}'.strip()
|
||
|
||
# Row A: SM-Nr + Startdatum + Ansprechpartner Name + Tel
|
||
line = row
|
||
for ci in range(1, 14):
|
||
ws.cell(row=line, column=ci).border = _tb()
|
||
c = ws.cell(row=line, column=1, value='SM-Nr.:')
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=2, value=_val(project.sm_nr))
|
||
c.font = value_font; c.alignment = shrink_val
|
||
ws.merge_cells(start_row=line, start_column=2, end_row=line, end_column=3)
|
||
c = ws.cell(row=line, column=4, value='Startdatum:')
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=5, value=_fmt_date(project.datum_start))
|
||
c.font = value_font; c.alignment = shrink_val
|
||
ws.merge_cells(start_row=line, start_column=5, end_row=line, end_column=6)
|
||
c = ws.cell(row=line, column=7, value='Name:')
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=8, value=_val(ap_name))
|
||
c.font = value_font; c.alignment = shrink_val
|
||
ws.merge_cells(start_row=line, start_column=8, end_row=line, end_column=10)
|
||
c = ws.cell(row=line, column=11, value='Tel:')
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=12, value=_val(project.ansprechpartner_tel))
|
||
c.font = value_font; c.alignment = shrink_val
|
||
ws.merge_cells(start_row=line, start_column=12, end_row=line, end_column=13)
|
||
row += 1
|
||
|
||
# Row B: Abruf-Nr + Enddatum + Email
|
||
line = row
|
||
for ci in range(1, 14):
|
||
ws.cell(row=line, column=ci).border = _tb()
|
||
c = ws.cell(row=line, column=1, value='Abruf-Nr.:')
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=2, value=_val(project.abruf_nr))
|
||
c.font = value_font; c.alignment = shrink_val
|
||
ws.merge_cells(start_row=line, start_column=2, end_row=line, end_column=3)
|
||
c = ws.cell(row=line, column=4, value='Enddatum:')
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=5, value=_fmt_date(project.datum_ende))
|
||
c.font = value_font; c.alignment = shrink_val
|
||
ws.merge_cells(start_row=line, start_column=5, end_row=line, end_column=6)
|
||
c = ws.cell(row=line, column=7, value='Email:')
|
||
c.font = label_font; c.fill = header_box_fill; c.alignment = label_align
|
||
c = ws.cell(row=line, column=8, value=_val(project.ansprechpartner_email))
|
||
c.font = value_font; c.alignment = shrink_val
|
||
ws.merge_cells(start_row=line, start_column=8, end_row=line, end_column=13)
|
||
row += 1
|
||
|
||
row += 1
|
||
|
||
headers = [
|
||
'Abschnitt', 'Pos-Nr', 'Faktor', 'Länge', 'Breite', 'Tiefe',
|
||
'Menge', 'EH', 'Kurztext', 'Bemerkung', 'Menge', 'EP (€)', 'GP (€)'
|
||
]
|
||
header_row = row
|
||
for col, h in enumerate(headers, 1):
|
||
cell = ws.cell(row=row, column=col, value=h)
|
||
cell.font = header_font
|
||
cell.fill = header_fill
|
||
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
|
||
cell.border = _tb()
|
||
|
||
data_last_row = header_row + len(positionen)
|
||
if positionen:
|
||
ws.auto_filter.ref = f'A{header_row}:M{data_last_row}'
|
||
|
||
start_row = row
|
||
|
||
pos_counter = 0
|
||
col_widths = [0.0] * 14
|
||
for i, pos in enumerate(positionen):
|
||
row = start_row + 1 + i
|
||
menge = pos.menge if pos.menge else None
|
||
menge_hinten = pos.menge_hinten if pos.menge_hinten else None
|
||
if pos.einheit in ('ST', 'LE', 'STD', 'h', 'Psch'):
|
||
menge = pos.faktor * 1 if pos.faktor else None
|
||
l = pos.laenge if pos.laenge else None
|
||
b = pos.breite if pos.breite else None
|
||
t = pos.tiefe if pos.tiefe else None
|
||
ep = pos.einzelpreis if pos.einzelpreis else None
|
||
gp = pos.gesamtpreis if pos.gesamtpreis else None
|
||
faktor = pos.faktor if pos.faktor else None
|
||
|
||
ist_trenner = _ist_trenner(pos)
|
||
|
||
if ist_trenner:
|
||
ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=13)
|
||
for c in range(1, 14):
|
||
cell = ws.cell(row=row, column=c)
|
||
cell.fill = trenner_fill
|
||
cell.border = _tb()
|
||
continue
|
||
|
||
pos_counter += 1
|
||
values = [
|
||
_val(pos.abschnitt),
|
||
pos.pos_nr or None,
|
||
faktor,
|
||
l, b, t,
|
||
menge,
|
||
pos.einheit or None,
|
||
_val(pos.kurztext),
|
||
_val(pos.bemerkung),
|
||
menge_hinten,
|
||
ep, gp,
|
||
]
|
||
for col, val in enumerate(values, 1):
|
||
cell = ws.cell(row=row, column=col, value=val) if val is not None else ws.cell(row=row, column=col, value='')
|
||
cell.font = value_font
|
||
cell.border = _tb()
|
||
if val is not None:
|
||
if col in (7, 11):
|
||
cell.number_format = '#,##0.00'
|
||
display = '{:,.2f}'.format(val)
|
||
elif col in (12, 13):
|
||
cell.number_format = '#,##0.00'
|
||
display = '{:,.2f}'.format(val)
|
||
elif col == 3:
|
||
display = '{:.2f}'.format(val) if isinstance(val, float) else str(val)
|
||
elif col in (4, 5, 6):
|
||
display = '{:.2f}'.format(val) if isinstance(val, float) else str(val)
|
||
else:
|
||
display = str(val)
|
||
else:
|
||
display = ''
|
||
col_widths[col] = max(col_widths[col], len(display))
|
||
|
||
sum_row = None
|
||
if positionen:
|
||
sum_row = start_row + 1 + len(positionen) + 1
|
||
ws.merge_cells(start_row=sum_row, start_column=1, end_row=sum_row, end_column=11)
|
||
ws.cell(row=sum_row, column=12, value='Summe:').font = Font(name='Calibri', size=11, bold=True)
|
||
ws.cell(row=sum_row, column=12).alignment = Alignment(horizontal='right')
|
||
gesamt = sum(p.gesamtpreis or 0 for p in positionen if not _ist_trenner(p))
|
||
cell = ws.cell(row=sum_row, column=13, value=gesamt)
|
||
cell.font = Font(name='Calibri', size=11, bold=True)
|
||
cell.number_format = '#,##0.00'
|
||
for c in range(1, 14):
|
||
ws.cell(row=sum_row, column=c).border = _tb()
|
||
|
||
if pos_counter > 0:
|
||
sum_end = sum_row if sum_row else (start_row + 1 + len(positionen))
|
||
summary_start = sum_end + 2
|
||
|
||
# Title row – merged across all 13 cols
|
||
for ci in range(1, 14):
|
||
ws.cell(row=summary_start, column=ci).border = _tb()
|
||
ws.cell(row=summary_start, column=ci).fill = PatternFill(start_color='D6E4F0', end_color='D6E4F0', fill_type='solid')
|
||
sc = ws.cell(row=summary_start, column=1, value='Mengen- und Positions-Zusammenfassung')
|
||
sc.font = Font(name='Calibri', size=12, bold=True, color='2F5496')
|
||
sc.alignment = Alignment(horizontal='center', vertical='center')
|
||
ws.merge_cells(start_row=summary_start, start_column=1, end_row=summary_start, end_column=13)
|
||
|
||
# Summary header – borders on 13 cols, gap cols 6-10 merged
|
||
shr = summary_start + 1
|
||
for ci in range(1, 14):
|
||
ws.cell(row=shr, column=ci).border = _tb()
|
||
ws.cell(row=shr, column=ci).fill = header_fill
|
||
sum_headers = {'Pos-Nr': (1, 1), 'Kurztext': (2, 10), 'Menge': (11, 11), 'EP (€)': (12, 12), 'GP (€)': (13, 13)}
|
||
for h, (cs, ce) in sum_headers.items():
|
||
cell = ws.cell(row=shr, column=cs, value=h)
|
||
cell.font = header_font
|
||
cell.alignment = Alignment(horizontal='center', vertical='center')
|
||
if ce > cs:
|
||
ws.merge_cells(start_row=shr, start_column=cs, end_row=shr, end_column=ce)
|
||
ws.merge_cells(start_row=shr, start_column=2, end_row=shr, end_column=10)
|
||
|
||
groups = defaultdict(lambda: {'kurztext': '', 'menge': 0.0, 'ep': 0.0, 'gp': 0.0})
|
||
seen_pos = []
|
||
for pos in positionen:
|
||
if _ist_trenner(pos) or not pos.pos_nr:
|
||
continue
|
||
key = pos.pos_nr
|
||
if key not in groups:
|
||
seen_pos.append(key)
|
||
groups[key]['kurztext'] = pos.kurztext or ''
|
||
groups[key]['menge'] += pos.menge_hinten if pos.menge_hinten else (pos.menge or 0)
|
||
groups[key]['ep'] = pos.einzelpreis or 0
|
||
groups[key]['gp'] += pos.gesamtpreis or 0
|
||
|
||
# Summary data rows – borders on 13 cols, gap cols 6-10 merged
|
||
r = shr + 1
|
||
for key in seen_pos:
|
||
g = groups[key]
|
||
for ci in range(1, 14):
|
||
ws.cell(row=r, column=ci).border = _tb()
|
||
vals = [(1, 1, key), (2, 10, g['kurztext']), (11, 11, g['menge']), (12, 12, g['ep']), (13, 13, g['gp'])]
|
||
for cs, ce, v in vals:
|
||
cell = ws.cell(row=r, column=cs, value=v)
|
||
cell.font = value_font
|
||
if cs >= 11:
|
||
cell.number_format = '#,##0.00'
|
||
if ce > cs:
|
||
ws.merge_cells(start_row=r, start_column=cs, end_row=r, end_column=ce)
|
||
# No gap merge needed since Kurztext goes to column 10
|
||
r += 1
|
||
|
||
# Summary sum row – borders on 13 cols, gap cols 1-10 merged
|
||
for ci in range(1, 14):
|
||
ws.cell(row=r, column=ci).border = _tb()
|
||
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=10)
|
||
ws.cell(row=r, column=12, value='Summe:').font = Font(name='Calibri', size=10, bold=True)
|
||
ws.cell(row=r, column=12).alignment = Alignment(horizontal='right')
|
||
total_gp = sum(g['gp'] for g in groups.values())
|
||
cell = ws.cell(row=r, column=13, value=total_gp)
|
||
cell.font = Font(name='Calibri', size=10, bold=True)
|
||
cell.number_format = '#,##0.00'
|
||
|
||
min_widths = [0, 17, 9, 10, 10, 9, 11, 10, 6, 9, 14, 10, 8, 10]
|
||
max_widths = [0, 17, 15, 10, 12, 12, 12, 14, 8, 55, 45, 14, 14, 16]
|
||
for i in range(1, 14):
|
||
raw = col_widths[i]
|
||
w = max(raw * 1.2 + 1, min_widths[i])
|
||
w = min(w, max_widths[i])
|
||
ws.column_dimensions[get_column_letter(i)].width = w
|
||
|
||
ws.print_area = f'A1:M{r}'
|
||
|
||
os.makedirs(os.path.dirname(output_path), exist_ok=True)
|
||
wb.save(output_path)
|
||
return output_path
|