数据源

Data sources, which are created each time a list, pivot table, or chart is inserted into an Odoo spreadsheet, connect the spreadsheet and the relevant model in your database, ensuring the data stays up-to-date and allowing you to access the underlying data.

每个数据源都由可通过 数据 菜单访问的属性定义。数据源由各自的 (数据透视表) (列表)(图表) 图标标识,后跟 ID 和名称,例如, (#1)产品销售分析

数据菜单中列出的数据源

Clicking on a data source opens the related properties in a panel on the right of the spreadsheet.

小訣竅

  • The properties panel can also be opened by right-clicking any cell of an inserted list or pivot table, then clicking See list properties or See pivot properties, or by clicking the (menu) icon at the top right of an inserted chart, then clicking Edit.

  • Once the properties of a specific data source are open, they remain open even when navigating between spreadsheet tabs. To close the properties panel, click the (close) icon at the top right of the panel.

  • Click (pin) at the top of the properties panel to allow another panel, such as the global filters panel, to open beside it.

備註

删除插入的列表或数据透视表,或删除插入该列表或数据透视表的工作表,不会删除底层数据源。插入列表或数据透视表的数据源只能通过数据源的属性删除。

数据 菜单中的警告会指出电子表格中不再出现相应列表或透视表的任何数据源。

关于未使用列表的警告信息

另一方面,删除插入的图表也会删除底层数据源。

Accessing underlying data

The underlying data of an inserted list, pivot table, or chart can be accessed at any time. To view:

  • an individual record of an inserted list, right-click any cell of the relevant row, then select See record

  • a list of records referenced by an individual cell of an inserted pivot table, right-click the cell, then select See records

  • a list of records represented by a data point of an inserted chart, click the data point.

小訣竅

Use the middle mouse button or Ctrl + left-click (Microsoft/Linux), or Command + left-click (Mac OS) to open the results in a new browser tab.

To return to the spreadsheet after viewing the underlying data, click the name of the spreadsheet in the breadcrumbs at the top of the page.

插入列表

重要

在电子表格中插入列表之前,请确保列表符合您的需求。考虑哪些字段应该可见,以及如何对记录进行筛选和/或排序。这会影响电子表格的加载时间和用户友好性。

插入列表:

  1. 在数据库中打开相关列表视图后,点击视图名称旁边的 (操作) 图标,然后点击 电子表格 ‣ 在电子表格中插入列表

    備註

    要只插入特定记录,请选择相关记录,点击屏幕顶部中央的 操作 按钮,然后点击 :icon:`ioi-view-list`插入电子表格

  2. 在打开的窗口中,根据需要编辑 列表名称

    列表名称用于工作表名称以及 列表属性 中。

    在电子表格中插入列表
  3. 如有需要,可编辑要插入的记录数量(即行数)。

    默认情况下,显示的数字是列表第一页可见的记录数量。例如,如果列表包含 150 条记录但只有 80 条可见,则该字段将显示 80。

    備註

    虽然通过与数据库的连接,列表中的数据可以保持更新,但已插入的列表不会自动扩展以容纳新的记录(例如,新的产品类别或新的销售人员)。

    如果预计会添加新记录,请考虑在插入列表时添加额外的行。也可以在插入电子表格后 手动添加记录/行

    Example

    您的公司目前有十个产品类别,您将此列表插入到电子表格中。如果创建了第11个产品类别,而您插入的列表只有十行,那么新类别将被插入到电子表格的适当位置,从而移除一个现有类别。

    避免这种情况的一个方法是在插入列表时 添加额外的行

  4. Click Blank spreadsheet to create a new spreadsheet, or select in which existing spreadsheet the list should be inserted.

    備註

    When inserting a list into a new spreadsheet, the spreadsheet is saved in the Odoo Documents app in the My Drive personal folder.

  5. 点击 确认

The list is inserted into a new sheet in the spreadsheet. The sheet tab in the bottom bar shows the name of the list followed by the list ID, e.g., Quotations by Total (List #1). A panel on the right side of the screen shows the list properties.

小訣竅

  • To sever the link between an inserted list and your database, select the entire list, right-click and select Copy then right-click again and select Paste special ‣ Paste as value.

  • 请勿修改工作表名称中的列表 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。电子表格中插入的第一个列表被分配为列表 ID 1,第二个被分配为列表 ID 2,等等。

  • 索引:标识插入前记录在列表中出现的行。第一行的索引为 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

After a list from an Odoo database has been inserted into an Odoo spreadsheet, you can:

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.

    Add records by dragging the cell down
  • Position your cursor in the top left cell of the sheet, click Data ‣ Re-insert list 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:

  1. Select the column to the right or left of where the new column should be inserted.

  2. Click Insert ‣ Insert column then Column left or Column right from the menu bar, or right-click then Insert column left or Insert column right as appropriate.

  3. Copy the header cell of any column, paste it into the header cell of the new column, and press Enter.

  4. 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.

    Add fields/columns by editing the formula
  5. 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.

  6. 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:

  1. Add a new sheet by clicking the (add sheet) icon at the bottom left of the spreadsheet.

  2. Click Data ‣ Re-insert list from the menu bar, then select the appropriate list.

  3. Define the number of records to insert and click Confirm.

  4. Edit the List Name in the properties panel if needed.

  5. 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.

删除列表

要彻底删除电子表格中的列表及其底层数据源,请按任意顺序执行以下步骤:

  • 使用您偏好的方式(如键盘命令、电子表格菜单或删除工作表)删除表格。这将清除数据的可视化呈现。

  • From the properties panel of the relevant list, click the (gear) icon then Delete. This deletes the data source of the list from the spreadsheet.

插入数据透视表

小訣竅

将插入的数据透视表转换为 动态数据透视表 后,您可以添加、删除和调整维度(即列和行)及度量值。因此,您可以先插入一个基础数据透视表,再转换为动态版本,并在电子表格中直接优化。

插入数据透视表:

  1. 在数据库中打开相关透视图后,点击 在电子表格中插入

  2. 在弹出的窗口中,按需编辑 透视表名称

    该名称用于工作表名称和 数据透视表属性

    在电子表格中插入数据透视表
  3. Click Blank spreadsheet to create a new spreadsheet, or select in which existing spreadsheet the pivot table should be inserted.

    備註

    When inserting a pivot table into a new spreadsheet, the spreadsheet is saved in the Odoo Documents app in the My Drive personal folder.

  4. 点击 确认

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 panel on the right side of the screen shows the pivot table properties.

小訣竅

  • 要切断插入的数据透视表和数据库之间的联系,请选择整个数据透视表,点击右键并选择 :guilabel:`复制,然后再次点击右键并选择 特殊粘贴 ‣ 粘贴为值

  • 请勿修改工作表名称中的数据透视表 ID,因为插入的数据透视表会在电子表格的整个生命周期中保留此 ID。该数据透视表 ID 将用于从数据库检索数据的 电子表格函数 中。

透视表函数

插入的数据透视表没有转换为 动态数据透视表 使用下列 函数 分别检索表头和字段值:

=PIVOT.HEADER(pivot_id, [domain_field_name, …], [domain_value, …])
=PIVOT.VALUE(pivot_id, measure_name, [domain_field_name, …], [domain_value, …])

函数参数说明如下:

  • pivot_id: the ID assigned when the pivot table is inserted. The first pivot table inserted in a spreadsheet is assigned pivot ID 1, the second, pivot ID 2, etc.

  • measure_name:被测量指标的技术名称,后跟聚合类型,例如`product__qty:sum`。

  • domain_field_name:用作维度的字段技术名称,例如`user_id`;若维度是时间段,则为日期字段技术名称后跟时间段,例如`date_order:month`。

  • domain_value:记录的ID;若维度是时间段,则为目标日期或时间段。

小訣竅

点击单个单元格时,公式栏会显示相关公式(如适用)。要同时显示电子表格中的所有公式,请点击菜单栏中的 视图 ‣ :icon:`fa-eye 显示 ‣ 公式。以下示例展示了用于获取静态数据透视表标题和值的函数。

静态数据透视表函数

数据透视表属性

插入数据透视表后,数据透视表属性会显示在屏幕右侧。您可以随时通过 数据 菜单访问这些属性,方法是点击相关的数据透视表,前面有 (数据透视表) 图标,或右击数据透视表上的任意位置并点击 查看数据透视表属性

可查看(部分可编辑)的透视表属性包括:

  • 透视表#:数据透视表ID。随着在电子表格中插入更多透视表,ID会按顺序分配。

  • 名称:数据透视表名称。可按需编辑。注意:在属性中修改名称不会改变工作表标签显示的名称,反之亦然。

  • 模型:从中提取数据的模型。

  • 列`和 :guilabel:`行:用于分类或分组模型数据的维度。

  • 度量值:基于所选维度进行测量或分析的内容。

    小訣竅

    若尝试修改刚插入电子表格的透视表的列、行或度量值,屏幕右上方将显示错误提示。

    Error message when trying to manipulate static pivot table

    要操作数据透视表属性,请将静态透视表转换为 动态数据透视表

  • :用于确定显示哪些记录的规则。点击 编辑 domain 添加或编辑规则。

    備註

    使用 全局筛选器 时,在将数据加载到电子表格之前,该域将与全局筛选器的选定值相结合。

To duplicate or delete a pivot table’s data source, click the (gear) icon then Duplicate or Delete.

管理插入的数据透视表

After a pivot table from an Odoo database has been inserted into an Odoo spreadsheet, you can:

复制数据透视表

通过数据透视表的属性复制数据透视表,可创建一个额外的数据源。这样就可以在一个电子表格中对相同的数据进行不同的操作。

例如,您可以查看按不同维度汇总的相同数据,或者使用 全局筛选器 来偏移日期,并创建数据透视表,将当前期间的数据与之前期间的数据进行比较。

要复制数据透视表,请执行以下步骤:

  1. 打开 数据透视表属性 时,点击 (齿轮) 图标,然后点击 复制

    The duplicated pivot table is automatically inserted into a new sheet in the spreadsheet, with the pivot table properties open in the right panel.

  2. Edit the Name in the properties panel and the sheet tab if needed.

新数据源被分配给下一个可用的数据透视表 ID。例如,若期间未插入其他数据透视表,复制*透视表#1*将创建*透视表#2*。

備註

  • 通过复制粘贴或复制工作表来复制已插入的数据透视表时,不会创建新的数据源。因此,对数据透视表属性所做的任何修改都会影响其所有副本。

  • 复制的数据透视表默认将成为 动态数据透视表

删除数据透视表

要彻底删除电子表格中的数据透视表及其底层数据源,请按任意顺序执行以下步骤:

  • 使用您偏好的方式(如键盘命令、电子表格菜单或删除工作表)删除表格。这将清除数据的可视化呈现。

  • From the properties panel of the relevant pivot table, click the (gear) icon then Delete. This deletes the data source of the pivot table.

插入图表

将 Odoo 数据库中的图表插入电子表格的操作流程:

  1. 在数据库中打开目标图表视图,点击 插入到电子表格

  2. 在弹出的窗口中编辑 :guilabel:`图表名称`(可选)。

  3. Click Blank spreadsheet to create a new spreadsheet, or select in which existing spreadsheet the chart should be inserted.

    備註

    When inserting a chart into a new spreadsheet, the spreadsheet is saved in the Odoo Documents app in the My Drive personal folder.

  4. 点击 确认

图表将被插入到电子表格的首个工作表。

小訣竅

点击图表数据点(如 Jessica Childs)可打开数据库中对应的列表视图。

指向 Odoo 菜单的可点击链接以及可点击的数据点

图表属性设置

在电子表格中插入图表时,屏幕右侧会显示图表属性。您可以随时通过 数据 菜单访问这些属性,方法是点击相关图表,并在图表前添加 (图表)`图标。或者,将鼠标悬停在图表上,然后点击 :icon:`fa-bars (菜单) 图标,再点击 编辑

在图表属性中, 配置 设计 选项卡可让您修改图表的各种元素。

配置

配置 选项卡包括以下部分:

  • 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.

    備註

    The chart types in the Other tab below are only available when creating a chart from spreadsheet data; an inserted chart cannot be converted to a chart type shown in that tab. All other chart types are available for both inserted charts and when creating a chart directly from spreadsheet data.

    Line chart icon

    Line: best for showing trends or changes over time, such as sales growth across months or temperature variations.

    Stacked line chart icon

    Stacked Line: useful for visualizing cumulative trends where multiple series contribute to a total, like revenue by department over time.

    Combo chart icon

    Combo: combines multiple chart types (e.g., bars and lines) to compare different data types or highlight key metrics alongside trends.

  • :用于确定显示哪些记录的规则。点击 编辑 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 allows you to 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, font size, and color of the title can be modified using the editor.

  • 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.

The Data Series section allows you to modify the following elements:

  • Series color: With the relevant data series selected, change the color of the related data points on the chart by clicking on the color dot circle. Choose one of the standard colors or click the icon to manually select a custom color.

  • Series name: Edit the name of a data series, if needed.

  • Serie type: For each data series of a Combo chart, determine whether the series is shown as a Bar or Line.

  • Vertical axis: For the selected data series of a Line, Area, or Column chart, select whether it should be displayed on the Left (primary) or Right (secondary) vertical axis.

  • Trend line: With the relevant data series selected, enable Show trend line then select the type of trend line from the dropdown; the options are Linear, Exponential, Polynomial, Logarithmic, and Trailing moving average. The color of the trend line can be changed by clicking on the color circle.

The Axes section allows you add a title to one or both axes of a chart. The font formatting, horizontal alignment, font size, and color of the title can be modified using the editor.

Waterfall charts have a dedicated Waterfall design section.

Manage an inserted chart

After a chart from an Odoo database has been inserted into an Odoo spreadsheet, you can:

  • move the chart within the same sheet by selecting it, then dragging the chart to the desired position

  • resize the chart by selecting it, then clicking and dragging the blue markers until the chart is the desired size

  • hover over the chart, then click the (menu) icon to reveal the following options:

    • Copy or Cut: to copy or cut a chart with the intention of pasting it within the same spreadsheet, click the relevant icon or use the relevant keyboard shortcut. Paste the chart in the desired location by clicking Edit ‣ Paste from the menu bar or use the relevant keyboard shortcut.

      備註

      Copying/cutting and pasting a chart in this way maintains the link between the chart and your database. The data in the pasted chart remains up-to-date, and clicking on a data point opens the related list view in the database.

    • Copy as image: to copy an image of a chart to your clipboard with the intention of pasting it in any location within or outside your spreadsheet, click Copy as image. Paste the image in the desired location using the paste function of the destination program or the relevant keyboard shortcut.

      備註

      Copying and pasting a static image of a chart implies there is no longer any link between the chart and your database.

    • Delete: delete a chart and its underlying data source by clicking Delete. Alternatively, use your preferred keyboard command to delete a chart and its data source.

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.