XLSX Generation

XLSX Generation

Structured guidance for generating, reading, and manipulating Excel XLSX files programmatically. Covers library selection, cell formatting, formulas, charts, advanced workbook features, and data pipeline integration across Python, JavaScript, and Java ecosystems.

When to Use This Skill

Use this skill for:

• Building automated report generators that output styled Excel workbooks
• Creating data export pipelines that produce XLSX files from databases or APIs
• Generating financial models, budgets, or forecasts as spreadsheets
• Formatting business dashboards with conditional formatting, charts, and pivot-ready data
• Converting DataFrame analysis results into multi-sheet Excel deliverables
• Integrating spreadsheet output into ETL or CI/CD workflows
• Reading and transforming existing Excel files (extract data, update cells, merge workbooks)
• Producing Excel templates with data validation, dropdowns, and protected ranges

Trigger phrases: "xlsx", "Excel generation", "spreadsheet", "openpyxl", "xlsxwriter", "ExcelJS", "Apache POI", "Excel report", "data export", "Excel automation", "workbook", "worksheet", "cell formatting", "Excel chart", "pivot table", "Excel formula", "DataFrame to Excel", "spreadsheet pipeline"

What This Skill Does

Provides Excel generation patterns including:

• Library Selection: Decision matrix for openpyxl, xlsxwriter, ExcelJS, Apache POI, and Pandas wrappers
• Cell Formatting: Fonts, fills, borders, alignment, number formats, merged cells, rich text
• Formulas: Cell references, named ranges, array formulas, cross-sheet formulas, formula auditing
• Charts: Bar, line, pie, scatter, combo charts with programmatic configuration and positioning
• Data Validation: Dropdown lists, numeric ranges, date constraints, custom formula validators
• Advanced Features: Autofilters, freeze panes, print setup, protection, VBA preservation, images
• Pandas Integration: DataFrame export, multi-sheet workbooks, Styler formatting, read/transform/write
• Performance: Streaming writes for large datasets, memory optimization, batch operations

Instructions

Step 1: Library Selection

Choosing the right library depends on whether you need read/write access, write-only performance, language ecosystem, and specific feature requirements.

Decision Matrix:

When to Use Each Library:

• openpyxl: Default choice for Python when you need both read and write, or must modify existing files. Best for template-based report generation where you load a template and fill in data
• xlsxwriter: Best for Python write-only scenarios requiring maximum performance and feature richness (conditional formatting, sparklines, data validation). Cannot read or modify existing files
• ExcelJS: The standard choice for Node.js/TypeScript projects. Supports read, write, and streaming. Good feature coverage for most business requirements
• Apache POI: The Java ecosystem standard. Use XSSF for full-featured access or SXSSF for streaming large datasets. Heaviest memory footprint but most mature library
• Pandas to_excel: Best when your data is already in DataFrames. Not a standalone Excel library; delegates to openpyxl or xlsxwriter under the hood. Use for quick exports; switch to the underlying library when you need formatting control

Installation and Setup:

# Python: openpyxl (read/write)
pip install openpyxl

# Python: xlsxwriter (write-only, high performance)
pip install xlsxwriter

# Python: Pandas with Excel support
pip install pandas openpyxl  # or pandas xlsxwriter

# Node.js: ExcelJS
npm install exceljs

<!-- Java: Apache POI (Maven) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.5</version>
</dependency>

Choosing Based on Use Case:

Need to read existing XLSX?
  ├─ Yes → openpyxl (Python), ExcelJS (Node), POI (Java)
  └─ No → Write-only?
       ├─ Yes, large dataset → xlsxwriter (Python), SXSSF (Java)
       ├─ Yes, data already in DataFrame → pandas.to_excel
       └─ Yes, Node.js project → ExcelJS

Need to preserve VBA macros?
  ├─ Yes → openpyxl (keep_vba=True), POI
  └─ No → Any library

Need charts + conditional formatting + data validation?
  ├─ Yes → xlsxwriter (Python), POI (Java), ExcelJS (Node)
  └─ Basic formatting only → Any library

Step 2: Python openpyxl Fundamentals

openpyxl is the most versatile Python library for Excel manipulation. It supports reading, writing, and modifying XLSX files with full formatting control.

Workbook and Worksheet Basics:

from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter

# Create a new workbook
wb = Workbook()
ws = wb.active  # Get the default sheet
ws.title = "Sales Report"

# Add additional sheets
ws2 = wb.create_sheet("Summary")
ws3 = wb.create_sheet("Raw Data", 0)  # Insert at position 0

# Write data to cells
ws["A1"] = "Product"
ws["B1"] = "Revenue"
ws["C1"] = "Quarter"

# Write by row and column index (1-based)
ws.cell(row=2, column=1, value="Widget A")
ws.cell(row=2, column=2, value=15000.50)
ws.cell(row=2, column=3, value="Q1 2026")

# Write rows in bulk
data = [
    ["Widget B", 22000.75, "Q1 2026"],
    ["Widget C", 8500.00, "Q1 2026"],
    ["Widget D", 31200.25, "Q1 2026"],
]
for row in data:
    ws.append(row)

# Set column widths
ws.column_dimensions["A"].width = 20
ws.column_dimensions["B"].width = 15
ws.column_dimensions["C"].width = 12

# Set row height
ws.row_dimensions[1].height = 25

# Save the workbook
wb.save("sales_report.xlsx")

Cell Data Types and Number Formats:

from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from datetime import datetime, date
from decimal import Decimal

wb = Workbook()
ws = wb.active

# String values
ws["A1"] = "Revenue Report"

# Numeric values (integers and floats)
ws["A2"] = 42
ws["B2"] = 3.14159
ws["C2"] = Decimal("15000.50")  # Converted to float internally

# Date and datetime values
ws["A3"] = date(2026, 3, 15)
ws["A3"].number_format = "YYYY-MM-DD"

ws["B3"] = datetime(2026, 3, 15, 14, 30, 0)
ws["B3"].number_format = "YYYY-MM-DD HH:MM:SS"

# Currency formatting
ws["A4"] = 15000.50
ws["A4"].number_format = '"$"#,##0.00'

# Percentage formatting
ws["B4"] = 0.1575
ws["B4"].number_format = "0.00%"

# Accounting format (negative in parentheses)
ws["C4"] = -5000.00
ws["C4"].number_format = '_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)'

# Custom number formats
ws["A5"] = 1234567
ws["A5"].number_format = "#,##0"  # Thousands separator

ws["B5"] = 0.5
ws["B5"].number_format = "0.0%"

# Boolean values
ws["A6"] = True  # Displays as TRUE in Excel

wb.save("data_types.xlsx")

Cell Styling (Fonts, Fills, Borders, Alignment):

from openpyxl import Workbook
from openpyxl.styles import (
    Font, PatternFill, Border, Side, Alignment, NamedStyle
)

wb = Workbook()
ws = wb.active

# Font styling
header_font = Font(
    name="Calibri",
    size=14,
    bold=True,
    italic=False,
    color="FFFFFF",  # White text
)

body_font = Font(name="Calibri", size=11, color="333333")

# Fill (background color)
header_fill = PatternFill(
    start_color="2F5496",  # Dark blue
    end_color="2F5496",
    fill_type="solid",
)

alternating_fill = PatternFill(
    start_color="D6E4F0",  # Light blue
    end_color="D6E4F0",
    fill_type="solid",
)

# Borders
thin_border = Border(
    left=Side(style="thin", color="999999"),
    right=Side(style="thin", color="999999"),
    top=Side(style="thin", color="999999"),
    bottom=Side(style="thin", color="999999"),
)

header_border = Border(
    bottom=Side(style="medium", color="2F5496"),
)

# Alignment
center_align = Alignment(
    horizontal="center",
    vertical="center",
    wrap_text=True,
)

# Apply styles to header row
headers = ["Product", "Revenue", "Cost", "Profit", "Margin"]
for col_idx, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col_idx, value=header)
    cell.font = header_font
    cell.fill = header_fill
    cell.border = thin_border
    cell.alignment = center_align

# Apply alternating row colors
data_rows = [
    ["Widget A", 15000, 8000, 7000, 0.4667],
    ["Widget B", 22000, 12000, 10000, 0.4545],
    ["Widget C", 8500, 5000, 3500, 0.4118],
    ["Widget D", 31200, 18000, 13200, 0.4231],
]

for row_idx, row_data in enumerate(data_rows, 2):
    for col_idx, value in enumerate(row_data, 1):
        cell = ws.cell(row=row_idx, column=col_idx, value=value)
        cell.font = body_font
        cell.border = thin_border
        if row_idx % 2 == 0:
            cell.fill = alternating_fill
        # Format currency columns
        if col_idx in (2, 3, 4):
            cell.number_format = '"$"#,##0'
        # Format percentage column
        if col_idx == 5:
            cell.number_format = "0.0%"

wb.save("styled_report.xlsx")

Named Styles for Reuse:

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

wb = Workbook()

# Define reusable named styles
header_style = NamedStyle(name="header_style")
header_style.font = Font(bold=True, size=12, color="FFFFFF")
header_style.fill = PatternFill(start_color="2F5496", fill_type="solid")
header_style.alignment = Alignment(horizontal="center", vertical="center")
header_style.border = Border(
    bottom=Side(style="medium", color="1F3864")
)
wb.add_named_style(header_style)

currency_style = NamedStyle(name="currency_style")
currency_style.number_format = '"$"#,##0.00'
currency_style.font = Font(size=11)
currency_style.alignment = Alignment(horizontal="right")
wb.add_named_style(currency_style)

# Apply named styles by name
ws = wb.active
ws["A1"].style = "header_style"
ws["A1"].value = "Amount"
ws["A2"].style = "currency_style"
ws["A2"].value = 15000.50

wb.save("named_styles.xlsx")

Merged Cells:

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

wb = Workbook()
ws = wb.active

# Merge cells for a title row
ws.merge_cells("A1:E1")
ws["A1"] = "Quarterly Sales Report - Q1 2026"
ws["A1"].font = Font(size=16, bold=True)
ws["A1"].alignment = Alignment(horizontal="center")

# Merge cells for section headers
ws.merge_cells("A3:B3")
ws["A3"] = "Product Details"
ws["A3"].font = Font(bold=True)

ws.merge_cells("C3:E3")
ws["C3"] = "Financial Metrics"
ws["C3"].font = Font(bold=True)

# Unmerge if needed
# ws.unmerge_cells("A1:E1")

wb.save("merged_cells.xlsx")

Loading and Modifying Existing Files:

from openpyxl import load_workbook

# Load an existing workbook
wb = load_workbook("template.xlsx")
ws = wb.active

# Read cell values
value = ws["A1"].value
print(f"Cell A1: {value}")

# Iterate over rows
for row in ws.iter_rows(min_row=2, max_col=5, values_only=True):
    product, revenue, cost, profit, margin = row
    print(f"{product}: ${revenue}")

# Modify cells
ws["F1"] = "Status"
for row_idx in range(2, ws.max_row + 1):
    profit = ws.cell(row=row_idx, column=4).value
    if profit and profit > 10000:
        ws.cell(row=row_idx, column=6, value="High")
    else:
        ws.cell(row=row_idx, column=6, value="Standard")

# Load with data_only to get calculated values instead of formulas
wb_values = load_workbook("report_with_formulas.xlsx", data_only=True)
# Note: data_only returns the cached value from the last Excel save,
# not a recalculated value. If the file was never opened in Excel,
# formula cells will return None.

wb.save("template_updated.xlsx")

Step 3: Python xlsxwriter

xlsxwriter is a write-only library optimized for performance and feature richness. It produces XLSX files without needing to read existing ones, making it ideal for report generation pipelines.

Basic Setup and Formatting:

import xlsxwriter

wb = xlsxwriter.Workbook("report.xlsx")
ws = wb.add_worksheet("Sales")

# Define reusable formats
header_fmt = wb.add_format({
    "bold": True,
    "font_size": 12,
    "font_color": "#FFFFFF",
    "bg_color": "#2F5496",
    "border": 1,
    "align": "center",
    "valign": "vcenter",
    "text_wrap": True,
})

currency_fmt = wb.add_format({
    "num_format": "$#,##0.00",
    "font_size": 11,
    "border": 1,
})

percent_fmt = wb.add_format({
    "num_format": "0.0%",
    "font_size": 11,
    "border": 1,
})

date_fmt = wb.add_format({
    "num_format": "yyyy-mm-dd",
    "border": 1,
})

# Write header row
headers = ["Product", "Revenue", "Cost", "Profit", "Margin", "Date"]
for col, header in enumerate(headers):
    ws.write(0, col, header, header_fmt)

# Write data rows
data = [
    ["Widget A", 15000.50, 8000, 7000.50, 0.4667, "2026-03-15"],
    ["Widget B", 22000.75, 12000, 10000.75, 0.4545, "2026-03-16"],
    ["Widget C", 8500.00, 5000, 3500.00, 0.4118, "2026-03-17"],
]

for row_idx, row_data in enumerate(data, 1):
    ws.write(row_idx, 0, row_data[0])             # String
    ws.write(row_idx, 1, row_data[1], currency_fmt) # Currency
    ws.write(row_idx, 2, row_data[2], currency_fmt) # Currency
    ws.write(row_idx, 3, row_data[3], currency_fmt) # Currency
    ws.write(row_idx, 4, row_data[4], percent_fmt)  # Percentage
    ws.write(row_idx, 5, row_data[5], date_fmt)     # Date

# Set column widths
ws.set_column("A:A", 20)
ws.set_column("B:D", 15)
ws.set_column("E:E", 10)
ws.set_column("F:F", 14)

# Set row heights
ws.set_row(0, 25)

wb.close()  # xlsxwriter uses close(), not save()

Conditional Formatting:

import xlsxwriter

wb = xlsxwriter.Workbook("conditional.xlsx")
ws = wb.add_worksheet()

# Write sample data
headers = ["Product", "Revenue", "Target", "Variance"]
for col, h in enumerate(headers):
    ws.write(0, col, h)

data = [
    ["Widget A", 15000, 12000, 3000],
    ["Widget B", 8000, 12000, -4000],
    ["Widget C", 22000, 12000, 10000],
    ["Widget D", 11000, 12000, -1000],
]
for r, row in enumerate(data, 1):
    for c, val in enumerate(row):
        ws.write(r, c, val)

# Color scale: green (high) to red (low) on revenue column
ws.conditional_format("B2:B5", {
    "type": "3_color_scale",
    "min_color": "#F8696B",   # Red
    "mid_color": "#FFEB84",   # Yellow
    "max_color": "#63BE7B",   # Green
})

# Data bar on revenue column
ws.conditional_format("B2:B5", {
    "type": "data_bar",
    "bar_color": "#2F5496",
})

# Icon set on variance column
ws.conditional_format("D2:D5", {
    "type": "icon_set",
    "icon_style": "3_traffic_lights",
    "icons": [
        {"criteria": ">=", "type": "number", "value": 5000},
        {"criteria": ">=", "type": "number", "value": 0},
        {"criteria": "<",  "type": "number", "value": 0},
    ],
})

# Cell-based conditional format: highlight negative variance in red
red_fmt = wb.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})
ws.conditional_format("D2:D5", {
    "type": "cell",
    "criteria": "<",
    "value": 0,
    "format": red_fmt,
})

# Highlight cells above average
green_fmt = wb.add_format({"bg_color": "#C6EFCE", "font_color": "#006100"})
ws.conditional_format("B2:B5", {
    "type": "average",
    "criteria": "above",
    "format": green_fmt,
})

# Formula-based: highlight entire row where variance is negative
row_red_fmt = wb.add_format({"bg_color": "#FFC7CE"})
ws.conditional_format("A2:D5", {
    "type": "formula",
    "criteria": "=$D2<0",
    "format": row_red_fmt,
})

wb.close()

Data Validation:

import xlsxwriter

wb = xlsxwriter.Workbook("validation.xlsx")
ws = wb.add_worksheet()

# Dropdown list validation
ws.write("A1", "Status")
ws.data_validation("A2:A100", {
    "validate": "list",
    "source": ["Active", "Inactive", "Pending", "Archived"],
    "input_title": "Select Status",
    "input_message": "Choose a status from the dropdown.",
    "error_title": "Invalid Status",
    "error_message": "Please select a valid status from the list.",
})

# Numeric range validation
ws.write("B1", "Quantity")
ws.data_validation("B2:B100", {
    "validate": "integer",
    "criteria": "between",
    "minimum": 1,
    "maximum": 10000,
    "input_title": "Enter Quantity",
    "input_message": "Quantity must be between 1 and 10,000.",
    "error_type": "stop",
})

# Date range validation
ws.write("C1", "Due Date")
ws.data_validation("C2:C100", {
    "validate": "date",
    "criteria": ">=",
    "value": "2026-01-01",
    "input_title": "Enter Date",
    "input_message": "Date must be on or after 2026-01-01.",
})

# Custom formula validation (value must be unique in column)
ws.write("D1", "Code")
ws.data_validation("D2:D100", {
    "validate": "custom",
    "value": "=COUNTIF($D:$D,D2)<=1",
    "input_title": "Unique Code",
    "input_message": "Enter a unique product code.",
    "error_title": "Duplicate",
    "error_message": "This code already exists in the column.",
})

wb.close()

Sparklines:

import xlsxwriter

wb = xlsxwriter.Workbook("sparklines.xlsx")
ws = wb.add_worksheet()

# Monthly revenue data
ws.write_row("A1", ["Product", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Trend"])
ws.write_row("A2", ["Widget A", 100, 120, 115, 140, 155, 170])
ws.write_row("A3", ["Widget B", 200, 190, 210, 195, 220, 240])
ws.write_row("A4", ["Widget C", 50, 60, 55, 70, 65, 80])

# Add sparklines in the Trend column
ws.add_sparkline("H2", {
    "range": "B2:G2",
    "type": "line",
    "markers": True,
    "high_point": True,
    "low_point": True,
})

ws.add_sparkline("H3", {
    "range": "B3:G3",
    "type": "column",
    "high_point": True,
})

ws.add_sparkline("H4", {
    "range": "B4:G4",
    "type": "win_loss",
})

ws.set_column("H:H", 20)
wb.close()

Memory-Optimized Writing for Large Datasets:

import xlsxwriter

# Enable constant_memory mode for large datasets
# Rows are flushed to disk and cannot be revisited
wb = xlsxwriter.Workbook("large_dataset.xlsx", {"constant_memory": True})
ws = wb.add_worksheet()

header_fmt = wb.add_format({"bold": True, "bg_color": "#2F5496", "font_color": "#FFFFFF"})

headers = ["ID", "Name", "Value", "Category", "Timestamp"]
for col, h in enumerate(headers):
    ws.write(0, col, h, header_fmt)

# Write 1 million rows efficiently
for row in range(1, 1_000_001):
    ws.write_number(row, 0, row)
    ws.write_string(row, 1, f"Item {row}")
    ws.write_number(row, 2, row * 1.5)
    ws.write_string(row, 3, f"Cat-{row % 10}")
    ws.write_string(row, 4, "2026-03-15T10:30:00")

wb.close()

Step 4: JavaScript ExcelJS

ExcelJS is the standard library for Excel generation in Node.js and TypeScript projects. It supports reading, writing, and streaming with comprehensive formatting options.

Basic Workbook Creation:

const ExcelJS = require("exceljs");

async function createReport() {
  const wb = new ExcelJS.Workbook();
  wb.creator = "Report Generator";
  wb.created = new Date();

  const ws = wb.addWorksheet("Sales Report", {
    properties: { tabColor: { argb: "2F5496" } },
    pageSetup: {
      paperSize: 9, // A4
      orientation: "landscape",
      fitToPage: true,
    },
  });

  // Define columns with headers, keys, and widths
  ws.columns = [
    { header: "Product", key: "product", width: 25 },
    { header: "Revenue", key: "revenue", width: 15, style: { numFmt: "$#,##0.00" } },
    { header: "Cost", key: "cost", width: 15, style: { numFmt: "$#,##0.00" } },
    { header: "Profit", key: "profit", width: 15, style: { numFmt: "$#,##0.00" } },
    { header: "Margin", key: "margin", width: 12, style: { numFmt: "0.0%" } },
  ];

  // Add rows using key-value objects
  ws.addRow({ product: "Widget A", revenue: 15000.50, cost: 8000, profit: 7000.50, margin: 0.4667 });
  ws.addRow({ product: "Widget B", revenue: 22000.75, cost: 12000, profit: 10000.75, margin: 0.4545 });
  ws.addRow({ product: "Widget C", revenue: 8500.00, cost: 5000, profit: 3500.00, margin: 0.4118 });

  // Style the header row
  const headerRow = ws.getRow(1);
  headerRow.eachCell((cell) => {
    cell.font = { bold: true, size: 12, color: { argb: "FFFFFFFF" } };
    cell.fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FF2F5496" },
    };
    cell.alignment = { horizontal: "center", vertical: "middle" };
    cell.border = {
      bottom: { style: "medium", color: { argb: "FF1F3864" } },
    };
  });
  headerRow.height = 25;

  await wb.xlsx.writeFile("sales_report.xlsx");
}

createReport();

Cell Styling and Rich Text:

const ExcelJS = require("exceljs");

async function styledWorkbook() {
  const wb = new ExcelJS.Workbook();
  const ws = wb.addWorksheet("Styled");

  // Rich text in a single cell
  ws.getCell("A1").value = {
    richText: [
      { font: { bold: true, size: 14, color: { argb: "FF2F5496" } }, text: "Q1 2026 " },
      { font: { italic: true, size: 14, color: { argb: "FF666666" } }, text: "Sales Report" },
    ],
  };

  // Conditional fill based on value
  const data = [
    { name: "Widget A", value: 15000 },
    { name: "Widget B", value: -3000 },
    { name: "Widget C", value: 22000 },
  ];

  data.forEach((item, idx) => {
    const row = idx + 3;
    ws.getCell(`A${row}`).value = item.name;
    const valueCell = ws.getCell(`B${row}`);
    valueCell.value = item.value;
    valueCell.numFmt = "$#,##0.00";

    if (item.value < 0) {
      valueCell.font = { color: { argb: "FF9C0006" } };
      valueCell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "FFFFC7CE" },
      };
    } else {
      valueCell.font = { color: { argb: "FF006100" } };
      valueCell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "FFC6EFCE" },
      };
    }
  });

  // Data validation dropdown
  ws.getCell("C3").dataValidation = {
    type: "list",
    allowBlank: true,
    formulae: ['"Active,Inactive,Pending"'],
    showErrorMessage: true,
    errorTitle: "Invalid",
    error: "Select a valid status.",
  };

  await wb.xlsx.writeFile("styled.xlsx");
}

styledWorkbook();

Streaming Writes for Large Datasets:

const ExcelJS = require("exceljs");
const fs = require("fs");

async function streamLargeDataset() {
  const options = {
    filename: "large_dataset.xlsx",
    useStyles: true,
    useSharedStrings: false, // Disable for better performance
  };

  const wb = new ExcelJS.stream.xlsx.WorkbookWriter(options);
  const ws = wb.addWorksheet("Data");

  // Define columns
  ws.columns = [
    { header: "ID", key: "id", width: 10 },
    { header: "Name", key: "name", width: 25 },
    { header: "Value", key: "value", width: 15 },
    { header: "Category", key: "category", width: 15 },
  ];

  // Stream 500,000 rows without holding them all in memory
  for (let i = 1; i <= 500_000; i++) {
    ws.addRow({
      id: i,
      name: `Item ${i}`,
      value: Math.round(Math.random() * 10000) / 100,
      category: `Cat-${i % 10}`,
    }).commit(); // Flush row to disk immediately
  }

  ws.commit();
  await wb.commit();
}

streamLargeDataset();

Adding Images:

const ExcelJS = require("exceljs");

async function addImages() {
  const wb = new ExcelJS.Workbook();
  const ws = wb.addWorksheet("With Image");

  // Add image from file
  const logoId = wb.addImage({
    filename: "logo.png",
    extension: "png",
  });

  // Position image over a cell range
  ws.addImage(logoId, {
    tl: { col: 0, row: 0 },       // Top-left anchor
    br: { col: 3, row: 4 },       // Bottom-right anchor
    editAs: "oneCell",             // Resize behavior
  });

  // Add image from buffer
  const imageBuffer = fs.readFileSync("chart_screenshot.png");
  const chartId = wb.addImage({
    buffer: imageBuffer,
    extension: "png",
  });

  ws.addImage(chartId, "E1:K15");  // Shorthand range notation

  await wb.xlsx.writeFile("with_images.xlsx");
}

Step 5: Formulas and Calculations

All major XLSX libraries support embedding Excel formulas in cells. Formulas are stored as text and evaluated by Excel when the file is opened.

Cell Formulas in openpyxl:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Simple formulas
ws["A1"] = "Revenue"
ws["A2"] = 15000
ws["A3"] = 22000
ws["A4"] = 8500
ws["A5"] = "=SUM(A2:A4)"            # Sum
ws["A6"] = "=AVERAGE(A2:A4)"        # Average
ws["A7"] = "=MAX(A2:A4)"            # Maximum
ws["A8"] = '=IF(A5>40000,"High","Low")'  # Conditional

# Cross-cell references
ws["B2"] = 8000   # Cost for row 2
ws["C2"] = "=A2-B2"  # Profit = Revenue - Cost
ws["D2"] = "=C2/A2"  # Margin = Profit / Revenue

# Fill formulas down a range
for row in range(2, 5):
    ws.cell(row=row, column=3).value = f"=A{row}-B{row}"
    ws.cell(row=row, column=4).value = f"=C{row}/A{row}"

# VLOOKUP and INDEX/MATCH
ws2 = wb.create_sheet("Lookup")
ws2["A1"] = "Product"
ws2["B1"] = "Category"
ws2["A2"] = "Widget A"
ws2["B2"] = "Electronics"
ws2["A3"] = "Widget B"
ws2["B3"] = "Hardware"

# Reference lookup from main sheet
ws["E2"] = "=VLOOKUP(A2,Lookup!A:B,2,FALSE)"
# Modern alternative: XLOOKUP (Excel 365+)
ws["F2"] = "=XLOOKUP(A2,Lookup!A:A,Lookup!B:B)"

wb.save("formulas.xlsx")

Named Ranges:

from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName

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

# Write data
ws["A1"] = "Revenue"
for row, val in enumerate([15000, 22000, 8500, 31200], 2):
    ws.cell(row=row, column=1, value=val)

# Create a named range
revenue_range = DefinedName("RevenueData", attr_text="Data!$A$2:$A$5")
wb.defined_names.add(revenue_range)

# Use the named range in formulas
ws["C1"] = "Total Revenue"
ws["C2"] = "=SUM(RevenueData)"
ws["C3"] = "Average Revenue"
ws["C4"] = "=AVERAGE(RevenueData)"

# Named range scoped to a specific sheet
local_range = DefinedName(
    "LocalTotal",
    attr_text="Data!$C$2",
    localSheetId=0,  # Sheet index
)
wb.defined_names.add(local_range)

# Print area as a named range
ws.print_area = "A1:C10"

# Print titles (repeat rows at top of each printed page)
ws.print_title_rows = "1:1"

wb.save("named_ranges.xlsx")

Array Formulas and Dynamic Arrays:

from openpyxl import Workbook
from openpyxl.worksheet.formula import ArrayFormula

wb = Workbook()
ws = wb.active

# Data
ws["A1"] = "Price"
ws["B1"] = "Quantity"
ws["C1"] = "Total"
for row, (price, qty) in enumerate([(10, 5), (20, 3), (15, 8)], 2):
    ws.cell(row=row, column=1, value=price)
    ws.cell(row=row, column=2, value=qty)

# Legacy CSE array formula (Ctrl+Shift+Enter)
# Computes sum of element-wise multiplication
ws["D1"] = "Sum of Products"
ws["D2"] = ArrayFormula("D2", "=SUM(A2:A4*B2:B4)")

# Individual cell formulas (non-array, but referencing ranges)
ws["C2"] = "=A2*B2"
ws["C3"] = "=A3*B3"
ws["C4"] = "=A4*B4"

# Dynamic array formulas (Excel 365+, spill into adjacent cells)
# These work when opened in Excel; the library writes the formula to the anchor cell
ws2 = wb.create_sheet("Dynamic")
ws2["A1"] = "=SORT(Data!A2:A4)"            # Spills sorted values
ws2["C1"] = "=UNIQUE(Data!A2:A10)"          # Spills unique values
ws2["E1"] = "=FILTER(Data!A2:C4,Data!C2:C4>50)"  # Filtered results

wb.save("array_formulas.xlsx")

Cross-Sheet References:

from openpyxl import Workbook

wb = Workbook()

# Create multiple sheets with data
regions = {
    "North": [10000, 12000, 15000],
    "South": [8000, 9000, 11000],
    "East": [14000, 13000, 16000],
    "West": [7000, 8500, 9500],
}

for region, values in regions.items():
    ws = wb.create_sheet(region)
    ws["A1"] = "Q1"
    ws["B1"] = "Q2"
    ws["C1"] = "Q3"
    for col, val in enumerate(values, 1):
        ws.cell(row=2, column=col, value=val)
    ws["D1"] = "Total"
    ws["D2"] = "=SUM(A2:C2)"

# Summary sheet with cross-sheet references
summary = wb.create_sheet("Summary", 0)
summary["A1"] = "Region"
summary["B1"] = "Q1"
summary["C1"] = "Q2"
summary["D1"] = "Q3"
summary["E1"] = "Total"

for row, region in enumerate(regions.keys(), 2):
    summary.cell(row=row, column=1, value=region)
    summary.cell(row=row, column=2).value = f"='{region}'!A2"
    summary.cell(row=row, column=3).value = f"='{region}'!B2"
    summary.cell(row=row, column=4).value = f"='{region}'!C2"
    summary.cell(row=row, column=5).value = f"='{region}'!D2"

# Grand total row
total_row = len(regions) + 2
summary.cell(row=total_row, column=1, value="Grand Total")
for col in range(2, 6):
    col_letter = chr(64 + col)  # B, C, D, E
    summary.cell(row=total_row, column=col).value = (
        f"=SUM({col_letter}2:{col_letter}{total_row - 1})"
    )

# Remove the default sheet
del wb["Sheet"]

wb.save("cross_sheet.xlsx")

Formula Auditing and Validation:

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import re

def audit_formulas(filepath: str) -> list[dict]:
    """Scan a workbook and report all formula cells with their references."""
    wb = load_workbook(filepath)
    findings = []

    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and cell.value.startswith("="):
                    formula = cell.value
                    # Extract cell references from the formula
                    refs = re.findall(
                        r"(?:'[^']+'\!)?\$?[A-Z]{1,3}\$?\d+(?::\$?[A-Z]{1,3}\$?\d+)?",
                        formula,
                    )
                    findings.append({
                        "sheet": ws.title,
                        "cell": cell.coordinate,
                        "formula": formula,
                        "references": refs,
                    })

    return findings

# Usage
# results = audit_formulas("complex_report.xlsx")
# for f in results:
#     print(f"{f['sheet']}!{f['cell']}: {f['formula']} -> refs: {f['references']}")

Step 6: Charts and Visualization

Both openpyxl and xlsxwriter support creating Excel-native charts. Charts are embedded in the worksheet and update dynamically when the underlying data changes.

openpyxl Charts:

from openpyxl import Workbook
from openpyxl.chart import (
    BarChart, LineChart, PieChart, ScatterChart, Reference
)
from openpyxl.chart.series import SeriesLabel
from openpyxl.chart.label import DataLabelList
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Chart Data"

# Write data
headers = ["Month", "Revenue", "Cost", "Profit"]
data = [
    ["Jan", 15000, 8000, 7000],
    ["Feb", 18000, 9500, 8500],
    ["Mar", 22000, 11000, 11000],
    ["Apr", 19000, 10000, 9000],
    ["May", 25000, 12500, 12500],
    ["Jun", 28000, 14000, 14000],
]

ws.append(headers)
for row in data:
    ws.append(row)

# --- Bar Chart ---
bar_chart = BarChart()
bar_chart.type = "col"  # "col" for vertical, "bar" for horizontal
bar_chart.title = "Monthly Revenue and Cost"
bar_chart.x_axis.title = "Month"
bar_chart.y_axis.title = "Amount ($)"
bar_chart.style = 10
bar_chart.width = 20
bar_chart.height = 12

# Data references (min_col/max_col are 1-based)
categories = Reference(ws, min_col=1, min_row=2, max_row=7)  # Month labels
revenue_data = Reference(ws, min_col=2, min_row=1, max_row=7)  # Include header
cost_data = Reference(ws, min_col=3, min_row=1, max_row=7)

bar_chart.add_data(revenue_data, titles_from_data=True)
bar_chart.add_data(cost_data, titles_from_data=True)
bar_chart.set_categories(categories)

# Customize series colors
bar_chart.series[0].graphicalProperties.solidFill = "2F5496"  # Blue
bar_chart.series[1].graphicalProperties.solidFill = "C00000"  # Red

ws.add_chart(bar_chart, "F2")

# --- Line Chart ---
line_chart = LineChart()
line_chart.title = "Profit Trend"
line_chart.x_axis.title = "Month"
line_chart.y_axis.title = "Profit ($)"
line_chart.style = 10
line_chart.width = 20
line_chart.height = 12

profit_data = Reference(ws, min_col=4, min_row=1, max_row=7)
line_chart.add_data(profit_data, titles_from_data=True)
line_chart.set_categories(categories)

# Add data labels
line_chart.series[0].graphicalProperties.line.width = 25000  # EMUs
line_chart.series[0].dLbls = DataLabelList()
line_chart.series[0].dLbls.showVal = True

ws.add_chart(line_chart, "F18")

# --- Pie Chart ---
pie_ws = wb.create_sheet("Pie Chart")
pie_ws["A1"] = "Category"
pie_ws["B1"] = "Amount"
pie_data_rows = [
    ["Electronics", 45000],
    ["Hardware", 30000],
    ["Software", 25000],
    ["Services", 15000],
]
for row in pie_data_rows:
    pie_ws.append(row)

pie_chart = PieChart()
pie_chart.title = "Revenue by Category"
pie_chart.width = 18
pie_chart.height = 14

pie_labels = Reference(pie_ws, min_col=1, min_row=2, max_row=5)
pie_values = Reference(pie_ws, min_col=2, min_row=1, max_row=5)
pie_chart.add_data(pie_values, titles_from_data=True)
pie_chart.set_categories(pie_labels)

# Show percentage labels
pie_chart.series[0].dLbls = DataLabelList()
pie_chart.series[0].dLbls.showPercent = True
pie_chart.series[0].dLbls.showCatName = True
pie_chart.series[0].dLbls.showVal = False

pie_ws.add_chart(pie_chart, "D2")

# --- Scatter Chart ---
scatter_ws = wb.create_sheet("Scatter")
scatter_ws.append(["Ad Spend", "Revenue"])
scatter_data_rows = [
    [1000, 12000], [2000, 18000], [3000, 22000],
    [4000, 28000], [5000, 32000], [6000, 35000],
]
for row in scatter_data_rows:
    scatter_ws.append(row)

scatter_chart = ScatterChart()
scatter_chart.title = "Ad Spend vs Revenue"
scatter_chart.x_axis.title = "Ad Spend ($)"
scatter_chart.y_axis.title = "Revenue ($)"
scatter_chart.width = 18
scatter_chart.height = 14

x_values = Reference(scatter_ws, min_col=1, min_row=2, max_row=7)
y_values = Reference(scatter_ws, min_col=2, min_row=2, max_row=7)
series = scatter_chart.series
from openpyxl.chart import Series
s = Series(y_values, x_values, title="Revenue")
scatter_chart.series.append(s)

# Add trendline
from openpyxl.chart.trendline import Trendline
s.trendline = Trendline(trendlineType="linear", dispRSqr=True, dispEq=True)

scatter_ws.add_chart(scatter_chart, "D2")

wb.save("charts.xlsx")

xlsxwriter Charts:

import xlsxwriter

wb = xlsxwriter.Workbook("xlsxwriter_charts.xlsx")
ws = wb.add_worksheet("Data")

# Write data
headers = ["Month", "Revenue", "Cost", "Profit"]
data = [
    ["Jan", 15000, 8000, 7000],
    ["Feb", 18000, 9500, 8500],
    ["Mar", 22000, 11000, 11000],
    ["Apr", 19000, 10000, 9000],
    ["May", 25000, 12500, 12500],
    ["Jun", 28000, 14000, 14000],
]

bold = wb.add_format({"bold": True})
for col, h in enumerate(headers):
    ws.write(0, col, h, bold)
for r, row in enumerate(data, 1):
    for c, val in enumerate(row):
        ws.write(r, c, val)

# --- Clustered Bar Chart ---
bar_chart = wb.add_chart({"type": "column"})

bar_chart.add_series({
    "name": "=Data!$B$1",
    "categories": "=Data!$A$2:$A$7",
    "values": "=Data!$B$2:$B$7",
    "fill": {"color": "#2F5496"},
    "gap": 150,
})
bar_chart.add_series({
    "name": "=Data!$C$1",
    "values": "=Data!$C$2:$C$7",
    "fill": {"color": "#C00000"},
})

bar_chart.set_title({"name": "Monthly Revenue and Cost"})
bar_chart.set_x_axis({"name": "Month"})
bar_chart.set_y_axis({"name": "Amount ($)", "num_format": "$#,##0"})
bar_chart.set_size({"width": 720, "height": 400})
bar_chart.set_legend({"position": "bottom"})

ws.insert_chart("F2", bar_chart)

# --- Combo Chart (Bar + Line) ---
combo_chart = wb.add_chart({"type": "column"})

combo_chart.add_series({
    "name": "Revenue",
    "categories": "=Data!$A$2:$A$7",
    "values": "=Data!$B$2:$B$7",
    "fill": {"color": "#2F5496"},
})

# Add a line series on a secondary axis
line_series = wb.add_chart({"type": "line"})
line_series.add_series({
    "name": "Profit",
    "categories": "=Data!$A$2:$A$7",
    "values": "=Data!$D$2:$D$7",
    "line": {"color": "#00B050", "width": 2.5},
    "marker": {"type": "circle", "size": 6},
    "y2_axis": True,
})

combo_chart.combine(line_series)
combo_chart.set_title({"name": "Revenue (Bars) vs Profit (Line)"})
combo_chart.set_y_axis({"name": "Revenue ($)"})
combo_chart.set_y2_axis({"name": "Profit ($)"})
combo_chart.set_size({"width": 720, "height": 400})

ws.insert_chart("F22", combo_chart)

# --- Stacked Area Chart ---
area_chart = wb.add_chart({"type": "area", "subtype": "stacked"})
area_chart.add_series({
    "name": "Cost",
    "categories": "=Data!$A$2:$A$7",
    "values": "=Data!$C$2:$C$7",
    "fill": {"color": "#FFC000"},
})
area_chart.add_series({
    "name": "Profit",
    "values": "=Data!$D$2:$D$7",
    "fill": {"color": "#00B050"},
})
area_chart.set_title({"name": "Revenue Composition"})
area_chart.set_size({"width": 720, "height": 400})

ws.insert_chart("F42", area_chart)

wb.close()

Data-Driven Chart Generation Helper:

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


def add_chart_from_data(
    wb: Workbook,
    sheet_name: str,
    chart_type: str,
    title: str,
    data_range: tuple[int, int, int, int],
    category_col: int,
    anchor_cell: str = "A1",
    width: int = 18,
    height: int = 12,
) -> None:
    """Add a chart to a worksheet from a data range.

    Args:
        wb: The workbook containing the data.
        sheet_name: Name of the sheet with data.
        chart_type: One of "bar", "line", "pie".
        title: Chart title.
        data_range: Tuple of (min_col, min_row, max_col, max_row) for data series.
        category_col: Column number for category labels.
        anchor_cell: Cell where the chart top-left corner is placed.
        width: Chart width in cm.
        height: Chart height in cm.
    """
    ws = wb[sheet_name]
    min_col, min_row, max_col, max_row = data_range

    chart_classes = {
        "bar": BarChart,
        "line": LineChart,
        "pie": PieChart,
    }

    chart_cls = chart_classes.get(chart_type)
    if chart_cls is None:
        raise ValueError(f"Unsupported chart type: {chart_type}. Use: {list(chart_classes.keys())}")

    chart = chart_cls()
    chart.title = title
    chart.width = width
    chart.height = height

    data_ref = Reference(ws, min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row)
    categories = Reference(ws, min_col=category_col, min_row=min_row + 1, max_row=max_row)

    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(categories)

    target_ws = ws
    target_ws.add_chart(chart, anchor_cell)


# Usage:
# wb = Workbook()
# ws = wb.active
# ws.append(["Month", "Revenue", "Cost"])
# for row in [["Jan", 15000, 8000], ["Feb", 18000, 9500]]:
#     ws.append(row)
# add_chart_from_data(wb, ws.title, "bar", "Revenue vs Cost", (2, 1, 3, 3), 1, "E2")
# wb.save("dynamic_chart.xlsx")

Step 7: Advanced Features

Production Excel files often require features beyond basic data and formatting: autofilters for interactive exploration, freeze panes for navigation, print configuration for physical output,

…(truncated)