以下这写代码是把公司的excel报表的原数据转变成需要的excel数据的过程。
def stepone2725(tuple,data_path,dict_fen):
for i in tuple:
'''
下面这段代码也可以用"list(iter(data_path.rglob(f'{i}果蔬销售源报表.xlsx')))[0]"
由于"data_path.rglob(f'{i}果蔬销售源报表.xlsx')"在此处的结果只有一个，所以将其转换成
列表加下标进行提取，但是绝不可用"data_path.rglob(f'{i}果蔬销售源报表.xlsx')[0]",
因为"data_path.rglob(f'{i}果蔬销售源报表.xlsx')"只是个对象，对象没有下标。如果非要迭代
可以用"for f_dir in data_path.rglob(f'{i}果蔬销售源报表.xlsx'):",这样会在语句上较
为麻烦。
'''
for f_dir in data_path.rglob(f'{i}果蔬销售源报表.xlsx'):
file=pd.read_excel(f_dir)#读取文件
file_fen = file.groupby(by="商品分类编码")#按字段“商品分类编码”进行分组
for name,group in file_fen:#通过遍历分组文件，分别提取两个类别（蔬菜、水果）并分别创建文件夹
p = Path(r'G:\生鲜每月数据分析\数据源\%s\%s' % (f_dir.parts[3],dict_fen[name]))#指定路径
p.mkdir(parents=True,exist_ok=True)#创建路径，如果找不到要建文件夹的上级则直接新建，如果新建的文件夹已存在也不会报错
print(f'正在生成{dict_fen[name]}报表\n请稍后...')
group.to_excel(p/Path(i+"%s销售源报表.xlsx" % dict_fen[name]),index=0)#将分组后的DataFrame对象写入指定路径中
def concat_excel(data_path):
'''
这段代码是将存放在"./数据源/2023年数据源"下面的"水果"和"蔬菜"文件夹和
"./数据源/2024年数据源"下面的"水果"和"蔬菜"文件夹里的“*销售源报表.xlsx”
通过pd.concat()函数拼接起来，并增加'日期起止'列，生成文件“*对比表.xlsx”
存放在“./数据源/合并分析”下面以日期起止格式命名的文件夹里。
'''
for i in "蔬菜","水果":#通过外for循环,分别从"水果"和"蔬菜"文件夹下面提取文件
file_list = []#用于存放要拼接的DataFrame对象
date_list=[]#用于存放日期字符，用于生成文件路径
#通过遍历在要查找的文件路径加入日期索引变量date_indexes_tuple
for t in tuple:
'''
用于生成date_list(日期拼接字符串列表)和file_List(用于拼接的文件对象列表)
用于concat_excel函数的内循环
'''
for file_dir in data_path.rglob(f'./{i}/{t}{i}*.xlsx'):
file = pd.read_excel(file_dir) # 读入文件
date_list.append("-".join(re.findall(r'\d+', str(file_dir))[1:4]) + "至" + "-".join(
re.findall(r'\d+', str(file_dir))[4:])) # 通过re模块的findall，找出数字在进行拼接和切片提取，再用"-".join
# 拼接后生成日期字符串，添加到date_list列表中
# 插入'日期起止'列，用列表推到式生成以'部门编码'列长度一致的日期字符串列表作为，新列'日期起止'的值value
file.insert(loc=0, column='日期起止',
value=["-".join(re.findall(r'\d+', str(file_dir))[1:4]) + "至" + "-".join(
re.findall(r'\d+', str(file_dir))[4:]) for i in range(len(file['部门编码']))])
file_list.append(file) # 将DataFrame对象添加到列表file_list里
file_mer=pd.concat(file_list)#拼接
pth_x = data_path/Path('合并分析/%s'% "__".join(date_list))#按日期字符串生成路径
pth_x.mkdir(parents=True,exist_ok=True)#创建路径，如果找不到要建文件夹的上级则直接新建，如果新建的文件夹已存在也不会报错
print(f'正在生成{pth_x.name}文件夹\n请稍后...')
file_mer.to_excel(pth_x/Path('%s%s对比表.xlsx'%(i,"和".join(date_list))),index=0)#将新DataFrame写入新路径
def creat_pivot(tuple,st_dic,data_path):
'''
这段代码是把存放在“./数据源/合并分析”下面以日期起止格式命名的文件夹里的文件“*对比表.xlsx”
通过pd.pivot_table()分别以'1':"部门名称",'2':"商品名称"为行标，以values=['销售金额','销售毛利','毛利率']
为值，以columns='日期起止'为列标创建数据透视表，并以"*透视表*.xlsx"文件名格式存放在“./数据源/合并分析”文件加下
'''
for i in data_path.rglob("*透视表*.xlsx"):#先把文件夹下已存在的透视表文件删除，这里必须是rglob逐级遍历才行
i.unlink(missing_ok=True)
for st in range(1,3):#外循环用range生成字典的键
for i in data_path.rglob(f'*{tuple[0]}和{tuple[1]}和{tuple[2]}对比表.xlsx'):#获取指定日期索引的文件路径
file=pd.read_excel(i)#向内存读入获取的对比表路径
#生成透视表
f=pd.pivot_table(file,values=['销售金额','销售毛利','毛利率'],index=st_dic[str(st)],columns='日期起止',aggfunc={'销售金额':'sum','销售毛利':'sum','毛利率':'mean'})
print(f'正在生成{st_dic[str(st)][:2]}透视表{i.stem[:-3]}\n请稍后...')
#将获取的透视表写入指定的路径
f.to_excel(r'G:\生鲜每月数据分析\数据源\合并分析\%s透视表%s.xlsx'%(st_dic[str(st)][:2],i.stem[:-3]))
def format_excel(tuple, data_path):#初步格式化，是透视表更具有表格的性质
'''
这段代码主要是将"./数据源/合并分析"下面的临时透视表格式后存入
"./数据源/合并分析/格式过分析表"
'''
print('初步格式过程，请稍后...')
app = xw.App(visible=False, add_book=False) # 启动excel程序，与app.quit()相对应，有启动就有退出，否则会出错
format_file_dir = data_path / Path('合并分析/格式过分析表') / "__".join(tuple) # 格式后文件存储的上一级路径"*__*"
for i in data_path.rglob('*透视表*%s和%s和%s.xlsx' % (tuple[0],tuple[1],tuple[2])):
workbook = app.books.open(i)
sheet = workbook.sheets[0]
columns=sheet.range('a2').expand('table').shape[1]#获取sheet总列数
area = sheet.range((1,2),(1,columns))#指定数据区域的第一行
area.unmerge()#主要是值（'销售金额','销售毛利','毛利率'）字段取消合并
# 用for循环分别从zip()函数对象中分别取出例如(2,(‘b2:c2’)),(4,(‘d2:e2’)),(6,(‘f2:g2’))
# 这样就可以让sheet.range('b2:c2')两个单元格分别加上sheet.range(1,2)也就是“b1”的值
# 这样就可以让sheet.range('d2:e2')两个单元格分别加上sheet.range(1,4)也就是“d1”的值
# 这样就可以让sheet.range('f2:g2')两个单元格分别加上sheet.range(1,6)也就是“f1”的值
for f, g in zip(range(2, columns, 3), (list(range(2,columns+1))[:len(list(range(2,columns+1)))//3], list(range(2,columns+1))[len(list(range(2,columns+1)))//3:-len(list(range(2,columns+1)))//3], list(range(2,columns+1))[-len(list(range(2,columns+1)))//3:])):
for a in g:
sheet.range(2,a).value = str(sheet.range(2,a).value) + str(sheet.range(1, f).value) # 使“日期起止”行的每一列标都加上值的列标也就是
# '销售金额','销售毛利','毛利率'，形成类似与
# “2024-5-1至2024-5-15销售金额”的列标
data = sheet.range('b2').expand('right').value
sheet.range('b3').value = data # 由于现在的第3行是空行，故把第二行值赋第3行，等待进一步删除多余的行
format_file_dir.mkdir(parents=True, exist_ok=True) # 创建路径，如果找不到要建文件夹的上级则直接新建，如果新建的文件夹已存在也不会报错
format_filename = Path("格式表" +i.name[:2] + i.name[5:]) # 构建要保存的文件名格式“*格式表*.xlsx”
workbook.save(format_file_dir / format_filename) # 保存文件
app.quit() # 退出excel,很重要哦，不然下面的程序会出错哦。
# 下面会多次出现“for i in format_file_dir.rglob('*格式表*%s和%s.xlsx'%(tuple[0],tuple[1])):”
# 原因是因为rglob()不可重复迭代，对已经初步格式过的文件“*格式表*.xlsx”继续格式。
for i in format_file_dir.rglob(f'格式*{tuple[0]}和{tuple[1]}和{tuple[2]}.xlsx'):
file = pd.read_excel(i, header=2, index_col=0) # 读入文件，列标签以第3行命名，行标签以第一列命名
file.insert(loc=0, column='销售同比', value=None)
file.insert(loc=0, column='销售环比', value=None)
file.insert(loc=0, column='毛利同比', value=None)
file.insert(loc=0, column='毛利环比', value=None) # 在第一列（不是行标签列）前先后插入“销售同比”，“毛利同比”两列
fil = file.fillna(value=0) # 以零填充所有缺失值，这里要用到“pd.set_option('future.no_silent_downcasting', True)”
# 否则将不能兼容
p = ~fil[fil.columns[-1]].isin([0]) # 从最后一列（本期‘销售金额’列）查找所有不是零的数据，赋给p
fil = fil[p] # 将过滤掉零的数据重新赋给fil
fil.sort_values(by=fil.columns[-1], ascending=False, inplace=True) # 按本期‘销售金额’降序排序
fil.to_excel(i, columns=list(fil.columns)[::-1]) # 写入excel，列标签是原来列标签的反转，也就是反向排序。用到了list[::-1]的语法
def fomula_cell():#给最后几列添加公式
"""
此函数是为了在最后几列添加公式
"""
format_file_dir = data_path / Path('合并分析/格式过分析表') / "__".join(tuple) # 格式后文件存储的上一级路径"*__*"
app = xw.App(visible=False, add_book=False) # 重新启动excel,继续格式，很重要哦，因为刚才已经退出，所以必须重新启动
for i in format_file_dir.rglob(
f'格式*{tuple[0]}和{tuple[1]}和{tuple[2]}.xlsx'): # 因为rglob()不可重复迭代，对已经初步格式过的文件“*格式表*.xlsx”继续格式。
print(f'正在给{i.name}添加公式\n请稍等...')
workbook = app.books.open(i)
worksheet = workbook.sheets[0]
# 下面是指定以列为单位的单元格区域
col_b = worksheet.range('b2').expand('down')
col_c = worksheet.range('c2').expand('down')
col_d = worksheet.range('d2').expand('down')
col_e = worksheet.range('e2').expand('down')
col_f = worksheet.range('f2').expand('down')
col_g = worksheet.range('g2').expand('down')
col_h = worksheet.range('h2').expand('down')
col_i = worksheet.range('i2').expand('down')
col_j = worksheet.range('j2').expand('down')
col_k = worksheet.range('k2').expand('down')
col_l = worksheet.range('l2').expand('down')
col_m = worksheet.range('m2').expand('down')
col_n = worksheet.range('n2').expand('down')
# 下面这段代码是分别在h--n列写入公式，用到了f-sting语法，他的优点是无须事先转换数据类型就能
# 将不同的数据类型拼接成字符串。f-sting的基本格式是用“F”或“f”引领的字符串，然后在字符串中用大括号{}
# 标明要拼接的变量。
for b, c, d, e, f, g, h, i,j,k,l,m,n in zip(col_b, col_c, col_d, col_e, col_f, col_g, col_h, col_i,col_j,col_k,col_l,col_m,col_n):
d_addr = d.get_address(row_absolute=False, column_absolute=False)
b_addr = b.get_address(row_absolute=False, column_absolute=False)
c_addr = c.get_address(row_absolute=False, column_absolute=False)
e_addr = e.get_address(row_absolute=False, column_absolute=False)
f_addr = f.get_address(row_absolute=False, column_absolute=False)
g_addr = g.get_address(row_absolute=False, column_absolute=False)
h.formula = f'=text(IFERROR({e_addr}/{b_addr},0),"0.00%")'
i.formula = f'=text(IFERROR({f_addr}/{c_addr},0),"0.00%")'
j.formula = f'=text(IFERROR({g_addr}/{d_addr},0),"0.00%")'
k.formula = f'=TEXT(IFERROR(({b_addr}-{d_addr})/{d_addr},0),"0.00%")'
l.formula = f'=TEXT(IFERROR(({b_addr}-{c_addr})/{c_addr},0),"0.00%")'
m.formula = f'=TEXT(IFERROR(({e_addr}-{g_addr})/abs({g_addr}),0),"0.00%")'
n.formula = f'=TEXT(IFERROR(({e_addr}-{f_addr})/abs({f_addr}),0),"0.00%")'
workbook.save()
app.quit()
"""
下面这几句代码没有什么用处
"""
# last_cell = worksheet.used_range.last_cell
# insert_row_index = last_cell.row+1
# #在下方插入一行
# worksheet.range((insert_row_index, 1)).api.EntireRow.Insert()
def insert_newrow():
"""
此函数是为了在表格结尾插入汇总行
"""
format_file_dir = data_path / Path('合并分析\格式过分析表') / "__".join(tuple) # 格式后文件存储的上一级路径"*__*"
for i in format_file_dir.rglob(
f'格式*{tuple[0]}和{tuple[1]}和{tuple[2]}.xlsx'): # 因为rglob()不可重复迭代，对已经初步格式过的文件“*格式表*.xlsx”继续格式。
print(f'正在给{i.name}添加汇总行\n请稍等...')
file=pd.read_excel(i)
sum_list=['汇总']
for col in file.columns[1:]:
if file[col].dtype == 'float64':
sum_list.append(file[col].sum())
else:
sum_list.append(0)
file.loc['汇总']=sum_list
file.to_excel(i,index=0)
def formatworkbook():
app = xw.App(visible=False, add_book=False) # 启动excel程序，与app.quit()相对应，有启动就有退出，否则会出错
format_file_dir = data_path / Path('合并分析\格式过分析表') / "__".join(tuple) # 格式后文件存储的上一级路径"*__*"
for i in format_file_dir.rglob(
f'格式*{tuple[0]}和{tuple[1]}和{tuple[2]}.xlsx'): # 因为rglob()不可重复迭代，对已经初步格式过的文件“*格式表*.xlsx”继续格式。
print(f'正在给{i.name}终极格式化\n请稍等...')
workbook = app.books.open(i)
worksheet = workbook.sheets[0]
worksheet.autofit() # 自动调节sheet行高列高
color_dict={2:'#FF0000',0:'#808080',1:'#0000FF'}
for i in range(2,11):
worksheet.range(1,i).color=color_dict[i%3]#按列号填充颜色
for i in range(11,15):
worksheet.range(1,i).color=color_dict[i%2]#按列号填充颜色
area1 = worksheet.range('a1').expand('right')
area2 = worksheet.range('a2').expand('table') # 指定两个单元格区域
worksheet.range('a1').color = '#008000' # 设置a1单元格的填充色为绿色
area1.api.WrapText = True # 设置自动换行
# 分别设置area1区域的字体、字号、字体粗细、字体颜色
area1.font.name = '微软雅黑'
area1.font.size = 11
area1.font.bold = True
area1.font.color = '#FFFF00'
# 分别设置area2区域的字体、字号、字体粗细、字体颜色
area2.font.name = '微软雅黑'
area2.font.size = 9
area2.font.bold = False
area2.font.color = '#000000'
a1 = worksheet.range('a1').expand('table')
a1.api.HorizontalAlignment = -4108 # 设置水平居中
a1.api.VerticalAlignment = -4108 # 设置垂直居中
# 通过循环设置上下左右边框，十字边框
for i in range(7, 13):
b = a1.api.Borders(i)
if i < 11: # 当i值小于11，设置外边
b.LineStyle = 1 # 设置线形为实线
b.Weight = 4 # 设置粗细为最粗
else:
b.LineStyle = -4115 # 设置内边，线形为虚线
b.Weight = 2 # 设置粗细为“细”
b.Color = rgb_to_int((255, 0, 255)) # 设置内边颜色为紫色
for col in a1.columns:
col.column_width = 19 # 设置每一列的宽距
for row_int in range(1, len(area2.rows) + 1):
if row_int % 2 == 0:
# 设置偶数行的填充颜色，这样更能在视觉上更加清楚的看数据
a1.rows[row_int].color = rgb_to_int((199, 237, 204))
workbook.save() # 分别保存工作簿
app.quit() # 退出excel
def concat_formatexcel(tuple,pth):
'''
这段代码是将两张格式表用pd.concat()合并后，生成新表
但，这段代码更精确，因为多了
if "蔬菜" in dir_sp.stem:
area_sp.copy(destination=area_sc)
else:
area_sp.copy(destination=area_sg)
该段代码进行验算
'''
#分别提取部门格式表和商品格式表，进行合并
app=xw.App(visible=False,add_book=False)#加载excel
for dir_bm,dir_sp in zip(pth.rglob(f'格式表部门*{tuple[0]}和{tuple[1]}和{tuple[2]}.xlsx'),pth.rglob(f'格式表商品*{tuple[0]}和{tuple[1]}和{tuple[2]}.xlsx')):
#指定新的生成文件的保存路径p
p=dir_bm.parent/Path(str(dir_bm.stem[5:])+'合成分析表'+str(dir_bm.suffixes[-1]))
workbook2=app.books.add()#利用excel创建workbook2工作簿用于创建合并的文件
workbook1=app.books.open(dir_bm)#打开部门格式表，创建workbook1工作簿
worksheet1=workbook1.sheets[0]#指定worksheet1
worksheet2 = workbook2.sheets[0]#指定目标worksheet2
worksheet1.copy(after=worksheet2)#首先将部门格式表worksheet1复制到目标worksheet2后面
worksheet2.delete()#再将worksheet2目标sheet删除
worksheet2 = workbook2.sheets[0]#在次复制给目标sheet2,此时worksheet2已经有了部门格式表的内容了
workbook_sp=app.books.open(dir_sp)#打开商品格式表
worksheet_sp=workbook_sp.sheets[0]#指定商品格式表的worksheet_sp
area_sp=worksheet_sp.range('a1').expand('table')#指定商品格式表sheet所以数据的单元格区域
area_2=worksheet2.range('a1').expand('down').last_cell.offset(row_offset=1)#指定目标粘贴区域的第一个空白单元格
area_sp.copy(destination=area_2)#执行赋值操作
#分别保存工作簿
workbook1.save(dir_bm)
workbook_sp.save(dir_sp)
workbook2.save(p)
app.quit()#退出excel
print(f'合并文件完成\n报表已全部生成，生成报表为：')
for i in p_end.rglob('*.xlsx'):
print(i.name)
if __name__ == "__main__":
from pathlib import Path # 导入Path 模块
import re # 导入re模块
import xlwings as xw # 导入xlwings模块
from xlwings.utils import rgb_to_int # 导入xlwings.utils模块下的颜色转换函数rgb_to_int
import pandas as pd # 导入pandas模块
pd.set_option('future.no_silent_downcasting', True) # 向下兼容,因为填充缺失值这句代码fil=file.fillna(value=0)
st_dic = {'1': "部门名称", '2': "商品名称"} # 创建用于遍历的字典，作为生成透视表的行字段
data_path = Path(r'G:\生鲜每月数据分析\数据源') # 查询的数据源路径
# 创建tuple元组用于存储输入的查询日期
# tuple = input('请输入去年同期的起止日期(格式："YYYY-MM-DD至YYYY-MM-DD")'), \
# input('请输入本年度(本期)的起止日期(格式："YYYY-MM-DD至YYYY-MM-DD")'), \
# input('请输入本年度(上期)的起止日期(格式："YYYY-MM-DD至YYYY-MM-DD")')
tuple=[]#其实应该是元组，但因为后来改进的不想把代码中的变量都得改过来，所以tuple=[]，有点好笑
for item in open(data_path/'date_list.txt',encoding='utf-8').readlines():#读取日期文本
tuple.append(item.strip())#strip()去除空格和换行符
#这里的date_indexex_tuple有2个元素分别是起始日和结束日
date_indexes_tuple = (tuple[0][8:10], tuple[0][-2:])
dict_fen = {25: '水果', 27: '蔬菜'} # 创建字典用于分别构建文件夹，当然这里也可以不指定字典，
# 可以在分组的时候直接用group中的name名称
# 这样就可以分出更多的科别，而不会报
pth = data_path / Path(r'合并分析\格式过分析表') # 创建查找路径
p_end=pth/Path("__".join(tuple))#这是最后遍历结果文件的路径
stepone2725(tuple, data_path, dict_fen)#将果蔬分开
concat_excel(data_path)#将本期，上期，去年同期文件分别合并
creat_pivot(tuple, st_dic, data_path)#创建透视表
format_excel(tuple, data_path)#初步格式化透视表
insert_newrow()#插入汇总行
fomula_cell()#在组后几列插入公式
formatworkbook()#再一次格式化
concat_formatexcel(tuple, pth)#合并部门和商品两张表格
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
