from openpyxl import Workbook

wb = Workbook()
ws = wb.active
print(ws.title)

ws.title = 'Overview'
print(ws.title)
print(wb.sheetnames)

details_sheet = wb.create_sheet('Details')
summary_sheet = wb.create_sheet('Summary', 0)
print(wb.sheetnames)

del wb['Details']
print(wb.sheetnames)

wb.save('workbook_basics.xlsx')
print('Saved workbook_basics.xlsx')

from openpyxl import load_workbook

reloaded = load_workbook('workbook_basics.xlsx')
print(reloaded.sheetnames)

wb = Workbook()
ws = wb.active
ws.title = 'Cells'

ws['A1'] = 'Product'
ws['B1'] = 'Units Sold'
ws['A2'] = 'Wireless Mouse'
ws['B2'] = 42

ws.cell(row=3, column=1, value='Desk Lamp')
ws.cell(row=3, column=2, value=17)
print(ws['A1'].value, ws['A3'].value, ws['B3'].value)

rows_to_add = [
    ('USB-C Cable', 88),
    ('Mechanical Keyboard', 23),
    ('Webcam', 15),
]
for product, units in rows_to_add:
    ws.append([product, units])
print(ws.max_row, ws.max_column)

for row in ws.iter_rows(min_row=1, max_row=ws.max_row, values_only=True):
    print(row)

total_units = sum(row[1] for row in ws.iter_rows(min_row=2, values_only=True))
print(f'Total units sold: {total_units}')

ws['C1'] = 'Restock Needed?'
for row_num in range(2, ws.max_row + 1):
    ws.cell(row=row_num, column=3, value=f'=IF(B{row_num}<20, "Yes", "No")')
wb.save('cells_and_formulas.xlsx')
print('Saved cells_and_formulas.xlsx')

formula_check = load_workbook('cells_and_formulas.xlsx')
check_sheet = formula_check.active
print(check_sheet['C2'].value)

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

wb = Workbook()
ws = wb.active
ws.title = 'Styled Report'

ws['A1'] = 'Quarterly Sales Report'
ws['A1'].font = Font(size=16, bold=True, color='1F4E78')
ws.merge_cells('A1:D1')
ws['A1'].alignment = Alignment(horizontal='center')

headers = ['Region', 'Product', 'Revenue']
ws.append([])
ws.append(headers)
header_fill = PatternFill(start_color='1F4E78', end_color='1F4E78', fill_type='solid')
header_font = Font(color='FFFFFF', bold=True)
for cell in ws[3]:
    cell.fill = header_fill
    cell.font = header_font

data_rows = [
    ('North', 'Wireless Mouse', 4200.50),
    ('South', 'Desk Lamp', 1875.00),
    ('East', 'Mechanical Keyboard', 3120.75),
]
for row in data_rows:
    ws.append(row)

thin = Side(style='thin', color='999999')
border = Border(left=thin, right=thin, top=thin, bottom=thin)
for row in ws.iter_rows(min_row=3, max_row=ws.max_row, min_col=1, max_col=3):
    for cell in row:
        cell.border = border
        if cell.column == 3 and cell.row > 3:
            cell.number_format = '$#,##0.00'

from openpyxl.utils import get_column_letter

for column_cells in ws.columns:
    lengths = [len(str(cell.value)) for cell in column_cells if cell.value is not None]
    longest = max(lengths) if lengths else 0
    letter = get_column_letter(column_cells[0].column)
    ws.column_dimensions[letter].width = longest + 4

wb.save('styled_report.xlsx')
print('Saved styled_report.xlsx')

from openpyxl.formatting.rule import ColorScaleRule, CellIsRule

wb = Workbook()
ws = wb.active
ws.title = 'Scores'
ws.append(['Student', 'Score'])
for name, score in [('Amir', 92), ('Bianca', 68), ('Carlos', 74), ('Deepa', 55), ('Elin', 88)]:
    ws.append([name, score])

color_rule = ColorScaleRule(
    start_type='min', start_color='F8696B',
    end_type='max', end_color='63BE7B'
)
ws.conditional_formatting.add(f'B2:B{ws.max_row}', color_rule)

fail_rule = CellIsRule(operator='lessThan', formula=['70'], fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid'))
ws.conditional_formatting.add(f'B2:B{ws.max_row}', fail_rule)
wb.save('conditional_scores.xlsx')
print('Saved conditional_scores.xlsx')

from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
ws.title = 'Revenue'
ws.append(['Product', 'Revenue'])
for product, revenue in [('Mouse', 4200), ('Keyboard', 3120), ('Lamp', 1875), ('Webcam', 990)]:
    ws.append([product, revenue])

chart = BarChart()
chart.title = 'Revenue by Product'
chart.x_axis.title = 'Product'
chart.y_axis.title = 'Revenue ($)'

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

ws.add_chart(chart, 'D2')
wb.save('revenue_chart.xlsx')
print('Saved revenue_chart.xlsx')

wb = Workbook()
ws = wb.active
ws.title = 'Large Table'
ws.append(['ID', 'Name', 'Department'])
for i in range(1, 41):
    ws.append([i, f'Employee {i}', 'Engineering' if i % 2 == 0 else 'Sales'])

ws.freeze_panes = 'A2'
print(ws.freeze_panes)

ws.column_dimensions['A'].width = 8
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 15
ws.row_dimensions[1].height = 22
wb.save('large_table.xlsx')
print('Saved large_table.xlsx')

from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.title = 'Orders'
ws.append(['Order ID', 'Status', 'Quantity'])
for i in range(1, 6):
    ws.append([1000 + i, '', 0])

status_rule = DataValidation(type='list', formula1='"Pending,Shipped,Delivered,Cancelled"', allow_blank=True)
status_rule.error = 'Please choose a valid status from the dropdown.'
status_rule.errorTitle = 'Invalid Status'
ws.add_data_validation(status_rule)
status_rule.add(f'B2:B{ws.max_row}')

quantity_rule = DataValidation(type='whole', operator='greaterThanOrEqual', formula1='0')
quantity_rule.error = 'Quantity cannot be negative.'
ws.add_data_validation(quantity_rule)
quantity_rule.add(f'C2:C{ws.max_row}')
wb.save('orders_validated.xlsx')
print('Saved orders_validated.xlsx')

wb = Workbook()
data_sheet = wb.active
data_sheet.title = 'Raw Data'
data_sheet.append(['Region', 'Revenue'])
for region, revenue in [('North', 12500), ('South', 9800), ('East', 15200), ('West', 11100)]:
    data_sheet.append([region, revenue])

summary_sheet = wb.create_sheet('Summary')
summary_sheet['A1'] = 'Total Revenue'
summary_sheet['B1'] = "=SUM('Raw Data'!B2:B5)"
summary_sheet['A2'] = 'Average Revenue'
summary_sheet['B2'] = "=AVERAGE('Raw Data'!B2:B5)"
wb.save('multi_sheet_report.xlsx')
print('Saved multi_sheet_report.xlsx')

import random

random.seed(11)
regions = ['North', 'South', 'East', 'West']
products = ['Wireless Mouse', 'Mechanical Keyboard', 'Desk Lamp', 'Webcam']
sales_records = []
for region in regions:
    for product in products:
        sales_records.append({
            'region': region,
            'product': product,
            'revenue': round(random.uniform(800, 6000), 2),
        })
print(len(sales_records), 'records generated')

def build_sales_report(records, output_path):
    wb = Workbook()
    raw = wb.active
    raw.title = 'Raw Data'
    raw.append(['Region', 'Product', 'Revenue'])
    for r in records:
        raw.append([r['region'], r['product'], r['revenue']])

    header_fill = PatternFill(start_color='1F4E78', end_color='1F4E78', fill_type='solid')
    header_font = Font(color='FFFFFF', bold=True)
    for cell in raw[1]:
        cell.fill = header_fill
        cell.font = header_font
    for row in raw.iter_rows(min_row=2, min_col=3, max_col=3):
        for cell in row:
            cell.number_format = '$#,##0.00'
    raw.freeze_panes = 'A2'
    raw.column_dimensions['B'].width = 22

    summary = wb.create_sheet('Summary')
    summary.append(['Region', 'Total Revenue'])
    for i, region in enumerate(regions, start=2):
        summary.cell(row=i, column=1, value=region)
        summary.cell(row=i, column=2, value=f"=SUMIF('Raw Data'!A:A,A{i},'Raw Data'!C:C)")
    for cell in summary[1]:
        cell.fill = header_fill
        cell.font = header_font

    color_rule = ColorScaleRule(start_type='min', start_color='F8696B', end_type='max', end_color='63BE7B')
    summary.conditional_formatting.add(f'B2:B{summary.max_row}', color_rule)

    chart = BarChart()
    chart.title = 'Total Revenue by Region'
    chart.y_axis.title = 'Revenue ($)'
    data = Reference(summary, min_col=2, min_row=1, max_row=summary.max_row)
    categories = Reference(summary, min_col=1, min_row=2, max_row=summary.max_row)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(categories)
    summary.add_chart(chart, 'D2')

    wb.save(output_path)
    return output_path

saved_path = build_sales_report(sales_records, 'sales_report.xlsx')
print(f'Report saved to {saved_path}')

verify = load_workbook('sales_report.xlsx')
print(verify.sheetnames)
print(verify['Summary']['A2'].value, '->', verify['Summary']['B2'].value)