# coding=utf-8

from openpyxl import Workbook, load_workbook

from openpyxl.drawing.image import Image

from openpyxl.styles import Font,colors

from datetime import datetime

import MySQLdb

class ExcelUtils(object):

    """

    pip install openpyxl

    pip install pillow
    pip install mysqlclient

    参考文档：https://openpyxl.readthedocs.io/en/stable

    """

    def __init__(self):

        self.wb = Workbook()

        # 激活表单

        self.ws = self.wb.active

        self.ws_two = self.wb.create_sheet('我的表单')

        self.ws.title = '你的表单'

        self.ws.sheet_properties.tabColor = 'ff000000'

        self.ws_three = self.wb.create_sheet()

    def do_sth(self):

        # 插入数据

        self.ws['A1'] = 66

        self.ws['A2'] = '你好'

        self.ws['A3'] = datetime.now()

        # 批量插入数字

        for row in self.ws_two['A1:E5']:

            for cell in row:

                cell.value = 2

        # 对数据进行求和

        self.ws_two['G1'] = '=SUM(A1:E1)'

        # 插入图片

        # img = Image('./static/temp.jpg')

        # self.ws.add_image(img,'B1')

        # 合并单元格

        self.ws.merge_cells('A4:E5')

        self.ws.unmerge_cells('A4:E5')

        # 插入文字

        font = Font(sz=18,color = colors.RED)

        self.ws['A2'].font = font

        self.wb.save('./static/test.xlsx')

    def read_xls(self):

        """

        读取excel数据

        :return:

        """

        ws = load_workbook('./static/templates.xlsx')

        names = ws.get_sheet_names()

        print(names)

        # 获取sheet的三种方法

        # wb = ws.active

        # wb = ws['北京大学统计']

        wb = ws[names[0]]

        # 先读取行

        for row in wb.rows:

            # 再读取列

            for cell in row:

                print(cell.value)

    def get_conn(self):

        """ 获取mysql的连接"""

        try:

            self.conn = MySQLdb.connect(

                host="localhost",

                port=3306,

                user="root",

                password="root",

                db="user_grade",

            )

        except MySQLdb.Error as e:

            print("Error %d:%s" % (e.args[0], e.args[1]))

        return self.conn

    def import_excel_todb(self):    # 将excel中的内容导入数据库中

        ws = load_workbook('./static/templates.xlsx')

        names = ws.get_sheet_names()

        # 获取数据的sheet

        wb = ws[names[0]]

        conn = self.get_conn()

        for (i,row) in enumerate(wb.rows):

            # 跳过标题部分

            if i < 2:

                continue

            # 获取年份，最高分，平均分

            year = wb['A{0}'.format(i+1)].value

            max = wb['B{0}'.format(i+1)].value

            avg = wb['C{0}'.format(i+1)].value

            # print(year, max, avg)

            cur = conn.cursor()

            # sql = "insert into score(year,max,avg) values(2005, 695, 468)"

            sql = "insert into score(year,max,avg) values({year},{max},{avg})".format(year = year,max = max,avg = avg)

            cur.execute(sql)

            conn.autocommit(True)

        conn.close()

    def export_db_toexcel(self):    # 从数据库导数据到excel里面

        # 从数据库中获取数据

        conn = self.get_conn()

        cur = conn.cursor()

        cur.execute("select year,max,avg from score")

        # 获取所有数据

        rows = cur.fetchall()

        # for row in rows:

        #     print(row[0],row[1],row[2])

        wb = Workbook()

        ws = wb.active

        ws.title = "高考成绩"

        for (i,row) in enumerate(rows):

            # 单个赋值

            # ws['A{0}'.format(i+1)] = row[0]

            # ws['B{0}'.format(i+1)] = row[1]

            # ws['C{0}'.format(i+1)] = row[2]

            # 批量赋值

            (ws['A{0}'.format(i+1)],

             ws['B{0}'.format(i + 1)],

             ws['C{0}'.format(i + 1)]

             ) = row

        wb.save("./static/myscore.xlsx")

        # 关闭数据库连接

        conn.close()

if __name__ == "__main__":

    client = ExcelUtils()

    # client.do_sth()

    # client.read_xls()

    # conn = client.get_conn()

    # client.import_excel_todb()

    client.export_db_toexcel()