tests/xls_xlsx_pdf/xls_cond_form.py

'''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()