Background
- 网上很多都是创建新的excel文件,然后写入内容,这里是把csv文件中的相关数据解析出来,然后把数据填写到原有的模板中。【每次会先复制模板生成新文件,新文件名包含当前日期,然后把数据填写到新文件中】
- python提供了很多修改excel的模块,包括xlsxWriter、xlrd&xlwt、openPyXL、Microsoft Excel API、Pandas等;其他的不作介绍,这里使用的是OpenPyXL模块。
- OpenPyXL:较好的支持对xlsx文件的修改,功能比较强大,适用于需要处理xlsx文件,需要修改xlsx文件中的值,最后生成xlsx。openpyxl(可读写excel表)专门处理Excel2007及以上版本产生的xlsx文件【不支持xls,但xls和xlsx之间转换容易】
csv文件
文件内容是从数据库中查出来的sql结果,共十组,第一组为模板中M列的数据(24小时单量),然后依次为10点,11点,12点,13点,14点,15点,16点,17点,18点。
pro_com_code | create_sum | finish_sum
--------------+------------+------------
11000000 | 2586 | 2096
12000000 | 999 | 819
13000000 | 3346 | 2642
14000000 | 1007 | 671
15000000 | 1165 | 861
21000000 | 2452 | 2083
21020000 | 714 | 602
22000000 | 867 | 702
23000000 | 935 | 762
31000000 | 1367 | 1128
32000000 | 6268 | 4945
33000000 | 4960 | 3983
33020000 | 689 | 579
34000000 | 2032 | 1643
35000000 | 1937 | 1434
35020000 | 620 | 501
36000000 | 2059 | 1597
37000000 | 3255 | 2427
37020000 | 753 | 645
41000000 | 2141 | 1634
42000000 | 2497 | 2052
43000000 | 2353 | 1815
44000000 | 7519 | 5874
44030000 | 1609 | 1282
45000000 | 1021 | 805
46000000 | 333 | 248
50000000 | 1419 | 1164
51000000 | 3203 | 2509
52000000 | 1655 | 1420
53000000 | 1737 | 1077
54000000 | 161 | 80
61000000 | 1525 | 1175
62000000 | 857 | 666
63000000 | 341 | 294
64000000 | 602 | 463
65000000 | 2271 | 1945
(36 rows)
pro_com_code | create_sum | finish_sum
--------------+------------+------------
11000000 | 233 | 158
12000000 | 53 | 38
13000000 | 288 | 209
14000000 | 92 | 46
15000000 | 103 | 65
21000000 | 227 | 178
21020000 | 46 | 40
........后面的省略
模板文件
源码
import csv
import shutil
from openpyxl import load_workbook
from time import strftime, localtime
# 去除字符串中空格、Tab及回车的方法
def strip_str(str_with_blank):
return str_with_blank.replace(" ", "").replace("\t", "").strip()
# 读取原始数据文件
csv_file = open('F:/wlf/sql_output.csv', 'r')
csv_data = csv.reader(csv_file)
rows_raw = [row for row in csv_data]
rows_deal = []
# 去掉无用行和行里的空白
for i in range(len(rows_raw)):
rows = rows_raw[i]
if len(rows) != 0:
row_line = rows[0]
if '0000' in row_line:
rows_deal.append(strip_str(row_line))
# 写入顺序[24小时单量,10点,11点,12点,13点,14点,15点,16点,17点,18点]
# 写入模板列的顺序[M, D, E, F, G, H, I, J, K, L]
cols_order_template = ['M', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L']
# 原模板文件
file_origin = 'F:/wlf/平台监控数据模板.xlsx'
# 在相同路径下复制模板文件,生成的新文件名带日期,例如:平台监控数据-2020-10-23.xlsx
now_date = strftime("%Y-%m-%d", localtime())
file_now = 'F:/wlf/平台监控数据-%s.xlsx' % now_date
shutil.copy(file_origin, file_now)
# 获取模板 excel 的操作对象
wb = load_workbook(filename=file_now)
# 根据 Sheet1 这个名字来获取 Sheet1
sheet1 = wb['Sheet1']
# 这层循环遍历所有要写入数据的列
for i in range(len(cols_order_template)):
# 这层循环取出每列对应的数值
data_col = []
for j in range(i * 36, 35 * (i + 1) + i + 1):
create_sum = rows_deal[j].split('|')[1]
finish_sum = rows_deal[j].split('|')[2]
data_col.append(create_sum)
data_col.append(finish_sum)
# 当 i 为 0 时,取出 M 列对应的数据,计算所有分公司 24 小时合计数量
create_sum_all = []
finish_sum_all = []
if i == 0:
# 奇数行为发起数量
create_sum_all = data_col[::2]
# 偶数行为完成数量
finish_sum_all = data_col[1::2]
# 计算总数
total_create = 0
total_finish = 0
for ii in range(0, len(create_sum_all)):
total_create += int(create_sum_all[ii])
total_finish += int(finish_sum_all[ii])
# 写入计算结果 D75 和 D76
sheet1['D75'] = total_create
sheet1['D76'] = total_finish
# 这层循环写入每列的值【每个省份的发起量和完成量】 每一列都是从第 3 行到第 74 行
for k in range(len(data_col)):
index_cell = k + 3
col_word = cols_order_template[i]
cell_code = col_word + str(index_cell)
sheet1[cell_code] = data_col[k]
# 修改后保存 excel
wb.save(file_now)
print('success')