"""
读取数据写入Excel文件
一个Excel文件 ---> 工作簿 ---> workbook
一个工作簿下面可以有一个或多个工作表 ---> sheet
"""
import MySQLdb
import xlwt

conn = MySQLdb.connect(host='47.104.31.138', port=3306,
                       user='root', password='Luohao.618',
                       database='hrs', charset='utf8')
try:
    with conn.cursor() as cursor:
        cursor.execute(
            'select eno, ename, job, sal, dname from tb_emp t1 '
            'inner join tb_dept t2 on t1.dno=t2.dno'
        )
        wb = xlwt.Workbook()
        sheet = wb.add_sheet('员工信息表')
        titles = ('工号', '姓名', '职位', '月薪', '部门')
        title_style = xlwt.XFStyle()
        font = xlwt.Font()
        font.name = '行楷-简'
        font.height = 400
        font.colour_index = 4
        font.bold = True
        title_style.font = font
        aligment = xlwt.Alignment()
        aligment.horz = xlwt.Alignment.HORZ_CENTER
        aligment.vert = xlwt.Alignment.VERT_CENTER
        title_style.alignment = aligment
        for col_index, title in enumerate(titles):
            sheet.write(0, col_index, title, title_style)
        for row_index, row in enumerate(cursor.fetchall()):
            for col_index, value in enumerate(row):
                sheet.write(row_index + 1, col_index, value)
        wb.save('hrs.xls')
except MySQLdb.MySQLError as err:
    print(err)
    conn.rollback()
finally:
    conn.close()