tessl/pypi-xlsxwriter

A Python module for creating Excel XLSX files.

Overview

Eval results

Files

Formatting and Styling

Name: tessl/pypi-xlsxwriter
Author: tessl

Complete cell formatting system including fonts, colors, borders, alignment, number formats, patterns, and fill options. The Format class provides comprehensive styling capabilities for Excel cells with support for all Excel formatting features.

Capabilities

Font Formatting

Configure font properties including typeface, size, style, and color.

def set_font_name(self, font_name):
    """
    Set the font name/typeface.
    
    Args:
        font_name (str): Font name (e.g., 'Arial', 'Times New Roman', 'Calibri')
    """

def set_font_size(self, font_size=11):
    """
    Set the font size.
    
    Args:
        font_size (int): Font size in points (default 11)
    """

def set_font_color(self, font_color):
    """
    Set the font color.
    
    Args:
        font_color (str): Color name, hex code, or RGB tuple
            Examples: 'red', '#FF0000', (255, 0, 0)
    """

def set_bold(self, bold=True):
    """
    Set font to bold.
    
    Args:
        bold (bool): True for bold, False for normal weight
    """

def set_italic(self, italic=True):
    """
    Set font to italic.
    
    Args:
        italic (bool): True for italic, False for normal style
    """

def set_underline(self, underline=1):
    """
    Set font underline style.
    
    Args:
        underline (int): Underline type:
            - 1: Single underline
            - 2: Double underline
            - 33: Single accounting underline
            - 34: Double accounting underline
    """

def set_font_strikeout(self, font_strikeout=True):
    """
    Set font strikethrough.
    
    Args:
        font_strikeout (bool): True for strikethrough, False for normal
    """

def set_font_script(self, font_script=1):
    """
    Set font script (superscript/subscript).
    
    Args:
        font_script (int): Script type:
            - 1: Superscript
            - 2: Subscript

def set_font_family(self, font_family):
    """
    Set the font family number.
    
    Args:
        font_family (int): Font family number for the format
    """

def set_font_outline(self, font_outline=True):
    """
    Set font outline (Mac only).
    
    Args:
        font_outline (bool): True for outline, False for normal
    """

def set_font_shadow(self, font_shadow=True):
    """
    Set font shadow (Mac only).
    
    Args:
        font_shadow (bool): True for shadow, False for normal
    """

Number Formatting

Configure how numbers, dates, and times are displayed in cells.

def set_num_format(self, num_format):
    """
    Set the number format for the cell.
    
    Args:
        num_format (str or int): Number format string or built-in format ID
            Examples:
            - '0.00': Two decimal places
            - '#,##0': Thousands separator
            - '$#,##0.00': Currency format
            - 'mm/dd/yyyy': Date format
            - 'h:mm AM/PM': Time format
            - '@': Text format
            - '0%': Percentage format
    """

Alignment and Text Control

Control text alignment, wrapping, rotation, and indentation.

def set_align(self, alignment):
    """
    Set cell alignment.
    
    Args:
        alignment (str): Alignment type:
            Horizontal: 'left', 'center', 'right', 'fill', 'justify', 
                       'center_across', 'distributed'
            Vertical: 'top', 'vcenter', 'bottom', 'vjustify', 'vdistributed'
    """

def set_valign(self, align):
    """
    Set vertical cell alignment property.
    
    Legacy method for vertical alignment that differentiates from horizontal alignment.
    
    Args:
        align (str): Vertical alignment:
            'top', 'vcenter', 'bottom', 'vjustify', 'vdistributed'
    """

def set_center_across(self, align_type=None):
    """
    Center text across selection.
    
    Args:
        align_type: Deprecated parameter (for compatibility)
    """

def set_text_wrap(self, text_wrap=True):
    """
    Enable text wrapping in the cell.
    
    Args:
        text_wrap (bool): True to wrap text, False for single line
    """

def set_rotation(self, rotation):
    """
    Set text rotation angle.
    
    Args:
        rotation (int): Rotation angle:
            - 0 to 90: Rotate counterclockwise
            - -1 to -90: Rotate clockwise
            - 255: Vertical text
    """

def set_indent(self, indent=1):
    """
    Set text indentation level.
    
    Args:
        indent (int): Indentation level (0-15)
    """

def set_shrink(self, shrink=True):
    """
    Shrink text to fit cell width.
    
    Args:
        shrink (bool): True to shrink text, False for normal sizing
    """

def set_reading_order(self, direction=0):
    """
    Set text reading order.
    
    Args:
        direction (int): Reading direction:
            - 0: Context (default)
            - 1: Left-to-right
            - 2: Right-to-left
    """

Fill and Background

Configure cell background colors and patterns.

def set_pattern(self, pattern=1):
    """
    Set fill pattern style.
    
    Args:
        pattern (int): Pattern type:
            - 0: No fill
            - 1: Solid fill
            - 2-18: Various pattern styles (dots, stripes, etc.)
    """

def set_bg_color(self, bg_color):
    """
    Set background color.
    
    Args:
        bg_color (str): Color name, hex code, or RGB tuple
            Examples: 'yellow', '#FFFF00', (255, 255, 0)
    """

def set_fg_color(self, fg_color):
    """
    Set foreground color (for patterns).
    
    Args:
        fg_color (str): Color name, hex code, or RGB tuple
    """

Border Formatting

Configure cell borders including style, color, and position.

def set_border(self, style=1):
    """
    Set all cell borders to the same style.
    
    Args:
        style (int): Border style:
            - 0: No border
            - 1: Thin
            - 2: Medium  
            - 3: Dashed
            - 4: Dotted
            - 5: Thick
            - 6: Double
            - 7: Hair
            - 8-13: Various dash/dot combinations
    """

def set_border_color(self, color):
    """
    Set all border colors.
    
    Args:
        color (str): Color name, hex code, or RGB tuple
    """

def set_left(self, left=1):
    """Set left border style."""

def set_right(self, right=1):
    """Set right border style."""

def set_top(self, top=1):
    """Set top border style."""

def set_bottom(self, bottom=1):
    """Set bottom border style."""

def set_left_color(self, left_color):
    """Set left border color."""

def set_right_color(self, right_color):
    """Set right border color."""

def set_top_color(self, top_color):
    """Set top border color."""

def set_bottom_color(self, bottom_color):
    """Set bottom border color."""

def set_diag_type(self, diag_type=1):
    """
    Set diagonal border type.
    
    Args:
        diag_type (int): Diagonal type:
            - 1: From bottom-left to top-right
            - 2: From top-left to bottom-right  
            - 3: Both diagonals
    """

def set_diag_border(self, diag_border=1):
    """Set diagonal border style."""

def set_diag_color(self, diag_color):
    """Set diagonal border color."""

Cell Protection

Configure cell locking and formula hiding for worksheet protection.

def set_locked(self, locked=True):
    """
    Set cell locked status for protection.
    
    Args:
        locked (bool): True to lock cell, False to unlock
        
    Note: Only takes effect when worksheet protection is enabled
    """

def set_hidden(self, hidden=True):
    """
    Set formula hidden status for protection.
    
    Args:
        hidden (bool): True to hide formulas, False to show
        
    Note: Only takes effect when worksheet protection is enabled
    """

Usage Examples

Basic Font Formatting

import xlsxwriter

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

# Create formats
title_format = workbook.add_format({
    'font_name': 'Arial',
    'font_size': 16,
    'bold': True,
    'font_color': 'blue'
})

header_format = workbook.add_format({
    'bold': True,
    'italic': True,
    'underline': True,
    'font_color': 'red'
})

# Apply formats
worksheet.write('A1', 'Title', title_format)
worksheet.write('A2', 'Header', header_format)

workbook.close()

Number Formatting Examples

# Create number formats
currency_format = workbook.add_format({'num_format': '$#,##0.00'})
percentage_format = workbook.add_format({'num_format': '0.00%'})
date_format = workbook.add_format({'num_format': 'mm/dd/yyyy'})
time_format = workbook.add_format({'num_format': 'h:mm AM/PM'})
fraction_format = workbook.add_format({'num_format': '# ?/?'})

# Write formatted numbers
worksheet.write(0, 0, 1234.56, currency_format)    # $1,234.56
worksheet.write(1, 0, 0.75, percentage_format)     # 75.00%
worksheet.write(2, 0, datetime.now(), date_format) # 12/25/2023
worksheet.write(3, 0, datetime.now(), time_format) # 2:30 PM
worksheet.write(4, 0, 1.25, fraction_format)       # 1 1/4

Alignment and Text Control

# Create alignment formats
center_format = workbook.add_format({
    'align': 'center',
    'valign': 'vcenter'
})

wrap_format = workbook.add_format({
    'text_wrap': True,
    'align': 'left',
    'valign': 'top'
})

rotated_format = workbook.add_format({
    'rotation': 45,
    'align': 'center'
})

# Apply formatting
worksheet.write('A1', 'Centered', center_format)
worksheet.write('A2', 'This is a long text that will wrap', wrap_format)
worksheet.write('A3', 'Rotated Text', rotated_format)

Border and Fill Formatting

# Create border format
border_format = workbook.add_format({
    'border': 2,              # Medium border all around
    'border_color': 'black',
    'top': 5,                 # Thick top border
    'top_color': 'red'
})

# Create fill format
fill_format = workbook.add_format({
    'pattern': 1,             # Solid fill
    'bg_color': 'yellow',
    'font_color': 'black'
})

# Combine formatting
combined_format = workbook.add_format({
    'bg_color': 'lightblue',
    'font_color': 'darkblue',
    'border': 1,
    'bold': True,
    'align': 'center'
})

worksheet.write('A1', 'Bordered', border_format)
worksheet.write('A2', 'Filled', fill_format)  
worksheet.write('A3', 'Combined', combined_format)

Conditional Formatting Integration

# Create formats for conditional formatting
red_format = workbook.add_format({
    'bg_color': '#FFC7CE',
    'font_color': '#9C0006'
})

green_format = workbook.add_format({
    'bg_color': '#C6EFCE', 
    'font_color': '#006100'
})

# Apply conditional formatting
worksheet.conditional_format('A1:A10', {
    'type': 'cell',
    'criteria': '>',
    'value': 50,
    'format': green_format
})

worksheet.conditional_format('A1:A10', {
    'type': 'cell', 
    'criteria': '<',
    'value': 25,
    'format': red_format
})

Format Properties Dictionary

# Create format using properties dictionary
complex_format = workbook.add_format({
    # Font properties
    'font_name': 'Times New Roman',
    'font_size': 12,
    'bold': True,
    'italic': False,
    'underline': 1,
    'font_strikeout': False,
    'font_color': '#0000FF',
    
    # Number format
    'num_format': '#,##0.00',
    
    # Alignment
    'align': 'center',
    'valign': 'vcenter',
    'text_wrap': True,
    'rotation': 0,
    'indent': 0,
    'shrink': False,
    
    # Pattern/Fill
    'pattern': 1,
    'bg_color': '#FFFF00',
    'fg_color': '#FF0000',
    
    # Borders
    'border': 1,
    'left': 1,
    'right': 1, 
    'top': 1,
    'bottom': 1,
    'border_color': '#000000',
    'left_color': '#000000',
    'right_color': '#000000',
    'top_color': '#000000',
    'bottom_color': '#000000',
    
    # Protection
    'locked': True,
    'hidden': False
})

Install with Tessl CLI