前言
年底来了,这时候公司各个部门开始算年业绩等情况。由于公司报表系统还未完善,只能靠人力查询数据库生成报表。刚开始一两个报表需求还好,使用手工帮他们做excel。最近一下子收到几十个报表要做。如果是手工做那还真的就是要人命了。只好自己做个小程序来自动生成了。
先决条件
这边的程序相对比较简单,就不像之前一样画流程图了。一下介绍一下我们需要的python 模块:
模块名
版本
其他
mysql-connector
2.1.3
MySQL官网的python链接模块
xlwt
1.0.0
生成excel模块
程序展示
主程序
if __name__ == '__main__':
info = {
'host' :'192.168.137.11',
'user' :'root',
'password':'root',
'database':'test'
}
conn = mysql.connector.connect(**info)
cursor = conn.cursor()
sql = get_sql()
cursor.execute(sql)
# 获得excel的title
title = get_title(cursor)
# 获得需要的数据
data = get_select_data(cursor)
# 获得每一列的最大长度
max_len = get_col_max_length(data, title)
work_book = xlwt.Workbook(encoding='utf-8')
# 创建一个excel模板
work_sheet = work_book.add_sheet('查询数据')
# 生成excel title
work_sheet = create_excel_title(work_sheet, title, title_style)
# 生成 excel 数据
work_sheet = create_excel_body(work_sheet, data)
# 设置每一列适当的长度
work_sheet = set_work_sheet_col_len(work_sheet, max_len)
# 保存 excel
work_book.save('data_{time}.xls'.format(time=time.time()))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
if__name__=='__main__':
info={
'host':'192.168.137.11',
'user':'root',
'password':'root',
'database':'test'
}
conn=mysql.connector.connect(**info)
cursor=conn.cursor()
sql=get_sql()
cursor.execute(sql)
# 获得excel的title
title=get_title(cursor)
# 获得需要的数据
data=get_select_data(cursor)
# 获得每一列的最大长度
max_len=get_col_max_length(data,title)
work_book=xlwt.Workbook(encoding='utf-8')
# 创建一个excel模板
work_sheet=work_book.add_sheet('查询数据')
# 生成excel title
work_sheet=create_excel_title(work_sheet,title,title_style)
# 生成 excel 数据
work_sheet=create_excel_body(work_sheet,data)
# 设置每一列适当的长度
work_sheet=set_work_sheet_col_len(work_sheet,max_len)
# 保存 excel
work_book.save('data_{time}.xls'.format(time=time.time()))
获得的sql方法
def get_sql():
'''
创建需要的sql语句
'''
sql = '''
SELECT tmp.mobile_phone AS '电话号码',
tmp.name AS '其中一个姓名',
tmall_shop_info.name AS '品牌商名称',
store.store_name AS '店铺名称',
tmp.num AS '重复个数'
FROM (
SELECT mobile_phone,
name,
store_no,
tmall_shop_id,
COUNT(*) AS num
FROM store_guide
WHERE mobile_phone IS NOT NULL
GROUP BY mobile_phone
HAVING num > 1
) AS tmp
LEFT JOIN tmall_shop_info USING(tmall_shop_id)
LEFT JOIN store USING(store_no)
'''
return sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
defget_sql():
'''
创建需要的sql语句
'''
sql='''
SELECT tmp.mobile_phone AS '电话号码',
tmp.name AS '其中一个姓名',
tmall_shop_info.name AS '品牌商名称',
store.store_name AS '店铺名称',
tmp.num AS '重复个数'
FROM (
SELECT mobile_phone,
name,
store_no,
tmall_shop_id,
COUNT(*) AS num
FROM store_guide
WHERE mobile_phone IS NOT NULL
GROUP BY mobile_phone
HAVING num > 1
) AS tmp
LEFT JOIN tmall_shop_info USING(tmall_shop_id)
LEFT JOIN store USING(store_no)
'''
returnsql
如果需要生成其他sql能查询出的数据直接就替换掉上面的sql就好了。
其他方法
def get_title(cursor):
'''
通过游标获得excel文件title
'''
return cursor.column_names
def get_select_data(cursor):
'''
通过游标获得数据列表(list)
'''
return [row for row in cursor]
def create_excel_title(work_sheet, title, title_style=None):
'''
生产exceltitle
'''
if not title_style:
title_style = default_style
for col_index, col_name in enumerate(title):
work_sheet.write(0, col_index, col_name, title_style)
return work_sheet
def create_excel_body(work_sheet, body, body_style=None):
'''
生成excel body信息
'''
if not title_style:
body_style = default_style
for row_num, row_data in enumerate(data, 1):
for col_index, col_value in enumerate(row_data):
work_sheet.write(row_num, col_index, col_value)
return work_sheet
def get_col_max_length(data, title):
'''
获得数据每列最大值长度
'''
col_len = map(len, map(str, title))
func = lambda x, y: y if y>x else x
for row in data:
row_len = map(len, map(str, row))
col_len = map(func, col_len, row_len)
return col_len
def set_work_sheet_col_len(work_sheet, max_len):
'''
设置列长度
'''
for col, len in enumerate(max_len):
work_sheet.col(col).width = 256 * (len + 1)
return work_sheet
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
defget_title(cursor):
'''
通过游标获得excel文件title
'''
returncursor.column_names
defget_select_data(cursor):
'''
通过游标获得数据列表(list)
'''
return[rowforrowincursor]
defcreate_excel_title(work_sheet,title,title_style=None):
'''
生产exceltitle
'''
ifnottitle_style:
title_style=default_style
forcol_index,col_nameinenumerate(title):
work_sheet.write(0,col_index,col_name,title_style)
returnwork_sheet
defcreate_excel_body(work_sheet,body,body_style=None):
'''
生成excel body信息
'''
ifnottitle_style:
body_style=default_style
forrow_num,row_datainenumerate(data,1):
forcol_index,col_valueinenumerate(row_data):
work_sheet.write(row_num,col_index,col_value)
returnwork_sheet
defget_col_max_length(data,title):
'''
获得数据每列最大值长度
'''
col_len=map(len,map(str,title))
func=lambdax,y:yify>xelsex
forrowindata:
row_len=map(len,map(str,row))
col_len=map(func,col_len,row_len)
returncol_len
defset_work_sheet_col_len(work_sheet,max_len):
'''
设置列长度
'''
forcol,leninenumerate(max_len):
work_sheet.col(col).width=256*(len+1)
returnwork_sheet
以上的get_col_max_length方法中使用了map函数来获得每一列的最大长度是多少,为了能在excel中能设置适当的宽度。
excel样式设置
# 默认样式
default_style = xlwt.easyxf('''
pattern: pattern solid;
borders: left 1, right 1, top 1, bottom 1;
align: horiz center''',
num_format_str='0,000.00')
# 标题栏样式
title_style = xlwt.easyxf('''
pattern: pattern solid, fore_colour yellow;
font: name Times New Roman, color-index black, bold on;
borders: left 1, right 1, top 1, bottom 1;
align: horiz center''',
num_format_str='0,000.00')
# 时间格式样式
time_style = xlwt.easyxf(num_format_str='YYYY-MM-DD h:mm:ss')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 默认样式
default_style=xlwt.easyxf('''
pattern: pattern solid;
borders: left 1, right 1, top 1, bottom 1;
align: horiz center''',
num_format_str='0,000.00')
# 标题栏样式
title_style=xlwt.easyxf('''
pattern: pattern solid, fore_colour yellow;
font: name Times New Roman, color-index black, bold on;
borders: left 1, right 1, top 1, bottom 1;
align: horiz center''',
num_format_str='0,000.00')
# 时间格式样式
time_style=xlwt.easyxf(num_format_str='YYYY-MM-DD h:mm:ss')
源代码
作者信息
昵称:HH
QQ:275258836
感觉本文内容不错,读后有收获?