Spreadsheet

Automate spreadsheet data management and integrate complex workbook operations into automated workflows

Category: productivity Source: openai/skills

Spreadsheet is a community skill for programmatically creating, reading, and manipulating spreadsheet files in Excel and CSV formats, covering cell operations, formula insertion, formatting, chart generation, and data analysis workflows.

What Is This?

Overview

Spreadsheet provides patterns for working with spreadsheet files using Python libraries such as openpyxl for Excel files and the csv module for CSV data. It covers cell reading and writing, formula insertion, conditional formatting, sheet management, chart creation, and data transformation operations. The skill enables automation of spreadsheet tasks that would otherwise require manual work in desktop applications.

Who Should Use This

This skill serves data analysts automating report generation, developers building export functionality that produces spreadsheet downloads, and teams that need to process incoming spreadsheet data from external sources into application databases or analysis pipelines.

Why Use It?

Problems It Solves

Manual spreadsheet creation for recurring reports wastes hours on repetitive formatting and data entry. Processing uploaded spreadsheets requires parsing logic that handles inconsistent formats and missing data. Generating formatted Excel files programmatically involves understanding a complex file format that simple CSV output cannot replicate. Data validation errors in spreadsheets propagate through downstream systems when imports lack proper checks.

Core Highlights

Cell-level operations support reading, writing, and formatting individual cells or ranges with type-specific handling. Formula injection inserts Excel formulas that compute values within the generated spreadsheet. Conditional formatting applies visual rules based on cell values for data highlighting. Chart generation creates embedded visualizations directly in the workbook from data ranges.

How to Use It?

Basic Usage

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

class SpreadsheetBuilder:
    def __init__(self):
        self.wb = Workbook()
        self.ws = self.wb.active

    def add_header(self, headers: list[str]):
        header_font = Font(bold=True, size=12)
        header_fill = PatternFill(start_color="4472C4", fill_type="solid")
        for col, header in enumerate(headers, 1):
            cell = self.ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal="center")

    def add_rows(self, data: list[list]):
        for row_idx, row in enumerate(data, 2):
            for col_idx, value in enumerate(row, 1):
                self.ws.cell(row=row_idx, column=col_idx, value=value)

    def auto_width(self):
        for col in range(1, self.ws.max_column + 1):
            max_len = max(
                len(str(self.ws.cell(row=r, column=col).value or ""))
                for r in range(1, self.ws.max_row + 1)
            )
            self.ws.column_dimensions[get_column_letter(col)].width = max_len + 2

    def save(self, path: str):
        self.auto_width()
        self.wb.save(path)

Real-World Examples

from openpyxl.chart import BarChart, Reference

class ReportGenerator:
    def __init__(self, builder: SpreadsheetBuilder):
        self.builder = builder

    def add_summary_formulas(self, data_rows: int):
        ws = self.builder.ws
        summary_row = data_rows + 2
        ws.cell(row=summary_row, column=1, value="Total")
        ws.cell(row=summary_row, column=2,
                value=f"=SUM(B2:B{data_rows + 1})")
        ws.cell(row=summary_row + 1, column=1, value="Average")
        ws.cell(row=summary_row + 1, column=2,
                value=f"=AVERAGE(B2:B{data_rows + 1})")

    def add_chart(self, title: str, data_rows: int):
        chart = BarChart()
        chart.title = title
        chart.style = 10
        data = Reference(self.builder.ws, min_col=2, min_row=1,
                        max_row=data_rows + 1)
        cats = Reference(self.builder.ws, min_col=1, min_row=2,
                        max_row=data_rows + 1)
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(cats)
        self.builder.ws.add_chart(chart, "D2")

report = ReportGenerator(SpreadsheetBuilder())
report.builder.add_header(["Product", "Revenue"])
report.builder.add_rows([["Widget", 15000], ["Gadget", 23000]])
report.add_summary_formulas(2)
report.add_chart("Revenue by Product", 2)
report.builder.save("report.xlsx")

Advanced Tips

Use named ranges for formula references to make spreadsheets more maintainable when row counts change. Apply data validation rules to input cells that restrict values to predefined lists or numeric ranges. Create multiple sheets within a single workbook to separate raw data from summary views and charts.

When to Use It?

Use Cases

Generate monthly financial reports with formatted tables, formulas, and charts. Process uploaded spreadsheets to extract data for database imports with validation. Create data export endpoints that produce downloadable Excel files from application queries.

Important Notes

Requirements

Python with the openpyxl package for Excel file operations. The csv standard library module for CSV handling. Understanding of Excel cell reference notation for formula construction.

Usage Recommendations

Do: validate data types before writing cells to prevent Excel type conversion issues. Use consistent formatting templates across generated reports for professional appearance. Handle large files with write-only mode to manage memory usage.

Don't: generate Excel files with macros enabled unless specifically required, as they trigger security warnings for recipients. Trust incoming spreadsheet data without validation against expected schemas. Ignore character encoding when reading CSV files from external sources.

Limitations

Excel files have row and column limits that constrain very large datasets. Complex formatting operations increase file generation time and output size. Formula evaluation happens in Excel at open time, not during programmatic generation, so formula results are not available for validation in code.