货物数据处理，pandas和openpyxl联合处理

处理文件题头格式

部门名称	年度名称	季节名称	商品名称	商品代码	品牌名称	品类名称	颜色名称	商店名称	0M	1L	1XL	27	28	29	2XL	30	31	32	33	3XL	4XL	5XL	6XL	S	均

1.导入包

导入源

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

2.加载excel文件

# 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='销售明细')

3.设置数据处理父亲

#销售明细
    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()
    index_list = ['部门名称', '年度名称','季节名称','商品代码', '商品名称', '品牌名称', '品类名称', '颜色名称']
    value_list = ['0M', '1L', '1XL', '27', '28', '29', '2XL', '30', '3XL', '4XL', '5XL', '6XL', 'S', '均', '合计']
   

4.父亲复制自己生成孩子

df1 = df0.pivot_table(index=index_list, values=value_list, aggfunc='sum').copy()

5.数据处理

行求和，排序，列求和

行求和

	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)

列求和和排序

	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['合计'] = df2.apply(lambda x: x.sum(), axis=0)
    # 重置索引，防止索引列导出消失
    df2 = df2.reset_index()

6.excel格式设置

插入列，合并单元格，设置文字居中，背景色填充，设置边框

# 销售明细
    # 加载工作簿
    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'].value
    ws1.insert_rows(0, 1)  # 插入一行
    min_row = ws1.min_row
    max_row = ws1.max_row
    min_col = ws1.min_column
    max_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 = font1
    ws1['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