Understanding the xls.workbooks Table in SaveToDB

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:

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.