from openpyxl import Workbook, load_workbook

# 创建新工作簿
wb = Workbook()
ws = wb.active
ws.title = "Sales Data"

# 写入数据
ws['A1'] = 'Product'
ws['B1'] = 'Quantity'
ws['C1'] = 'Price'

data = [
    ['Apple', 100, 1.5],
    ['Banana', 200, 0.8],
    ['Cherry', 50, 3.0],
]

for row in data:
    ws.append(row)

# 添加公式
ws['D1'] = 'Total'
for row in range(2, 5):
    ws[f'D{row}'] = f'=B{row}*C{row}'

ws['D5'] = '=SUM(D2:D4)'

# 保存
wb.save('sales.xlsx')

# 读取工作簿
wb = load_workbook('sales.xlsx')
ws = wb.active
print(ws['A1'].value)  # Product

from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

# 字体
ws['A1'].font = Font(bold=True, size=14, color='FF0000')

# 填充
ws['A1'].fill = PatternFill(start_color='FFFF00', fill_type='solid')

# 对齐
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')

# 边框
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
ws['A1'].border = thin_border

# 列宽
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 15

from openpyxl.chart import BarChart, Reference

# 创建柱状图
chart = BarChart()
chart.title = "Sales Report"
chart.x_axis.title = "Product"
chart.y_axis.title = "Revenue"

data = Reference(ws, min_col=4, min_row=1, max_row=4)
categories = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

ws.add_chart(chart, "F2")

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

def generate_sales_report(sales_data, output_file):
    wb = Workbook()
    ws = wb.active
    ws.title = "Monthly Report"
    
    # 标题
    ws.merge_cells('A1:D1')
    ws['A1'] = f'Sales Report - {datetime.now().strftime("%B %Y")}'
    ws['A1'].font = Font(bold=True, size=16)
    
    # 表头
    headers = ['Product', 'Units', 'Unit Price', 'Revenue']
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=3, column=col, value=header)
        cell.font = Font(bold=True)
        cell.fill = PatternFill(start_color='4472C4', fill_type='solid')
        cell.font = Font(bold=True, color='FFFFFF')
    
    # 数据
    for row_idx, item in enumerate(sales_data, 4):
        ws.cell(row=row_idx, column=1, value=item['product'])
        ws.cell(row=row_idx, column=2, value=item['units'])
        ws.cell(row=row_idx, column=3, value=item['price'])
        ws.cell(row=row_idx, column=4).value = f'=B{row_idx}*C{row_idx}'
    
    # 总计
    total_row = len(sales_data) + 4
    ws.cell(row=total_row, column=1, value='Total').font = Font(bold=True)
    ws.cell(row=total_row, column=4).value = f'=SUM(D4:D{total_row-1})'
    
    wb.save(output_file)
    print(f"Report saved to {output_file}")

# 使用
sales = [
    {'product': 'Widget A', 'units': 150, 'price': 29.99},
    {'product': 'Widget B', 'units': 200, 'price': 19.99},
    {'product': 'Widget C', 'units': 75, 'price': 49.99},
]
generate_sales_report(sales, 'report.xlsx')