tessl/pypi-xlsxwriter

A Python module for creating Excel XLSX files.

Overview

Eval results

Files

Utility Functions and Helpers

Name: tessl/pypi-xlsxwriter
Author: tessl

Essential utility functions for Excel coordinate conversions, cell reference handling, range creation, and worksheet name management. These functions help convert between different Excel addressing systems and handle Excel-specific formatting requirements.

Capabilities

Cell Reference Conversion

Convert between row/column numbers and Excel cell references.

def xl_rowcol_to_cell(row, col, row_abs=False, col_abs=False):
    """
    Convert row and column numbers to Excel cell reference.
    
    Args:
        row (int): Cell row (0-indexed)
        col (int): Cell column (0-indexed)  
        row_abs (bool): Make row reference absolute (add $)
        col_abs (bool): Make column reference absolute (add $)
        
    Returns:
        str: Excel cell reference (e.g., 'A1', '$A$1', 'B5')
        
    Examples:
        xl_rowcol_to_cell(0, 0)        # 'A1'
        xl_rowcol_to_cell(0, 0, True, True)  # '$A$1' 
        xl_rowcol_to_cell(10, 5)       # 'F11'
    """

def xl_rowcol_to_cell_fast(row, col):
    """
    Fast conversion of row/column to cell reference (relative only).
    
    Args:
        row (int): Cell row (0-indexed)
        col (int): Cell column (0-indexed)
        
    Returns:
        str: Excel cell reference (e.g., 'A1', 'B5')
        
    Note: Optimized version without absolute reference support
    """

def xl_cell_to_rowcol(cell_str):
    """
    Convert Excel cell reference to row and column numbers.
    
    Args:
        cell_str (str): Excel cell reference (e.g., 'A1', 'B5', '$A$1')
        
    Returns:
        tuple: (row, col) as 0-indexed integers
        
    Examples:
        xl_cell_to_rowcol('A1')        # (0, 0)
        xl_cell_to_rowcol('F11')       # (10, 5)
        xl_cell_to_rowcol('$A$1')      # (0, 0)
    """

def xl_cell_to_rowcol_abs(cell_str):
    """
    Convert Excel cell reference to row/column with absolute flags.
    
    Args:
        cell_str (str): Excel cell reference (e.g., 'A1', '$A$1', 'A$1')
        
    Returns:
        tuple: (row, col, row_abs, col_abs)
            - row (int): Row number (0-indexed)
            - col (int): Column number (0-indexed)
            - row_abs (bool): True if row is absolute
            - col_abs (bool): True if column is absolute
            
    Examples:
        xl_cell_to_rowcol_abs('A1')    # (0, 0, False, False)
        xl_cell_to_rowcol_abs('$A$1')  # (0, 0, True, True)
        xl_cell_to_rowcol_abs('A$1')   # (0, 0, True, False)
    """

Column Name Conversion

Convert between column numbers and Excel column names.

def xl_col_to_name(col, col_abs=False):
    """
    Convert column number to Excel column name.
    
    Args:
        col (int): Column number (0-indexed)
        col_abs (bool): Make column reference absolute (add $)
        
    Returns:
        str: Excel column name (e.g., 'A', 'B', 'AA', '$A')
        
    Examples:
        xl_col_to_name(0)              # 'A'
        xl_col_to_name(25)             # 'Z'
        xl_col_to_name(26)             # 'AA'
        xl_col_to_name(0, True)        # '$A'
    """

Range Creation

Create Excel range strings from coordinates.

def xl_range(first_row, first_col, last_row, last_col):
    """
    Create Excel range string from cell coordinates.
    
    Args:
        first_row (int): First row of range (0-indexed)
        first_col (int): First column of range (0-indexed)
        last_row (int): Last row of range (0-indexed)
        last_col (int): Last column of range (0-indexed)
        
    Returns:
        str: Excel range string (e.g., 'A1:B5')
        
    Examples:
        xl_range(0, 0, 4, 1)           # 'A1:B5'
        xl_range(2, 2, 2, 2)           # 'C3'
    """

def xl_range_abs(first_row, first_col, last_row, last_col):
    """
    Create absolute Excel range string from cell coordinates.
    
    Args:
        first_row (int): First row of range (0-indexed)
        first_col (int): First column of range (0-indexed)
        last_row (int): Last row of range (0-indexed)
        last_col (int): Last column of range (0-indexed)
        
    Returns:
        str: Absolute Excel range string (e.g., '$A$1:$B$5')
    """

def xl_range_formula(sheetname, first_row, first_col, last_row, last_col):
    """
    Create Excel range formula with sheet reference.
    
    Args:
        sheetname (str): Worksheet name
        first_row (int): First row of range (0-indexed)
        first_col (int): First column of range (0-indexed)
        last_row (int): Last row of range (0-indexed)
        last_col (int): Last column of range (0-indexed)
        
    Returns:
        str: Excel range formula (e.g., 'Sheet1!A1:B5', "'Sheet Name'!A1:B5")
    """

Worksheet Name Handling

Handle worksheet names with special characters.

def quote_sheetname(sheetname):
    """
    Quote worksheet name if it contains special characters.
    
    Args:
        sheetname (str): Worksheet name
        
    Returns:
        str: Quoted worksheet name if needed (e.g., "'Sheet Name'")
        
    Quotes are added if the name contains:
    - Spaces
    - Special characters: []:'*?/\
    - Starts with a digit
    """

Text Width Calculations

Calculate text width for column sizing and autofit functionality.

def xl_pixel_width(string):
    """
    Calculate the pixel width of a string in Excel's default font.
    
    Args:
        string (str): Text string to measure
        
    Returns:
        int: Approximate pixel width of the string
        
    Used internally for autofit calculations and column width estimation.
    """

def cell_autofit_width(string):
    """
    Calculate the autofit width for a cell containing the given string.
    
    Args:
        string (str): Text string in the cell
        
    Returns:
        int: Recommended column width for autofit
    """

Exception Classes

Error handling classes for XlsxWriter operations.

class XlsxWriterException(Exception):
    """Base exception class for all XlsxWriter errors."""

class XlsxInputError(XlsxWriterException):
    """Exception for input data related errors."""

class XlsxFileError(XlsxWriterException):
    """Exception for file operation related errors."""

class EmptyChartSeries(XlsxInputError):
    """Chart must contain at least one data series."""

class DuplicateTableName(XlsxInputError):
    """Worksheet table name already exists."""

class InvalidWorksheetName(XlsxInputError):
    """Worksheet name is too long or contains restricted characters."""

class DuplicateWorksheetName(XlsxInputError):
    """Worksheet name already exists."""

class OverlappingRange(XlsxInputError):
    """Worksheet merge range or table overlaps previous range."""

class UndefinedImageSize(XlsxFileError):
    """No size data found in image file."""

class UnsupportedImageFormat(XlsxFileError):
    """Unsupported image file format."""

class FileCreateError(XlsxFileError):
    """IO error when creating xlsx file."""

class FileSizeError(XlsxFileError):
    """Filesize would require ZIP64 extensions."""

Usage Examples

Cell Reference Conversions

import xlsxwriter
from xlsxwriter.utility import *

# Convert row/col to cell reference
cell_ref = xl_rowcol_to_cell(0, 0)        # 'A1'
abs_ref = xl_rowcol_to_cell(0, 0, True, True)  # '$A$1'

# Convert cell reference to row/col
row, col = xl_cell_to_rowcol('F11')       # (10, 5)
row, col, row_abs, col_abs = xl_cell_to_rowcol_abs('$A$1')  # (0, 0, True, True)

# Column name conversions
col_name = xl_col_to_name(26)             # 'AA'
abs_col = xl_col_to_name(0, True)         # '$A'

Range Creation

# Create range strings
range_str = xl_range(0, 0, 4, 1)          # 'A1:B5'
abs_range = xl_range_abs(0, 0, 4, 1)      # '$A$1:$B$5'

# Create range formulas with sheet names
formula = xl_range_formula('Data', 0, 0, 4, 1)     # 'Data!A1:B5'
formula = xl_range_formula('My Sheet', 0, 0, 4, 1) # "'My Sheet'!A1:B5"

Dynamic Range Building

workbook = xlsxwriter.Workbook('utility_demo.xlsx')
worksheet = workbook.add_worksheet()

# Write data using utility functions
data = [
    ['Name', 'Score', 'Grade'],
    ['Alice', 95, 'A'],
    ['Bob', 87, 'B'],
    ['Charlie', 92, 'A']
]

for row_num, row_data in enumerate(data):
    for col_num, cell_data in enumerate(row_data):
        # Use utility function to get cell reference
        cell_ref = xl_rowcol_to_cell(row_num, col_num)
        worksheet.write(cell_ref, cell_data)

# Create chart using utility functions for ranges
chart = workbook.add_chart({'type': 'column'})

# Use utility functions to create ranges
categories_range = xl_range_formula('Sheet1', 1, 0, 3, 0)  # Names
values_range = xl_range_formula('Sheet1', 1, 1, 3, 1)     # Scores

chart.add_series({
    'categories': f'={categories_range}',
    'values': f'={values_range}',
    'name': 'Student Scores'
})

worksheet.insert_chart('E2', chart)
workbook.close()

Working with Large Datasets

# Generate large dataset with utility functions
num_rows = 1000
num_cols = 10

workbook = xlsxwriter.Workbook('large_data.xlsx')
worksheet = workbook.add_worksheet()

# Write headers
for col in range(num_cols):
    col_name = xl_col_to_name(col)
    worksheet.write(0, col, f'Column {col_name}')

# Write data using row/col coordinates
for row in range(1, num_rows + 1):
    for col in range(num_cols):
        worksheet.write(row, col, row * col)

# Create summary using range functions
summary_range = xl_range(1, 0, num_rows, num_cols - 1)
worksheet.write(num_rows + 2, 0, 'Data Range:')
worksheet.write(num_rows + 2, 1, summary_range)

workbook.close()

Worksheet Name Handling

# Handle worksheet names with special characters
names = [
    'Sheet1',           # No quoting needed
    'My Data',          # Contains space - will be quoted
    'Sales[2023]',      # Contains brackets - will be quoted  
    '2023_Results',     # Starts with digit - will be quoted
    'Summary'           # No quoting needed
]

workbook = xlsxwriter.Workbook('sheet_names.xlsx')

for name in names:
    worksheet = workbook.add_worksheet(name)
    
    # Create reference to this sheet using utility function
    quoted_name = quote_sheetname(name)
    range_formula = f'{quoted_name}!A1:B5'
    
    worksheet.write('A1', f'This is {name}')
    worksheet.write('A2', f'Range: {range_formula}')

workbook.close()

Error Handling

try:
    workbook = xlsxwriter.Workbook('test.xlsx')
    worksheet = workbook.add_worksheet()
    
    # This might raise an exception
    worksheet.add_table(0, 0, 5, 5, {'name': 'Table1'})
    worksheet.add_table(3, 3, 8, 8, {'name': 'Table1'})  # Duplicate name
    
except DuplicateTableName as e:
    print(f"Table name error: {e}")
except XlsxInputError as e:
    print(f"Input error: {e}")
except XlsxWriterException as e:
    print(f"XlsxWriter error: {e}")
finally:
    if 'workbook' in locals():
        workbook.close()

Column Width Calculations

# Calculate column widths based on content
workbook = xlsxwriter.Workbook('autofit_demo.xlsx')
worksheet = workbook.add_worksheet()

data = [
    ['Short', 'Medium Length Text', 'This is a very long text that needs more space'],
    ['A', 'Some data here', 'Even longer text content that definitely needs sizing'],
    ['B', 'More content', 'Another example of lengthy text in cells']
]

# Write data and calculate column widths
for row_num, row_data in enumerate(data):
    for col_num, cell_data in enumerate(row_data):
        worksheet.write(row_num, col_num, cell_data)

# Calculate and set optimal column widths
for col in range(len(data[0])):
    max_width = 0
    for row in range(len(data)):
        cell_width = xl_pixel_width(str(data[row][col]))
        max_width = max(max_width, cell_width)
    
    # Convert pixels to Excel width units (approximately)
    excel_width = max_width / 7.0
    worksheet.set_column(col, col, min(excel_width, 50))  # Cap at 50

workbook.close()

Install with Tessl CLI