插入并链接 Odoo 数据¶
可将 Odoo 数据库中的多个元素插入 Odoo 电子表格，即：
每次插入列表、透视表或图表时，都会创建一个 数据源。该数据源将电子表格连接到 Odoo 数据库，每次打开电子表格、重新加载浏览器页面或从菜单栏点击 手动刷新数据时，都会检索最新信息。
插入列表 和 插入数据透视表 使用带有 Odoo 特定的 列表功能 和 数据透视表功能 的公式从数据库检索数据，并可在电子表格中进一步操作。可以修改 插入图表 的某些元素，但不能进行数据操作或计算。
備註
来自不同应用程序和模型的列表、透视表和图表可以插入到同一个电子表格中。
小訣竅
如果您打算使用 全局筛选器 在电子表格或仪表板中动态筛选 Odoo 数据，请不要使用相同的条件在数据库中建立初始列表、透视表或图表。
也有可能：
数据源¶
每次在 Odoo 电子表格中插入 列表, pivot table 或 图表 时，都会创建数据源，连接电子表格和数据库中的相关 模型 ，使电子表格中的数据保持最新。
每个数据源都由可通过 数据 菜单访问的属性定义。数据源由各自的 （数据透视表）、 （列表） 或 （图表） 图标标识，后跟 ID 和名称，例如， (#1)产品销售分析。
点击数据源可在电子表格右侧的窗格中打开相关属性。
小訣竅
右键点击插入的列表或透视表的任意单元格，然后点击 :icon:`ioi-view-list` 查看列表属性 或 :icon:`ioi-view-pivot` 查看枢轴属性，或点击插入的图表右上方的 (菜单) 图标，然后点击 编辑 也可打开属性窗格。
打开特定数据源的属性后，即使在电子表格选项卡之间导航，也会保持打开状态。要关闭属性窗格，请点击窗格右上角的 （关闭） 图标。
備註
删除插入的列表或数据透视表，或删除插入该列表或数据透视表的工作表，不会删除底层数据源。插入列表或数据透视表的数据源只能通过数据源的属性删除。
在 数据 菜单中的警告会指出电子表格中不再出现相应列表或透视表的任何数据源。
另一方面，删除插入的图表也会删除底层数据源。
插入列表¶
重要
在电子表格中插入列表之前，请确保列表符合您的需求。考虑哪些字段应该可见，以及如何对记录进行筛选和/或排序。这会影响电子表格的加载时间和用户友好性。
插入列表：
在数据库中打开相关列表视图后，点击视图名称旁边的 （操作） 图标，然后点击 。
備註
要只插入特定记录，请选择相关记录，点击屏幕顶部中央的 操作 按钮，然后点击 :icon:`ioi-view-list`插入电子表格。
在打开的窗口中，根据需要编辑 列表名称。
列表名称用于工作表名称以及 列表属性 中。
如有需要，可编辑要插入的记录数量（即行数）。
默认情况下，显示的数字是列表第一页可见的记录数量。例如，如果列表包含 150 条记录但只有 80 条可见，则该字段将显示 80。
点击 空白电子表格 或选择应在哪个现有电子表格中插入列表。
備註
新的电子表格会保存在 Odoo 文件管理 应用程序中的 我的云盘 个人工作区，或者，如果电子表格已启用 文件集中化 ，则会保存在 电子表格 工作区。
点击 确认。
列表会插入电子表格中的一个新工作表。底栏的工作表选项卡显示列表名称，后跟列表 ID，如 按总数计算的报价（列表 #1）。屏幕右侧的窗格显示 :ref:` 列表属性 <spreadsheet/insert/list-properties>`。
小訣竅
要查看已插入列表的单条记录，请右键点击相关行的任意单元格，然后点击 查看记录。要返回电子表格，请点击页面顶部面包屑导航中的电子表格名称。
要切断插入列表与数据库之间的联系，请选择整个列表，点击右键并选择 :
guilabel:`复制，然后再次点击右键并选择 。请勿修改工作表名称中的列表 ID，因为已插入的列表会在电子表格的整个生命周期中保留此 ID。该列表 ID 将用于从数据库中检索数据的 电子表格函数。
列表函数¶
在电子表格中插入列表时，将使用以下 函数 分别检索表头和字段值：
=ODOO.LIST.HEADER(list_id, field_name)
=ODOO.LIST(list_id, index, field_name)
The arguments of the function are as follows:
list_id：插入列表时分配的 ID。电子表格中插入的第一个列表被分配为列表 ID1，第二个被分配为列表 ID2，等等。索引：标识插入前记录在列表中出现的行。第一行的索引为1，第二行的索引为2，等等。field_name：字段的技术名称。
小訣竅
点击单个单元格后，相关公式会显示在公式栏中。要同时显示电子表格中的所有公式，请点击菜单栏上的 。下面的示例显示了用于检索列表标题和数值的函数。
列表屬性¶
插入列表时，列表属性会显示在屏幕右侧。您可以随时通过 数据 菜单访问这些属性，方法是点击相关列表（前缀为 （列表） 图标，或右键点击列表上的任意位置并点击 查看列表属性。
下面显示的是列表属性，其中一些属性可以编辑：
列表 #：列表 ID。在电子表格中插入其他列表时，会按顺序分配列表 ID。
列表名称：列表名称。如有需要，可对其进行编辑。请注意，编辑列表属性中的列表名称不会修改工作表名称中显示的列表名称，反之亦然。
模型：从中提取数据的模型。
列：插入列表时可见的模型字段。
域：用于确定显示哪些记录的规则。点击 编辑 domain 添加或编辑规则。
備註
使用 全局筛选器 时，在将数据加载到电子表格之前，该域将与全局筛选器的选定值相结合。
排序：数据的排序方式（如适用）。要添加排序规则，请点击 添加，选择字段，然后选择排序方式是 升序 还是 降序。点击 （删除） 图标，删除排序规则。
To duplicate or delete a list’s data source, click the (gear) icon, then click Duplicate or Delete as relevant.
Manage an inserted list¶
Once a list from an Odoo database has been inserted into an Odoo spreadsheet, you can:
add records, i.e., rows
add fields, i.e., columns
duplicate the list to create a new, identical data source
Add records/rows to a list¶
To add records to a list, use one of the following methods:
Select the last row of the table, then hover over the blue square until the plus icon appears. Click and drag down to add the desired number of rows. The cells of the new rows are populated with the appropriate formula to retrieve the list values. If there is corresponding data in your database, the cells are populated.
Position your cursor in the top left cell of the sheet, click from the menu bar, then select the appropriate list. In the pop-up window, indicate the number of records to insert and click Confirm. An updated list is inserted, overwriting the previous list.
小訣竅
The above methods can also be used to add additional blank rows to your spreadsheet table. This may be useful for lists where you expect additional records to be generated in your database, e.g., new product categories or new salespersons.
Add fields/columns to a list¶
To add fields/columns to a list:
Select the column to the right or left of where the new column should be inserted.
Click then or from the menu bar, or right-click then Insert column left or Insert column right as appropriate.
Copy the header cell of any column, paste it into the header cell of the new column, and press
Enter.Double-click the new header cell then click on the field name that appears in quotation marks at the end of the formula; a list of all the technical names of the fields of the related model appears.
Select the appropriate field name and press
Enter. The field’s label appears in the header.小訣竅
To know a field’s technical name, navigate to the relevant view, activate developer mode, then check the field name by hovering over the question mark beside a field’s label.
With the header cell selected, double-click on the blue square in the bottom-right corner. The cells of the column are populated with the appropriate formula to retrieve the list values. If there is corresponding data in your database, the cells are populated.
Duplicate a list¶
Duplicating a list via the list’s properties creates an additional data source. This allows for different manipulations to be performed on the same data within one spreadsheet.
With the list properties open, click the (gear) icon then Duplicate.
The new data source is assigned the next available list ID. For example, if no other lists have been inserted in the meantime, duplicating List #1 results in the creation of List #2.
Unlike when you insert a list, a duplicated list is not automatically inserted into the spreadsheet. To insert it, perform the following steps:
Add a new sheet by clicking the (add sheet) icon at the bottom left of the spreadsheet.
Click from the menu bar, then select the appropriate list.
Define the number of records to insert and click Confirm.
Edit the List Name in the properties pane if needed.
Rename the sheet by right-clicking on the sheet tab, selecting Rename, and entering the new sheet name.
備註
Duplicating an inserted list by copying and pasting it or by duplicating the sheet into which it has been inserted does not create a new data source. Any changes made to the list’s properties would therefore impact any copies of the list.
Delete a list¶
To fully delete a list and the underlying data source from a spreadsheet, perform the following steps in any order:
Delete the spreadsheet table using your preferred means, e.g., via keyboard commands, spreadsheet menus, or by deleting the sheet. This deletes the visual representation of the data.
From the properties pane of the relevant list, click the (gear) icon then Delete. This deletes the data source of the list from the spreadsheet.
Insert a pivot table¶
小訣竅
Converting an inserted pivot table to a dynamic pivot table allows you to add, remove, and manipulate dimensions (i.e., columns and rows) and measures. It is therefore possible to insert a basic pivot table with minimal configuration, convert it to a dynamic pivot table, then refine it directly in the spreadsheet.
To insert a pivot table:
With the relevant pivot view open in your database, click Insert in Spreadsheet.
In the window that opens, edit the Name of the pivot if needed.
This name is used in the sheet name and in the pivot table properties.
Click Blank spreadsheet or select in which existing spreadsheet the pivot table should be inserted.
備註
新的电子表格会保存在 Odoo 文件管理 应用程序中的 我的云盘 个人工作区，或者，如果电子表格已启用 文件集中化 ，则会保存在 电子表格 工作区。
点击 确认。
The pivot table is inserted into a new sheet in the spreadsheet. The sheet tab in the bottom bar shows the name of the pivot table followed by the pivot table ID, e.g., Sales Analysis by Sales Team (Pivot #1). A pane on the right side of the screen shows the pivot table properties.
小訣竅
To view the records referenced by an individual cell of a pivot table, right-click on the cell, then click See record. To return to the spreadsheet, click the name of the spreadsheet in the breadcrumbs at the top of the page.
To sever the link between an inserted pivot table and your database, select the entire pivot table, right-click and select Copy, then right-click again and select .
Do not modify the pivot table ID in the sheet name, as the inserted pivot table retains this ID for the lifetime of the spreadsheet. This pivot table ID is used in the spreadsheet functions that retrieve data from your database.
Pivot table functions¶
An inserted pivot table that has not been converted to a dynamic pivot table uses the following functions to retrieve the header and field values, respectively:
=PIVOT.HEADER(pivot_id, [domain_field_name, …], [domain_value, …]) =PIVOT.VALUE(pivot_id, measure_name, [domain_field_name, …], [domain_value, …])
The arguments of the functions are as follows:
pivot_id: the ID assigned when the pivot table is inserted. The first pivot table inserted in a spreadsheet is assigned pivot ID1, the second, pivot ID2, etc.measure_name: the technical name of what is being measured, followed by the type of aggregation, e.g.,product_uom_qty:sum.domain_field_name: the technical name of the field used as a dimension, e.g.,user_id, or, if the dimension is a time period, the technical name of the date field, followed by the time period, e.g.,date_order:month.domain_value: the ID of the record, or, if the dimension is a time period, the date or time period targeted.
小訣竅
Clicking on an individual cell displays the related formula, if relevant, in the formula bar. To display all the formulas of a spreadsheet at the same time, click on the menu bar. The example below shows the functions used to retrieve headers and values of a static pivot table.
Pivot table properties¶
The pivot table properties appear on the right side of the screen when a pivot table is inserted. They can be accessed at any time via the Data menu by clicking the relevant pivot table, as prefaced by the (pivot) icon, or by right-clicking anywhere on the pivot table and clicking See pivot properties.
The following pivot table properties are shown, some of which can be edited:
Pivot #: the pivot table ID. Pivot table IDs are assigned sequentially as additional pivot tables are inserted in the spreadsheet.
Name: the name of the pivot table. Edit this if needed. Note that editing the name in the pivot table properties does not modify the name shown in the sheet name, and vice versa.
模型：从中提取数据的模型。
Columns and Rows: dimensions you are using to categorize or group data from the model.
Measures: what you are measuring, or analyzing, based on the dimensions you have chosen.
小訣竅
If you attempt to make changes to the columns, rows, or measures of a pivot table that has just been inserted into a spreadsheet, an error appears at the top right of the screen.
To be able to manipulate a pivot table’s properties, convert a static pivot table to a dynamic pivot table.
域：用于确定显示哪些记录的规则。点击 编辑 domain 添加或编辑规则。
備註
使用 全局筛选器 时，在将数据加载到电子表格之前，该域将与全局筛选器的选定值相结合。
To duplicate or delete a pivot table’s data source, click the (gear) icon then Duplicate or Delete.
Manage an inserted pivot table¶
Once a pivot table from an Odoo database has been inserted into an Odoo spreadsheet, you can:
convert it to a dynamic pivot table to be able to manipulate the dimensions and measures
duplicate the pivot table to create a new, identical data source
Duplicate a pivot table¶
Duplicating a pivot table via the pivot table’s properties creates an additional data source. This allows for different manipulations to be performed on the same data within one spreadsheet.
For example, you can see the same data aggregated by different dimensions or use global filters to offset the date and create pivot tables that compare the current period’s data with a previous period.
To duplicate a pivot table, perform the following steps:
With the pivot table properties open, click the (gear) icon then Duplicate.
The duplicated pivot table is automatically inserted into a new sheet in the spreadsheet, with the pivot table properties open in the right pane.
Edit the Name in the properties pane and the sheet tab if needed.
The new data source is assigned the next available pivot table ID. For example, if no other pivot tables have been inserted in the meantime, duplicating Pivot #1 results in the creation of Pivot #2.
備註
Duplicating an inserted pivot table by copying and pasting it or by duplicating the sheet does not create a new data source. Any changes made to the pivot table’s properties would therefore impact any copies of the pivot table.
When a pivot table is duplicated, the new pivot table is by default a dynamic pivot table.
Delete a pivot table¶
To fully delete a pivot table and the underlying data source from a spreadsheet, perform the following steps in any order:
Delete the spreadsheet table using your preferred means, e.g., via keyboard commands, spreadsheet menus, or by deleting the sheet. This deletes the visual representation of the data.
From the properties pane of the relevant pivot table, click the (gear) icon then Delete. This deletes the data source of the pivot table.
Insert a chart¶
To insert a chart from an Odoo database into an Odoo spreadsheet:
With the relevant graph view open in your database, click Insert in Spreadsheet.
In the window that opens, edit the Name of the graph if needed.
Click Blank spreadsheet or select in which existing spreadsheet the chart should be inserted.
備註
新的电子表格会保存在 Odoo 文件管理 应用程序中的 我的云盘 个人工作区，或者，如果电子表格已启用 文件集中化 ，则会保存在 电子表格 工作区。
点击 确认。
Charts are inserted on the first sheet of the spreadsheet.
小訣竅
Clicking on a data point in a chart opens the relevant list view in the database. In the example, clicking on Jessica Childs opens the list view of all sales by this salesperson that match the domain of the chart.
Chart properties¶
When you insert a chart into a spreadsheet, the chart properties appear on the right side of the screen. Access these at any time via the Data menu by clicking the relevant chart, as prefaced by the (chart) icon. Alternatively, hover over the chart then click the (menu) icon and click Edit.
In the chart properties, the Configuration and Design tabs let you modify various elements of the chart.
配置¶
The Configuration tab includes the following sections:
Chart type: the type of chart. By default, this indicates the type of chart you selected in the graph view in the database before inserting the chart in the spreadsheet.
After a chart has been inserted, more chart types are available. Click the dropdown menu to select the most appropriate chart type for the data.
Line: best for showing trends or changes over time, such as sales growth across months or temperature variations.
Stacked Line: useful for visualizing cumulative trends where multiple series contribute to a total, like revenue by department over time.
Column: ideal for comparing values across discrete categories, such as sales per product or revenue by region.
Stacked Column: displays part-to-whole relationships within categories, such as regional contributions to total sales.
Area: similar to a line chart but fills the area beneath the lines to emphasize magnitude, perfect for cumulative metrics over time.
Stacked Area: visualizes the composition of changes over time, such as market share by product category.
Pie: best for showing proportions or percentages of a whole, such as market share or budget allocation.
When creating a chart from spreadsheet data, rather than inserting one from a graph view, the following chart types are also available:
Combo: combines multiple chart types (e.g., bars and lines) to compare different data types or highlight key metrics alongside trends.
Bar: similar to a column chart but horizontal, making it better for comparing long category names or datasets.
Stacked Bar: highlights cumulative contributions across categories, often used in demographic or resource allocation analysis.
Doughnut: A variation of the pie chart with a hollow center, offering similar use cases but with a modern aesthetic.
Scatter: ideal for analyzing relationships or correlations between two numerical variables, such as price vs. quantity sold.
Gauge: displays progress toward a goal or a single key metric, such as performance against a target.
Scorecard: used to summarize key performance indicators (KPIs) in a compact format, such as total sales or conversion rates, and compare to a baseline or a previous value.
Waterfall: ideal for visualizing cumulative effects of sequential positive and negative values, such as profit/loss analysis.
Population Pyramid: a specialized chart for comparing distributions, often used in demographics, such as age and gender group analysis.
域：用于确定显示哪些记录的规则。点击 编辑 domain 添加或编辑规则。
Link to Odoo menu: to add a clickable link from a chart to an Odoo menu item, i.e., a specific view of a model.
設計¶
Depending on the chart type, the Design tab has one or more sections.
The General section lets you modify the following elements:
Background color: Add or change the background color by clicking on the color dot. Choose one of the standard colors or click the icon to manually select a custom color.
Chart title: Edit the chart title, if needed. The font formatting, horizontal alignment and color of the title can be modified using the editor.
Vertical axis position: Choose whether the vertical axis is placed on the left or right in line, column, and area charts.
Legend position: Change the position of the legend or opt to have no legend.
Enable Show values to add numeric values to the data points on the chart.
Enable Show trend line to add a trend line to line, column, and area charts.
For line, column, and area charts, the Axis section lets you add a title to one or both axes. The font formatting, horizontal alignment, and color of the title can be modified using the editor.
Insert clickable links¶
Adding links to related or supporting information can make your report or dashboard more user-friendly and effective.
You can insert a clickable link from any spreadsheet cell to:
an Odoo menu item
another sheet inside the same spreadsheet
an external URL
備註
Clicking a link to a menu item provides the same result as navigating via the Odoo menu within an app, e.g., the menu item Sales/Orders/Quotations corresponds to the default view when navigating to .
It is also possible to insert a clickable link to a specific view of a model in a spreadsheet starting from the view itself. However, as this method inserts each new link in a new sheet, it is more efficient to create links to specific views starting from the spreadsheet.
You can insert a clickable link from any chart to an Odoo menu item.
Insert a clickable link from a cell¶
To insert a clickable link from a cell:
Click from the menu bar or right-click on the cell, then click Insert link. Next, depending on the desired outcome, perform one of the following actions:
Click the (menu) icon, then Link an Odoo menu. Select the relevant menu item from the list or click Search more to choose from a list of all menu items. Click Confirm.
Click the (menu) icon, then Link sheet, then choose the relevant sheet from the current spreadsheet.
Under Link, type a URL.
Enter or edit the label for the link in the Text field.
点击 确认。
Insert a clickable link from a chart¶
To insert a clickable link from a chart to an Odoo menu item:
Hover over the top right of the chart’s box, then click the (menu) icon, then Edit. The chart properties appear at the right of the screen.
At the bottom of the Configuration tab of the chart properties pane, click under Link to Odoo menu, then select a menu.
Hover over the top right of the chart’s box to see that a new (external link) icon has been added.
Insert financial data¶
When building reports and dashboards, it may be useful to include certain accounting-related data, such as account IDs, credits and debits for specific accounts, and dates of the start and end of the tax year.
Odoo-specific spreadsheet functions allow you to retrieve such accounting data from your database and insert it into a spreadsheet.