# 读取MySql数据库，写入excel文件
import mysql.connector as mysc
from openpyxl import Workbook
wb = Workbook()
ws = wb.active

ip = "127.0.0.1"
cnn = mysc.connect(host=ip,user='user', password='password', database='db')
cursor = cnn.cursor()
#先写表头，用字段名
cursor.execute("desc fenl")
data = cursor.fetchall()
i=1
for da in data:
    ws.cell(1,i).value = da[0]
    i += 1
#再逐行写
cursor.execute("select * from fenl")
data = cursor.fetchall()
j=1
print('The program is running!')
for da in data:
    da2 = [str(da1) for da1 in da]  #将所有值转换为字符串，避免长串数字被科学记数
    ws.append(da2)
    print('\r',('progress:'+str(round(j/len(data)*100))+'%').ljust(15),end='')  #输出个进度显示
    j += 1
#ws.column_dimensions['A'].number_format='@'，这个设置只对无值格有效，因此无用
wb.save('mqtoxl.xlsx')    #同名文件会不提示直接覆盖
cnn.close()