Python-データ表のデータをエクセルにエクスポートします。

8892 ワード

'''
  :     ,        ,           ,   excel  
  :
1、'select * from %s' ,          
2、         excel  xlwt
'''


import pymysql,hashlib,xlwt

def op_mysql(sql:str):
    mysql_info = {
        'host': 'XXX.XXX.XXX.XXX',
        'port': XXXX,
        'password': 'XXXX',
        'user': 'XXXX',
        'db': 'XXXX',
        'charset': 'utf8',
        'autocommit': True
    }
    result = '    '
    conn = pymysql.connect(**mysql_info)
    # cur = conn.cursor(pymysql.cursors.DictCursor) #    
    cur = conn.cursor() #    
    cur.execute(sql)
    field = [ t[0] for t in cur.description ] #           
    if sql.strip().lower().startswith('select'):
        # result  = cur.fetchone()
        result  = cur.fetchall()
    cur.close()
    conn.close()
    print('     ',field)
    print('     ',result)
    result = list(result) #        ,    ,    list
    result.insert(0,field) #      
    return result

def export_excel(table_name):
    sql ='select * from %s;'%table_name
    result = op_mysql(sql)
    book = xlwt.Workbook()
    sheet = book.add_sheet('sheet')
    for row, line in enumerate(result):
        for col, t in enumerate(line):
            sheet.write(row, col, t)
    book.save('%s.xls'%table_name)
export_excel('CJ_test')
export_excel('app_myuser')


import xlwt
book = xlwt.Workbook()
sheet = book.add_sheet('sheet')l = [
    [1,'dsk','xxdfsdfsd',0],
    [2,'ldd','xxdfsdfsd',0],
    [3,'lsdd','xxdfsdfsd',0],
    [4,'lsd1','xxdfsdfsd',0],
]

#
# row = 0#  
# for line in l: #      ,     ,  excel    
#     # col = 0#     [1,'dsk','xxdfsdfsd',0]
#     col = 0
#     for t in line:#           ,         
#         sheet.write(row,col,t)
#         col+=1
#     row+=1
#

#
for row,line in enumerate(l):
    for col,t in enumerate(line):
        sheet.write(row,col,t)

book.save('user.xls')
 
転載先:https://www.cnblogs.com/huoxn/p/10919413.html