import pandas as pd

def generate_excel_template(file_path):
    # 创建各工作表的数据
    summary_data = {
        "指标": ["年度总营收", "年度总成本", "年度总利润", "年度毛利率", "总资产", "国家数量", "订单总量"],
        "值": [5800, 4500, 1300, "22.4%", 360000, 15, 90]
    }
    
    revenue_breakdown = {
        "类型": ["订单销售", "售后服务", "金融业务", "其他收入"],
        "金额 (万元)": [3800, 500, 200, 100]
    }
    
    cost_breakdown = {
        "类型": ["直接材料", "直接人工", "制造费用", "销售费用", "管理费用"],
        "金额 (万元)": [40, 20, 15, 5, 8]
    }
    
    country_performance = {
        "国家": ["澳大利亚", "美国", "英国", "德国", "法国", "日本", "韩国", "巴西", "印度", "俄罗斯"],
        "订单数量": [20, 25, 30, 35, 40, 45, 50, 55, 60, 65],
        "收入 (万元)": [20, 30, 40, 50, 60, 70, 80, 90, 100, 110],
        "利润 (万元)": [25.2, 27.7, 30.2, 32.7, 35.2, 37.7, 40.2, 42.7, 45.2, 47.7]
    }
    
    product_line_profit = {
        "类型": ["羊毛毯", "纯棉毯", "亚麻毯", "丝绸毯", "竹纤维毯", "涤纶毯", "腈纶毯", "混纺毯", "法兰绒毯", "珊瑚绒毯"],
        "利润 (万元)": [6.9, 7.8, 8.3, 9.1, 10.4, 11.2, 12.5, 13.5, 14.1, 15.2],
        "毛利率 (%)": [35.2, 28.3, 31.2, 26.9, 33.5, 30.4, 34.1, 27.8, 32.5, 29.1]
    }
    
    monthly_trade_data = {
        "月份": ["1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月"],
        "收入 (万元)": [380, 420, 510, 490, 560, 590, 720, 670, 810, 780, 920, 940],
        "成本 (万元)": [310, 340, 390, 370, 420, 400, 470, 430, 480, 460, 520, 550],
        "利润 (万元)": [70, 80, 120, 120, 140, 190, 250, 240, 330, 320, 400, 390],
        "利润率 (%)": [18.4, 19.0, 23.5, 24.5, 25.0, 32.2, 34.7, 35.8, 40.7, 41.0, 43.5, 41.5]
    }
    
    forecast_data = {
        "月份": ["1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月"],
        "预测收入 (万元)": [1000, 1100, 1200, 1300, 1400, 1500, 1600, 1700, 1800, 1900, 2000, 2100],
        "预测利润率 (%)": [42.0, 40.9, 40.0, 39.2, 38.6, 38.0, 37.5, 37.1, 36.7, 36.3, 36.0, 35.7],
        "预测资产 (万元)": [370000, 380000, 390000, 400000, 410000, 420000, 430000, 440000, 450000, 460000, 470000, 480000]
    }
    
    # 创建DataFrame
    summary_df = pd.DataFrame(summary_data)
    revenue_df = pd.DataFrame(revenue_breakdown)
    cost_df = pd.DataFrame(cost_breakdown)
    country_df = pd.DataFrame(country_performance)
    product_df = pd.DataFrame(product_line_profit)
    monthly_df = pd.DataFrame(monthly_trade_data)
    forecast_df = pd.DataFrame(forecast_data)
    
    # 将数据写入Excel文件
    with pd.ExcelWriter(file_path) as writer:
        summary_df.to_excel(writer, sheet_name='Summary', index=False)
        revenue_df.to_excel(writer, sheet_name='Revenue Breakdown', index=False)
        cost_df.to_excel(writer, sheet_name='Cost Breakdown', index=False)
        country_df.to_excel(writer, sheet_name='Country Performance', index=False)
        product_df.to_excel(writer, sheet_name='Product Line Profit', index=False)
        monthly_df.to_excel(writer, sheet_name='Monthly Trade Data', index=False)
        forecast_df.to_excel(writer, sheet_name='Forecast', index=False)
    
    print(f"Excel模板已生成：{file_path}")

if __name__ == '__main__':
    file_path = 'sales_data_template.xlsx'
    generate_excel_template(file_path)