| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071 |
- 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)
|
