xls.handlers

xls.handlers

The xls.handlers table is a part of the SaveToDB Framework used to configure application features:

Below is a configuration sample:

Sample of Handler Configuration

The table has the following fields:

  1. ID
  2. TABLE_SCHEMA
  3. TABLE_NAME
  4. COLUMN_NAME
  5. EVENT_NAME
  6. HANDLER_SCHEMA
  7. HANDLER_NAME
  8. HANDLER_TYPE
  9. HANDLER_CODE
  10. TARGET_WORKSHEET
  11. MENU_ORDER
  12. EDIT_PARAMETERS

Use TABLE_SCHEMA and TABLE_NAME to define a database object being configured.

Use COLUMN_NAME to define a column or parameter being configured. Leave it blank for table-level handlers.

Use EVENT_NAME to define an event type. See the description below.

Use HANDLER_SCHEMA and HANDLER_NAME to define a handler object.

Use HANDLER_TYPE to define the handler type. See the description below.

Use HANDLER_CODE to define a handler-specific code like an SQL code or URL.
You may leave it blank for SQL-based objects configured in xls.objects.

Use TARGET_WORKSHEET to configure a handler-specific output. See the description below.

Use MENU_ORDER to define menu item order in the application and context Actions menus.

Use EDIT_PARAMETERS to show or hide the Edit Parameters dialog box before executing menu items.

Note that DBEdit supports a subset of features configured in the SaveToDB add-in.

However, it has the same behavior for supported features. So, developers do not need to create separate configurations.

Also, note that you may create your tables and views with the xls.handlers field structure. DBEdit reads multiple configuration objects as well.

Event Types

DBEdit supports the following event types in the EVENT_NAME field:

Use Actions to configure the application Actions menu.

Use ContextMenu to configure the context Actions menu.

Use Change to define server-side cell change handlers.

Use ValidationList and SelectionList types to define lists of cell values.
DBEdit has the same behavior for both types, while the SaveToDB add-in has different.

Use ParameterValues to define lists of parameter values.

Use DataType<Type> handlers to define data types of columns or parameters.
As DBEdit reads types from the database metadata, use these handlers to specify data types of SQL code parameters or stored procedure columns that cannot be detected.

Use DoNotChange to make a column or a table read-only.

Use DoNotSave to skip saving a column or to disable saving data changes using the Save button for the entire table.

Use DoNotSelect to prevent selecting a column or to prevent connection to an object.

Use DoNotSort to prevent sorting a column or the entire table.

Use DoNotTranslate to prevent translating a column header or the entire table.

Use DoNotAddValidation to disable adding automatic value lists.
For example, to use identifier values instead of values from a foreign key table.

Use DoNotAddChangeHandler to disable linking a procedure with the _change suffix as a change handler.

Use ProtectRows to disable adding and deleting rows by a user.

The License handler allows saving changes of the specified object in a free DBEdit version.
It is used in other Gartle products like Business Time Tracker or Market.db.

Use AddHyperlinks to add hyperlinks for URLs or connected database object names in the specified column.
For example, this allows using database views and procedures as workbook indexes.

Use BitColumn to format integer-type columns as check-boxes.
DBEdit does this automatically for bit and boolean columns of tables and views. Use this handler for stored procedure columns.

Use RegEx to process a single-cell text output of a stored procedure using regular expressions placed in the HANDLER_CODE column.
Specify a stored procedure is being processed in the TABLE_SCHEMA and TABLE_NAME columns.

Use DynamicColumns to specify a view or stored procedure that returns source column names, column aliases, and visibility flags.
DBEdit applies these settings to a configured object.

Use Format to specify the table format in the HANDLER_CODE field.
Use the COLUMN_NAME field to specify the column to apply the column-specific format.

Use Information to specify information records in the xls.handlers table.
For example, SaveToDB Frameworks use it to store current versions.

Handler Types

DBEdit supports the following handler types in the HANDLER_TYPE field:

Use TABLE, VIEW, FUNCTION, or PROCEDURE for existing objects.

Note that you may customize select and where fields for tables and views.

Use CODE for SQL-based objects, HTTP for URLs, and CMD for Windows commands or batch files.

Place the code and URLs of the CODE, HTTP, and CMD handlers into the HANDLER_CODE field.

Use VALUES for comma- or semicolon-separated values. Place values into the HANDLER_CODE field.

Use MENUSEPARATOR to add a separator into the Actions menu. Specify a unique handler name and its menu order.

Use SHOWSHEETS and HIDESHEETS to show or hide worksheets. Specify a comma-separated list of database object names the HANDLER_CODE field.

Use SELECTSHEET to select a worksheet. Specify a database object name in the TARGET_WORKSHEET field.

Use ATTRIBUTE instead of the NULL values for other handler types.

TARGET_WORKSHEET Values

DBEdit supports the following specific keys in the TARGET_WORKSHEET field:

Use _none to execute the handler in a silent mode.

Use _msgbox to show the handler output in the modal message box.

Use _popup or _taskpane to show the handler output in a separate window.

Use _transpose and _transposeauto to transpose an output table. In the last case, DBEdit selects the best display form itself.

Use _reload to reload data when the handler finished. You may combine this value with the _popup option.

Use _commit with the cell change handlers to point out that the handler commits changes in a database.

Use _notransaction to disable the transaction mode for the handler.

Formats

Database developers may define table formats in the HANDLER_CODE field with the Format value in the EVENT_NAME column of the xls.handlers table.

Here is a format sample:

[{"format":"font-family: Calibri; font-size: 11pt; border-style: none"},
{"formula":"row_format=1","format":"background-color: rgb(33,89,103); color: white !important; font-weight: bold","stopIfTrue":true},
{"formula":"row_format=2","format":"font-weight: bold"},
{"formula":"row_format=9","format":"background-color: rgb(0,33,96); color: white !important; font-weight: bold"},
{"formula":"<0","format":"color:red","columns":"data00,data01,data02,data03,data04,data05,data06,data07,data08,data09,data10,data11,data12"},
{"formula":"[cf_sign=0][pl_sign=-1][=null]","format":"background-color: rgb(255,255,0);","columns":"asset_account_id"}]

The format definition is a JSON array of format objects.

The format objects may have the following properties: format, formula, columns, and stopIfTrue.

The format property contains CSS format definition. DBEdit supports the following rule subset:

The formula property defines conditions to apply the format. Use square brackets to define several conditions with the AND operator (like in CSS).

Use a field name, sign, and value like row_format=1, or just a sign and value like <0.

The formula field also supports special formulas: even, odd, tr:nth-child(even), and tr:nth-child(odd).

The columns property defines target columns. Alternatively, use the COLUMN_NAME field to define a single column (usually automatically by views).

Use the stopIfTrue property to stop all the subsequent rules. Note that DBEdit ignores the !important tag (used in ODataDB).

You may use the following steps to create complicated formats in a visual mode: