📗 Excel 表格处理

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active
ws.title = "Revenue Forecast"

# 设置标题（黑色）
ws['A1'] = "Revenue Forecast ($M)"
ws['A1'].font = Font(bold=True, color="000000")

# 年份列（文本格式）
ws['B1'] = "2024"
ws['C1'] = "2025"
ws['D1'] = "2026"

# 假设区域（蓝色文字）
ws['A3'] = "Assumptions"
ws['A4'] = "Growth Rate"
ws['B4'] = 0.15  # 15% 增长
ws['B4'].font = Font(color="0000FF")  # 蓝色=输入

# 基准收入（蓝色文字）
ws['A6'] = "Base Year Revenue"
ws['B6'] = 100.0
ws['B6'].font = Font(color="0000FF")

# 收入预测（黑色文字=公式）
ws['A8'] = "Projected Revenue"
ws['B8'] = "=B6"  # 2024 = 基准
ws['C8'] = "=B8*(1+$B$4)"  # 2025 = 2024 * (1+增长率)
ws['D8'] = "=C8*(1+$B$4)"  # 2026 = 2025 * (1+增长率)

for cell in [ws['B8'], ws['C8'], ws['D8']]:
    cell.font = Font(color="000000")  # 黑色=公式

wb.save("revenue_forecast.xlsx")

# 重新计算公式值
import subprocess
subprocess.run(["python", "recalc.py", "revenue_forecast.xlsx"])

import pandas as pd
import matplotlib.pyplot as plt

# 读取数据
df = pd.read_excel("sales_data.xlsx")

# 数据分析
monthly_sales = df.groupby('Month')['Revenue'].sum()
top_products = df.groupby('Product')['Revenue'].sum().sort_values(ascending=False).head(5)

# 创建分析报表
with pd.ExcelWriter("sales_analysis.xlsx", engine='openpyxl') as writer:
    # 写入汇总数据
    monthly_sales.to_excel(writer, sheet_name='Monthly Summary')
    top_products.to_excel(writer, sheet_name='Top Products')
    
    # 详细数据
    df.to_excel(writer, sheet_name='Raw Data', index=False)

from openpyxl import load_workbook

departments = ["销售部", "市场部", "技术部"]
data = {
    "销售部": {"revenue": 5000000, "growth": 0.23},
    "市场部": {"revenue": 1200000, "growth": 0.18},
    "技术部": {"revenue": 800000, "growth": 0.15}
}

for dept in departments:
    wb = load_workbook("template.xlsx")
    ws = wb.active
    
    # 更新标题
    ws['A1'] = f"{dept} - 季度报告"
    
    # 填入数据（蓝色=输入）
    ws['B5'] = data[dept]["revenue"]
    ws['B5'].font = Font(color="0000FF")
    
    ws['B6'] = data[dept]["growth"]
    ws['B6'].font = Font(color="0000FF")
    
    # 其他单元格包含公式（自动计算）
    
    wb.save(f"{dept}_report.xlsx")
    
    # 重算公式
    subprocess.run(["python", "recalc.py", f"{dept}_report.xlsx"])

import pandas as pd

# 读取 Excel
df = pd.read_excel("data.xlsx")

# 数据预览
print(df.head())
print(df.describe())

# 数据筛选
high_value = df[df['Sales'] > 10000]

# 导出结果
high_value.to_excel("high_value_sales.xlsx", index=False)

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 数据列
ws['A1'] = "Product"
ws['B1'] = "Quantity"
ws['C1'] = "Price"
ws['D1'] = "Total"

ws['A2'] = "Product A"
ws['B2'] = 10
ws['C2'] = 50
ws['D2'] = "=B2*C2"  # 公式，不是硬编码

ws['A3'] = "Product B"
ws['B3'] = 15
ws['C3'] = 80
ws['D3'] = "=B3*C3"

# 总计（使用 SUM 公式）
ws['D4'] = "=SUM(D2:D3)"

wb.save("products.xlsx")

# ⚠️ 必须重算公式！
import subprocess
subprocess.run(["python", "recalc.py", "products.xlsx"])

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active

# 输入区域（蓝色）
ws['A1'] = "Sales Growth Assumption"
ws['B1'] = 0.15
ws['B1'].font = Font(color="0000FF")  # 蓝色=用户输入

# 公式区域（黑色）
ws['A3'] = "Projected Sales"
ws['B3'] = "=100*(1+B1)"
ws['B3'].font = Font(color="000000")  # 黑色=公式

# 需要关注的假设（黄色背景）
ws['B1'].fill = PatternFill(start_color="FFFF00", fill_type="solid")

wb.save("styled.xlsx")

# ❌ 错误：在 Python 中计算并硬编码
total = df['Sales'].sum()  # 假设是 5000
ws['B10'] = 5000  # 硬编码！

# ✅ 正确：使用 Excel 公式
ws['B10'] = "=SUM(B2:B9)"  # 动态计算