XLSX

XLSX is an AI skill that creates, edits, and analyzes Excel spreadsheet files programmatically. It handles workbook generation with formatted cells, formulas, charts, pivot table data, and conditional formatting, enabling automated spreadsheet workflows for financial reports, data exports, and business analytics without manual editing.

What Is This?

Overview

XLSX provides full programmatic control over Excel files. It creates workbooks with multiple sheets, writes data with cell-level formatting including fonts, borders, and number formats, inserts Excel formulas that calculate when opened, generates charts from data ranges, applies conditional formatting rules, and handles large datasets efficiently through streaming write modes. The skill manages OpenXML spreadsheet complexities so developers focus on data and presentation logic.

Who Should Use This

This skill serves developers building automated financial reporting systems, data engineers creating formatted data exports for business users, analysts generating repeatable spreadsheet deliverables from database queries, and operations teams that need scheduled spreadsheet generation for stakeholders who work exclusively in Excel.

Why Use It?

Problems It Solves

Manually creating formatted spreadsheets from data is tedious and introduces human error in formulas and formatting. Recurring reports require rebuilding the same structure each cycle. CSV exports lose all formatting, formulas, and multiple sheet organization that business users expect. Teams waste significant time reformatting data exports to match stakeholder presentation requirements.

Core Highlights

The skill generates spreadsheets with professional formatting that includes number formats, conditional formatting, and chart visualizations. It supports formula insertion that Excel evaluates upon opening, handles datasets with hundreds of thousands of rows through streaming modes, and preserves formatting when modifying existing workbooks. Output files are fully compatible with Excel, Google Sheets, and LibreOffice Calc.

How to Use It?

Basic Usage

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

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

headers = ["Product", "Units Sold", "Revenue", "Margin %"]
header_fill = PatternFill(start_color="2563EB", fill_type="solid")
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(color="FFFFFF", bold=True, size=11)
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

data = [("Widget A", 1250, 45000, 0.32), ("Widget B", 890, 31150, 0.28)]
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        cell = ws.cell(row=row_idx, column=col_idx, value=value)
        if col_idx == 3: cell.number_format = '$#,##0'
        if col_idx == 4: cell.number_format = '0.0%'

wb.save("sales_report.xlsx")

Real-World Examples

from openpyxl.formatting.rule import CellIsRule

ws["B5"] = "=SUM(B2:B4)"
ws["C5"] = "=SUM(C2:C4)"
ws["D5"] = "=AVERAGE(D2:D4)"
ws["A5"] = "Total"
ws["A5"].font = Font(bold=True)

red_fill = PatternFill(start_color="FEE2E2", fill_type="solid")
green_fill = PatternFill(start_color="DCFCE7", fill_type="solid")
ws.conditional_formatting.add("D2:D4",
    CellIsRule(operator="lessThan", formula=["0.3"], fill=red_fill))
ws.conditional_formatting.add("D2:D4",
    CellIsRule(operator="greaterThanOrEqual", formula=["0.3"], fill=green_fill))

for col in range(1, 5):
    ws.column_dimensions[get_column_letter(col)].width = 15

wb.save("sales_report.xlsx")

Advanced Tips

Use openpyxl's write-only mode for datasets exceeding 100,000 rows to avoid memory issues. Create named styles for reusable formatting definitions that keep your code clean. When generating reports from templates, load the template workbook and modify data cells while preserving existing charts and formatting.

When to Use It?

Use Cases

Use XLSX when building automated financial reporting that outputs formatted Excel files, when creating data exports that include formulas and conditional formatting, when generating multi-sheet workbooks from database queries, or when producing spreadsheet deliverables for stakeholders who require Excel format.

Related Topics

Openpyxl and XlsxWriter libraries, pandas DataFrame to Excel export, Excel formula reference, spreadsheet template design, data validation rules, and CSV to Excel conversion all complement the XLSX automation workflow.

Important Notes

Requirements

The openpyxl library handles both reading and writing Excel files. XlsxWriter is an alternative for write-only scenarios with better chart support. Python 3.7 or later is recommended.

Usage Recommendations

Do: use number formats on cells to ensure proper display of currencies, percentages, and dates. Test generated files in the target Excel version to verify formula compatibility. Use streaming write mode for large datasets to keep memory usage manageable.

Don't: store dates as strings when Excel date formats provide proper sorting and calculation support. Ignore cell data types, as writing numbers as strings prevents Excel formulas from operating on them correctly. Assume all Excel features are supported by openpyxl, as pivot tables and VBA macros require specialized handling.

Limitations

Openpyxl does not support creating pivot tables from scratch, though existing pivot tables in templates are preserved. VBA macros require the .xlsm format and specialized libraries. Very large files with complex formatting may have slow generation times. Chart types and formatting options are more limited programmatically than through Excel's native editor.