from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Report"

# Header row
headers = ["Region", "Q1", "Q2", "Total"]
ws.append(headers)
header_font = Font(name="Arial", bold=True, color="FFFFFF")
header_fill = PatternFill("solid", fgColor="4472C4")
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

# Data + formula per row
for row in [["East", 100, 150], ["West", 90, 120]]:
    ws.append(row)
for r in range(2, ws.max_row + 1):
    ws.cell(row=r, column=4).value = f"=SUM(B{r}:C{r})"

# Merged title above the table: insert row first, then merge
ws.insert_rows(1)
ws["A1"] = "Quarterly Sales"
ws.merge_cells("A1:D1")
ws["A1"].font = Font(size=14, bold=True)

# Column widths (openpyxl never auto-sizes)
for col in range(1, 5):
    ws.column_dimensions[get_column_letter(col)].width = 14

# Number format
for r in range(3, ws.max_row + 1):
    for c in range(2, 5):
        ws.cell(row=r, column=c).number_format = "#,##0"

wb.save("report.xlsx")

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = "Revenue by Region"
data = Reference(ws, min_col=2, max_col=3, min_row=2, max_row=ws.max_row)  # includes header row for series names
cats = Reference(ws, min_col=1, min_row=3, max_row=ws.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "F3")   # anchor = top-left cell of the chart
wb.save("report.xlsx")

from openpyxl import load_workbook

wb = load_workbook("report.xlsx", data_only=True)

# All sheets → list of rows
for name in wb.sheetnames:
    ws = wb[name]
    rows = [[c.value for c in row] for row in ws.iter_rows()]

# Used range only (skips trailing empty rows/cols)
ws = wb["Report"]
data = [[c.value for c in row] for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=ws.max_column)]

# Streaming read for large files
wb_big = load_workbook("big.xlsx", read_only=True, data_only=True)
for row in wb_big["Sheet1"].iter_rows(values_only=True):
    pass  # process row tuple
wb_big.close()  # read_only keeps the file handle open — always close

from openpyxl import load_workbook
wb = load_workbook("report.xlsx")
ws = wb["Report"]

# Insert / delete
ws.insert_rows(2)          # one row above row 2
ws.delete_cols(3)          # delete column C
ws.insert_cols(3, amount=2)

# Find-and-replace (string cells only)
for row in ws.iter_rows():
    for cell in row:
        if isinstance(cell.value, str) and "East" in cell.value:
            cell.value = cell.value.replace("East", "North-East")

# Add a sheet at a position
summary = wb.create_sheet("Summary", 0)  # index 0 = first tab
summary["A1"] = "=Report!D3"

wb.save("report.xlsx")

# CSV → xlsx
import csv
from openpyxl import Workbook
wb = Workbook(); ws = wb.active
with open("data.csv", newline="") as f:
    for row in csv.reader(f):
        ws.append(row)
wb.save("data.xlsx")

# xlsx → CSV (one sheet)
import csv
from openpyxl import load_workbook
ws = load_workbook("data.xlsx", data_only=True).active
with open("out.csv", "w", newline="") as f:
    csv.writer(f).writerows([c if c is not None else "" for c in row]
                            for row in ws.iter_rows(values_only=True))

# Bulk I/O escape hatch
import pandas as pd
df = pd.read_excel("data.xlsx", sheet_name="Report")   # needs openpyxl installed
df.to_excel("out.xlsx", index=False)                   # loses all formulas/formatting