2020-09-20

import os
from openpyxl import load_workbook
from openpyxl.styles import Border, Font, Side, colors
import datetime
import pandas as pd
import numpy as np


class SQLList:
    sql_1 = pd.DataFrame(np.random.rand(3, 8))
    sql_2 = pd.DataFrame(np.random.rand(3, 5))
    df_3 = pd.DataFrame(np.random.rand(3, 2))
    df_4 = pd.DataFrame(np.random.rand(3, 2))
    df_5 = pd.DataFrame(np.random.rand(3, 3))
    df_3 = pd.merge(df_3, df_4, left_index=True, right_index=True, how='outer')
    df_3 = pd.merge(df_3, df_5, left_index=True, right_index=True, how='outer')
    sql_3 = df_3


class BorderStyle:
    border_thin = Border(top=Side(border_style='thin', color=colors.BLACK),
                         right=Side(border_style='thin', color=colors.BLACK),
                         bottom=Side(border_style='thin', color=colors.BLACK),
                         left=Side(border_style='thin', color=colors.BLACK))
    border_left_medium = Border(left=Side(border_style='medium', color=colors.BLACK))
    border_top_medium = Border(top=Side(border_style='medium', color=colors.BLACK))


class SendMail(object):
    def __init__(self, template, filename, sheet_name='Sheet1', to=None, subject=None, to_others=None):
        self.to = to
        self.subject = subject
        self.to_others = to_others
        self.template = template
        self.filename = filename
        self.sheet_name = sheet_name
        self.start_row = 6

    def qury_data(self):
        data = []
        data.append(pd.DataFrame(np.random.rand(3, 8)).values.tolist())
        data.append(pd.DataFrame(np.random.rand(3, 5)).values.tolist())
        data.append(pd.DataFrame(np.random.rand(3, 7)).values.tolist())
        return data

    def data_to_excel(self, ws, data_list):
        for data in data_list:
            ws.insert_rows(self.start_row, len(data))
            for row in data:
                column = 1
                for col in row:
                    ws.cell(row=self.start_row, column=column).value = col
                    ws.cell(row=self.start_row, column=column).border = BorderStyle.border_thin
                    column = column + 1
                self.start_row = self.start_row + 1
            self.start_row = self.start_row + 3

    def send_mail(self):
        "send mail"
        pass

    def execute(self, **context):
        wb = load_workbook(self.template)
        ws = wb.active
        ws.sheet_view.showGridLines = False
        ws['H2'] = datetime.datetime.now().strftime('%Y-%m-%d')
        data = self.qury_data()
        self.data_to_excel(ws, data)

        max_col = ws.max_column
        max_row = ws.max_row
        for i in range(1, max_row):
            ws.cell(row=i, column=max_col + 1).border = BorderStyle.border_left_medium
        for i in range(1, max_col + 1):
            ws.cell(row=max_row, column=i).border = BorderStyle.border_top_medium
        wb.save(self.filename)
        self.send_mail()
        os.remove(self.filename)


obj = SendMail(template='df_style.xlsx', filename=r'test.xlsx')
obj.execute()