'''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 = '''
Delegacio elszamolas {} -> {}.
A csatolt tablazatban le lehet ellenorizni a fenti periodusra:
delegaciokat (C oszlop delegacio szam, D oszlop periodus, H oszlop napi dij),
egyeb elszamolni valot mint szallas szamlak, egyebek (leroy stb...)
'''.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()