周销表格数据处理

本文主要是介绍周销表格数据处理，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

周销表格数据处理

下载依赖

pip install openpyxl -i https://pypi.doubanio.com/simple

pip install pandas -i https://pypi.doubanio.com/simple

引入依赖

from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string

数据读入

	filePath1 = './src/超级原始数据精修.xlsx'# 加载工作簿wb = load_workbook(filePath1)# 获取sheet页,修改第一个sheet页面为name1 = wb.sheetnames[0]ws1 = wb[name1]ws1.title = "销售明细"#销售明细df0 = pd.read_excel(filePath1, sheet_name='销售明细')

自适应列宽封装类

def number_to_column(n):"""Convert a number to the corresponding column letter in Excel"""column = ""while n > 0:n -= 1column = string.ascii_uppercase[n % 26] + columnn //= 26return column
def auto_col_width(ws):lks = []  # 英文变量太费劲，用汉语首字拼音代替for i in range(1, ws.max_column + 1):  # 每列循环lk = 1  # 定义初始列宽，并在每个行循环完成后重置for j in range(1, ws.max_row + 1):  # 每行循环sz = ws.cell(row=j, column=i).value  # 每个单元格内容if isinstance(sz, str):  # 中文占用多个字节，需要分开处理lk1 = len(sz.encode('gbk'))  # gbk解码一个中文两字节，utf-8一个中文三字节，gbk合适else:lk1 = len(str(sz))if lk < lk1:lk = lk1  # 借助每行循环将最大值存入lk中# print(lk)lks.append(lk)  # 将每列最大宽度加入列表。（犯了一个错，用lks = lks.append(lk)报错，append会修改列表变量，返回值none，而none不能继续用append方法）# 第二步：设置列宽for i in range(1, ws.max_column + 1):k = number_to_column(i)  # 将数字转化为列名,26个字母以内也可以用[chr(i).upper() for i in range(97, 123)]，不用导入模块ws.column_dimensions[k].width = lks[i - 1] + 2  # 设置列宽，一般加两个字节宽度，可以根据实际情况灵活调整return ws

设置全部字体和字体居中封装函数

def set_cell_style(ws):# 边框border = Border(left=Side(border_style='thin', color='000000'),right=Side(border_style='thin', color='000000'),top=Side(border_style='thin', color='000000'),bottom=Side(border_style='thin', color='000000'),)# 对齐alignment = Alignment(horizontal='center',vertical='center',text_rotation=0,indent=0)# 字体font = Font(name='微软雅黑',size=11,bold=False,italic=False,strike=False,color='000000')rows = ws.max_row  # 获取最大行columns = ws.max_column  # 获取最大列# 遍历表格内容,注意：openpyxl下标是从1开始for i in range(1, rows + 1):for j in range(1, columns + 1):# 设置边框ws.cell(i, j).border = border# 设置居中对齐ws.cell(i, j).alignment = alignment# 设置字体ws.cell(i, j).font = fontreturn ws

设置第二行和末尾行字体加粗

def font_bold(ws):max_col = ws.max_columnmax_row = ws.max_row# 字体font = Font(name='微软雅黑',size=12,bold=True,italic=False,strike=False,color='000000')for i in range(1, max_col+1) :for cell in ws[2]:cell.font = fontfor cell in ws[max_row]:cell.font = fontreturn ws

处理品类的完整代码

from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string
import re
import os
def print_hi(name):# Use a breakpoint in the code line below to debug your script.print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.def number_to_column(n):"""Convert a number to the corresponding column letter in Excel"""column = ""while n > 0:n -= 1column = string.ascii_uppercase[n % 26] + columnn //= 26return columndef auto_col_width(ws):lks = []  # 英文变量太费劲，用汉语首字拼音代替for i in range(1, ws.max_column + 1):  # 每列循环lk = 0.5  # 定义初始列宽，并在每个行循环完成后重置for j in range(1, ws.max_row + 1):  # 每行循环sz = ws.cell(row=j, column=i).value  # 每个单元格内容lk1 = 0.7 * len(re.findall('([\u4e00-\u9fa5])', str(sz))) + len(str(sz))if lk < lk1:lk = lk1  # 借助每行循环将最大值存入lk中# print(lk)lks.append(lk)  # 将每列最大宽度加入列表。（犯了一个错，用lks = lks.append(lk)报错，append会修改列表变量，返回值none，而none不能继续用append方法）# 第二步：设置列宽for i in range(1, ws.max_column + 1):k = number_to_column(i)  # 将数字转化为列名,26个字母以内也可以用[chr(i).upper() for i in range(97, 123)]，不用导入模块ws.column_dimensions[k].width = lks[i - 1] + 2  # 设置列宽，一般加两个字节宽度，可以根据实际情况灵活调整return wsdef set_cell_style(ws):# 边框border = Border(left=Side(border_style='thin', color='000000'),right=Side(border_style='thin', color='000000'),top=Side(border_style='thin', color='000000'),bottom=Side(border_style='thin', color='000000'),)# 对齐alignment = Alignment(horizontal='center',vertical='center',text_rotation=0,indent=0)# 字体font = Font(name='微软雅黑',size=9,bold=False,italic=False,strike=False,color='000000')rows = ws.max_row  # 获取最大行columns = ws.max_column  # 获取最大列# 遍历表格内容,注意：openpyxl下标是从1开始for i in range(1, rows + 1):for j in range(1, columns + 1):# 设置边框ws.cell(i, j).border = border# 设置居中对齐ws.cell(i, j).alignment = alignment# 设置字体ws.cell(i, j).font = fontreturn wsdef font_bold(ws):max_col = ws.max_columnmax_row = ws.max_row# 字体font = Font(name='微软雅黑',size=12,bold=True,italic=False,strike=False,color='000000')for i in range(1, max_col+1) :for cell in ws[2]:cell.font = fontfor cell in ws[max_row]:cell.font = fontreturn wsdef data_deal(df0):index_list = ['部门名称', '年度名称', '季节名称', '商品代码', '商品名称', '品牌名称', '品类名称', '颜色名称', '商店名称']# 求和方法二，需将文本的列指定为索引df0 = df0.set_index(index_list)# df1.loc["按列求和"] = df1.apply(lambda x: x.sum())df0['合计'] = df0.apply(lambda x: x.sum(), axis=1)# df1=df1.sum()# 重置索引，防止单元格合并df0 = df0.reset_index()index_list = ['部门名称', '年度名称', '季节名称', '商品代码', '商品名称', '品牌名称', '品类名称', '颜色名称']value_list = ['00S', '0M', '1L', '1XL', '27', '28', '29', '30', '31', '32', '33', '34', '2XL',  '3XL', '4XL', '5XL', '6XL', '均', '合计']df1 = df0.pivot_table(index=index_list, values=value_list, aggfunc='sum').copy()df1.sort_values(by="合计", axis=0, ascending=False, inplace=True)# 列求和df1 = df1.reset_index()df1 = df1.set_index('部门名称')df1.loc['合计'] = df1.apply(lambda x: x.sum(), axis=0)# df1=df1.sum()df1['合计'] = df1.pop('合计')# 重置索引，防止单元格合并df1 = df1.reset_index()# 单款排名# 去除年度和季节进行聚类df2 = df0.copy()df2.pop('年度名称')df2.pop('季节名称')index_list = ['部门名称', '商品代码', '商品名称', '品牌名称', '品类名称', '颜色名称']value_list = ['00S', '0M', '1L', '1XL', '27', '28', '29', '30', '31', '32', '33', '34', '2XL',  '3XL', '4XL', '5XL', '6XL', '均', '合计']# 使用透视表聚合df2 = df2.pivot_table(index=index_list, values=value_list, aggfunc='sum')# 重置索引，防止单元格合并df2 = df2.reset_index()# 将合计列放到最后面# dataframe中某一列放到最后，并改名为合计df2['合计'] = df2.pop('合计')df2.sort_values(by="合计", axis=0, ascending=False, inplace=True)# 列求和df2 = df2.set_index('部门名称')df2.loc['合计'] = df2.apply(lambda x: x.sum(), axis=0)# 重置索引，防止索引列导出消失df2 = df2.reset_index()# 品牌排名df3 = df0.copy()# 使用透视表聚合df3 = df3.pivot_table(index='品牌名称', values='合计', aggfunc='sum')# 重置索引，防止单元格合并df3 = df3.reset_index()# 将合计列放到最后面# dataframe中某一列放到最后，并改名为销售明细df3['销售明细'] = df3.pop('合计')df3.sort_values(by="销售明细", axis=0, ascending=False, inplace=True)df3 = df3.set_index('品牌名称')df3.loc['合计'] = df3.apply(lambda x: x.sum(), axis=0)# 重置索引，防止单元格合并df3 = df3.reset_index()# 年度销售df4 = df0.copy()# 使用透视表聚合df4 = df4.pivot_table(index=['年度名称', '季节名称'], values='合计', aggfunc='sum')# 重置索引，防止单元格合并df4 = df4.reset_index()# 将合计列放到最后面# dataframe中某一列放到最后，并改名为销售明细df4['销售明细'] = df4.pop('合计')df4.sort_values(by="销售明细", axis=0, ascending=False, inplace=True)df4 = df4.set_index('年度名称')df4.loc['合计'] = df4.apply(lambda x: x.sum(), axis=0)# 重置索引，防止单元格合并df4 = df4.reset_index()# 季节销售df5 = df0.copy()# 使用透视表聚合df5 = df5.pivot_table(index=['季节名称'], values='合计', aggfunc='sum')# 重置索引，防止单元格合并df5 = df5.reset_index()df5['销售明细'] = df5.pop('合计')df5.sort_values(by="销售明细", axis=0, ascending=False, inplace=True)df5 = df5.set_index('季节名称')df5.loc['合计'] = df5.apply(lambda x: x.sum(), axis=0)# 重置索引，防止单元格合并df5 = df5.reset_index()# 品类排名df6 = df0.copy()# 使用透视表聚合df6 = df6.pivot_table(index=['品类名称'], values='合计', aggfunc='sum')# 重置索引，防止单元格合并df6 = df6.reset_index()df6['销售明细'] = df6.pop('合计')df6.sort_values(by="销售明细", axis=0, ascending=False, inplace=True)df6 = df6.set_index('品类名称')df6.loc['合计'] = df6.apply(lambda x: x.sum(), axis=0)# 重置索引，防止单元格合并df6 = df6.reset_index()# print(df1)# 客户周销df7 = df0.pivot_table(index=['商店名称'], values='合计', aggfunc='sum').copy()# 重置索引，防止单元格合并df7 = df7.reset_index()df7['销售明细'] = df7.pop('合计')df7.sort_values(by="销售明细", axis=0, ascending=False, inplace=True)df7 = df7.set_index('商店名称')df7.loc['合计'] = df7.apply(lambda x: x.sum(), axis=0)# 重置索引，防止单元格合并df7 = df7.reset_index()# 加载工作簿filePath2 = './src/处理结果精修.xlsx'#没有就创建if os.path.exists(filePath2):print("文件已存在")print(filePath2)else:# 创建一个新的 Excel 文件wb = Workbook()wb.save(filePath2)wb = load_workbook(filePath2)# 获取sheet页,修改第一个sheet页面为name1 = wb.sheetnames[0]ws1 = wb[name1]ws1.title = "销售明细"# 创建工作表wb.create_sheet("单款排名")wb.create_sheet("品牌排名")wb.create_sheet("年度销售")wb.create_sheet("季节销售")wb.create_sheet("品类排名")wb.create_sheet("客户周销")wb.save(filePath2)# 将生成的工作表导入到程序中result_sheet = pd.ExcelWriter(filePath2, engine='openpyxl')  # 先定义要存入的文件名xxx，然后分别存入xxx下不同的sheet# df1将0转变为空df1 = df1.replace(0, '')df1.to_excel(result_sheet, "销售明细", index=False, na_rep=0, inf_rep=0)df2 = df2.replace(0, '')df2.to_excel(result_sheet, "单款排名", index=False, na_rep=0, inf_rep=0)df3.to_excel(result_sheet, "品牌排名", index=False, na_rep=0, inf_rep=0)df4.to_excel(result_sheet, "年度销售", index=False, na_rep=0, inf_rep=0)df5.to_excel(result_sheet, "季节销售", index=False, na_rep=0, inf_rep=0)df6.to_excel(result_sheet, "品类排名", index=False, na_rep=0, inf_rep=0)df7.to_excel(result_sheet, "客户周销", index=False, na_rep=0, inf_rep=0)# 这步不能省，否则不生成文件result_sheet.save()return filePath2def style_deal(filePath2):high_light_color = "7FFFD4"# 销售明细# 加载工作簿wb1 = load_workbook(filePath2)# 获取sheet页ws1 = wb1['销售明细']# 设置字体# 创建一个字体对象并设置属性font1 = Font(name='微软雅黑',  # 字体名称size=18,  # 字体大小bold=True,  # 是否加粗italic=False,  # 是否斜体underline='none',  # 下划线类型：single、double、none等strike=False,  # 是否有删除线color='000000',  # 字体颜色，十六进制RGB值vertAlign='baseline')dept_name = ws1['A2'].valuews1.insert_rows(0, 1)  # 插入一行min_row = ws1.min_rowmax_row = ws1.max_rowmin_col = ws1.min_columnmax_col = ws1.max_columnws1 = set_cell_style(ws1)# 设置单元格格式# 调整行高ws1.row_dimensions[1].height = 24# 合并单元格# 需要合并的左上方和右下方单元格坐标ws1.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max_col)# 在单元格A1中应用字体样式ws1['A1'].font = font1ws1['A1'].value = dept_name + '销售明细'# 设置字体居中cell = ws1['A1']cell.alignment = styles.Alignment(horizontal='center', vertical='center')# 首行设置底色# 设置第一行单元格的背景颜色为黄色for cell in ws1[2]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 列颜色填充for cell in ws1[number_to_column(max_col)]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 设置边框cell.border = Border(left=Side(style='thin'), bottom=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'))# 处理最后一行# 调整行高ws1.row_dimensions[max_row].height = 24# 设置末尾行单元格的背景颜色为黄色for cell in ws1[max_row]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 设置边框cell.border = Border(left=Side(style='thin'), bottom=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'))# 合并单元格# 需要合并的左上方和右下方单元格坐标ws1.merge_cells(start_row=max_row, start_column=1, end_row=max_row, end_column=8)# 在单元格A1中应用字体样式cell = ws1.cell(column=1, row=max_row)cell.alignment = styles.Alignment(horizontal='center', vertical='center')cell.font = font1cell.value = '合计'# 调整列宽和加粗ws1 = auto_col_width(ws1)ws1 = font_bold(ws1)# 单款排名# 获取sheet页ws2 = wb1['单款排名']# 设置字体# 创建一个字体对象并设置属性dept_name = ws2['A2'].valuews2.insert_rows(0, 1)  # 插入一行# 调整单元格格式ws2 = set_cell_style(ws2)min_row = ws2.min_rowmax_row = ws2.max_rowmin_col = ws2.min_columnmax_col = ws2.max_column# 设置单元格格式# 调整行高ws2.row_dimensions[1].height = 24# 合并单元格# 需要合并的左上方和右下方单元格坐标ws2.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max_col)# 在单元格A1中应用字体样式ws2['A1'].font = font1ws2['A1'].value = '单款排名'# 设置字体居中cell = ws2['A1']cell.alignment = styles.Alignment(horizontal='center', vertical='center')# 首行设置底色# 设置第一行单元格的背景颜色为黄色for cell in ws2[2]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 列颜色填充for cell in ws2[number_to_column(max_col)]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 设置边框cell.border = Border(left=Side(style='thin'), bottom=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'))# 处理最后一行# 调整行高ws2.row_dimensions[max_row].height = 24# 设置末尾行单元格的背景颜色为黄色for cell in ws2[max_row]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 设置边框cell.border = Border(left=Side(style='thin'), bottom=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'))# 合并单元格# 需要合并的左上方和右下方单元格坐标ws2.merge_cells(start_row=max_row, start_column=1, end_row=max_row, end_column=6)# 在单元格A1中应用字体样式cell = ws2.cell(column=1, row=max_row)cell.alignment = styles.Alignment(horizontal='center', vertical='center')cell.font = font1cell.value = '合计'# 调整列宽和加粗ws2 = auto_col_width(ws2)ws2 = font_bold(ws2)# 品牌排名# 获取sheet页ws3 = wb1['品牌排名']ws3.insert_rows(0, 1)  # 插入一行# 调整单元格格式ws3 = set_cell_style(ws3)min_row = ws3.min_rowmax_row = ws3.max_rowmin_col = ws3.min_columnmax_col = ws3.max_column# 设置单元格格式# 调整行高ws3.row_dimensions[1].height = 24# 合并单元格# 需要合并的左上方和右下方单元格坐标ws3.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max_col)# 在单元格A1中应用字体样式ws3['A1'].font = font1ws3['A1'].value = '品牌排名'# 设置字体居中cell = ws3['A1']cell.alignment = styles.Alignment(horizontal='center', vertical='center')# 首行设置底色# 设置第一行单元格的背景颜色为黄色for cell in ws3[2]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 调整行高ws3.row_dimensions[max_row].height = 18# 设置末尾行单元格的背景颜色为黄色for cell in ws3[max_row]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 设置边框cell.border = Border(left=Side(style='thin'), bottom=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'))# 调整列宽和加粗ws3 = auto_col_width(ws3)ws3 = font_bold(ws3)# 年度销售ws4 = wb1['年度销售']ws4.insert_rows(0, 1)  # 插入一行# 调整单元格格式ws4 = set_cell_style(ws4)min_row = ws4.min_rowmax_row = ws4.max_rowmin_col = ws4.min_columnmax_col = ws4.max_column# 设置单元格格式# 调整行高ws4.row_dimensions[1].height = 24# 合并单元格# 需要合并的左上方和右下方单元格坐标ws4.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max_col)# 在单元格A1中应用字体样式ws4['A1'].font = font1ws4['A1'].value = '年度销售'# 设置字体居中cell = ws4['A1']cell.alignment = styles.Alignment(horizontal='center', vertical='center')# 首行设置底色# 设置第一行单元格的背景颜色为黄色for cell in ws4[2]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 调整行高ws4.row_dimensions[max_row].height = 18# 设置末尾行单元格的背景颜色为黄色for cell in ws4[max_row]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 设置边框cell.border = Border(left=Side(style='thin'), bottom=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'))# 合并单元格# 需要合并的左上方和右下方单元格坐标ws4.merge_cells(start_row=max_row, start_column=1, end_row=max_row, end_column=2)# 在单元格A1中应用字体样式cell = ws4.cell(column=1, row=max_row)cell.alignment = styles.Alignment(horizontal='center', vertical='center')cell.font = font1cell.value = '合计'# 调整列宽和加粗ws4 = auto_col_width(ws4)ws4 = font_bold(ws4)# 季节销售ws5 = wb1['季节销售']ws5.insert_rows(0, 1)  # 插入一行# 调整单元格格式ws5 = set_cell_style(ws5)min_row = ws5.min_rowmax_row = ws5.max_rowmin_col = ws5.min_columnmax_col = ws5.max_column# 设置单元格格式# 调整行高ws5.row_dimensions[1].height = 24# 合并单元格# 需要合并的左上方和右下方单元格坐标ws5.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max_col)# 在单元格A1中应用字体样式ws5['A1'].font = font1ws5['A1'].value = '季节销售'# 设置字体居中cell = ws5['A1']cell.alignment = styles.Alignment(horizontal='center', vertical='center')# 首行设置底色# 设置第一行单元格的背景颜色为黄色for cell in ws5[2]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 调整行高ws5.row_dimensions[max_row].height = 18# 设置末尾行单元格的背景颜色为黄色for cell in ws5[max_row]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 设置边框cell.border = Border(left=Side(style='thin'), bottom=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'))# 调整列宽和加粗ws5 = auto_col_width(ws5)ws5 = font_bold(ws5)# 品类排名ws6 = wb1['品类排名']ws6.insert_rows(0, 1)  # 插入一行# 调整单元格格式ws6 = set_cell_style(ws6)min_row = ws6.min_rowmax_row = ws6.max_rowmin_col = ws6.min_columnmax_col = ws6.max_column# 设置单元格格式# 调整行高ws6.row_dimensions[1].height = 24# 合并单元格# 需要合并的左上方和右下方单元格坐标ws6.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max_col)# 在单元格A1中应用字体样式ws6['A1'].font = font1ws6['A1'].value = '品类排名'# 设置字体居中cell = ws6['A1']cell.alignment = styles.Alignment(horizontal='center', vertical='center')# 首行设置底色# 设置第一行单元格的背景颜色为黄色for cell in ws6[2]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 调整行高ws6.row_dimensions[max_row].height = 18# 设置末尾行单元格的背景颜色为黄色for cell in ws6[max_row]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 设置边框cell.border = Border(left=Side(style='thin'), bottom=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'))# 调整列宽和加粗ws6 = auto_col_width(ws6)ws6 = font_bold(ws6)# 客户周销# 品类排名ws7 = wb1['客户周销']ws7.insert_rows(0, 1)  # 插入一行# 调整单元格格式ws7 = set_cell_style(ws7)min_row = ws7.min_rowmax_row = ws7.max_rowmin_col = ws7.min_columnmax_col = ws7.max_column# 设置单元格格式# 调整行高ws7.row_dimensions[1].height = 24# 合并单元格# 需要合并的左上方和右下方单元格坐标ws7.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max_col)# 在单元格A1中应用字体样式ws7['A1'].font = font1ws7['A1'].value = '客户周销'# 设置字体居中cell = ws7['A1']cell.alignment = styles.Alignment(horizontal='center', vertical='center')# 首行设置底色# 设置第一行单元格的背景颜色为黄色for cell in ws7[2]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 调整行高ws7.row_dimensions[max_row].height = 18# 设置末尾行单元格的背景颜色为黄色for cell in ws7[max_row]:cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")# 设置边框cell.border = Border(left=Side(style='thin'), bottom=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'))# 调整列宽和加粗ws7 = auto_col_width(ws7)ws7 = font_bold(ws7)# 保存工作簿filePath3 = './src/' + dept_name + '销售明细.xlsx'wb1.save(filePath3)# 4部门数据明细,生成四个文件
def four_dept_deal():filePath1 = './src/超级原始数据.xlsx'# 加载工作簿wb = load_workbook(filePath1)# 获取sheet页,修改第一个sheet页面为name1 = wb.sheetnames[0]ws1 = wb[name1]ws1.title = "销售明细"wb.save(filePath1)# 销售明细df0 = pd.read_excel(filePath1, sheet_name='销售明细')# 商品1部# df1 = df0df1 = df0[df0["部门名称"] == "商品1部"].copy()# 处理数据filePath1 = data_deal(df1)filePath2 = './src/处理结果精修.xlsx'style_deal(filePath2)# 商品3部df3 = df0[df0["部门名称"] == "商品3部"].copy()# 处理数据filePath1 = data_deal(df3)filePath2 = './src/处理结果精修.xlsx'style_deal(filePath2)# 商品6部df6 = df0[df0["部门名称"] == "商品6部"].copy()# 处理数据filePath1 = data_deal(df6)filePath2 = './src/处理结果精修.xlsx'style_deal(filePath2)# 直营4部df4 = df0[df0["部门名称"] == "直营4部"].copy()# 处理数据filePath1 = data_deal(df4)filePath2 = './src/处理结果精修.xlsx'style_deal(filePath2)# 全部门数据明细,生成一个文件
# 文件名为随机一个部门，需要将以前生成的部门数据删除
def all_dept_deal():filePath1 = './src/超级原始数据.xlsx'# 加载工作簿wb = load_workbook(filePath1)# 获取sheet页,修改第一个sheet页面为name1 = wb.sheetnames[0]ws1 = wb[name1]ws1.title = "销售明细"wb.save(filePath1)# 销售明细df0 = pd.read_excel(filePath1, sheet_name='销售明细')df1 = df0.copy()# 处理数据filePath1 = data_deal(df1)filePath2 = './src/处理结果精修.xlsx'style_deal(filePath2)if __name__ == '__main__':# 处理格式four_dept_deal()# all_dept_deal()

这篇关于周销表格数据处理的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---