tessl/pypi-xlsxwriter

A Python module for creating Excel XLSX files.

Overview

Eval results

Files

Advanced Features

Name: tessl/pypi-xlsxwriter
Author: tessl

Advanced Excel functionality including data validation, conditional formatting, autofilters, tables, sparklines, images, textboxes, comments, and worksheet protection. These features provide professional Excel capabilities for data analysis and presentation.

Capabilities

Data Validation

Create dropdown lists and input validation rules for cells.

def data_validation(self, first_row, first_col, last_row, last_col, options=None):
    """
    Add data validation to a cell range.
    
    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)
        options (dict, optional): Validation options:
            - validate (str): Validation type:
                'any', 'integer', 'decimal', 'list', 'date', 'time',
                'length', 'custom', 'whole'
            - criteria (str): Validation criteria:
                'between', 'not between', 'equal to', 'not equal to',
                'greater than', 'less than', 'greater than or equal to',
                'less than or equal to'
            - value (str/int/float): Primary validation value
            - minimum (str/int/float): Minimum value for 'between'
            - maximum (str/int/float): Maximum value for 'between'
            - source (str): List source for 'list' validation
            - dropdown (bool): Show dropdown arrow
            - input_title (str): Input message title
            - input_message (str): Input message text
            - error_title (str): Error alert title
            - error_message (str): Error alert message
            - error_type (str): Error alert type:
                'stop', 'warning', 'information'
    """

Conditional Formatting

Apply dynamic formatting based on cell values and formulas.

def conditional_format(self, first_row, first_col, last_row, last_col, options=None):
    """
    Add conditional formatting to a cell range.
    
    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)
        options (dict, optional): Formatting options:
            - type (str): Condition type:
                'cell', 'text', 'time_period', 'date', 'average',
                'duplicate', 'unique', 'top', 'bottom', 'blanks',
                'no_blanks', 'errors', 'no_errors', 'formula',
                'data_bar', 'color_scale', 'icon_set'
            - criteria (str): Condition criteria (for 'cell' type)
            - value (str/int/float): Comparison value
            - minimum (str/int/float): Minimum value for ranges
            - maximum (str/int/float): Maximum value for ranges
            - format (Format): Format to apply when condition is met
            - multi_range (str): Apply to multiple ranges
            - stop_if_true (bool): Stop processing other rules
    """

Tables and Autofilters

Create Excel tables and autofilter functionality.

def add_table(self, first_row, first_col, last_row, last_col, options=None):
    """
    Add an Excel table to a cell range.
    
    Args:
        first_row (int): First row of table (0-indexed)
        first_col (int): First column of table (0-indexed)
        last_row (int): Last row of table (0-indexed)
        last_col (int): Last column of table (0-indexed)
        options (dict, optional): Table options:
            - data (list): 2D list of table data
            - autofilter (bool): Enable autofilter (default True)
            - header_row (bool): First row contains headers (default True)
            - banded_columns (bool): Apply banded column formatting
            - banded_rows (bool): Apply banded row formatting (default True)
            - first_column (bool): Emphasize first column
            - last_column (bool): Emphasize last column
            - style (str): Table style name
            - name (str): Table name (auto-generated if not provided)
            - total_row (bool): Add total row
            - columns (list): Column specifications with headers and formulas
    """

def autofilter(self, first_row, first_col, last_row, last_col):
    """
    Add autofilter to a cell range.
    
    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)
    """

def filter_column(self, col, criteria):
    """
    Set filter criteria for an autofilter column.
    
    Args:
        col (int): Column number (0-indexed)
        criteria (str): Filter criteria or comparison
    """

def filter_column_list(self, col, filters):
    """
    Set a list of filter values for an autofilter column.
    
    Args:
        col (int): Column number (0-indexed)
        filters (list): List of values to show
    """

Sparklines

Add small charts within cells for data visualization.

def add_sparkline(self, row, col, options=None):
    """
    Add a sparkline chart to a cell.
    
    Args:
        row (int): Cell row (0-indexed)
        col (int): Cell column (0-indexed)
        options (dict, optional): Sparkline options:
            - range (str): Data range for the sparkline
            - type (str): Sparkline type ('line', 'column', 'win_loss')
            - style (int): Built-in sparkline style (1-36)
            - markers (bool): Show data point markers
            - negative_points (bool): Highlight negative points
            - axis (bool): Show axis line
            - reverse (bool): Plot data right-to-left
            - weight (float): Line weight for line sparklines
            - high_point (bool): Highlight highest point
            - low_point (bool): Highlight lowest point
            - first_point (bool): Highlight first point
            - last_point (bool): Highlight last point
            - max (float): Maximum scale value
            - min (float): Minimum scale value
            - empty_cells (str): How to plot empty cells ('gaps'/'zero'/'connect')
            - show_hidden (bool): Include hidden cell data
            - date_axis (str): Date range for X-axis
    """

Images and Graphics

Insert and manage images and graphics in worksheets.

def insert_image(self, row, col, source, options=None):
    """
    Insert an image into the worksheet.
    
    Args:
        row (int): Cell row for image position (0-indexed)
        col (int): Cell column for image position (0-indexed)
        source (str or BytesIO): Image file path or image data stream
        options (dict, optional): Image options:
            - x_offset (int): Horizontal offset in pixels
            - y_offset (int): Vertical offset in pixels
            - x_scale (float): Horizontal scaling factor
            - y_scale (float): Vertical scaling factor
            - object_position (int): How image moves/sizes with cells (1-4)
            - url (str): Hyperlink URL for the image
            - tip (str): Hyperlink tooltip
            - description (str): Alt text description
            - decorative (bool): Mark as decorative (accessibility)
    """

def embed_image(self, row, col, source, options=None):
    """
    Embed an image in the workbook (reduces file duplication).
    
    Args:
        row (int): Cell row for image position (0-indexed)
        col (int): Cell column for image position (0-indexed)  
        source (str or BytesIO): Image file path or image data stream
        options (dict, optional): Same options as insert_image
    """

def set_background(self, source, is_byte_stream=False):
    """
    Set a background image for the worksheet.
    
    Args:
        source (str or BytesIO): Image file path or image data
        is_byte_stream (bool): True if source is BytesIO object
    """

Text Boxes and Shapes

Add text boxes and drawing shapes to worksheets.

def insert_textbox(self, row, col, text, options=None):
    """
    Insert a text box into the worksheet.
    
    Args:
        row (int): Cell row for textbox position (0-indexed)
        col (int): Cell column for textbox position (0-indexed)
        text (str): Text content for the textbox
        options (dict, optional): Textbox options:
            - width (int): Textbox width in pixels
            - height (int): Textbox height in pixels
            - x_offset (int): Horizontal offset in pixels
            - y_offset (int): Vertical offset in pixels
            - font (dict): Font formatting for text
            - align (dict): Text alignment options
            - border (dict): Textbox border formatting
            - fill (dict): Textbox background fill
            - gradient (dict): Gradient fill options
            - object_position (int): How textbox moves/sizes with cells
    """

Comments and Annotations

Add comments and annotations to cells.

def write_comment(self, row, col, comment, options=None):
    """
    Add a comment to a worksheet cell.
    
    Args:
        row (int): Cell row (0-indexed)
        col (int): Cell column (0-indexed)
        comment (str): Comment text
        options (dict, optional): Comment options:
            - author (str): Comment author name
            - visible (bool): Show comment by default
            - width (int): Comment box width
            - height (int): Comment box height
            - x_scale (float): Horizontal scaling factor
            - y_scale (float): Vertical scaling factor
            - color (str): Comment background color
            - start_cell (str): Cell reference for comment position
            - start_row (int): Row offset for comment position
            - start_col (int): Column offset for comment position
            - x_offset (int): Horizontal pixel offset
            - y_offset (int): Vertical pixel offset
    """

def show_comments(self):
    """Show all comments on the worksheet by default."""

def set_comments_author(self, author):
    """
    Set the default author for comments.
    
    Args:
        author (str): Default author name for new comments
    """

Form Controls

Add interactive form controls to worksheets.

def insert_button(self, row, col, options=None):
    """
    Insert a form control button.
    
    Args:
        row (int): Cell row for button position (0-indexed)
        col (int): Cell column for button position (0-indexed)
        options (dict, optional): Button options:
            - macro (str): VBA macro to run when clicked
            - caption (str): Button text
            - width (int): Button width in pixels
            - height (int): Button height in pixels
            - x_offset (int): Horizontal offset in pixels
            - y_offset (int): Vertical offset in pixels
            - x_scale (float): Horizontal scaling factor
            - y_scale (float): Vertical scaling factor
    """

def insert_checkbox(self, row, col, boolean, cell_format=None):
    """
    Insert a checkbox form control.
    
    Args:
        row (int): Cell row for checkbox position (0-indexed)
        col (int): Cell column for checkbox position (0-indexed)
        boolean (bool): Initial checked state
        cell_format (Format, optional): Cell formatting
    """

Cell Merging

Merge cells across ranges for layout and formatting.

def merge_range(self, first_row, first_col, last_row, last_col, data, cell_format=None):
    """
    Merge a range of cells and add data.
    
    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)
        data: Data to write to the merged range
        cell_format (Format, optional): Format for merged cells
    """

Usage Examples

Data Validation Examples

# Dropdown list validation
worksheet.data_validation('A1:A10', {
    'validate': 'list',
    'source': ['Option 1', 'Option 2', 'Option 3'],
    'dropdown': True
})

# Number range validation
worksheet.data_validation('B1:B10', {
    'validate': 'integer',
    'criteria': 'between',
    'minimum': 1,
    'maximum': 100,
    'input_title': 'Enter a number',
    'input_message': 'Please enter a number between 1 and 100'
})

# Date validation
worksheet.data_validation('C1:C10', {
    'validate': 'date',
    'criteria': 'greater than',
    'value': datetime(2023, 1, 1),
    'error_title': 'Invalid Date',
    'error_message': 'Date must be after 2023-01-01'
})

Conditional Formatting Examples

# Create formats for conditional formatting
red_format = workbook.add_format({'bg_color': '#FFC7CE'})
green_format = workbook.add_format({'bg_color': '#C6EFCE'})
yellow_format = workbook.add_format({'bg_color': '#FFEB9C'})

# Value-based formatting
worksheet.conditional_format('A1:A10', {
    'type': 'cell',
    'criteria': 'greater than',
    'value': 50,
    'format': green_format
})

# Text-based formatting  
worksheet.conditional_format('B1:B10', {
    'type': 'text',
    'criteria': 'containing',
    'value': 'Error',
    'format': red_format
})

# Data bars
worksheet.conditional_format('C1:C10', {
    'type': 'data_bar',
    'bar_color': '#638EC6'
})

# Color scales
worksheet.conditional_format('D1:D10', {
    'type': 'color_scale',
    'min_color': '#FF0000',
    'max_color': '#00FF00'
})

Excel Tables

# Create table with data
data = [
    ['Name', 'Age', 'City', 'Salary'],
    ['Alice', 25, 'New York', 50000],
    ['Bob', 30, 'London', 60000],
    ['Charlie', 35, 'Tokyo', 70000]
]

# Write data
for row, row_data in enumerate(data):
    worksheet.write_row(row, 0, row_data)

# Add table
worksheet.add_table(0, 0, 3, 3, {
    'style': 'Table Style Medium 2',
    'name': 'EmployeeTable',
    'total_row': True,
    'columns': [
        {'header': 'Name'},
        {'header': 'Age'},
        {'header': 'City'},
        {'header': 'Salary', 'total_function': 'average'}
    ]
})

Sparklines

# Add data for sparklines
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']
sales_data = [100, 120, 110, 150, 140, 180]

worksheet.write_row(0, 0, months)
worksheet.write_row(1, 0, sales_data)

# Add sparkline
worksheet.add_sparkline(1, 6, {
    'range': 'A2:F2',
    'type': 'line',
    'markers': True,
    'high_point': True,
    'low_point': True,
    'style': 12
})

Images and Graphics

# Insert image with positioning
worksheet.insert_image('A1', 'logo.png', {
    'x_offset': 10,
    'y_offset': 10,
    'x_scale': 0.5,
    'y_scale': 0.5,
    'url': 'https://www.example.com',
    'description': 'Company Logo'
})

# Insert textbox
worksheet.insert_textbox('C3', 'Important Notice!', {
    'width': 200,
    'height': 100,
    'font': {'bold': True, 'size': 14, 'color': 'red'},
    'border': {'color': 'black', 'width': 2},
    'fill': {'color': 'yellow'}
})

Comments

# Add comments with formatting
worksheet.write_comment('A1', 'This is a comment', {
    'author': 'John Doe',
    'visible': False,
    'width': 300,
    'height': 100,
    'color': 'lightblue'
})

# Set default comment author
worksheet.set_comments_author('Default Author')

Form Controls

# Insert button (requires VBA project)
worksheet.insert_button('A10', {
    'macro': 'ButtonClick',
    'caption': 'Click Me!',
    'width': 100,
    'height': 30
})

# Insert checkbox
worksheet.insert_checkbox('B10', True)  # Initially checked

Install with Tessl CLI