1.现有数据和目标成果
1.1现有数据
源数据保存在数据库中,使用的数据库管理软件是PostgreSQL。
本质上来说,数据存储在数据库中是以记录存储在表上实现的,在excel表格中也是以记录的形式存在。所以数据库中表的列(字段)可以与excel表的列一一对应。形式大致如下:
1.2目标成果
导出成果是excel表格,文件后缀名是.xlsx。形式大致如下:
2.代码过程
2.1连接数据库获取数据
要获取数据库数据必须要先连接数据库,连接PostgreSQL的方法网上很多参考,也可以参考我源代码的方式。
执行SQL(结构化查询语言),获取数据库要导出数据的表的字段数,以及要导出的记录。
一个简单的示例如下:
'''连接数据获取数据,WHU_Fan,0704'''
def getData():
'''databese是要连接数据库的名字,user是访问用户(创建数据库时设置),password是创建数据库的密码,host填localhost,端口为安装数据库时设置的端口'''
'''这里是PostgreSQL的连接方法,MySQL也类似,端口可能不一样'''
conn = psycopg2.connect(database='test2',user='postgres',password='admin',host='localhost',port='5432')
cur = conn.cursor()
''''设置自己的sql语句'''
'''例如'''
tableName = 'outcome'
commandFindColumn = "select COLUMN_NAME from information_schema.COLUMNS where table_name='%s' "% (tableName)
'''执行SQL语句获取数据'''
cur.execute(commandFindColumn)
columnRows = cur.fetchall()
'''SQL语句:导出outcome表的全部'''
commandFindRecord = "select * from %s order by 相似度 desc"%(tableName)
'''执行SQL语句获取数据'''
cur.execute(commandFindRecord)
recordRows = cur.fetchall()
'''提交确认'''
conn.commit()
'''关闭连接'''
cur.close()
conn.close()
'''返回数据'''
return columnRows,recordRows
2.2解析数据
获取的字段变量columnRows,是一个list,顺序的包含数据库对应表的每一个字段。
获取的记录信息recordRows,也是一个list,包含所有满足条件的记录。
2.3写到excel中
excel中写入数据相当于给键值对dic赋值一样,sheet[loc:value]
1.先创建一个excel文件,然后创建一个sheet(excel的一页,一个excel可能包含多页)。
2.创建字段行,把表的字段先写出来。第一个字段对应A1,第二个是B1,依次类推,当字段数超过26时,添加AA,AB,AC,…
3.把记录写入表中,每条记录对应A2,B2,C2,…
4.保存表格。
示例代码如下:
'''写到excel,WHU_Fan,0704'''
def writeDataToExcel(name):
'''调用连接数据库函数'''
columnRows,recordRows = getData()
'''创建excel文件wb'''
wb = openpyxl.Workbook()
'''创建sheet,以传入的name为名字,默认索引0'''
wb.create_sheet(name, 0)
'''获取活动的sheet作为活动页面'''
sheet = wb.get_sheet_by_name(name)
'''获取当前日期,得到一个datetime对象如:(2019, 7, 2, 23, 12, 23, 424000)'''
'''#将获取到的datetime对象仅取日期如:2019-7-2'''
'''作为excel最后命名用'''
today = datetime.today()
today_date = datetime.date(today)
'''创建每列的字母集,当列数超过26时,添加AA,AB,AC...'''
myAlphbet = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z']
'''把每一个字段写入excel中,excel写入的顺序是A1,B1,C1,…'''
'''excel中写入数据相当于给键值对dic赋值一样,sheet[loc:value]'''
for i in range(len(columnRows)):
loc = myAlphbet[i] + str(1)
sheet[loc] = columnRows[i][0]
'''针对每条记录,写入表中,顺序是A2,B2,C2,…,A3,B3,C3,…,这解释为什么是 i+2'''
for i in range(len(recordRows)):
for j in range(len(recordRows[i])):
loc = myAlphbet[j] + str(i + 2)
sheet[loc] = recordRows[i][j]
'''保存excel文件,默认文件位置是当前文件夹'''
'''以传递的name+当前日期作为excel名称保存'''
wb.save(name+ '_' + str(today_date) + '.xlsx')
3.源代码
# encoding:utf-8
'''written by WHU_Fan,0704'''
import psycopg2
import openpyxl
from datetime import datetime
def getData():
conn = psycopg2.connect(database='test2',user='postgres',password='admin',host='localhost',port='5432')
cur = conn.cursor()
tableName = 'outcome'
commandFindColumn = "select COLUMN_NAME from information_schema.COLUMNS where table_name='%s' "%(tableName)
cur.execute(commandFindColumn)
columnRows = cur.fetchall()
'''导出outcome的全部'''
commandFindRecord = "select * from %s order by 相似度 desc"%(tableName)
cur.execute(commandFindRecord)
recordRows = cur.fetchall()
conn.commit()
cur.close()
conn.close()
return columnRows,recordRows
def writeDataToExcel(name):
columnRows,recordRows = getData()
wb = openpyxl.Workbook()
wb.create_sheet(name, 0)
sheet = wb.get_sheet_by_name(name)
today = datetime.today() #获取当前日期,得到一个datetime对象如:(2019, 7, 2, 23, 12, 23, 424000)
today_date = datetime.date(today) #将获取到的datetime对象仅取日期如:2019-7-2
#当列数超过26时,添加AA,AB,AC...
myAlphbet = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z']
for i in range(len(columnRows)):
loc = myAlphbet[i] + str(1)
sheet[loc] = columnRows[i][0]
for i in range(len(recordRows)):
for j in range(len(recordRows[i])):
loc = myAlphbet[j] + str(i + 2)
sheet[loc] = recordRows[i][j]
wb.save(name+ '_' + str(today_date) + '.xlsx') #以传递的name+当前日期作为excel名称保存
if __name__ == '__main__':
writeDataToExcel("outcome")
print("succeed")
4.成果总结
导出excel本身的过程并不复杂,重要为以下几点:
1.如何给sheet赋值,excel表的loc需要与获取的字段和记录的结构对应起来。
2.如何利用循环实现赋值,详见源代码的两个循环。