#!/bin/env python # -*- coding:utf-8 -*- import xlwt, os, time, pymysql, datetime f = xlwt.Workbook(encoding='utf-8') # 创建Exce def ExcelWrite(TableHead=None, SQLData=None, TableName='sheet1'): LineNum = 1 sheet = f.add_sheet(TableName, cell_overwrite_ok=True) # 创建一个sheet # 表头数据 for i in TableHead: sheet.write(0, 0 + TableHead.index(i), i) # 第一个0是行数 # 表数据 for line in SQLData.split('\n'): for data in line.split('\n'): ColumnNum = 0 for noe in data.split('\t'): sheet.write(LineNum, ColumnNum, noe) ColumnNum += 1 LineNum += 1 def MySQLData(addr=None, user=None, passwd=None, database=None, port=3306, charset='utf8', sql=None): try: conn = pymysql.connect(host=addr, user=user, passwd=passwd, db=database, port=port, charset=charset) cur = conn.cursor() # 获取一个游标 cur.execute(sql) # 执行查询 data = cur.fetchall() # 获取查询到数据,返回的是元组数据结构 conn.commit() # 提交事务 cur.close() # 关闭游标 conn.close() # 释放数据库资源 except Exception as e: conn.rollback() # 如果发生错误则回滚 exit(e) return data def DataProcess(data=None): sqldata = """""" # 从数据库中查到的数据进行处理,便于表格的存储。 for column in range(len(data)): for row in range(len(data[column])): sqldata = sqldata + str(data[column][row]) + '\t' sqldata = sqldata + '\n' return sqldata if __name__ == '__main__': excelname = "数据.xls" # 如果有多页数据,只复制进行修改如下五行就可以了, select_sql = "SELECT user FROM user;" tablehead = ['用户'] tablename = "sheet1" # 这个名字必须唯一。 # 数据库查询数据。 data = MySQLData(addr='192.16.1.2', user='user', passwd='useruser', database='aa', sql=select_sql) # 存储表格。 ExcelWrite(TableHead=tablehead, SQLData=DataProcess(data=data), TableName=tablename) # 如果有多个页,一定要最后保存要不然会覆盖数据的。 f.save(excelname)