XLSX Manipulation Skill

Overview

This skill enables programmatic creation, editing, and manipulation of Microsoft Excel (.xlsx) spreadsheets using the openpyxl library. Create professional spreadsheets with formulas, formatting, charts, and data validation without manual editing.

How to Use

• Describe the spreadsheet you want to create or modify

• Provide data, formulas, or formatting requirements

• I'll generate openpyxl code and execute it

Example prompts:

• "Create a budget spreadsheet with monthly tracking"

• "Add conditional formatting to highlight values above threshold"

• "Generate a pivot-table-like summary from this data"

• "Create a dashboard with charts and KPIs"

Domain Knowledge

openpyxl Fundamentals

from openpyxl import Workbook, load_workbook

from openpyxl.styles import Font, Fill, Border, Alignment

from openpyxl.chart import BarChart, Reference

# Create new workbook

wb = Workbook()

ws = wb.active

# Or open existing

wb = load_workbook('existing.xlsx')

ws = wb.active

Workbook Structure

Workbook

├── worksheets (sheets/tabs)

│   ├── cells (data storage)

│   ├── rows/columns (formatting)

│   ├── merged_cells

│   └── charts

├── defined_names (named ranges)

└── styles (formatting templates)

Working with Cells

#### Basic Cell Operations

# By cell reference

ws['A1'] = 'Header'

ws['B1'] = 42

# By row, column

ws.cell(row=1, column=3, value='Data')

# Multiple cells

ws['A1:C1'] = [['Col1', 'Col2', 'Col3']]

# Append rows

ws.append(['Row', 'Data', 'Here'])

#### Reading Cells

# Single cell

value = ws['A1'].value

# Cell range

for row in ws['A1:C3']:

    for cell in row:

        print(cell.value)

# Iterate rows

for row in ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=3):

    for cell in row:

        print(cell.value)

Formulas

# Basic formulas

ws['D1'] = '=SUM(A1:C1)'

ws['D2'] = '=AVERAGE(A2:C2)'

ws['E1'] = '=IF(D1>100,"High","Low")'

# Named ranges

from openpyxl.workbook.defined_name import DefinedName

ref = "Sheet!$A$1:$C$10"

defn = DefinedName("SalesData", attr_text=ref)

wb.defined_names.add(defn)

# Use named range

ws['F1'] = '=SUM(SalesData)'

Formatting

#### Cell Styles

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

# Font

ws['A1'].font = Font(

    name='Arial',

    size=14,

    bold=True,

    italic=False,

    color='FF0000'  # Red

)

# Fill (background)

ws['A1'].fill = PatternFill(

    start_color='FFFF00',  # Yellow

    end_color='FFFF00',

    fill_type='solid'

)

# Border

thin_border = Border(

    left=Side(style='thin'),

    right=Side(style='thin'),

    top=Side(style='thin'),

    bottom=Side(style='thin')

)

ws['A1'].border = thin_border

# Alignment

ws['A1'].alignment = Alignment(

    horizontal='center',

    vertical='center',

    wrap_text=True

)

#### Number Formats

# Currency

ws['B2'].number_format = '$#,##0.00'

# Percentage

ws['C2'].number_format = '0.00%'

# Date

ws['D2'].number_format = 'YYYY-MM-DD'

# Custom

ws['E2'].number_format = '#,##0.00 "units"'

#### Conditional Formatting

from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule

from openpyxl.styles import PatternFill

# Color scale (heatmap)

color_scale = ColorScaleRule(

    start_type='min', start_color='FF0000',

    end_type='max', end_color='00FF00'

)

ws.conditional_formatting.add('A1:A10', color_scale)

# Cell value rule

red_fill = PatternFill(start_color='FFCCCC', end_color='FFCCCC', fill_type='solid')

rule = CellIsRule(operator='greaterThan', formula=['100'], fill=red_fill)

ws.conditional_formatting.add('B1:B10', rule)

Charts

from openpyxl.chart import BarChart, LineChart, PieChart, Reference

# Prepare data

data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=5)

categories = Reference(ws, min_col=1, min_row=2, max_row=5)

# Bar Chart

chart = BarChart()

chart.type = "col"  # or "bar" for horizontal

chart.title = "Sales by Region"

chart.add_data(data, titles_from_data=True)

chart.set_categories(categories)

chart.shape = 4

ws.add_chart(chart, "E1")

# Line Chart

line = LineChart()

line.title = "Trend Analysis"

line.add_data(data, titles_from_data=True)

line.set_categories(categories)

ws.add_chart(line, "E15")

# Pie Chart

pie = PieChart()

pie.add_data(data, titles_from_data=True)

pie.set_categories(categories)

ws.add_chart(pie, "M1")

Data Validation

from openpyxl.worksheet.datavalidation import DataValidation

# Dropdown list

dv = DataValidation(

    type="list",

    formula1='"Option1,Option2,Option3"',

    allow_blank=True

)

dv.error = "Please select from list"

dv.errorTitle = "Invalid Input"

ws.add_data_validation(dv)

dv.add('A1:A100')

# Number range

dv_num = DataValidation(

    type="whole",

    operator="between",

    formula1="1",

    formula2="100"

)

ws.add_data_validation(dv_num)

dv_num.add('B1:B100')

Sheet Operations

# Create new sheet

ws2 = wb.create_sheet("Data")

ws3 = wb.create_sheet("Summary", 0)  # At position 0

# Rename

ws.title = "Main Report"

# Delete

del wb["Sheet2"]

# Copy

source = wb["Template"]

target = wb.copy_worksheet(source)

Row/Column Operations

# Set column width

ws.column_dimensions['A'].width = 20

# Set row height

ws.row_dimensions[1].height = 30

# Hide column

ws.column_dimensions['C'].hidden = True

# Freeze panes

ws.freeze_panes = 'B2'  # Freeze row 1 and column A

# Auto-filter

ws.auto_filter.ref = "A1:D100"

Best Practices

• Use Templates: Start with a .xlsx template for complex formatting

• Batch Operations: Minimize cell-by-cell operations for speed

• Named Ranges: Use defined names for clearer formulas

• Data Validation: Add validation to prevent input errors

• Save Incrementally: For large files, save periodically

Common Patterns

Data Import

def import_csv_to_xlsx(csv_path, xlsx_path):

    import csv

    wb = Workbook()

    ws = wb.active

    with open(csv_path) as f:

        reader = csv.reader(f)

        for row in reader:

            ws.append(row)

    wb.save(xlsx_path)

Report Template

def create_monthly_report(data, output_path):

    wb = Workbook()

    ws = wb.active

    ws.title = "Monthly Report"

    # Headers

    headers = ['Date', 'Revenue', 'Expenses', 'Profit']

    ws.append(headers)

    # Style headers

    for col in range(1, 5):

        cell = ws.cell(1, col)

        cell.font = Font(bold=True)

        cell.fill = PatternFill('solid', fgColor='4472C4')

        cell.font = Font(bold=True, color='FFFFFF')

    # Data

    for row in data:

        ws.append(row)

    # Add totals

    last_row = len(data) + 1

    ws.cell(last_row + 1, 1, 'TOTAL')

    ws.cell(last_row + 1, 2, f'=SUM(B2:B{last_row})')

    ws.cell(last_row + 1, 3, f'=SUM(C2:C{last_row})')

    ws.cell(last_row + 1, 4, f'=SUM(D2:D{last_row})')

    wb.save(output_path)

Examples

Example 1: Budget Tracker

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 = "Budget 2024"

# Headers

months = ['Category', 'Jan', 'Feb', 'Mar', 'Q1 Total']

ws.append(months)

# Categories and data

budget_data = [

    ['Salary', 5000, 5000, 5000],

    ['Rent', -1500, -1500, -1500],

    ['Utilities', -200, -180, -220],

    ['Food', -400, -450, -380],

    ['Transport', -150, -160, -140],

    ['Entertainment', -200, -250, -200],

]

for row in budget_data:

    ws.append(row + [f'=SUM(B{ws.max_row + 1}:D{ws.max_row + 1})'])

# Total row

ws.append(['TOTAL',

    f'=SUM(B2:B{ws.max_row})',

    f'=SUM(C2:C{ws.max_row})',

    f'=SUM(D2:D{ws.max_row})',

    f'=SUM(E2:E{ws.max_row})'

])

# Formatting

header_fill = PatternFill('solid', fgColor='366092')

header_font = Font(bold=True, color='FFFFFF')

for cell in ws[1]:

    cell.fill = header_fill

    cell.font = header_font

    cell.alignment = Alignment(horizontal='center')

# Currency format

for row in ws.iter_rows(min_row=2, min_col=2, max_col=5):

    for cell in row:

        cell.number_format = '$#,##0.00'

# Column widths

ws.column_dimensions['A'].width = 15

for col in range(2, 6):

    ws.column_dimensions[get_column_letter(col)].width = 12

wb.save('budget_2024.xlsx')

Example 2: Sales Dashboard

from openpyxl import Workbook

from openpyxl.chart import BarChart, PieChart, Reference

from openpyxl.styles import Font, PatternFill

wb = Workbook()

ws = wb.active

ws.title = "Sales Dashboard"

# Data

ws.append(['Region', 'Q1', 'Q2', 'Q3', 'Q4'])

data = [

    ['North', 150000, 165000, 180000, 195000],

    ['South', 120000, 125000, 140000, 155000],

    ['East', 180000, 190000, 210000, 225000],

    ['West', 95000, 110000, 125000, 140000],

]

for row in data:

    ws.append(row)

# Bar Chart

data_ref = Reference(ws, min_col=2, min_row=1, max_col=5, max_row=5)

cats_ref = Reference(ws, min_col=1, min_row=2, max_row=5)

bar = BarChart()

bar.type = "col"

bar.title = "Quarterly Sales by Region"

bar.add_data(data_ref, titles_from_data=True)

bar.set_categories(cats_ref)

bar.height = 10

bar.width = 15

ws.add_chart(bar, "A8")

# Pie Chart - Q4 breakdown

pie_data = Reference(ws, min_col=5, min_row=1, max_row=5)

pie = PieChart()

pie.title = "Q4 Market Share"

pie.add_data(pie_data, titles_from_data=True)

pie.set_categories(cats_ref)

ws.add_chart(pie, "J8")

wb.save('sales_dashboard.xlsx')

Limitations

• Cannot execute VBA macros

• Complex pivot tables not fully supported

• Limited sparkline support

• External data connections not supported

• Some advanced chart types unavailable

Installation

pip install openpyxl

Resources

• openpyxl Documentation

• GitHub Repository

• Working with Styles