零售行业无论电商或者便利店,在上架初期必然是考虑将高利润商品进行黄金位置的上架摆放。面对多数据量,使用python可以瞬间完成计算,将商品进行高毛利到低毛利进行排序,同时基于条形编码将生成的条形与商品进行并列。
# 导入所需库
import pandas as pd
from barcode import Code128 # 用于生成CODE128条形码(通用型)
from barcode.writer import ImageWriter
import os
import uuid
from concurrent.futures import ThreadPoolExecutor, as_completed
def generate_barcode(barcode_value, product_name, save_path):
"""生成条形码并返回文件路径"""
try:
# 清理商品名称中的非法字符
safe_name = ''.join([c for c in product_name if c not in r'\/:*?"<>|'])
# 添加UUID防止命名冲突
unique_id = str(uuid.uuid4())[:8] # 取UUID前8位
filename = f"{safe_name}_{unique_id}_{barcode_value}.png"
full_path = os.path.join(save_path, filename)
# 生成条形码
barcode = Code128(str(barcode_value), writer=ImageWriter())
barcode.write(full_path)
return full_path
except Exception as e:
print(f"生成条形码失败(商品:{product_name},条码:{barcode_value}):{e}")
return None
def process_row(row, output_dir):
"""处理单行数据并生成条形码"""
barcode_path = generate_barcode(
barcode_value=str(row['条码']),
product_name=row['商品名称'],
save_path=output_dir
)
return row.name, barcode_path # 返回索引和路径
def main():
# ====================== 步骤1:读取Excel数据 ======================
file_path = r'D:\商品利润\lirun.xlsx' # 原始文件路径
try:
df = pd.read_excel(
file_path,
usecols=['条码', '商品名称', '零售价', '平均进货价']
)
except FileNotFoundError:
print(f"错误:未找到文件 {file_path}")
return
except Exception as e:
print(f"读取Excel失败:{e}")
return
# 检查必要列是否存在
required_columns = ['条码', '商品名称', '零售价', '平均进货价']
if not all(col in df.columns for col in required_columns):
print("错误:Excel文件缺少必要列")
return
# ====================== 步骤2:数据清洗与预处理 ======================
for col in ['零售价', '平均进货价']:
df[col] = pd.to_numeric(df[col], errors='coerce')
df_clean = df.dropna(subset=['条码', '商品名称', '零售价', '平均进货价']).copy()
if df_clean.empty:
print("错误:清洗后无有效数据")
return
# ====================== 步骤3:计算利润并排序 ======================
df_clean['利润'] = df_clean['零售价'] - df_clean['平均进货价']
df_sorted = df_clean.sort_values(by='利润', ascending=False).reset_index(drop=True)
# 创建目录用于存储条形码
output_barcode_dir = os.path.join(os.path.dirname(file_path), '商品条形码')
os.makedirs(output_barcode_dir, exist_ok=True)
# ====================== 步骤4:多线程生成条形码 ======================
print(f"开始生成{len(df_sorted)}个商品的条形码...")
df_sorted['条形码路径'] = "" # 新增列存储路径
# 使用线程池并行处理
with ThreadPoolExecutor() as executor:
futures = []
for _, row in df_sorted.iterrows():
futures.append(executor.submit(process_row, row, output_barcode_dir))
success_count = 0
for future in as_completed(futures):
try:
idx, barcode_path = future.result()
if barcode_path:
df_sorted.at[idx, '条形码路径'] = barcode_path
success_count += 1
except Exception as e:
print(f"处理任务时出错: {e}")
print(f"条形码生成完成,成功:{success_count},失败:{len(df_sorted) - success_count}")
# ====================== 步骤5:保存结果 ======================
result_file = os.path.join(os.path.dirname(file_path), '商品利润排序结果.xlsx')
try:
df_sorted.to_excel(result_file, index=False)
print(f"排序结果已保存至:{result_file}")
print(f"条形码存储在:{output_barcode_dir}")
except Exception as e:
print(f"保存结果文件失败:{e}")
if __name__ == "__main__":
main()