冬装活动提成计算

冬装奖励内容

['大衣', '派克服', '皮毛一体', '皮羽绒', '羽绒服', '麂皮绒', '坎毛', '水貂派克服', '雪兰貂', '鹅绒服']

活动品类

大衣，派克服，皮毛一体，皮羽绒，羽绒服，麂皮绒，坎毛，水貂派克服，雪兰貂，鹅绒服

导入包

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

涉及字段

部门名称	季节名称	商店名称	品类名称	商品代码	日期	直销价	销售数量	批发价金额	直销价金额

修改列的数据类型

import pandas as pd
# 创建DataFrame
data = {'A': [1, 2, 3], 'B': ['a', 'b', 'c']}
df = pd.DataFrame(data)
print("原始DataFrame:\n", df)
 
# 将'A'列转换为float类型
df['A'] = df['A'].astype('float')
print("\n转换后的DataFrame:\n", df)

初始字段

部门名称	季节名称	商店名称	品类名称	商品代码	日期	直销价	销售数量	批发价金额	直销价金额

冬装数据结果处理(品类日期版)

import numpy as np
from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string
import os
from openpyxl import Workbook


def create_excel(file_path):
    # 没有就创建
    if os.path.exists(file_path):
        print("文件已存在")
        print(file_path)
    else:
        # 创建一个新的 Excel 文件
        wb = Workbook()
        wb.save(file_path)


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 = font
    return ws


def number_to_column(n):
    """Convert a number to the corresponding column letter in Excel"""
    column = ""
    while n > 0:
        n -= 1
        column = string.ascii_uppercase[n % 26] + column
        n //= 26
    return 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 style_deal():
    file_path1 = './src/冬季奖励数据处理结果.xlsx'
    wb = load_workbook(file_path1)
    name_list = wb.sheetnames

    for name in name_list:
        ws = wb[name]
        set_cell_style(ws)
        #自适应列宽
        auto_col_width(ws)
    wb.save(file_path1)


def data_deal():
    file_path1 = './src/冬装奖励数据.xlsx'
    file_path2 = './src/冬季奖励数据处理结果.xlsx'
    sheet_name_deal(file_path1)
    create_excel(file_path2)

    # 读入数据
    df1 = pd.read_excel(file_path1, sheet_name='销售明细')
    df2 = df1[df1['季节名称'] == '冬'].copy()
    # 筛选品类
    category_list = ['大衣', '派克服', '皮毛一体', '皮羽绒', '羽绒服', '麂皮绒', '坎毛', '水貂派克服', '雪兰貂', '鹅绒服']
    #字典
    category_dict = dict(zip(category_list, [True]*len(category_list)))
    df2 = df2.loc[df2['品类名称'].isin(category_list), :]

    # 修改直销价为浮点类型
    df2['直销价'] = df2['直销价'].astype('float')
    # 单价在500元以上 1000元以下的衣服
    df3 = df2[(df2['直销价'] >= 500) & (df2['直销价'] < 1000)].copy()
    # 单价在1000元以上的衣服
    df4 = df2[df2['直销价'] >= 1000].copy()

    # 500-1000部门商店品类销售数量
    index_list = ['日期', '部门名称', '商店名称', '品类名称']
    value_list = ['销售数量']
    df31 = df3.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df31['提成金额'] = df31['销售数量'] * 50
    df31 = df31.reset_index()
    # 部门客户销售数量
    index_list = ['日期', '部门名称', '商店名称']
    value_list = ['销售数量']
    df32 = df3.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df32['提成金额'] = df32['销售数量'] * 50
    df32 = df32.reset_index()
    # 1000元以上部门商店品类销售数量
    index_list = ['日期', '部门名称', '商店名称', '品类名称']
    value_list = ['销售数量']
    df41 = df4.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df41['提成金额'] = df41['销售数量'] * 100
    df41 = df41.reset_index()
    # 部门客户销售数量
    index_list = ['日期', '部门名称', '商店名称']
    value_list = ['销售数量']
    df42 = df4.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df42['提成金额'] = df42['销售数量'] * 100
    df42 = df42.reset_index()

    #部门品类销售数量
    df51 = df31.copy()
    df52 = df41.copy()
    df51['500元档位数量'] = df51.pop('销售数量')
    df52['1000元档位数量'] = df52.pop('销售数量')
    index_list = ['部门名称', '品类名称']
    value_list = ['500元档位数量']
    df511 = df51.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    value_list = ['1000元档位数量']
    df521 = df52.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df53 = pd.merge(df511, df521, how='outer', left_index=True, right_index=True)
    df54 = df53.reset_index()
    df54 = df54.fillna(0)

    #日期部门客户大类备注
    df60 = df2.copy()
    df61 = df60[df60['直销价'] >= 500]
    index_list = ['日期', '部门名称', '商店名称']
    column_list = ['品类名称']
    #value_list = ['销售数量']
    df666 = df61.pivot_table(index=index_list, columns=column_list, values='销售数量', aggfunc='sum')
    df666 = df666.reset_index()
    # 生成备注列
    notes_list = []
    # 获取列名
    start_len = len(index_list)
    column_list = df666.columns.tolist()
    df666 = df666.fillna(0)
    for row in df666.itertuples():
        strings = ''
        for pos in range(start_len, len(column_list)):
            col_name = column_list[pos]
            num = getattr(row, col_name)
            num = int(num)
            if num != 0:
                strings = strings+str(num)+'件'+col_name+','
        # 切片操作
        strings = strings[:-1] + ""  # 将最后一个字符替换为空
        notes_list.append(strings)
    # 删除品类列
    sum_len = len(column_list)
    df667 = df666.copy()
    for pos in range(start_len, sum_len):
        col_name = column_list[pos]
        df667.pop(col_name)
    df667['备注'] = pd.Series(notes_list)


    # 获取部门客户品类
    df71 = df31.copy()
    df72 = df41.copy()
    df71['500元档位数量'] = df71.pop('销售数量')
    df72['1000元档位数量'] = df72.pop('销售数量')
    index_list = ['日期', '部门名称', '商店名称']
    value_list = ['500元档位数量']
    df711 = df71.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    value_list = ['1000元档位数量']
    df721 = df72.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df73 = pd.merge(df711, df721, how='outer', left_index=True, right_index=True)
    df74 = df73.reset_index()
    df74 = df74.fillna(0)
    df74['总奖励金额'] = df74['500元档位数量'] * 50 + df74['1000元档位数量']*100
    df75 = pd.merge(df74, df667, how='left')
    wb = load_workbook(file_path2)
    # 获取sheet页,修改第一个sheet页面为
    name1 = wb.sheetnames[0]
    ws1 = wb[name1]
    ws1.title = "500-1000冬装销售明细"
    # 创建工作表
    wb.create_sheet("500-1000客户品类销售")
    wb.create_sheet("500-1000客户销售")
    wb.create_sheet("1000以上冬装销售明细")
    wb.create_sheet("1000以上客户品类销售")
    wb.create_sheet("1000以上客户销售")
    wb.create_sheet("部门品类销售")
    wb.create_sheet("部门客户品类销售")
    wb.create_sheet("部门客户销售备注")
    wb.create_sheet("部门客户档位销售")
    wb.save(file_path2)

    # 将生成的工作表导入到程序中
    result_sheet = pd.ExcelWriter(file_path2, engine='openpyxl')  # 先定义要存入的文件名xxx，然后分别存入xxx下不同的sheet
    # df1将0转变为空
    df3.to_excel(result_sheet, "500-1000冬装销售明细", index=False, na_rep=0, inf_rep=0)
    df31.to_excel(result_sheet, "500-1000客户品类销售", index=False, na_rep=0, inf_rep=0)
    df32.to_excel(result_sheet, "500-1000客户销售", index=False, na_rep=0, inf_rep=0)

    df4.to_excel(result_sheet, "1000以上冬装销售明细", index=False, na_rep=0, inf_rep=0)
    df41.to_excel(result_sheet, "1000以上客户品类销售", index=False, na_rep=0, inf_rep=0)
    df42.to_excel(result_sheet, "1000以上客户销售", index=False, na_rep=0, inf_rep=0)
    df54.to_excel(result_sheet, "部门品类销售", index=False, na_rep=0, inf_rep=0)

    df666.to_excel(result_sheet, "部门客户品类销售", index=False, na_rep=0, inf_rep=0)
    df667.to_excel(result_sheet, "部门客户销售备注", index=False, na_rep=0, inf_rep=0)
    df75.to_excel(result_sheet, "客户档位销售", index=False, na_rep=0, inf_rep=0)
    # 这步不能省，否则不生成文件
    result_sheet.save()


def sheet_name_deal(file_path):
    wb = load_workbook(file_path)
    # 获取sheet页,修改第一个sheet页面名为销售明细
    name1 = wb.sheetnames[0]
    ws1 = wb[name1]
    ws1.title = "销售明细"
    wb.save(file_path)


if __name__ == '__main__':
    data_deal()
    style_deal()

冬装数据结果处理(品类版, 结果无日期)

import numpy as np
from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string
import os
from openpyxl import Workbook


def create_excel(file_path):
    # 没有就创建
    if os.path.exists(file_path):
        print("文件已存在")
        print(file_path)
    else:
        # 创建一个新的 Excel 文件
        wb = Workbook()
        wb.save(file_path)


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 = font
    return ws


def number_to_column(n):
    """Convert a number to the corresponding column letter in Excel"""
    column = ""
    while n > 0:
        n -= 1
        column = string.ascii_uppercase[n % 26] + column
        n //= 26
    return 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 style_deal():
    file_path1 = './src/冬季奖励数据处理结果.xlsx'
    wb = load_workbook(file_path1)
    name_list = wb.sheetnames

    for name in name_list:
        ws = wb[name]
        set_cell_style(ws)
        #自适应列宽
        auto_col_width(ws)
    wb.save(file_path1)


def data_deal():
    file_path1 = './src/冬装奖励数据.xlsx'
    file_path2 = './src/冬季奖励数据处理结果.xlsx'
    sheet_name_deal(file_path1)
    create_excel(file_path2)

    # 读入数据
    df1 = pd.read_excel(file_path1, sheet_name='销售明细')
    df2 = df1[df1['季节名称'] == '冬'].copy()
    # 筛选品类
    category_list = ['大衣', '派克服', '皮毛一体', '皮羽绒', '羽绒服', '麂皮绒', '坎毛', '水貂派克服', '雪兰貂', '鹅绒服']
    #字典
    category_dict = dict(zip(category_list, [True]*len(category_list)))
    df2 = df2.loc[df2['品类名称'].isin(category_list), :]

    # 修改直销价为浮点类型
    df2['直销价'] = df2['直销价'].astype('float')
    # 单价在500元以上 1000元以下的衣服
    df3 = df2[(df2['直销价'] >= 500) & (df2['直销价'] < 1000)].copy()
    # 单价在1000元以上的衣服
    df4 = df2[df2['直销价'] >= 1000].copy()

    # 500-1000部门商店品类销售数量
    index_list = ['日期', '部门名称', '商店名称', '品类名称']
    value_list = ['销售数量']
    df31 = df3.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df31['提成金额'] = df31['销售数量'] * 50
    df31 = df31.reset_index()
    # 部门客户销售数量
    index_list = ['日期', '部门名称', '商店名称']
    value_list = ['销售数量']
    df32 = df3.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df32['提成金额'] = df32['销售数量'] * 50
    df32 = df32.reset_index()
    # 1000元以上部门商店品类销售数量
    index_list = ['日期', '部门名称', '商店名称', '品类名称']
    value_list = ['销售数量']
    df41 = df4.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df41['提成金额'] = df41['销售数量'] * 100
    df41 = df41.reset_index()
    # 部门客户销售数量
    index_list = ['日期', '部门名称', '商店名称']
    value_list = ['销售数量']
    df42 = df4.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df42['提成金额'] = df42['销售数量'] * 100
    df42 = df42.reset_index()

    #部门品类销售数量
    df51 = df31.copy()
    df52 = df41.copy()
    df51['500元档位数量'] = df51.pop('销售数量')
    df52['1000元档位数量'] = df52.pop('销售数量')
    index_list = ['部门名称', '品类名称']
    value_list = ['500元档位数量']
    df511 = df51.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    value_list = ['1000元档位数量']
    df521 = df52.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df53 = pd.merge(df511, df521, how='outer', left_index=True, right_index=True)
    df54 = df53.reset_index()
    df54 = df54.fillna(0)

    #日期部门客户大类备注
    df60 = df2.copy()
    df61 = df60[df60['直销价'] >= 500]
    index_list = ['部门名称', '商店名称']
    column_list = ['品类名称']
    #value_list = ['销售数量']
    df666 = df61.pivot_table(index=index_list, columns=column_list, values='销售数量', aggfunc='sum')
    df666 = df666.reset_index()
    # 生成备注列
    notes_list = []
    # 获取列名
    start_len = len(index_list)
    column_list = df666.columns.tolist()
    df666 = df666.fillna(0)
    for row in df666.itertuples():
        strings = ''
        for pos in range(start_len, len(column_list)):
            col_name = column_list[pos]
            num = getattr(row, col_name)
            num = int(num)
            if num != 0:
                strings = strings+str(num)+'件'+col_name+','
        # 切片操作
        strings = strings[:-1] + ""  # 将最后一个字符替换为空
        notes_list.append(strings)
    # 删除品类列
    sum_len = len(column_list)
    df667 = df666.copy()
    for pos in range(start_len, sum_len):
        col_name = column_list[pos]
        df667.pop(col_name)
    df667['备注'] = pd.Series(notes_list)


    # 获取部门客户品类
    df71 = df31.copy()
    df72 = df41.copy()
    df71['500元档位数量'] = df71.pop('销售数量')
    df72['1000元档位数量'] = df72.pop('销售数量')
    index_list = ['部门名称', '商店名称']
    value_list = ['500元档位数量']
    df711 = df71.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    value_list = ['1000元档位数量']
    df721 = df72.pivot_table(index=index_list, values=value_list, aggfunc='sum')
    df73 = pd.merge(df711, df721, how='outer', left_index=True, right_index=True)
    df74 = df73.reset_index()
    df74 = df74.fillna(0)
    df74['总奖励金额'] = df74['500元档位数量'] * 50 + df74['1000元档位数量']*100
    df75 = pd.merge(df74, df667, how='left')
    wb = load_workbook(file_path2)
    # 获取sheet页,修改第一个sheet页面为
    name1 = wb.sheetnames[0]
    ws1 = wb[name1]
    ws1.title = "500-1000冬装销售明细"
    # 创建工作表
    wb.create_sheet("500-1000客户品类销售")
    wb.create_sheet("500-1000客户销售")
    wb.create_sheet("1000以上冬装销售明细")
    wb.create_sheet("1000以上客户品类销售")
    wb.create_sheet("1000以上客户销售")
    wb.create_sheet("部门品类销售")
    wb.create_sheet("部门客户品类销售")
    wb.create_sheet("部门客户销售备注")
    wb.create_sheet("部门客户档位销售")
    wb.save(file_path2)

    # 将生成的工作表导入到程序中
    result_sheet = pd.ExcelWriter(file_path2, engine='openpyxl')  # 先定义要存入的文件名xxx，然后分别存入xxx下不同的sheet
    # df1将0转变为空
    df3.to_excel(result_sheet, "500-1000冬装销售明细", index=False, na_rep=0, inf_rep=0)
    df31.to_excel(result_sheet, "500-1000客户品类销售", index=False, na_rep=0, inf_rep=0)
    df32.to_excel(result_sheet, "500-1000客户销售", index=False, na_rep=0, inf_rep=0)

    df4.to_excel(result_sheet, "1000以上冬装销售明细", index=False, na_rep=0, inf_rep=0)
    df41.to_excel(result_sheet, "1000以上客户品类销售", index=False, na_rep=0, inf_rep=0)
    df42.to_excel(result_sheet, "1000以上客户销售", index=False, na_rep=0, inf_rep=0)
    df54.to_excel(result_sheet, "部门品类销售", index=False, na_rep=0, inf_rep=0)

    df666.to_excel(result_sheet, "部门客户品类销售", index=False, na_rep=0, inf_rep=0)
    df667.to_excel(result_sheet, "部门客户销售备注", index=False, na_rep=0, inf_rep=0)
    df75.sort_values(by="部门名称", axis=0, ascending=True, inplace=True)
    df75.to_excel(result_sheet, "客户档位销售", index=False, na_rep=0, inf_rep=0)
    # 这步不能省，否则不生成文件
    result_sheet.save()


def sheet_name_deal(file_path):
    wb = load_workbook(file_path)
    # 获取sheet页,修改第一个sheet页面名为销售明细
    name1 = wb.sheetnames[0]
    ws1 = wb[name1]
    ws1.title = "销售明细"
    wb.save(file_path)


if __name__ == '__main__':
    data_deal()
    style_deal()