# 单据导出到Excel模板

**Repository Path**: yuzelin/zelin_export_excel_template

## Basic Information

- **Project Name**: 单据导出到Excel模板
- **Description**: 将单据导出到Excel模板，部分取代标准的打印格式功能，
- **Primary Language**: Unknown
- **License**: AGPL-3.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 9
- **Forks**: 32
- **Created**: 2024-07-30
- **Last Updated**: 2025-09-14

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## 单据导出到Excel模板

单据导出到Excel模板，一定程度上可取代标准打印功能

#### License

agpl-3.0

#### 使用方法

先决条件

进入 bench 工作台目录；

1.新安装

1.1、获取对应版本APP

bench get-app https://gitee.com/yuzelin/zelin_export_excel_template.git

1.2、安装APP(有多个站点且未设默认站点的请加--site参数）

bench install-app zelin_export_excel_template

升级（之前安装过未拆分前版本的请谨慎更新）

2.1、bench update 命令

bench update --apps zelin_export_excel_template --pull --reset

3、安装需要的依赖

3.1、安装 LibreOffice（用于PDF导出）
```bash
sudo apt-get update
sudo apt-get install -y libreoffice-writer libreoffice-calc
```

单据Excel模板导出功能

概述
类似打印格式，可为每个单据定义带字段变量与jinja表达式的excel模板，在单据表单界面导出当前单据内容到excel模板或pdf。

场景1：基于销售出库单一键导出excel版装箱单，报关单，合同，形式发票与申报要素，交给货代去录入海关系统作报关申请。无此功能时，需手工逐单逐字段复制粘贴到对应excel模板，耗时耗力，容易错漏。

场景2：发给供应商采购订单或给客户销售出货单，除给PDF或纸单外，可以给excel电子版，方便对方批量导入或接口程序自动录入系统。

场景3：从报价单提取指定字段的物料明细到excel，用于向供应商询价
系统明细表可导出，但包括了全部字段以及不需要的表头，导出字段不可定制
可自定义报表选择需导出的字段，使用过滤条件筛选，再点导出，还需要将导出数据复制粘贴到自己的excel模板，操作繁琐

实现原理
上传为目标单据类型定义的带字段变量与Jinja表达式的Excel模板到服务器，单据表单界面新增导出excel菜单按钮，代码加载excel模板，逐个excel单元格解析文本串中包含字段与jinja表达式变量，替换变量值为当前单据字段值或Jinja表达式结果，下载成excel文件。

具体操作

一、维护Excel模板

1.变量处理
(1)当前单据变量为doc
(2)变量用双花括号{{}}表示
①使用{{doc.字段名}}引用普通字段(非明细表), 如{{doc.customer}} 当前单据客户字段
②支持jinja表达式变量：即可返回文本的一条语句python脚本
用途	函数	范例
显示链接的关联字段(excel vlookup)	frappe.db.get_value	{{frappe.db.get_value("Address",doc.shipping_address ,"country")}}
根据地址编号返回地址表中的国家
翻译为打印语言	_()	{{_(row.item_name)}}  翻译明细行的物料名称
金额大写	doc.money_in_words	{{doc.money_in_words(doc.total)}}
单据制单人	doc.get_owner_username	{{doc.get_owner_username()}}
单据审批人	doc.get_submit_username	{{doc.get_submit_username()}}
明细表数量、金额字段汇总小计	frappe.db.get_value	{{frappe.db.get_value('Delivery Note Item', {'parent':doc.name},'sum(total_weight)')}}
基于明细表总重量计算整个单据总重量

(3)同一单元格式支持正常文本与变量混合，如客户名称: {{doc.customer_name}}, 
(4)一个文本串中可包含多个变量

2.明细表处理
(1)需在输出明细行内容的第1列(A列)填写如下格式的批注：row=doc.items, 其中row=是固定内容，代码通过这个固定文本为明细行变量赋值（绑定子表字段)，doc.items表示该明细行对应的子表字段名
(2)支持批注 merge_v:true,竖向合并单元格
(3)支持批注 style_cond:len>3:bold,size=15，基于单元格长度，字体加粗和设置字体大小
(4)当前明细行对象变量为row, 如{{row.item_code}}表示明细行的物料编号字段
(5)模板定义时明细行输出内容只需保留一行，代码会根据单据实际明细行自动新增行，如单据有5个明细行，最终输出结果会在原模板一行明细定义基础上再新增4行，底下所有行自动下移

3.特殊处理
(1)引用不存在字段，excel文件输出原变量字符串，系统错误日志有详细出错信息
(2)空值字段，返回空字符串
(3)系统内地址信息是通过地址模板定义带换行符的多行文本，Excel模板中相关单元格格式需设为自动换行(可同时合并上下单元格)
(4)图片字段，如订单明细中的image字段，可以单元格写字段变量{{row.image}},代码会将以.png,.jpg,.img结尾的图片加载到绑定的单元格，需注意上传的图片尺寸。
(5)为考虑性能，模板excel文件内容最多支持50列，200行

二、维护单据类型Excel导出模板

单据类型
模板名称
模板文件：上传已定义的Excel模板文件
允许导出PDF: 勾选后表单界面会出现导出pdf下拉菜单
多语言：如果勾选导出Excel时用户需选择语言，同时Excel模板中需调用_()函数翻译标签，类似这样{{_('Item Code')}}
导出文件名: python表达式，默认的导出文件名为{{template_name}}_{{doc.name}}.{{path[-4:]}}，模板名_单据名.文件名后缀，可使用doc，template_name，path(含后缀的文件名)

三、单据导出excel文件

本app也支持word文件模板

#### 25/01/22 feat:(PDF Export)

支持 Excel (.xlsx) 转 PDF，支持 Word (.docx) 转 PDF，保持原文件格式和样式

功能特点：
1. 保持原文件格式和样式
2. 支持多语言模板
3. 支持自定义文件名
4. 支持js表达式过滤适用于当前单据的模板
5. 与Excel导出保持一致的用户体验
6. 支持before_print事件对doc进行输出前预处理

使用方法：
1. 在单据表单界面，每个导出模板都会显示两个按钮：
   - Export Excel：导出Excel格式（导出模板界面有勾选允许导出Excel)
   - Export PDF：导出PDF格式（导出模板界面有勾选允许导出PDF)
2. 如果模板支持多语言，会弹出语言选择对话框
3. 生成的PDF文件会自动下载

Document Excel Template Export Functionality
Overview
Similar to print formats, you can define Excel templates with field variables and Jinja expressions for each document. This allows you to export the current document's content to an Excel template or PDF directly from the document form interface.

Scenario 1: One-click export of packing lists, customs declarations, contracts, proforma invoices, and declaration elements based on a Sales Invoice. This can be provided to freight forwarders for entry into the customs system for customs declaration applications. Without this feature, you would need to manually copy and paste data field by field into the corresponding Excel template, which is time-consuming, labor-intensive, and prone to errors and omissions.

Scenario 2: When sending purchase orders to suppliers or sales orders to customers, in addition to PDF or paper documents, you can provide an electronic Excel version. This facilitates batch import or automated data entry into their systems via interfaces.

Scenario 3: Extracting material details with specified fields from a quotation into Excel for supplier inquiries.
System detail tables can be exported, but they include all fields and unwanted headers. The exported fields are not customizable.
You can customize report fields, select desired fields, filter with conditions, and then export. However, you still need to copy and paste the exported data into your own Excel template, which is a cumbersome operation.

Implementation Principle
Upload an Excel template defined for the target document type, containing field variables and Jinja expressions, to the server. A new "Export Excel" button will appear on the document form interface. The code loads the Excel template, parses text strings within each Excel cell for included fields and Jinja expression variables, and replaces these variables with the current document's field values or Jinja expression results, downloading the output as an Excel file.

Detailed Operations
I. Maintaining Excel Templates
Variable Handling

(1) The current document variable is doc.

(2) Variables are enclosed in double curly braces {{}}.

Use {{doc.field_name}} to reference ordinary fields (non-detail tables), e.g., {{doc.customer}} for the current document's customer field.

Supports Jinja Expressions: Any Python script statement that can return text.
| Use Case                     | Function             | Example                                                              | Description                                          |
| :--------------------------- | :------------------- | :------------------------------------------------------------------- | :--------------------------------------------------- |
| Link to related fields (Excel VLOOKUP) | frappe.db.get_value | {{frappe.db.get_value("Address", doc.shipping_address, "country")}} | Returns the country from the Address table based on the address ID. |
| Translate to print language  | _()                | {{_(row.item_name)}}                                               | Translates the item name of a detail row.            |
| Amount in words              | doc.money_in_words | {{doc.money_in_words(doc.total)}}                                  | Converts the total amount to words.                  |
| Document preparer            | doc.get_owner_username | {{doc.get_owner_username()}}                                       | Gets the username of the document creator.           |
| Document approver            | doc.get_submit_username | {{doc.get_submit_username()}}                                      | Gets the username of the document approver.          |
| Subtotal for quantity/amount in detail table | frappe.db.get_value | {{frappe.db.get_value('Delivery Note Item', {'parent':doc.name},'sum(total_weight)')}} | Calculates the total weight for the entire document based on detail table total weights. |

(3) The same cell can support a mix of normal text and variables, e.g., Customer Name: {{doc.customer_name}}.

(4) A single text string can contain multiple variables.

Detail Table Handling

(1) In the first column (Column A) where detail row content needs to be output, enter a comment in the format: row=doc.items. row= is fixed content; the code uses this fixed text to assign values to detail row variables (binding child table fields). doc.items represents the name of the child table field corresponding to the detail row.

(2) Supports the comment merge_v:true for vertically merging cells.

(3) Supports the comment style_cond:len>3:bold,size=15 for conditional formatting based on cell length, such as bold font and setting font size.

(4) The current detail row object variable is row. For example, {{row.item_code}} refers to the item code field of the detail row.

(5) When defining the template, only one row of detail content needs to be retained. The code will automatically add rows based on the actual detail rows of the document. If the document has 5 detail rows, the final output will add 4 additional rows to the original one-row detail definition in the template, and all subsequent rows will shift down automatically.

Special Handling

(1) If a non-existent field is referenced, the Excel file will output the original variable string. Detailed error information will be available in the system error log.

(2) For empty fields, an empty string will be returned.

(3) Address information within the system is defined as multi-line text with line breaks using address templates. The corresponding cells in the Excel template should be set to "Wrap Text" (you can also merge adjacent cells vertically).

(4) For image fields, such as the image field in order details, you can write the field variable {{row.image}} in the cell. The code will load images ending with .png, .jpg, or .img into the bound cell. Pay attention to the dimensions of the uploaded images.

(5) For performance considerations, Excel template files are limited to a maximum of 50 columns and 200 rows.

II. Maintaining Document Type Excel Export Templates
Document Type: The type of document for which this template is defined.

Template Name: A descriptive name for the template.

Template File: Upload the pre-defined Excel template file.

Allow Export PDF: If checked, an "Export PDF" dropdown menu will appear on the form interface.

Multi-language: If checked, the user will need to select a language during Excel export. The Excel template must also call the _() function for label translation, like {{_('Item Code')}}.

Export Filename: A Python expression. The default export filename is {{template_name}}_{{doc.name}}.{{path[-4:]}}, which translates to template_name_document_name.file_extension. You can use doc, template_name, and path (the filename including the extension).

III. Exporting Excel Files from Documents
This app also supports Word file templates.

25/01/22 feat: (PDF Export)
Supports converting Excel (.xlsx) and Word (.docx) files to PDF, preserving original file format and styles.

Features:

Preserves original file format and styles.

Supports multi-language templates.

Supports custom filenames.

Supports JavaScript expression filtering for templates applicable to the current document.

Consistent user experience with Excel export.

Supports the before_print event for pre-processing the doc object before output.

Usage:

On the document form interface, each export template will display two buttons:

Export Excel: Exports in Excel format (enabled if "Allow Export Excel" is checked in the export template configuration).

Export PDF: Exports in PDF format (enabled if "Allow Export PDF" is checked in the export template configuration).

If the template supports multiple languages, a language selection dialog will pop up.

The generated PDF file will be automatically downloaded.