xlsxtpl

https://github.com/zhangyu836/python-xlsx-template
Admin
xlsxtpl is a Python module that generates XLSX files from XLSX templates using openpyxl and Jinja2...
Tokens:5,015
Snippets:34
Trust Score:6.8
License:MIT
Update:6 months ago
Show doc for...
Tokens:
Raw
### Complete Example: Invoice Generation

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

A comprehensive example showing how to generate an invoice using xlsxtpl, including loops, type-preserving variables, and multiple sheets.

```APIDOC
## Complete Example: Invoice Generation

A comprehensive example demonstrating template rendering with loops, type-preserving variables, and multiple sheets.

### Request Example
```python
from datetime import datetime
from xlsxtpl.writerx import BookWriter

class InvoiceItem:
    def __init__(self, name, category, price, count):
        self.name = name
        self.category = category
        self.price = price
        self.count = count
        self.date = datetime.now()

def generate_invoice():
    writer = BookWriter('invoice_template.xlsx')
    writer.set_jinja_globals(dir=dir, getattr=getattr)

    # Create line items
    items = [
        InvoiceItem("Product A", "Electronics", 299.99, 2),
        InvoiceItem("Product B", "Accessories", 49.99, 5),
        InvoiceItem("Product C", "Software", 199.00, 1),
    ]

    # Customer data payload
    customer_invoice = {
        'name': 'Acme Corporation',
        'address': '100 Business Park Drive',
        'date': datetime.now(),
        'items': items,
        'rows': [[item.name, item.price, item.count] for item in items],
        'sheet_name': 'Invoice',
        'tpl_index': 0
    }

    # Render and save
    writer.render_sheet(customer_invoice)
    writer.save('generated_invoice.xlsx')

if __name__ == "__main__":
    generate_invoice()
```
```

--------------------------------

### Install xlsxtpl using pip

Source: https://github.com/zhangyu836/python-xlsx-template/blob/master/README.md

Install the xlsxtpl package using pip. This command downloads and installs the latest version of the library and its dependencies.

```shell
pip install xlsxtpl
```

--------------------------------

### Working with Images

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Demonstrates how to embed images into Excel templates using the xlsxtpl library, requiring Pillow to be installed.

```APIDOC
## Working with Images

xlsxtpl supports embedding images in templates when PIL/Pillow is installed. Images can be passed as file paths or PIL Image objects in the payload data.

### Prerequisites
- Pillow library must be installed (`pip install Pillow`).

### Request Body Fields for Images
- **image_field_name** (string or PIL.Image.Image) - Required - The name of the field in the payload that holds the image data. This can be a file path to the image or a PIL Image object.

### Request Example
```python
import os
from datetime import datetime
from xlsxtpl.writerx import BookWriter

try:
    from PIL import Image as PILImage
    has_pillow = True
except ImportError:
    has_pillow = False
    print('Pillow is required for image support')

writer = BookWriter('template_with_images.xlsx')

payload = {
    'name': 'Product Catalog',
    'date': datetime.now(),
}

if has_pillow:
    # Pass image as file path
    payload['logo'] = '/path/to/logo.jpg'

    # Or pass as PIL Image object
    product_image = PILImage.open('/path/to/product.jpg')
    payload['product_img'] = product_image

writer.render_sheet(payload, 'Catalog')
writer.save('catalog_with_images.xlsx')
```
```

--------------------------------

### Embed images in Excel templates

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Pass file paths or PIL Image objects within the payload dictionary to render images. Requires the Pillow library to be installed.

```python
import os
from datetime import datetime
from xlsxtpl.writerx import BookWriter

try:
    from PIL import Image as PILImage
    has_pillow = True
except ImportError:
    has_pillow = False
    print('Pillow is required for image support')

writer = BookWriter('template_with_images.xlsx')

payload = {
    'name': 'Product Catalog',
    'date': datetime.now(),
}

if has_pillow:
    # Pass image as file path
    payload['logo'] = '/path/to/logo.jpg'

    # Or pass as PIL Image object
    product_image = PILImage.open('/path/to/product.jpg')
    payload['product_img'] = product_image

writer.render_sheet(payload, 'Catalog')
writer.save('catalog_with_images.xlsx')
```

--------------------------------

### Initialize BookWriter and set Jinja2 globals

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Initialize the BookWriter with a template file and optionally add custom functions to the Jinja2 environment for use in templates.

```python
from xlsxtpl.writerx import BookWriter

# Initialize BookWriter with a template file
writer = BookWriter('template.xlsx')

# Optionally add custom functions to the Jinja2 environment
writer.jinja_env.globals.update(dir=dir, getattr=getattr)
# Or use the alternative method:
writer.set_jinja_globals(dir=dir, getattr=getattr)
```

--------------------------------

### BookWriter Class Initialization

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Initializes the BookWriter instance with a template file and configures the Jinja2 environment.

```APIDOC
## BookWriter Initialization

### Description
Initializes the BookWriter class to read an XLSX template file and optionally configure Jinja2 globals.

### Parameters
- **template_file** (string) - Required - Path to the .xlsx template file.

### Request Example
from xlsxtpl.writerx import BookWriter
writer = BookWriter('template.xlsx')
writer.set_jinja_globals(dir=dir, getattr=getattr)
```

--------------------------------

### render_book2() Method

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Renders multiple worksheets using an alternative payload structure with nested context.

```APIDOC
## render_book2()

### Description
Renders multiple worksheets where template configuration is separated from the data context using a 'ctx' key.

### Parameters
- **payloads** (list) - Required - List of dictionaries containing configuration (tpl_name, tpl_idx, sheet_name) and a 'ctx' dictionary for data.
```

--------------------------------

### render_book() Method

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Renders multiple worksheets from a list of payloads.

```APIDOC
## render_book()

### Description
Renders multiple worksheets from a list of payloads, allowing each sheet to specify its own template source and name.

### Parameters
- **payloads** (list) - Required - List of dictionaries containing data and configuration (tpl_idx, tpl_name, sheet_name).
```

--------------------------------

### Generate invoices with complex data

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Demonstrates rendering a template using custom objects and list comprehensions for row data. Use set_jinja_globals to expose Python functions to the template.

```python
from datetime import datetime
from xlsxtpl.writerx import BookWriter

class InvoiceItem:
    def __init__(self, name, category, price, count):
        self.name = name
        self.category = category
        self.price = price
        self.count = count
        self.date = datetime.now()

def generate_invoice():
    writer = BookWriter('invoice_template.xlsx')
    writer.set_jinja_globals(dir=dir, getattr=getattr)

    # Create line items
    items = [
        InvoiceItem("Product A", "Electronics", 299.99, 2),
        InvoiceItem("Product B", "Accessories", 49.99, 5),
        InvoiceItem("Product C", "Software", 199.00, 1),
    ]

    # Customer data payload
    customer_invoice = {
        'name': 'Acme Corporation',
        'address': '100 Business Park Drive',
        'date': datetime.now(),
        'items': items,
        'rows': [[item.name, item.price, item.count] for item in items],
        'sheet_name': 'Invoice',
        'tpl_index': 0
    }

    # Render and save
    writer.render_sheet(customer_invoice)
    writer.save('generated_invoice.xlsx')

if __name__ == "__main__":
    generate_invoice()
```

--------------------------------

### Template Syntax with Jinja2 Custom Tags

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Explains the custom Jinja2 tags used by xlsxtpl for Excel-specific features like type preservation and loops.

```APIDOC
## Template Syntax with Jinja2 Custom Tags

xlsxtpl uses Jinja2 templating with custom tags for Excel-specific features. The `xv` tag preserves variable types (numbers stay as numbers), while standard `{{ }}` syntax converts to strings. Use `{% for %}` loops to generate dynamic rows.

### Custom Tags
- **`{{ variable }}`**: Standard Jinja2 variable substitution. Converts values to strings.
- **`{%xv variable%}`**: Type-preserving variable substitution. Preserves original data types (numbers, dates, etc.).
- **`{% sec N %}` ... `{% endsec %}`**: Defines sections within a cell for rich text formatting.
- **`{% for item in items %}` ... `{% endfor %}`**: Jinja2 loop for iterating over collections.
- **`{% row N %}`**: Marks the beginning of a new row for dynamic row generation.
- **`{% cell N %}` ... `{% endcell %}`**: Defines a cell within a dynamically generated row.

### Template Example
```jinja2
{# In your Excel template cells, use these patterns: #}

{# Simple variable substitution (converts to string) #}
{{name}}
{{address}}

{# Type-preserving variable (keeps number/date types) #}
{%xv amount%}
{%xv date%}

{# Rich text with multiple sections in one cell #}
{% sec 0 %}{{name}}{% endsec %}{% sec 1 %}{{address}}{% endsec %}

{# Loop to generate multiple rows #}
{% for item in items %}
{% row 64 %}
{% cell 65 %}{{item.name}}{% endcell %}
{% cell 66 %}{{item.category}}{% endcell %}
{% cell 67 %}{%xv item.price%}{% endcell %}
{% cell 68 %}{%xv item.count%}{% endcell %}
{% endfor %}
```
```

--------------------------------

### Render worksheets with nested context using render_book2

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Use render_book2 for an alternative payload structure where template context is nested under a 'ctx' key. This separates sheet configuration from the data context.

```python
from datetime import datetime
from xlsxtpl.writerx import BookWriter

writer = BookWriter('template.xlsx')

# Prepare context data
context1 = {
    'name': 'Company Report',
    'address': 'Corporate HQ',
    'date': datetime.now(),
    'rows': [['Q1', '1000'], ['Q2', '1500'], ['Q3', '2000']]
}

context2 = {
    'name': 'Branch Report',
    'address': 'Regional Office',
    'date': datetime.now(),
    'rows': [['Q1', '500'], ['Q2', '750'], ['Q3', '900']]
}

# Create payloads with nested context structure
payloads = [
    {'tpl_name': 'cn', 'sheet_name': 'Chinese Report', 'ctx': context1},
    {'tpl_name': 'en', 'sheet_name': 'English Report', 'ctx': context2},
    {'tpl_idx': 2, 'ctx': context2}  # Use template by index
]

# Render using the alternative method
writer.render_book2(payloads=payloads)
writer.save('reports.xlsx')
```

--------------------------------

### Render multiple worksheets with multiple payloads

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Render multiple worksheets from a list of payloads. Each payload can specify its template index or name, and output sheet name. This is useful for generating multi-sheet workbooks.

```python
from datetime import datetime
from xlsxtpl.writerx import BookWriter

writer = BookWriter('template.xlsx')

# Define multiple payloads for different sheets
person1 = {
    'name': 'Alice Smith',
    'address': '456 Oak Avenue',
    'date': datetime.now(),
    'rows': [['A1', 'B1', 'C1'], ['A2', 'B2', 'C2']],
    'tpl_idx': 0  # Use first template sheet
}

person2 = {
    'name': 'Bob Johnson',
    'address': '789 Pine Road',
    'date': datetime.now(),
    'rows': [['X1', 'Y1', 'Z1'], ['X2', 'Y2', 'Z2']],
    'tpl_name': 'en',  # Use template by name
    'sheet_name': 'English Form'  # Custom output sheet name
}

# Render all sheets at once
payloads = [person1, person2]
writer.render_book(payloads=payloads)

# Save the multi-sheet workbook
writer.save('multi_sheet_output.xlsx')
```

--------------------------------

### render_sheets() Method

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Renders multiple sheets to the workbook, allowing for multiple calls to append additional sheets.

```APIDOC
## render_sheets() Method

The render_sheets() method renders multiple sheets and can be called multiple times to append additional sheets to the workbook. Each call adds new sheets based on the provided payloads.

### Parameters
#### Request Body
- **payloads** (list) - Required - A list of dictionaries, where each dictionary represents a sheet to be rendered. Each dictionary should contain keys like 'name', 'date', 'sheet_name', and 'tpl_index'.

### Request Example
```python
from datetime import datetime
from xlsxtpl.writerx import BookWriter

writer = BookWriter('template.xlsx')

# First batch of sheets
batch1 = [
    {'name': 'Report A', 'date': datetime.now(), 'sheet_name': 'Sheet A', 'tpl_index': 0},
    {'name': 'Report B', 'date': datetime.now(), 'sheet_name': 'Sheet B', 'tpl_index': 1}
]
writer.render_sheets(payloads=batch1)

# Append more sheets with another call
batch2 = [
    {'name': 'Report C', 'date': datetime.now(), 'sheet_name': 'Sheet C', 'tpl_index': 0}
]
writer.render_sheets(payloads=batch2)

# All sheets are combined in the output
writer.save('combined_output.xlsx')
```

### Response
This method does not return a value directly but modifies the workbook object in place.
```

--------------------------------

### render_sheet() Method

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Renders a single worksheet from a template using a provided data payload.

```APIDOC
## render_sheet()

### Description
Renders a single worksheet using provided payload data.

### Parameters
- **payload** (dict) - Required - Dictionary containing template variables.
- **sheet_name** (string) - Optional - Name for the output sheet.
- **tpl_index** (int) - Optional - Index of the template sheet to use.
```

--------------------------------

### Apply Jinja2 template syntax in Excel cells

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Use standard Jinja2 tags for string substitution and custom 'xv' tags to preserve numeric or date types. The 'row' and 'cell' tags are used to define dynamic table structures.

```jinja2
{# In your Excel template cells, use these patterns: #}

{# Simple variable substitution (converts to string) #}
{{name}}
{{address}}

{# Type-preserving variable (keeps number/date types) #}
{%xv amount%}
{%xv date%}

{# Rich text with multiple sections in one cell #}
{% sec 0 %}{{name}}{% endsec %}{% sec 1 %}{{address}}{% endsec %}

{# Loop to generate multiple rows #}
{% for item in items %}
{% row 64 %}
{% cell 65 %}{{item.name}}{% endcell %}
{% cell 66 %}{{item.category}}{% endcell %}
{% cell 67 %}{%xv item.price%}{% endcell %}
{% cell 68 %}{%xv item.count%}{% endcell %}
{% endfor %}
```

--------------------------------

### Render multiple sheets with render_sheets()

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Use render_sheets to append multiple sheets to a workbook by passing a list of payloads. Each payload must specify the target sheet name and template index.

```python
from datetime import datetime
from xlsxtpl.writerx import BookWriter

writer = BookWriter('template.xlsx')

# First batch of sheets
batch1 = [
    {'name': 'Report A', 'date': datetime.now(), 'sheet_name': 'Sheet A', 'tpl_index': 0},
    {'name': 'Report B', 'date': datetime.now(), 'sheet_name': 'Sheet B', 'tpl_index': 1}
]
writer.render_sheets(payloads=batch1)

# Append more sheets with another call
batch2 = [
    {'name': 'Report C', 'date': datetime.now(), 'sheet_name': 'Sheet C', 'tpl_index': 0}
]
writer.render_sheets(payloads=batch2)

# All sheets are combined in the output
writer.save('combined_output.xlsx')
```

--------------------------------

### Jinja2 Template Syntax for xlsx-template

Source: https://github.com/zhangyu836/python-xlsx-template/blob/master/README.md

This syntax is used within XLSX files to define dynamic content and structure. It leverages Jinja2 templating with custom tags like row, cell, sec, and xv for cell manipulation and variable insertion.

```jinja2
...
...
{% row 45 %}
{% cell 46 %}{% endcell %}
{% cell 47 %}{% endcell %}
{% cell 48 %}{{address}}  {%xv v%}{% endcell %}
{% cell 49 %}{% endcell %}
{% cell 50 %}{% endcell %}
{% cell 51 %}{% endcell %}
{% cell 52 %}{% endcell %}
{% cell 53 %}{% endcell %}
{% row 54 %}
{% cell 55 %}{% endcell %}
{% cell 56 %}{% sec 0 %}{{name}}{% endsec %}{% sec 1 %}{{address}}{% endsec %}{% endcell %}
...
...
{% for item in items %}
{% row 64 %}
{% cell 65 %}{% endcell %}
{% cell 66 %}{% endcell %}
{% cell 67 %}{% endcell %}
{% cell 68 %}{% endcell %}
{% cell 69 %}{% endcell %}
{% cell 70 %}{% endcell %}
{% cell 71 %}{% endcell %}
{% cell 72 %}{% endcell %}
{% endfor %}
...
...
```

--------------------------------

### Render a single worksheet with payload data

Source: https://context7.com/zhangyu836/python-xlsx-template/llms.txt

Render a single worksheet using provided payload data. The payload dictionary contains template variables. You can specify the sheet name and template index or name.

```python
from datetime import datetime
from xlsxtpl.writerx import BookWriter

writer = BookWriter('template.xlsx')

# Prepare data payload with template variables
payload = {
    'name': 'John Doe',
    'address': '123 Main Street',
    'date': datetime.now(),
    'items': [
        {'product': 'Widget A', 'price': 10.99, 'quantity': 5},
        {'product': 'Widget B', 'price': 25.50, 'quantity': 2},
    ]
}

# Render a single sheet with payload data
# Arguments: payload dict, sheet_name (optional), tpl_index (optional)
writer.render_sheet(payload, 'Invoice', 0)

# Using template name instead of index
payload['tpl_name'] = 'invoice_template'
payload['sheet_name'] = 'Monthly Invoice'
writer.render_sheet(payload)

# Save the result
writer.save('output.xlsx')
```
Tokens:
Raw