xls.workbooks
The xls.workbooks table stores workbook definitions in a database.
- SaveToDB users can create workbooks using the Application Workbooks wizard.
- DBEdit users can create new workbooks via the File > New From Server... menu item.
The table structure is as follows:
ID | NAME | TEMPLATE | DEFINITION | TABLE_SCHEMA |
---|---|---|---|---|
<workbook name> | [<workbook template>] | <workbook definition> | [<schema>] |
The workbook name appears in the wizard.
The workbook template can be either a filename or a URL pointing to a Microsoft Excel workbook. Note that this feature is supported only by SaveToDB.
The workbook definition is detailed below.
The schema is optional and serves to filter records.
Workbook Definition
The workbook definition specifies workbook sheets, connected objects, and query parameters.
Here’s a simple example:
cashbook=s02.cashbook view_cashbook=s02.view_cashbook usp_cashbook=s02.usp_cashbook cash_by_months=s02.usp_cash_by_months
Here’s 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":2022},"ListObjectName":"cash_by_months"}
You can use the SaveToDB add-in (Wizards, Developer Tools, **Show Workbook Definition**) to retrieve the definition of the active workbook.
The formal grammar for the workbook definition is as follows:
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 containing two properties: Parameters
and ListObjectName
.
The Parameters
value is an object that holds properties with parameter names and their corresponding values.