Advanced Usage

This section covers advanced features of SerializableExcel, including bytes/stream support for APIs, dynamic columns, custom validators, and getters.

Working with Bytes and Streams

SerializableExcel supports working with bytes and BytesIO objects, making it perfect for web API integration where files are exchanged as byte streams rather than saved to disk.

Reading from Bytes/BytesIO

The from_excel() method accepts file paths, bytes, or BytesIO objects:

from io import BytesIO
from serializable_excel import ExcelModel, Column

class UserModel(ExcelModel):
    name: str = Column(header="Name")
    email: str = Column(header="Email")

# From file path (traditional)
users = UserModel.from_excel("users.xlsx")

# From bytes (e.g., from HTTP request body)
file_bytes = request.files['file'].read()
users = UserModel.from_excel(file_bytes)

# From BytesIO (e.g., from file-like object)
stream = BytesIO(uploaded_file.read())
users = UserModel.from_excel(stream)

Writing to Bytes

The to_excel() method can return bytes instead of writing to a file:

from io import BytesIO

# Save to file (traditional)
UserModel.to_excel(users, "output.xlsx")

# Return as bytes
excel_bytes = UserModel.to_excel(users, return_bytes=True)

# Use in API response (FastAPI/Starlette)
from starlette.responses import StreamingResponse
return StreamingResponse(
    BytesIO(excel_bytes),
    media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    headers={"Content-Disposition": "attachment; filename=export.xlsx"}
)

# Use in Flask
from flask import send_file
return send_file(
    BytesIO(excel_bytes),
    mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    as_attachment=True,
    download_name="export.xlsx"
)

Complete API Example

Here’s a complete FastAPI example showing upload and download:

from io import BytesIO
from fastapi import FastAPI, UploadFile, File
from fastapi.responses import StreamingResponse
from serializable_excel import ExcelModel, Column, CellStyle, Colors

app = FastAPI()

class OrderModel(ExcelModel):
    order_id: str = Column(header="Order ID")
    customer: str = Column(header="Customer")
    amount: float = Column(header="Amount")

@app.post("/orders/import")
async def import_orders(file: UploadFile = File(...)):
    """Import orders from Excel file"""
    content = await file.read()
    orders = OrderModel.from_excel(content)

    # Save to database
    for order in orders:
        db.save(order)

    return {"imported": len(orders)}

@app.get("/orders/export")
async def export_orders():
    """Export orders to Excel file"""
    orders = db.get_all_orders()

    excel_bytes = OrderModel.to_excel(orders, return_bytes=True)

    return StreamingResponse(
        BytesIO(excel_bytes),
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": "attachment; filename=orders.xlsx"}
    )

Dynamic Columns

Use DynamicColumn to handle columns that are not known at design time. Values are stored in a dict on the model. You can apply validators, cell color getters, and now also provide a getter function to compute the dynamic values during export.

How Dynamic Columns Work

1. Static Columns: Defined in the model class using Column() descriptor

2. Dynamic Columns: Additional columns in Excel that are not defined in the model

3. Detection: When dynamic_columns=True is passed to .from_excel(), the method scans Excel headers and captures columns not matching static field definitions

4. Storage: Dynamic column values are stored in a dictionary field marked with DynamicColumn()

Example: Forecast with Dynamic Characteristics

Consider a forecasting system where each forecast has static fields (month, curator, manager, object) and dynamic characteristics that administrators can configure:

import enum
from serializable_excel import ExcelModel, Column, DynamicColumn

class ForecastValueTypeEnum(enum.Enum):
    """Value types for forecast characteristics"""
    INTEGER = 'Integer'
    STRING = 'String'

def type_dynamic(column_name: str, row_data: dict) -> ExcelType:
    """Return ExcelType for a dynamic column."""
    if column_name == "Sales":
        return ExcelType.INTEGER
    return ExcelType.STRING

class ForecastModel(ExcelModel):
    # Static fields - always present in Excel
    month: str = Column(header="Month")
    curator: str = Column(header="Curator")
    manager: str = Column(header="Manager")
    object_title: str = Column(header="Object")

    # Dynamic characteristics - detected from Excel headers at runtime
    characteristics: dict = DynamicColumn(
        getter=lambda inst: {"Sales": inst.monthly_sales, "Priority": inst.priority},
        getter_cell_color=lambda val, row, col, idx: CellStyle(fill_color=Colors.CHANGED) if val and int(val) > 100 else None,
        type_getter=type_dynamic,
    )

# Read forecasts with dynamic characteristics
forecasts = ForecastModel.from_excel(
    "forecasts.xlsx",
    dynamic_columns=True  # Enable dynamic column detection
)

# Each forecast now contains:
# - Static fields: month, curator, manager, object_title
# - characteristics dict with dynamically detected columns
# Example: {"Sales Volume": "1000", "Priority": "High", "Region": "North"}

Use Case: Admin-Configurable Characteristics

This pattern is useful in systems like:

• Forecast Management: Administrators add new forecast characteristics (e.g., “Sales Volume”, “Priority”, “Region”) via a web interface

• Product Catalogs: Dynamic product attributes that vary by category

• Survey Data: Questions that can be added or modified by administrators

• Configuration Import: Flexible configuration files with optional fields

Excel file example:

Month	Curator	Manager	Object	Sales Volume	Priority	Region
2024-01	John	Alice	Bldg A	1000	High	North
2024-02	John	Bob	Bldg B	1500	Medium	South

The last three columns (Sales Volume, Priority, Region) are dynamic and will be captured in the characteristics dictionary.

Column Validators

You can attach custom validation functions to any column using the validator parameter. Validators are called during .from_excel() to ensure data integrity:

from serializable_excel import ExcelModel, Column

def validate_email(value: str) -> str:
    """Validate email format"""
    if value and '@' not in value:
        raise ValueError(f"Invalid email format: {value}")
    return value.strip().lower()

def validate_age(value: int) -> int:
    """Validate age is within reasonable range"""
    if value < 0 or value > 150:
        raise ValueError(f"Age must be between 0 and 150, got: {value}")
    return value

class UserModel(ExcelModel):
    name: str = Column(header="Name")
    age: int = Column(header="Age", validator=validate_age)
    email: str = Column(header="Email", validator=validate_email)

Column Getters

When exporting to Excel using .to_excel(), you may need to extract values from complex database models. Use the getter parameter to define how values are retrieved:

from serializable_excel import ExcelModel, Column

def get_curator_name(model) -> str:
    """Extract curator name from relationship"""
    return model.curator.full_name if model.curator else ""

def get_object_title(model) -> str:
    """Extract object title, fallback to manual title if object is None"""
    if model.object:
        return model.object.title
    return model.object_title or ""

class ForecastExportModel(ExcelModel):
    month: str = Column(header="Month")
    curator: str = Column(header="Curator", getter=get_curator_name)
    manager: str = Column(header="Manager", getter=lambda m: m.manager.full_name)
    object_title: str = Column(header="Object", getter=get_object_title)

# Export database models to Excel
forecasts = db.query(Forecast).all()
ForecastExportModel.to_excel(forecasts, "export.xlsx")

Dynamic Column Validators

For dynamic columns, you can pass a validator function or a dictionary of validators per column name. The validator receives both the column name and value:

Single Validator Function

Using a single validator function for all dynamic columns:

from serializable_excel import ExcelModel, Column, DynamicColumn

def validate_dynamic_value(column_name: str, value: str, value_type: str) -> str:
    """Validate dynamic column value based on expected type"""
    if value_type == 'INTEGER':
        try:
            int(value)
        except ValueError:
            raise ValueError(f"Column '{column_name}' expects integer, got: {value}")
    return value

class ForecastModel(ExcelModel):
    month: str = Column(header="Month")
    characteristics: dict = DynamicColumn(validator=validate_dynamic_value)

Per-Column Validators

Or using a dictionary of validators per column name:

from serializable_excel import ExcelModel, Column, DynamicColumn

validators = {
    "Sales Volume": lambda name, val: int(val),  # Convert to int
    "Priority": lambda name, val: val if val in ["High", "Medium", "Low"]
                                  else (_ for _ in ()).throw(ValueError(f"Invalid priority: {val}")),
}

class ForecastModelWithTypedValidators(ExcelModel):
    month: str = Column(header="Month")
    characteristics: dict = DynamicColumn(validators=validators)

Validating with Database Metadata

For advanced scenarios where dynamic column types are stored in a database, you can build validators at runtime:

from serializable_excel import ExcelModel, Column, DynamicColumn

# Fetch characteristic definitions from database
characteristics = db.query(ForecastCharacteristic).all()

# Build validators based on database metadata
def build_validators(characteristics):
    validators = {}
    for char in characteristics:
        if char.value_type == ForecastValueTypeEnum.INTEGER:
            validators[char.title] = lambda name, val: int(val) if val else None
        else:
            validators[char.title] = lambda name, val: str(val) if val else ""
    return validators

class ForecastModel(ExcelModel):
    month: str = Column(header="Month")
    curator: str = Column(header="Curator")
    manager: str = Column(header="Manager")
    object_title: str = Column(header="Object")
    characteristics: dict = DynamicColumn(validators=build_validators(characteristics))

# Now when reading from Excel, each dynamic column is validated
# according to its type defined in the database
forecasts = ForecastModel.from_excel("forecasts.xlsx", dynamic_columns=True)

Cell Styling

SerializableExcel allows you to conditionally style Excel cells based on cell values and row data. This is useful for highlighting changes, errors, or important information in exported Excel files.

How Cell Styling Works

1. Define a style function: Create a function that receives cell context and returns a CellStyle object

2. Attach to column: Use getter_cell_color parameter in Column() or DynamicColumn()

3. Automatic application: Styles are automatically applied when exporting to Excel

Style Function Signature

The style function receives four parameters:

def style_function(cell_value, row_data, column_name, row_index):
    """
    Args:
        cell_value: Value of the current cell
        row_data: Dict[str, Any] with all values in the row {header: value}
        column_name: Header name of the current column
        row_index: Row index (0-based, data rows only)
    Returns:
        CellStyle or None (no styling)
    """
    # Your styling logic here
    return CellStyle(fill_color=Colors.CHANGED)

Example: Highlighting Changes

Highlight cells based on value comparison with previous data:

from serializable_excel import ExcelModel, Column, CellStyle, Colors

def highlight_changes(cell_value, row_data, column_name, row_index):
    """Highlight if value changed from previous export"""
    # Get previous value from database or cache
    user_id = row_data.get("ID")
    old_value = get_previous_value(user_id, column_name)

    if old_value is None:
        return CellStyle(fill_color=Colors.NEW)  # New entry
    if old_value == cell_value:
        return CellStyle(fill_color=Colors.UNCHANGED)  # Unchanged
    return CellStyle(fill_color=Colors.CHANGED)  # Changed

class UserModel(ExcelModel):
    id: int = Column(header="ID")
    name: str = Column(header="Name", getter_cell_color=highlight_changes)
    age: int = Column(header="Age", getter_cell_color=highlight_changes)

UserModel.to_excel(users, "output.xlsx")

Example: Conditional Formatting

Apply different styles based on cell values:

from serializable_excel import ExcelModel, Column, CellStyle, Colors

def highlight_age(cell_value, row_data, column_name, row_index):
    """Highlight age based on value"""
    if cell_value and cell_value > 30:
        return CellStyle(fill_color=Colors.WARNING, font_bold=True)
    return CellStyle(fill_color=Colors.UNCHANGED)

def highlight_email(cell_value, row_data, column_name, row_index):
    """Highlight email if contains 'example'"""
    if cell_value and "example" in cell_value:
        return CellStyle(fill_color=Colors.INFO, font_italic=True)
    return None

class UserModel(ExcelModel):
    name: str = Column(header="Name")
    age: int = Column(header="Age", getter_cell_color=highlight_age)
    email: str = Column(header="Email", getter_cell_color=highlight_email)

Dynamic Column Styling

You can also style dynamic columns:

from serializable_excel import ExcelModel, Column, DynamicColumn, CellStyle, Colors

def highlight_dynamic(cell_value, row_data, column_name, row_index):
    """Highlight dynamic columns with high values"""
    if cell_value and str(cell_value).isdigit() and int(cell_value) > 100:
        return CellStyle(fill_color=Colors.CHANGED, font_bold=True)
    return None

class ForecastModel(ExcelModel):
    month: str = Column(header="Month")
    characteristics: dict = DynamicColumn(getter_cell_color=highlight_dynamic)

# Or use per-column styling
style_getters = {
    "Sales Volume": lambda val, row, col, idx: CellStyle(fill_color=Colors.CHANGED) if val and int(val) > 100 else None,
    "Priority": lambda val, row, col, idx: CellStyle(font_bold=True) if val == "High" else None,
}
class ForecastModel(ExcelModel):
    characteristics: dict = DynamicColumn(getters_cell_color=style_getters)

Available Colors

Use predefined colors from Colors class:

• Colors.CHANGED - Yellow for changed values

• Colors.UNCHANGED - Light green for unchanged values

• Colors.ERROR - Light red for errors

• Colors.WARNING - Orange for warnings

• Colors.INFO - Light blue for information

• Colors.NEW - Pale green for new entries

Or use custom HEX colors:

style = CellStyle(fill_color="FF0000")  # Red background
style = CellStyle(font_color="0000FF")  # Blue text

Column Ordering

You can control the order of columns in exported Excel files using the column_order and dynamic_column_order parameters in to_excel(). This is useful when you need specific column arrangements, such as placing dynamic characteristics before static fields.

Static Column Ordering

For static columns, you can provide either a function or a dictionary:

Using a Function

from typing import Optional
from serializable_excel import ExcelModel, Column

class UserModel(ExcelModel):
    name: str = Column(header="Name")
    age: int = Column(header="Age")
    email: str = Column(header="Email")

def column_order(header: str) -> Optional[int]:
    """Specify order: Email first, then Name, then Age"""
    order_map = {"Email": 1, "Name": 2, "Age": 3}
    return order_map.get(header)

users = [UserModel(name="Alice", age=25, email="alice@test.com")]
UserModel.to_excel(users, "output.xlsx", column_order=column_order)
# Columns will be: Email, Name, Age

Using a Dictionary

column_order_dict = {"Email": 1, "Name": 2, "Age": 3}
UserModel.to_excel(users, "output.xlsx", column_order=column_order_dict)

Dynamic Column Ordering

For dynamic columns, provide a function that receives a dictionary of column names and their initial order numbers, and returns a normalized dictionary:

from typing import Dict
from serializable_excel import ExcelModel, Column, DynamicColumn

class ForecastModel(ExcelModel):
    month: str = Column(header="Month")
    manager: str = Column(header="Manager")
    characteristics: dict = DynamicColumn()

def dynamic_order(orders: Dict[str, int]) -> Dict[str, int]:
    """
    Normalize dynamic column orders.
    Input: {"Sales": 1, "Priority": 5, "Status": 10}
    Output: {"Sales": 1, "Priority": 2, "Status": 3} (normalized, no gaps)
    """
    sorted_items = sorted(orders.items(), key=lambda x: x[1])
    return {title: idx + 1 for idx, (title, _) in enumerate(sorted_items)}

forecasts = [
    ForecastModel(
        month="2024-01",
        manager="Alice",
        characteristics={"Sales": 150, "Priority": "High", "Status": "Active"}
    )
]

ForecastModel.to_excel(
    forecasts,
    "output.xlsx",
    dynamic_column_order=dynamic_order
)

Using Database Order Values

When dynamic columns have order values stored in a database (e.g., order_layer field), you can use them:

def dynamic_order(orders: Dict[str, int]) -> Dict[str, int]:
    """
    Use order_layer from database to determine column order.
    Normalize orders to remove gaps (1, 5, 10 -> 1, 2, 3).
    """
    # Fetch order_layer values from database
    initial_orders = {}
    for title in orders:
        char = db.query(ForecastCharacteristic).filter_by(title=title).first()
        if char:
            initial_orders[title] = char.order_layer

    # Update orders dict with database values
    for key in orders:
        if key in initial_orders:
            orders[key] = initial_orders[key]

    # Normalize: sort by order and create sequential numbers
    sorted_items = sorted(orders.items(), key=lambda x: x[1])
    return {title: idx + 1 for idx, (title, _) in enumerate(sorted_items)}

Mixed Static and Dynamic Ordering

When both static and dynamic columns have order numbers, they are normalized together and sorted. Columns with the same order number are sorted alphabetically by name:

def static_order(header: str) -> Optional[int]:
    """Manager first, then Month"""
    order_map = {"Manager": 1, "Month": 2}
    return order_map.get(header)

def dynamic_order(orders: Dict[str, int]) -> Dict[str, int]:
    """Priority first, then Sales"""
    initial_orders = {"Priority": 1, "Sales": 5}
    for key in orders:
        if key in initial_orders:
            orders[key] = initial_orders[key]
    sorted_items = sorted(orders.items(), key=lambda x: x[1])
    return {title: idx + 1 for idx, (title, _) in enumerate(sorted_items)}

ForecastModel.to_excel(
    forecasts,
    "output.xlsx",
    column_order=static_order,
    dynamic_column_order=dynamic_order
)
# All columns with order are normalized together:
# Manager (1), Priority (1) -> Manager, Priority (alphabetically)
# Month (2), Sales (5) -> Month (2), Sales (3 after normalization)

Order Normalization

Order numbers are automatically normalized to remove gaps. For example, if you specify orders 1, 5, and 10, they will be normalized to 1, 2, and 3. This ensures columns are placed sequentially without empty spaces.

Columns without specified order are placed at the end, maintaining their original order (static columns first, then dynamic columns).

Column Parameters Summary

Next Steps

• API Reference - Full API reference

• Examples - More practical examples