库存分析实销-代码

准备工作

导入包

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

原始数据的字段

部门名称	品牌名称	00-期初	01-进货	09-配货入	10-配退入	12-移仓入	18-零售退	01-进货退	07-配货出	08-配退出	11-移仓出	17-零售	22-调整	23-期末

数据读入

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

修改工作表名

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)

创建文件

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

选择列

import pandas as pd

df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]})
df = df[['B', 'C']]

删除列

df.drop(['A', 'B'], axis=1, inplace=True)

现有列名

部门名称	品牌名称	00-期初	01-进货	09-配货入	10-配退入	12-移仓入	18-零售退	01-进货退	07-配货出	08-配退出	11-移仓出	17-零售	22-调整	23-期末

完整代码

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

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


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)


# 处理格式
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):
    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, file_path2):

    create_excel(file_path2)
    # 读入数据
    sheet_name_deal(file_path1)
    df1 = pd.read_excel(file_path1, sheet_name='销售明细')
    # 去除NaN,变为0
    df1 = df1.fillna('0')

    # print(df1)
    # 判断列是否存在
    # 创建厂进货
    if '00-期初' in df1.columns:
        df1['00-期初'] = df1['00-期初'].astype(int)
        df1["01-进货"] = df1["01-进货"].astype(int)
        df1['厂进货'] = df1['00-期初'] + df1["01-进货"]
    else:
        df1["01-进货"] = df1["01-进货"].astype(int)
        df1['厂进货'] = df1["01-进货"] * 1.0

    # 此时是浅拷贝，修改了df1

    # df2 = df1[df1['厂进货'] == 0]
    # df2['厂进货'] = df2['22-调整']
    # 创建厂退货
    df1['01-进货退'] = df1['01-进货退'].astype(int)
    df1['厂退货'] = df1['01-进货退'] * (-1)
    # 创建实际进货
    df1['实际进货'] = df1["厂进货"] - df1['厂退货']
    # 创建销售数量
    df1['销售数量'] = df1['17-零售'] * (-1)
    # 填充空值为0
    df1['销售数量'] = df1['销售数量'].fillna(0)
    df1['销售数量'] = df1['销售数量'].replace('', 0)
    print(df1['销售数量'])
    df1['销售数量'] = df1['销售数量'].astype(int)
    # 创建库存数量
    df1['库存数量'] = df1['23-期末']
    # 创建销比
    df1['销比'] = df1['销售数量'].div(df1['厂进货']).fillna('')
    # 创建备注
    df1['备注'] = ''
    # 选取列
    result_list = ['部门名称', '品牌名称', '厂进货', '厂退货', '实际进货', '销售数量', '库存数量', '销比', '备注']
    df1 = df1[result_list]
    # 将0值变为空
    df1 = df1.replace(0, '')
    df1 = df1.replace('0', '')
    result_sheet = pd.ExcelWriter(file_path2, engine='openpyxl')  # 先定义要存入的文件名xxx，然后分别存入xxx下不同的sheet
    # df1将0转变为空
    df1.to_excel(result_sheet, "销售明细", index=False, na_rep=0, inf_rep=0)
    # 这步不能省，否则不生成文件
    result_sheet.save()


def column_ordinal_by_name(ws, name):
    header_row = 1  # 假设表头在第1行
    header_column = None
    for row in ws.iter_rows(min_row=header_row, max_row=header_row):
        for cell in row:
            if cell.value == name:
                header_column = cell.column
                break

    if header_column is not None:
        print(f"表头'Name'在第{header_column}列")
    else:
        print("未找到指定的表头")
    return header_column


def self_style(file_path2):
    sheet_name_deal(file_path2)
    # df1 = pd.read_excel(file_path1, sheet_name='销售明细')
    # 选择需要修改格式的列，例如第一列(A列)
    wb = load_workbook(file_path2)
    ws = wb['销售明细']
    # 获取列名的列序号
    column_ordinal_num = column_ordinal_by_name(ws, '销比')
    # 将列数字序号转化为字母序号
    column_ordinal_letter = number_to_column(column_ordinal_num)
    print(column_ordinal_letter)
    # 将列格式转换为百分比
    column = ws[column_ordinal_letter]
    for cell in column:
        cell.number_format = '0.00%'

    # 全局染色
    high_light_color = 'D8E4BC'
    for i in range(1, ws.max_row+1):
        for cell in ws[i]:
            cell.fill = PatternFill(start_color=high_light_color, end_color="FF0000", fill_type="solid")
            #print(cell)

    # 关键染色
    key_color = '92D050'
    for cell in ws[1]:
        cell.fill = PatternFill(start_color=key_color, end_color="FF0000", fill_type="solid")
    for cell in ws['A']:
        cell.fill = PatternFill(start_color=key_color, end_color="FF0000", fill_type="solid")
    for cell in ws[ws.max_row]:
        cell.fill = PatternFill(start_color=key_color, end_color="FF0000", fill_type="solid")
    # 品牌名为合计染色
    for i in range(1, ws.max_row+1):
        if ws['B'+str(i)].value == '合计':
            for cell in ws[i]:
                cell.fill = PatternFill(start_color=key_color, end_color="FF0000", fill_type="solid")

    wb.save(file_path2)


if __name__ == '__main__':
    file_path1 = './src/23春数据.xlsx'
    file_path2 = './src/23春库存精修.xlsx'
    data_deal(file_path1, file_path2)
    style_deal(file_path2)

    self_style(file_path2)