xls.workbooks

xls.workbooks

The xls.workbooks table allows storing workbook definitions in a database.

SaveToDB users can create such workbooks using the Application Workbooks wizard.

DBEdit users can create new workbooks using the File, New From Server... menu item.

The table has the following structure:

IDNAMETEMPLATEDEFINITIONTABLE_SCHEMA
<workbook name>[<workbook template>]<workbook definition>[<schema>]

The workbook name is shown in the wizard.

The workbook template can be a filename or a URL of the Microsoft Excel workbook. The feature is supported by SaveToDB only.

The workbook definition is described below.

The schema is optional and is used to filter records.

Workbook Definition

The workbook definition defines workbook sheets, connected objects, and query parameters.

Below is a simple example:

cashbook=s02.cashbook
view_cashbook=s02.view_cashbook
usp_cashbook=s02.usp_cashbook
cash_by_months=s02.usp_cash_by_months

Below is a sample with query parameters:

cashbook=s02.cashbook,(Default),False,$B$3,,{"Parameters":{"account_id":null,"item_id":null,"company_id":null},"ListObjectName":"cashbook"}
view_cashbook=s02.view_cashbook,(Default),False,$B$3,,{"Parameters":{"account_id":null,"item_id":null,"company_id":null},"ListObjectName":"view_cashbook"}
usp_cashbook=s02.usp_cashbook,(Default),False,$B$3,,{"Parameters":{"account_id":null,"item_id":null,"company_id":null},"ListObjectName":"usp_cashbook"}
cash_by_months=s02.usp_cash_by_months,(Default),False,$B$3,,{"Parameters":{"year":2021},"ListObjectName":"cash_by_months"}

You can use the SaveToDB add-in (Wizards, Developer Tools, **Show Workbook Definition**) to acquire the definition of the active workbook.

Below is a formal grammar of the workbook definition:

workbook-definition = sheet-definition | sheet-definition NEWLINE workbook-definition

sheet-definition = sheet-name "=" table-definition

table-definition = query-object
    | query-object "," query-list
    | query-object "," query-list "," query-list-enabled
    | query-object "," query-list "," query-list-enabled "," cell-address
    | query-object "," query-list "," query-list-enabled "," cell-address "," visible
    | query-object "," query-list "," query-list-enabled "," cell-address "," visible "," json-parameters

query-list = EMPTY | "(Default)" | query-list-object | schema

query-list-enabled = EMPTY | "True" | "False"

visible = EMPTY | "True" | "False"

The json-parameters is a JSON object with two properties: Parameters and ListObjectName.

The Parameters value is an object that contains properties with parameter names and values.