tidyxl 0.1.9

A Python package that imports Excel files (.xlsx, .xlsm) into a tidy format where each cell is represented as a single row with detailed metadata. This package is inspired by and closely mirrors the functionality of the R tidyxl package.

Installation

pip install tidyxl

Quick Start

from tidyxl import xlsx_cells, xlsx_sheet_names

# Get all sheet names
sheets = xlsx_sheet_names("data.xlsx")
print(f"Found sheets: {sheets}")

# Read all cells from all sheets
cells = xlsx_cells("data.xlsx")
print(f"Total cells: {len(cells)}")

# Read specific sheet only
employees = xlsx_cells("data.xlsx", sheets="Employees")

# Filter for cells with content
content_cells = cells[~cells['is_blank']]
print(content_cells[['sheet', 'address', 'data_type', 'character', 'numeric']].head())

Features

• Tidy Format: Each Excel cell becomes one row with comprehensive metadata
• Complete Cell Information: Extract values, formulas, formatting, comments, and more
• Multiple Worksheets: Process all sheets or specify particular ones
• Named Ranges: Extract and analyze Excel named ranges and formulas
• Data Validation: Discover data validation rules applied to cells
• Type Safety: Separate columns for different data types (numeric, character, logical, date, error)
• R tidyxl Compatible: Identical API and output structure to the R package

Core Functions

xlsx_cells()

Extract all cell data in tidy format:

from tidyxl import xlsx_cells

# Read all sheets
cells = xlsx_cells("file.xlsx")

# Read specific sheets
cells = xlsx_cells("file.xlsx", sheets=["Sheet1", "Sheet2"])
cells = xlsx_cells("file.xlsx", sheets="Data")

# Include/exclude blank cells
cells = xlsx_cells("file.xlsx", include_blank_cells=False)

xlsx_sheet_names()

List all worksheet names:

from tidyxl import xlsx_sheet_names

sheets = xlsx_sheet_names("file.xlsx")
# Returns: ['Sheet1', 'Data', 'Summary']

xlsx_names()

Extract named ranges and formulas:

from tidyxl import xlsx_names

names = xlsx_names("file.xlsx")
print(names[['name', 'formula', 'sheet', 'is_range']])

xlsx_validation()

Extract data validation rules:

from tidyxl import xlsx_validation

validation = xlsx_validation("file.xlsx")
print(validation[['sheet', 'ref', 'type', 'formula1']])

xlsx_formats()

Extract formatting information:

from tidyxl import xlsx_formats

formats = xlsx_formats("file.xlsx")
# Returns dict with keys: fonts, fills, borders, number_formats

Output Structure

The xlsx_cells() function returns a pandas DataFrame with 23 columns matching the R tidyxl package:

License

This project is licensed under the MIT License.

Acknowledgments

• Inspired by the excellent R tidyxl package by Duncan Garmonsway
• Built on top of openpyxl for Excel file processing
• Uses pandas for data structure management