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:
| ID | NAME | TEMPLATE | DEFINITION | TABLE_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":2022},"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.