Installation and Core Components

To begin using openpyxl, install it via pip:

pip install openpyxl

For image insertion capabilities, install pillow as well.

Key style components include:

• Font (typeface, size, color, underline)
• Fill (background colors/patterns)
• Border (cell perimeter styling)
• Alignment (text positioning)
• NumberFormat (data display format)
• Protection (write protection settings)

Creating and Populating Workbooks

from openpyxl import Workbook
import datetime
import time

# Initialize new workbook
workbook = Workbook()
sheet = workbook.active

# Direct cell assignment
sheet['A1'] = 42
sheet['B1'] = "Hello World"

# Append rows
sheet.append([1, 2, 3])

# Date handling
sheet['A2'] = datetime.datetime.now()
sheet['A3'] = time.strftime("%Y-%m-%d %H:%M:%S")

workbook.save("output.xlsx")

Worksheet Management

from openpyxl import Workbook

wb = Workbook()

# Create worksheets
ws1 = wb.create_sheet("DataSheet")
ws1.title = "Analytics"

# Insert at specific position
ws2 = wb.create_sheet("Temp", 0)
ws2.title = "Summary"

# Style worksheet tab
ws1.sheet_properties.tabColor = "FF0000"

# Access worksheets
print(wb["Analytics"])
print(wb.sheetnames)

# Duplicate worksheet
source = wb["Analytics"]
target = wb.copy_worksheet(source)

wb.save("multi_sheet.xlsx")

Cell Operations

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Cell manipulation
ws['A1'] = 123.45
ws['B2'] = "Sample Text"
cell = ws.cell(row=5, column=3, value=100)

# Access values
print(ws['A1'].value)
print(ws['B2'].value)
print(cell.value)

wb.save("cells.xlsx")

Bulk Cell Processing

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Populate data
for row in range(1, 4):
    for col in range(1, 4):
        ws.cell(row=row, column=col, value=row*col)

# Iterate columns
for col in ws['A:C']:
    for cell in col:
        print(cell.value)

# Iterate rows
for row in ws.iter_rows(min_row=1, max_row=3, max_col=3):
    for cell in row:
        print(cell.value)

wb.save("bulk.xlsx")

Working with Existing Files

from openpyxl import load_workbook

wb = load_workbook("existing.xlsx")
ws = wb.active

# Modify content
ws['D1'] = "Updated Value"

# Type handling
wb.guess_types = True
ws['E1'] = "15%"
print(ws['E1'].value)  # Shows as decimal

wb.save("modified.xlsx")

Formula Implementation

from openpyxl import load_workbook

wb = load_workbook("data.xlsx")
ws = wb.active

# Input formulas
ws['A1'] = 10
ws['A2'] = 20
ws['A3'] = "=SUM(A1:A2)"
ws['B1'] = "=AVERAGE(A1:A2)"

print(ws['A3'].value)  # Displays formula string

wb.save("formulas.xlsx")

Cell Merging Operations

from openpyxl import load_workbook

wb = load_workbook("data.xlsx")
ws = wb.active

# Merge cells
ws.merge_cells('B2:D2')

# Unmerge cells
ws.unmerge_cells('B2:D2')

# Alternative syntax
ws.merge_cells(start_row=3, start_column=1, 
               end_row=3, end_column=4)

wb.save("merged.xlsx")

Image Integration

from openpyxl import load_workbook
from openpyxl.drawing.image import Image

wb = load_workbook("report.xlsx")
ws = wb.active

# Insert image
logo = Image("company_logo.png")
ws.add_image(logo, 'A1')

wb.save("with_images.xlsx")

Chart Generation

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

# Prepare data
data = [[i] for i in range(10)]
for row in data:
    ws.append(row)

# Create chart
values = Reference(ws, min_col=1, min_row=1, 
                   max_col=1, max_row=10)
chart = BarChart()
chart.add_data(values)

ws.add_chart(chart, "E15")
wb.save("chart.xlsx")

Style Customization

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

wb = Workbook()
ws = wb.active

# Font styling
header_font = Font(name='Arial', size=14, 
                  bold=True, color='FF0000')
ws['A1'].font = header_font

# Background fill
cell_fill = PatternFill(start_color='FFFF00', 
                        end_color='FFFF00', 
                        fill_type='solid')
ws['A1'].fill = cell_fill

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

wb.save("styled.xlsx")

Table Formatting

from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active

# Prepare tabular data
data = [
    ['Product', 'Q1', 'Q2', 'Q3', 'Q4'],
    ['Laptops', 120, 150, 180, 200],
    ['Phones', 300, 280, 320, 350],
    ['Tablets', 80, 90, 85, 95]
]

for row in data:
    ws.append(row)

# Create table
table = Table(displayName="SalesData", ref="A1:E4")

# Apply style
style = TableStyleInfo(name="TableStyleMedium9", 
                       showRowStripes=True)
table.tableStyleInfo = style

ws.add_table(table)
wb.save("table.xlsx")

Advanced Styling

from openpyxl import Workbook
from openpyxl.styles import NamedStyle, Font, Alignment

wb = Workbook()
ws = wb.active

# Create custom style
highlight_style = NamedStyle(name="highlight")
highlight_style.font = Font(bold=True, size=12, 
                            color='FF6600')
highlight_style.alignment = Alignment(
    horizontal='center', 
    vertical='center'
)

# Apply to range
for cell in ws['A1:C1']:
    cell.style = highlight_style

wb.save("advanced_style.xlsx")