1. Overview and Key Features
This module allows you to define Excel templates containing document variables or Jinja expressions. The system processes these templates to replace variables with real-time data and generates a downloadable Excel file.
Custom Layouts: Create professional documents that match your specific branding or regulatory requirements.
Logical Control: Use Jinja expressions to handle complex logic, such as conditional text or data formatting.
Automated Lists: Automatically expand line items based on the actual data in the document.
2. Template Syntax and Variables
The system uses standard Jinja2-style syntax to identify dynamic content.
2.1 Basic Variables
Main Document: Use doc to access primary fields. Example: {{ doc.customer }}.
Table Rows: Use row to access fields within a child table. Example: {{ row.item_code }}.
Mixed Content: You can combine text and variables in a single cell, such as Total Amount: {{ doc.total }}.
2.2 Formatting and Logic
Conditional Blocks: Show or hide content using {% if %} statements.
Example: {% if doc.currency == “USD” %} USD Account: 32001 {% else %} Settlement Account: Please configure other account
{% endif %}
Numeric Precision: Control decimals using the format filter: {{ “%.2f”|format(row.qty) }} for 2 decimal places.
System Functions: Access built-in functions like {{ doc.money_in_words(doc.total) }} for currency in words.
3. Handling Child Tables (Line Items)
To export lists such as “Items” or “Taxes,” you must define the mapping in the template’s Column A .
Row Definition: Select the row where you want the line items to appear.
Mapping Comment: Add an Excel comment (Note) in Column A with the format row=doc.items.
Note: Ensure the comment text contains only the mapping string and no other characters .
Dynamic Expansion: Define your variables (like {{ row.qty }}) in that row. The system will automatically insert new rows for every item in the document and shift the remaining content down.
4. Advanced Capabilities
Image Rendering: If a field contains a File URL (e.g., .jpg, .png), the system will automatically download and embed the image into the corresponding cell.
Vertical Merging: To merge cells vertically for identical values, add the comment merge_v:true to the column’s title cell.
Dynamic Formatting: You can use Server Scripts to apply “Pre-print” logic, such as changing row background colors or merging cells based on data conditions using instructions like formatter:merge_h(2,10);background_color(grey).
5. Setup and Implementation
Create Template: Design your .xlsx file with the variables and comments mentioned above.
Upload to System: Navigate to the “Excel Export Template” list and create a new record.
Set Filters: (Optional) Define conditions so the template only appears for specific customers or document statuses.
Execute Export: Open the target document and select your template from the “Export Excel” dropdown menu.
repository https://gitee.com/yuzelin/zelin_export_excel_template
bench get-app https://gitee.com/yuzelin/zelin_export_excel_template.git
bench install-app zelin_export_excel_template