货物数据处理pandas版

1求和

from openpyxl import load_workbook
import pandas as pddef 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.# Press the green button in the gutter to run the script.
if __name__ == '__main__':filePath1 = './src/原始数据精修.xlsx'df1 = pd.read_excel(filePath1, sheet_name='销售明细')index_list = ['部门名称', '年度名称', '季节名称', '商品代码', '商品名称', '品牌名称', '品类名称', '颜色名称']# 求和方法二，需将文本的列指定为索引df1 = df1.set_index(index_list)df1['合计'] = df1.apply(lambda x: x.sum(), axis=1)filePath2 = './src/处理结果精修.xlsx'#重置索引，防止单元格合并df1 = df1.reset_index()df1.to_excel(filePath2, sheet_name="new_sheet", index=False, na_rep=0, inf_rep=0)print(df1)

2.去除季节年度进行聚类

	#去除年度和季节进行聚类df2 = pd.read_excel(filePath2, sheet_name='单款排名')df2.pop('年度名称')df2.pop('季节名称')index_list = ['部门名称', '商品代码', '商品名称', '品牌名称', '品类名称', '颜色名称']value_list = ['0M','1L','1XL','27','28','29','2XL','30','3XL','4XL','5XL','6XL','S','均','合计']#使用透视表聚合df2 = df2.pivot_table(index= index_list,values=value_list,aggfunc='sum')# 重置索引，防止单元格合并df2 = df2.reset_index()#将合计列放到最后面#dataframe中某一列放到最后，并改名为销售明细df2['销售明细'] = df2.pop('合计')df2.to_excel(filePath2, sheet_name="单款排名", index=False, na_rep=0, inf_rep=0)

3完整数据处理过程代码

from openpyxl import load_workbook
import pandas as pddef 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.# Press the green button in the gutter to run the script.
if __name__ == '__main__':filePath1 = './src/原始数据精修.xlsx'# 加载工作簿wb = load_workbook(filePath1)# 获取sheet页,修改第一个sheet页面为name1 = wb.sheetnames[0]ws1 = wb[name1]ws1.title = "销售明细"wb.save(filePath1)#销售明细df1 = pd.read_excel(filePath1, sheet_name='销售明细')index_list = ['部门名称', '年度名称', '季节名称', '商品代码', '商品名称', '品牌名称', '品类名称', '颜色名称']# 求和方法二，需将文本的列指定为索引df1 = df1.set_index(index_list)df1['合计'] = df1.apply(lambda x: x.sum(), axis=1)filePath2 = './src/处理结果精修.xlsx'#重置索引，防止单元格合并df1 = df1.reset_index()df1.to_excel(filePath2, sheet_name="new_sheet", index=False, na_rep=0, inf_rep=0)#单款排名#去除年度和季节进行聚类df2 = df1.copy()df2.pop('年度名称')df2.pop('季节名称')index_list = ['部门名称', '商品代码', '商品名称', '品牌名称', '品类名称', '颜色名称']value_list = ['0M','1L','1XL','27','28','29','2XL','30','3XL','4XL','5XL','6XL','S','均','合计']#使用透视表聚合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)#品牌排名df3 = df1.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)#年度销售df4 = df1.copy()# 使用透视表聚合print(df4)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)#季节销售df5 = df1.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)#品类排名df6 = df1.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)# print(df1)# 加载工作簿filePath2 = './src/处理结果精修.xlsx'wb = load_workbook(filePath2)#客户周销df7 = pd.DataFrame({'商店名称':[], '销售数量':[]})#创建表ws2 = wb.create_sheet("单款排名")ws3 = wb.create_sheet("品牌排名")ws4 = wb.create_sheet("年度销售")ws5 = wb.create_sheet("季节销售")ws6 = wb.create_sheet("品类排名")ws7 = wb.create_sheet("客户周销")wb.save(filePath2)#将生成的工作表导入到程序中result_sheet = pd.ExcelWriter(filePath2, engine='openpyxl')  # 先定义要存入的文件名xxx，然后分别存入xxx下不同的sheetdf1.to_excel(result_sheet, "销售明细", index=False, na_rep=0, inf_rep=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()

初始文件格式

最终结果，处理完成

求行和和求列合

	#销售明细df1 = pd.read_excel(filePath1, sheet_name='销售明细')index_list = ['部门名称', '年度名称', '季节名称', '商品代码', '商品名称', '品牌名称', '品类名称', '颜色名称']# 求和方法二，需将文本的列指定为索引df1 = df1.set_index(index_list)#df1.loc["按列求和"] = df1.apply(lambda x: x.sum())df1['合计'] = df1.apply(lambda x: x.sum(), axis=1)#求列合df1 = df1.reset_index()df1 = df1.set_index('部门名称')df1.loc['合计'] = df1.apply(lambda x: x.sum(), axis=0)#重置索引，防止单元格合并df1 = df1.reset_index()df1.to_excel("new_sheet1.xlsx", sheet_name="new_sheet", index=False, na_rep=0, inf_rep=0)

4格式处理

通过openpyxl进行格式处理

#销售明细
# 加载工作簿
wb1 = load_workbook('Mytest.xlsx')
# 获取sheet页
ws2 = wb1['销售明细']
#获取行
for cell in ws1['1']:#代码省略
#获取列
for cell in ws1['A']:#代码省略

字体示例

from openpyxl import Workbook
from openpyxl.styles import Font# 创建一个新的工作簿和工作表
workbook = Workbook()
sheet = workbook.active# 创建一个字体对象并设置属性
font = Font(name='Arial',   # 字体名称size=12,        # 字体大小bold=True,      # 是否加粗italic=True,    # 是否斜体underline='single',  # 下划线类型：single、double、none等strike=True,    # 是否有删除线color='FF0000'  # 字体颜色，十六进制RGB值
)# 在单元格A1中应用字体样式
sheet['A1'].font = font
sheet['A1'].value = 'Hello, World!'# 保存工作簿
workbook.save('E:\\UserData\\Desktop\\font_example.xlsx')

背景边框示例

	# 销售明细# 加载工作簿wb1 = load_workbook(filePath2)# 获取sheet页ws1 = wb1['销售明细']#设置字体# 创建一个字体对象并设置属性font = 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_column#设置单元格格式# 调整行高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 = fontws1['A1'].value = dept_name + '销售明细'#设置字体居中# 设置字体居中#要导入 from openpyxl import stylescell = ws1['A1']cell.alignment = styles.Alignment(horizontal='center', vertical='center')#首行设置底色# 设置第一行单元格的背景颜色为红色#from openpyxl.styles import PatternFillfor cell in ws[1]:cell.fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")# 保存工作簿wb1.save(filePath2)

将数字转换为excel的列序号

import stringdef 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# Example usage
print(number_to_column(1))  # Output: "A"
print(number_to_column(27))  # Output: "AA"
print(number_to_column(28))  # Output: "AB"
print(number_to_column(53))  # Output: "BA"
print(number_to_column(1000))  # Output: "ALL"

设置销售明细的代码

# 销售明细# 加载工作簿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_column# 设置单元格格式# 调整行高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="FFFF00", end_color="FF0000", fill_type="solid")#列颜色填充for cell in ws1[number_to_column(max_col)]:cell.fill = PatternFill(start_color="FFFF00", 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="FFFF00", 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 = '合计'

单款排名格式

#单款排名# 获取sheet页ws2 = wb1['单款排名']# 设置字体# 创建一个字体对象并设置属性dept_name = ws2['A2'].valuews2.insert_rows(0, 1)  # 插入一行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="FFFF00", end_color="FF0000", fill_type="solid")# 列颜色填充for cell in ws2[number_to_column(max_col)]:cell.fill = PatternFill(start_color="FFFF00", 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="FFFF00", 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 = '合计'

完整代码

from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *import pandas as pd
from openpyxl.styles import Font
import stringdef 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 column# Press the green button in the gutter to run the script.
if __name__ == '__main__':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='销售明细')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()filePath2 = './src/处理结果精修.xlsx'#重置索引，防止单元格合并df0 = df0.reset_index()df1 = df0.copy()# 列求和df1 = df1.set_index('部门名称')df1.loc['合计'] = df1.apply(lambda x: x.sum(), axis=0)# df1=df1.sum()filePath2 = './src/处理结果精修.xlsx'# 重置索引，防止单元格合并df1 = df1.reset_index()df1.to_excel("new_sheet1.xlsx", sheet_name="new_sheet", index=False, na_rep=0, inf_rep=0)#单款排名#去除年度和季节进行聚类df2 = df0.copy()df2.pop('年度名称')df2.pop('季节名称')index_list = ['部门名称', '商品代码', '商品名称', '品牌名称', '品类名称', '颜色名称']value_list = ['0M','1L','1XL','27','28','29','2XL','30','3XL','4XL','5XL','6XL','S','均','合计']#使用透视表聚合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['合计'] = df1.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()# 使用透视表聚合print(df4)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)# 加载工作簿filePath2 = './src/处理结果精修.xlsx'wb = load_workbook(filePath2)#客户周销df7 = pd.DataFrame({'商店名称':[], '销售数量':[]})#创建表ws2 = wb.create_sheet("单款排名")ws3 = wb.create_sheet("品牌排名")ws4 = wb.create_sheet("年度销售")ws5 = wb.create_sheet("季节销售")ws6 = wb.create_sheet("品类排名")ws7 = wb.create_sheet("客户周销")wb.save(filePath2)#将生成的工作表导入到程序中result_sheet = pd.ExcelWriter(filePath2, engine='openpyxl')  # 先定义要存入的文件名xxx，然后分别存入xxx下不同的sheetdf1.to_excel(result_sheet, "销售明细", index=False, na_rep=0, inf_rep=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()# 销售明细# 加载工作簿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_column# 设置单元格格式# 调整行高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="FFFF00", end_color="FF0000", fill_type="solid")#列颜色填充for cell in ws1[number_to_column(max_col)]:cell.fill = PatternFill(start_color="FFFF00", 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="FFFF00", 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 = '合计'#单款排名# 获取sheet页ws2 = wb1['单款排名']# 设置字体# 创建一个字体对象并设置属性dept_name = ws2['A2'].valuews2.insert_rows(0, 1)  # 插入一行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="FFFF00", end_color="FF0000", fill_type="solid")# 列颜色填充for cell in ws2[number_to_column(max_col)]:cell.fill = PatternFill(start_color="FFFF00", 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="FFFF00", 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 = '合计'# 保存工作簿wb1.save(filePath2)