Introduction

Working with Excel spreadsheets programmatically is a common requirement in data processing, reporting, and automation workflows. aspose-cells-foss is an open-source Python library that lets you create, read, and modify .xlsx workbooks without any dependency on Microsoft Excel or other office suites.

Released under the MIT license, aspose-cells-foss provides a rich API surface covering cell values, formulas, styling, charts, tables, shapes, pictures, data validation, comments, hyperlinks, auto-filters, and conditional formatting. The library requires Python 3.7 or higher and installs with a single pip command.

This post walks through the spreadsheet management capabilities that aspose-cells-foss brings to your Python projects, with working code examples grounded in the actual API.

What’s Included

Cell Values and Formulas

The Cell class supports integers, floats, strings, and formulas. You can set a value directly through put_value() or assign a formula string to the formula property. The FormulaEvaluator class can evaluate formulas programmatically.

import aspose_cells_foss
from aspose.cells_foss import Workbook, Cell

wb = Workbook()
ws = wb.worksheets[0]

# Set different value types
ws.cells["A1"] = Cell(42)
ws.cells["A2"] = Cell(3.14159)
ws.cells["A3"] = Cell("Revenue Report")

# Set a formula
ws.cells["A4"] = Cell(None, "=SUM(A1:A2)")

# Check the stored values
print(ws.cells["A1"].value)    # 42
print(ws.cells["A4"].formula)  # =SUM(A1:A2)

Styling and Formatting

The Style class provides methods for fills, borders, number formats, and alignment. Apply a style to any cell with Cell.apply_style().

from aspose.cells_foss import Workbook, Cell, Style

wb = Workbook()
ws = wb.worksheets[0]
ws.cells["A1"] = Cell("Total")
ws.cells["B1"] = Cell(25000)

style = Style()
style.set_fill_color("FFD9E1F2")
style.set_border("bottom", "thin", "FF000000", 1)
style.set_number_format("#,##0.00")
ws.cells["B1"].apply_style(style)

wb.save("styled_report.xlsx")

Charts

The ChartCollection class offers dedicated add_line(), add_bar(), add_pie(), add_scatter(), and many other methods to insert charts into a worksheet. Once created, configure the Chart object’s title, legend, category data, and series.

from aspose.cells_foss import Workbook

wb = Workbook()
ws = wb.worksheets[0]

# Populate data
months = ["Jan", "Feb", "Mar", "Apr"]
sales = [120, 150, 180, 200]
for i, (m, s) in enumerate(zip(months, sales), 2):
    ws.cells[f"A{i}"].value = m
    ws.cells[f"B{i}"].value = s
ws.cells["A1"].value = "Month"
ws.cells["B1"].value = "Sales"

# Create a line chart
chart = ws.charts.add_line(0, 4, 20, 12)
chart.title = "Monthly Sales"
chart.category_data = "A2:A5"
chart.n_series.add("B2:B5", category_data="A2:A5", name="Sales")
chart.show_legend = True

wb.save("sales_chart.xlsx")

Data Validation

The DataValidation class lets you enforce dropdown lists, number ranges, and custom validation rules on cell ranges. Properties include type, operator, formula1, formula2, alert_style, error_title, and error_message.

from aspose.cells_foss import Workbook

wb = Workbook()
ws = wb.worksheets[0]

# Add a dropdown validation
dv = ws.data_validations.add("B2:B100")
dv.type = 3       # List validation
dv.formula1 = '"Yes,No,Pending"'
dv.show_error_message = True
dv.error_title = "Invalid Entry"
dv.error_message = "Please select Yes, No, or Pending."

wb.save("validated.xlsx")

Auto-Filters and Tables

Apply auto-filtering with the AutoFilter class by calling set_range() and then filtering individual columns. For structured data, the TableCollection.add() method creates ListObject tables with styles and auto-filters.

from aspose.cells_foss import Workbook

wb = Workbook()
ws = wb.worksheets[0]

# Populate header + data
ws.cells["A1"].value = "Name"
ws.cells["B1"].value = "Score"
for i, (n, s) in enumerate([("Alice", 92), ("Bob", 78), ("Carol", 95)], 2):
    ws.cells[f"A{i}"].value = n
    ws.cells[f"B{i}"].value = s

# Apply an auto-filter
ws.auto_filter.set_range(0, 0, 3, 1)

# Or create a structured table
table = ws.tables.add(0, 0, 3, 1, True, "ScoreTable")

wb.save("filtered_data.xlsx")

Workbook and Worksheet Protection

Protect workbooks with Workbook.protect() and individual worksheets with Worksheet.protect(). The protection API supports granular permissions such as allowing formatting or inserting rows while blocking deletions.

from aspose.cells_foss import Workbook

wb = Workbook()
ws = wb.worksheets[0]
ws.cells["A1"].value = "Protected content"

# Protect the worksheet with a password
ws.protect("secret123", format_cells=True, insert_rows=True,
           delete_columns=False, delete_rows=False)

# Protect the workbook structure
wb.protect("workbook_pass", lock_structure=True, lock_windows=False)
wb.save("protected.xlsx")

Quick Start

Install the package from PyPI:

pip install aspose-cells-foss>=26.3.1

Create a workbook, populate some data, and save it:

import aspose_cells_foss
from aspose.cells_foss import Workbook, Cell, Style

# Create a new workbook
wb = Workbook()
ws = wb.worksheets[0]
ws.name = "Quarterly Report"

# Add headers with styling
header_style = Style()
header_style.set_fill_color("FF4472C4")
for col, label in enumerate(["Region", "Q1", "Q2", "Q3", "Q4"], 1):
    cell_ref = f"{chr(64 + col)}1"
    ws.cells[cell_ref] = Cell(label)
    ws.cells[cell_ref].apply_style(header_style)

# Add data rows
data = [
    ("North", 12000, 15000, 13000, 16000),
    ("South", 9000, 11000, 10500, 12000),
    ("East", 14000, 13500, 15000, 17000),
]
for r, row in enumerate(data, 2):
    for c, val in enumerate(row, 1):
        ws.cells[f"{chr(64 + c)}{r}"].value = val

# Save as XLSX
wb.save("quarterly_report.xlsx")

# Also export to CSV
wb.save_as_csv("quarterly_report.csv")

Supported Formats

FormatExtensionReadWrite
XLSX.xlsxYesYes
CSV.csvYesYes
TSV.tsvNoYes
JSON.jsonNoYes
Markdown.mdNoYes

The SaveFormat enum defines these output formats: AUTO, XLSX, CSV, TSV, MARKDOWN, and JSON. The library reads .xlsx files natively and can import CSV data through Workbook.load_csv().

Open Source & Licensing

aspose-cells-foss is released under the MIT license, which permits unrestricted use in both commercial and non-commercial projects. The source code is available on GitHub. The library depends on pycryptodome (>= 3.15.0) and olefile (>= 0.46) for encryption support.

Getting Started

Explore the full documentation and reference material to start building with aspose-cells-foss: