1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283 |
- '''Created 26 May 2022 Levi'''
- import calendar
- from datetime import date
- from datetime import timedelta
- import openpyxl as xl
- import yagmail
- from openpyxl import styles
- from openpyxl import formatting
- from openpyxl.styles.borders import Side
- class MySMTP(yagmail.SMTP):
- def __enter__(self):
- pass
- def __exit__(self, exc_type, exc_val, exc_tb):
- pass
- path = 'C:/Users/Levi/Downloads/ciel/dlg_25.07.2022.xlsx'
- if date.today().day > 15:
- start = date.today() - timedelta(days=date.today().day - 1)
- end = date(date.today().year, date.today().month, 15)
- else:
- if date.today().month == 1:
- start = date(date.today().year - 1, 12, 15)
- end = date(date.today().year - 1, 12, calendar.monthrange(date.today().year - 1, 12)[1])
- else:
- start = date(date.today().year, date.today().month - 1, 15)
- end = date(date.today().year, date.today().month - 1, calendar.monthrange(date.today().year, date.today().month - 1)[1])
- subject = 'Delegacio elszamolas {} -> {}'.format(start, end)
- body = '''<p>Delegacio elszamolas <span style="color:Tomato"><b>{} -> {}</b>.</span></p>
- <p>A csatolt tablazatban le lehet ellenorizni a fenti periodusra:</p>
- <li>delegaciokat (C oszlop delegacio szam, D oszlop periodus, H oszlop napi dij),</li>
- <li>egyeb elszamolni valot mint szallas szamlak, egyebek (leroy stb...)</li>
- '''.format(start, end)
- to = ['office@mzk.ro']
- # to = ['deeejas@gmail.com']
- yag = MySMTP(user={'levente.marton@mzk.ro': 'levente.marton@mzk.ro'}, host='192.168.201.4', port=25, smtp_starttls=True, smtp_ssl=False)
- attachments = [path]
- bottom_border = styles.Border(bottom=Side(style='thin'))
- wb = xl.load_workbook(path)
- ws = wb.worksheets[0]
- for i in range(1, ws.max_row + 1):
- if ws.cell(row=i, column=3).value in ['SOLD INITIAL', 'TOTAL', 'TOTAL ANGAJAT']:
- ws.delete_rows(i, 1)
- # print(f'i = {i}\tcell value (i, 1) is {ws.cell(row=i, column=1).value}')
- for i in range(1, ws.max_row + 1):
- if ws.cell(row=i, column=1).value in ['Cont: TOTAL ANGAJAT ']:
- ws.delete_rows(i, 1)
- # print(f'i = {i}\tcell value (i, 1) is {ws.cell(row=i, column=1).value}')
- ws.delete_cols(10)
- col_dims = {'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'}
- dims = {}
- for row in ws.rows:
- for cell in row:
- if cell.value:
- dims[cell.column_letter] = max((dims.get(cell.column_letter, 0), len(str(cell.value))))
- for col, value in dims.items():
- if col in col_dims:
- ws.column_dimensions[col].width = value + 2
- ws.conditional_formatting.add(
- 'A3:I100',
- formatting.rule.FormulaRule(formula=['NOT($C4=$C3)'], stopIfTrue=True, border=bottom_border)
- )
- wb.save(path)
- wb.close()
- print(subject, end='\n')
- print(body)
- yag.send(to=to, subject=subject, contents=body, attachments=attachments)
- yag.close()
|