Python导入excel xlsx格式文件

%reset -f
%clear

import pandas as pd

file_path = "D:/Python code/0717销售订单.xlsx".strip()

try:
    # 使用 openpyxl 引擎读取 .xlsx 文件
    df = pd.read_excel(file_path, engine='openpyxl', header=None)

    # 假设业务员名称在第11列（索引为10），ABS订单销售在第X列，订单状态在第Y列，销售规格在Z列
    salesman_col = 10  # 业务员名称的列索引
    sales_amount_col = 30  # ABS订单销售的列索引
    sales_state_col = 44  # 订单状态的列索引
    sales_type_col = 25  # 销售规格列索引

    salesmen_ABS_total = {}  # 用于存储满足条件的业务员的ABS订单销售总和
    salesmen_AS_total = {}  # 用于存储满足条件的业务员的AS订单销售总和
    salesmen = ['ZZF', 'MMM', 'WW', 'QQQ', 'WQ', 'JJJ', 'LLL', 'LQ', 'SSS']  # 我们关心的业务员列表
    eastern_men = salesmen[:5]  # 华东地区的业务员列表
    southern_men = salesmen[-4:]

    # 遍历DataFrame，累加满足条件的业务员的订单销售总和
    for index, row in df.iterrows():
        salesman_name = row[salesman_col]
        sales_state = row[sales_state_col]
        sales_amount = row[sales_amount_col]
        sales_type = row[sales_type_col]

        if (salesman_name in salesmen and sales_state in ['有效', '完结']):
            if sales_type in ['ZA6666', 'ZA6666-G', 'ZA7777', 'ZA0211-G']:
                if salesman_name in salesmen_ABS_total:
                    salesmen_ABS_total[salesman_name] += sales_amount
                else:
                    salesmen_ABS_total[salesman_name] = sales_amount
            elif sales_type in ['ZS6666', 'ZS6666-G', 'ZS7777']:
                if salesman_name in salesmen_AS_total:
                    salesmen_AS_total[salesman_name] += sales_amount
                else:
                    salesmen_AS_total[salesman_name] = sales_amount

    # 打印每个业务员的ABS订单销售总和（满足订单状态为“有效”或“完结”）
    for name in salesmen:
        if name in salesmen_ABS_total:
            print(f"{name}的ABS订单销售总和（有效或完结状态）为: {salesmen_ABS_total[name]}")
        else:
            print(f"没有找到{name}的ABS订单销售（有效或完结状态），或数据为空。")

    # 计算并打印华东地区业务员的ABS订单销售总和
    eastern_ABS_sales = sum(salesmen_ABS_total[name] for name in eastern_men if name in salesmen_ABS_total)
    print(f"华东合计的ABS订单销售总和（有效或完结状态）为：{eastern_ABS_sales}")

    # 计算并打印华南地区业务员的ABS订单销售总和
    southern_ABS_sales = sum(salesmen_ABS_total[name] for name in southern_men if name in salesmen_ABS_total)
    print(f"华南合计的ABS订单销售总和（有效或完结状态）为：{southern_ABS_sales}")

    # 打印每个业务员的AS订单销售总和（满足订单状态为“有效”或“完结”）
    for name in salesmen:
        if name in salesmen_AS_total:
            print(f"{name}的AS订单销售总和（有效或完结状态）为: {salesmen_AS_total[name]}")
        else:
            print(f"没有找到{name}的AS订单销售（有效或完结状态），或数据为空。")

    # 计算并打印华东地区业务员的AS订单销售总和
    eastern_AS_sales = sum(salesmen_AS_total[name] for name in eastern_men if name in salesmen_AS_total)
    print(f"华东合计的AS订单销售总和（有效或完结状态）为：{eastern_AS_sales}")

    # 计算并打印华南地区业务员的AS订单销售总和
    southern_AS_sales = sum(salesmen_AS_total[name] for name in southern_men if name in salesmen_AS_total)
    print(f"华南合计的AS订单销售总和（有效或完结状态）为：{southern_AS_sales}")
    


except FileNotFoundError:
    print(f"File {file_path} not found.")
except Exception as e:
    print(f"An error occurred: {e}")