from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Color, PatternFill, Border, Side
from openpyxl.styles.colors import BLUE
import pymysql

def create_sheet(table_name, table_desc, wb):
    ws = wb.create_sheet(table_name)
    ws['A1'] = '表名'
    set_theme(ws['A1'])
    ws['B1'] = table_name
    set_font(ws['B1'])
    ws['C1'] = '表名描述'
    set_theme(ws['C1'])
    ws['D1'] = table_desc
    set_font(ws['D1'])
    ws['A2'] = '字段'
    set_theme(ws['A2'])
    ws['B2'] = '字段类型'
    set_theme(ws['B2'])
    ws['C2'] = '是否可为空'
    set_theme(ws['C2'])
    ws['D2'] = '默认值'
    set_theme(ws['D2'])
    ws['E2'] = '字段描述'
    set_theme(ws['E2'])
    ws['F2'] = '自增属性'
    set_theme(ws['F2'])
    return ws

def set_columns(ws, line_number, result):
    ws['A'+str(line_number)]=result[2]
    set_font(ws['A'+str(line_number)])
    ws['B'+str(line_number)]=result[3]
    set_font(ws['B'+str(line_number)])
    ws['C'+str(line_number)]=result[4]
    set_font(ws['C'+str(line_number)])
    ws['D'+str(line_number)]=result[5]
    set_font(ws['D'+str(line_number)])
    ws['E'+str(line_number)]=result[6]
    set_font(ws['E'+str(line_number)])
    ws['F'+str(line_number)]=result[7]
    set_font(ws['F'+str(line_number)])

def create_total():
    wb = Workbook()
    ws1 = wb.create_sheet("表汇总")
    ws1['A1'] = '表名'
    ws1['B1'] = '描述'
    set_theme(ws1['A1'])
    set_theme(ws1['B1'])

    return ws1, wb

def set_font(cell):
    thin = Side(border_style="thin", color="000000")
    cell.border = Border(top=thin, left=thin, right=thin, bottom=thin)
    cell.font = Font(size=16)

def set_theme(cell):
    set_font(cell)
    cell.fill=PatternFill("solid", fgColor=Color(indexed=42))

def create_new_sheet(wb, table_name, table_desc, line_number, total_sheet):
    ws = create_sheet(table_name, table_desc, wb)
    # 汇总页 table_name 增加超链接，设置字体颜色为蓝色，下划线
    total_sheet['A'+str(line_number)] = table_name
    set_theme(total_sheet['A'+str(line_number)])
    link = "#" +table_name+"!A1"
    total_sheet['A'+str(line_number)].hyperlink=(link)
    total_sheet['A'+str(line_number)].font = Font(size=16, color=BLUE, underline="single")

    total_sheet['B'+str(line_number)] = table_desc
    set_font(total_sheet['B'+str(line_number)])
    return ws

def execute_export(db, schema, filename):
    sql = """
    SELECT T.TABLE_NAME,
        T.TABLE_COMMENT,
        C.COLUMN_NAME,
        C.COLUMN_TYPE,
        C.IS_NULLABLE,
        C.COLUMN_DEFAULT,
        C.COLUMN_COMMENT,
        C.EXTRA
    FROM information_schema.COLUMNS C
            INNER JOIN information_schema.TABLES T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
        AND C.TABLE_NAME = T.TABLE_NAME
    WHERE T.TABLE_SCHEMA = '""" + schema + """'
    and T.TABLE_NAME not in ('databasechangelog', 'databasechangeloglock');
    """
    cursor = db.cursor()

    cursor.execute(sql)
    print(cursor.rownumber)
    result = cursor.fetchone()
    table_name = ''
    

    line_number = 3
    ws = None
    ws1, wb = create_total()
    ws1_line_number = 2
    while result != None:
        if (result[0] != table_name):
            table_name = result[0]
            table_desc = result[1]
            # 创建新 sheet 页，并且写入汇总页
            ws = create_new_sheet(wb, table_name, table_desc, ws1_line_number, ws1)
            line_number = 3
            ws1_line_number += 1
        # 数据部分遍历插入 sheet
        set_columns(ws, line_number, result)
        line_number += 1
        result = cursor.fetchone()

    wb.save(filename)

# 生成列名字典，为了方便修改列宽时指定列，key:数字，从1开始；value:列名，从A开始
def get_num_colnum_dict():
    num_str_dict = {}
    A_Z = [chr(a) for a in range(ord('A'), ord('Z') + 1)]
    AA_AZ = ['A' + chr(a) for a in range(ord('A'), ord('Z') + 1)]
    A_AZ = A_Z + AA_AZ
    for i in A_AZ:
        num_str_dict[A_AZ.index(i) + 1] = i
    return num_str_dict

# 自适应列宽
def style_excel(excel_name):
    '''
    :param sheet_name:  excel中的sheet名
    :return:
    '''
    # 打开excel
    wb = load_workbook(excel_name)
    # 移除默认生成的空白 Sheet
    std= wb['Sheet']
    wb.remove(std)
    # 遍历 sheet，设置自适应列宽
    for sheet_name in wb.sheetnames:
        sheet = wb[sheet_name]
        max_column = sheet.max_column
        max_row = sheet.max_row
        max_column_dict = {}
        num_str_dict = get_num_colnum_dict()

        # 遍历全部列
        for i in range(1, max_column + 1):
            for j in range(1, max_row + 1):
                column = 0
                sheet_value = sheet.cell(row=j, column=i).value
                sheet_value_list = [k for k in str(sheet_value)]
                for v in sheet_value_list:
                    # 如果不是中文，宽度加 1.5，否则宽度加 3
                    if not '\u4e00' <= v <= '\u9fa5':
                        column += 1.5
                    else:
                        column += 3
                try:
                    if column > max_column_dict[i]:
                        max_column_dict[i] = column
                except Exception as e:
                    max_column_dict[i] = column
        # 设置列宽
        for key, value in max_column_dict.items():
            sheet.column_dimensions[num_str_dict[key]].width = value
    # 保存
    wb.save(excel_name)


if __name__ == "__main__":
    '''
    运行说明：pip3 install openpyxl pymysql
    '''
    ### 需配置内容
    schema = 'test'
    export_filename = schema + " 数据字典.xlsx"
    db_host="localhost"
    db_port=3306
    db_user="root"
    db_password="root"


    db = pymysql.connect(host=db_host, port=db_port, user=db_user, password=db_password, db="information_schema")
    execute_export(db, schema, export_filename)
    style_excel(export_filename)