不放回抽样_生成不重样菜单

任务背景：
某餐馆有猪肉菜、牛羊肉、家禽、鱼类等菜品分类，想制作每天中晚餐不重复菜单，要求每顿菜品由猪+其他组成，且最好不要连续都是猪肉的皮排骨、烧肉系列，要间隔开。

from openpyxl.styles import Alignment
import random
from collections import deque


def generate_non_repeating_meals(total_days=56):
    """菜单生成器"""
    pork_categories = {
        'pork_1': [
            "红烧肉", "萝卜烧肉", "豆泡烧肉", "海带烧肉", "鹌鹑蛋烧肉",
            "莲藕烧肉", "毛芋仔烧肉", "香菇烧肉", "虎皮蛋烧肉", "干豆条烧肉",
            "板栗烧肉", "咸鱼烧肉", "鱼蛋烧肉", "鹿茸菇烧肉", "梅干菜烧肉", "鲍鱼烧肉"],
        'pork_2': [
            "粉蒸排骨", "糯米排骨", "萝卜烧排骨", "鹌鹑蛋烧排骨", "莲藕烧排骨",
            "香芋蒸排骨", "糖醋排骨", "红烧大排", "荔浦芋头烧排", "土豆焖排骨", "豉香排骨"],
        'pork_3': [
            "红烧猪脚", "梅菜猪脚", "黄豆猪脚"],
        'pork_4': [
            "米粉蒸肉", "红烧狮子头", "盐菜扣肉", "太阳蛋"]}

    other_categories = {
        'beef': ['红烧牛杂', '红烧牛腩', '牛腩烧萝卜', '牛腩腐竹', '回锅牛肉',
                 '杭椒牛柳', '黑椒牛仔骨', '牛腩烧牛肚', '牛腩烧牛筋', '羊肉烧红萝卜',
                 '红烧羊肉', '葱爆牛肉', '孜然羊排'],
        'poultry': ['香辣鸭三件', '辣子鸡', '红烧鸡', '香菇烧鸡', '白切鸡',
                    '香酥鸭', '虎皮凤爪', '鹌鹑蛋凤爪', '啤酒鸭', '红烧鸭',
                    '香菇烧鸣', '烤鸭', '板鸭炖豆腐', '板鸭烧萝卜', '红烧鸡腿',
                    '可乐鸡中翅', '香辣卤鸡腿', '糯米蒸鸡', '莲花血鸭', '仔姜烧鹅',
                    '大盘鸡'],
        'fish': ['红烧鱼块', '南昌小炒鱼', '红烧鲫鱼', '红烧带鱼', '酒糟鱼',
                 '酸菜鱼丸', '白灼基围虾', '深海鱼尾', '小黄鱼', '酸菜鱼',
                 '鲇鱼烧豆腐', '香酥椒盐虾', '蒜蓉基围虾']}

    # 初始化状态
    last_used = {cat: deque(maxlen=3) for cat in ['pork_1', 'pork_2', 'pork_3']}
    used_dishes = {cat: set() for cat in pork_categories}
    used_other_dishes = {cat: set() for cat in other_categories}

    # 记录各类别的使用次数
    usage_count = {cat: 0 for cat in pork_categories}
    # 总餐数
    total_meals = total_days * 2  

    def select_category(category_name):
        """选择指定类别的菜品"""
        available = [d for d in pork_categories[category_name] if d not in used_dishes[category_name]]
        if not available:
            # 重置该类别的已使用记录
            used_dishes[category_name] = set()
            available = pork_categories[category_name]
        dish = random.choice(available)
        used_dishes[category_name].add(dish)
        return dish

    def select_pork(meal_type, day):
        """选择猪肉菜品并更新状态"""
        nonlocal usage_count

        available_cats = []

        # 首先检查有间隔限制的类别 (pork_1, pork_2, pork_3)
        for cat in ['pork_1', 'pork_2', 'pork_3']:
            # 检查间隔限制 - 增加到3顿间隔
            if last_used[cat]:
                last_day, last_meal_type = last_used[cat][-1]
                # 检查是否间隔足够（至少间隔3顿）
                # 如果同一天，或者第二天，或者第三天同一餐次，都不能使用
                if (day == last_day) or \
                        (day == last_day + 1) or \
                        (day == last_day + 2 and meal_type == last_meal_type):
                    continue

            # 检查可用菜品
            available = [d for d in pork_categories[cat] if d not in used_dishes[cat]]
            if available:
                available_cats.append((cat, available))

        # 计算当前pork_4的使用率
        pork_4_usage_rate = usage_count['pork_4'] / total_meals if total_meals > 0 else 0

        # 决定是否使用pork_4 - 更严格的控制
        use_pork_4 = False

        # 只有在以下情况下才使用pork_4：
        # 1. 没有其他可用类别
        # 2. pork_4使用率低于25%且随机概率很低
        if not available_cats:
            use_pork_4 = True
        elif pork_4_usage_rate < 0.25 and random.random() < 0.05:  # 降低到5%概率
            use_pork_4 = True

        # 优先选择pork_1和pork_2
        if available_cats and not use_pork_4:
            # 给pork_1和pork_2更高的权重
            weighted_cats = []
            for cat, dishes in available_cats:
                if cat == 'pork_1':
                    weighted_cats.extend([(cat, dishes)] * 4)  # 增加pork_1权重为4
                elif cat == 'pork_2':
                    weighted_cats.extend([(cat, dishes)] * 3)  # 增加pork_2权重为3
                else:  # pork_3
                    weighted_cats.append((cat, dishes))  # pork_3权重为1

            cat, dishes = random.choice(weighted_cats)
            dish = random.choice(dishes)
            used_dishes[cat].add(dish)
            usage_count[cat] += 1

            # 更新使用记录
            if cat in ['pork_1', 'pork_2', 'pork_3']:
                last_used[cat].append((day, meal_type))

            return dish, cat
        else:
            # 使用pork_4
            available_pork_4 = [d for d in pork_categories['pork_4'] if d not in used_dishes['pork_4']]
            if available_pork_4:
                dish = random.choice(available_pork_4)
                used_dishes['pork_4'].add(dish)
                usage_count['pork_4'] += 1
                return dish, 'pork_4'
            else:
                # 如果pork_4不可用，尝试重置其他类别
                for cat in ['pork_1', 'pork_2', 'pork_3']:
                    if not [d for d in pork_categories[cat] if d not in used_dishes[cat]]:
                        used_dishes[cat] = set()  # 重置该类别的已使用记录

                # 重新检查可用类别
                available_cats = []
                for cat in ['pork_1', 'pork_2', 'pork_3']:
                    if last_used[cat]:
                        last_day, last_meal_type = last_used[cat][-1]
                        if (day == last_day) or \
                                (day == last_day + 1) or \
                                (day == last_day + 2 and meal_type == last_meal_type):
                            continue

                    available = [d for d in pork_categories[cat] if d not in used_dishes[cat]]
                    if available:
                        available_cats.append((cat, available))

                if available_cats:
                    # 使用权重选择
                    weighted_cats = []
                    for cat, dishes in available_cats:
                        if cat == 'pork_1':
                            weighted_cats.extend([(cat, dishes)] * 4)
                        elif cat == 'pork_2':
                            weighted_cats.extend([(cat, dishes)] * 3)
                        else:
                            weighted_cats.append((cat, dishes))

                    cat, dishes = random.choice(weighted_cats)
                    dish = random.choice(dishes)
                    used_dishes[cat].add(dish)
                    usage_count[cat] += 1

                    if cat in ['pork_1', 'pork_2', 'pork_3']:
                        last_used[cat].append((day, meal_type))

                    return dish, cat
                else:
                    # 紧急情况使用pork_4（重置）
                    used_dishes['pork_4'] = set()
                    dish = random.choice(pork_categories['pork_4'])
                    used_dishes['pork_4'].add(dish)
                    usage_count['pork_4'] += 1
                    return dish, 'pork_4'

    def select_other():
        """选择其他肉类"""
        available_cats = []
        for cat, dishes in other_categories.items():
            available = [d for d in dishes if d not in used_other_dishes[cat]]
            if available:
                available_cats.append((cat, available))

        if not available_cats:
            # 重置所有其他肉类池
            for cat in other_categories:
                used_other_dishes[cat] = set()
            available_cats = [(cat, dishes) for cat, dishes in other_categories.items()]

        cat, dishes = random.choice(available_cats)
        dish = random.choice(dishes)
        used_other_dishes[cat].add(dish)
        return dish

    # 生成菜单
    meals = []
    for day in range(total_days):
        # 午餐
        lunch_pork, lunch_pork_type = select_pork('lunch', day)
        lunch_other = select_other()

        # 晚餐
        dinner_pork, dinner_pork_type = select_pork('dinner', day)
        dinner_other = select_other()

        meals.append({
            'lunch': [lunch_pork, lunch_other],
            'dinner': [dinner_pork, dinner_other],
            'lunch_pork_type': lunch_pork_type,
            'dinner_pork_type': dinner_pork_type
        })

    return meals


def write_meat_to_all_sheets(file_path, output_path):
    """Excel写入器"""
    wb = load_workbook(file_path)
    all_meals = generate_non_repeating_meals(56)

    # 按周分组
    weekly_meals = [all_meals[i * 7:(i + 1) * 7] for i in range(8)]

    for sheet_idx, (sheet_name, week_meals) in enumerate(zip(wb.sheetnames, weekly_meals)):
        if sheet_idx >= len(weekly_meals):
            break

        ws = wb[sheet_name]

        # 指定写入数据列（A、C、E、G、I、K、M列）
        odd_columns = [1, 3, 5, 7, 9, 11, 13]

        # 写入数据
        for day, meals in enumerate(week_meals):
            col = odd_columns[day]

            # 午餐
            ws.cell(row=4, column=col, value=meals['lunch'][0])
            ws.cell(row=5, column=col, value=meals['lunch'][1])

            # 晚餐
            ws.cell(row=11, column=col, value=meals['dinner'][0])
            ws.cell(row=12, column=col, value=meals['dinner'][1])

        # 设置格式
        for row in [4, 5, 11, 12]:
            for col in odd_columns:
                ws.cell(row=row, column=col).alignment = Alignment(
                    horizontal='center',
                    vertical='center',
                    wrap_text=True)

        print(f"已处理 sheet【{sheet_name}】的肉类数据")

    wb.save(output_path)
    print(f"所有肉类数据已写入: {output_path}")

    # 打印统计信息
    print("\n菜单统计:")
    pork_types_count = {"pork_1": 0, "pork_2": 0, "pork_3": 0, "pork_4": 0}
    for meal in all_meals:
        pork_types_count[meal['lunch_pork_type']] += 1
        pork_types_count[meal['dinner_pork_type']] += 1

    total_pork_meals = sum(pork_types_count.values())
    for cat, count in pork_types_count.items():
        percentage = count / total_pork_meals * 100
        print(f"{cat}使用次数: {count} ({percentage:.1f}%)")

if __name__ == "__main__":
    input_path = r'D:\文档\WXWork\1688855493661308\Cache\File\2025-11\食堂菜单 - 副本.xlsm'
    output_path = r'D:\优化版食堂菜单.xlsx'

    write_meat_to_all_sheets(input_path, output_path)```