如何使用Python将数据从MySQL数据库保存到excel文件，而不是数字

Below is the script. I am pulling some data and save to Excel In the output file, all column are marked "Number stored as Text" What can I do in the script to change all columns to number (int)? Thanks

下面是脚本。我正在提取一些数据并保存到Excel在输出文件中，所有列都标记为“Number as as Text”我可以在脚本中将所有列更改为number（int）？谢谢

wb = openpyxl.Workbook()


db = MySQLdb.connect(host="...",port=...,user="...",passwd="...",db='...')
cursor = db.cursor()

cursor.execute("""
SELECT shopid,userid,count(orderid)as no_of_orders
from order_viw
group by shopid,userid
""")

data = cursor.fetchall()

rowNum = 2
col = 1
sheet = wb.get_sheet_by_name("Sheet")

for shopid,userid,no_of_orders in data:
    sheet.cell(column=col, row=rowNum, value="%d" % shopid)
    sheet.cell(column=col+1, row=rowNum, value="%d" % userid)
    sheet.cell(column=col+2, row=rowNum, value="%d" % no_of_orders)
    rowNum += 1
wb.save('C:/example.xlsx')
db.close()

1 个解决方案

#1

As a forewarning, I'm running on Linux, have not got a MySql database and I am using libreoffice to check the results.
With those caveats in place, the following looks like it works for me:

作为预警，我在Linux上运行，没有MySql数据库，我使用libreoffice检查结果。有了这些警告，以下内容看起来对我有用：

import openpyxl
wb = openpyxl.Workbook()
rowNum = 2
col = 1
sheet = wb.get_sheet_by_name("Sheet")
#data = [[42,1,43],[82,2,28]]
data = [['42','1','43'],['82','2','28']]
for shopid,userid,no_of_orders in data:
    sheet.cell(column=col, row=rowNum).value=int(shopid)
    sheet.cell(column=col+1, row=rowNum).value=int(userid)
    sheet.cell(column=col+2, row=rowNum).value=int(no_of_orders)
    rowNum += 1
wb.save('example.xlsx')