xls.handlers

xls.handlers

The xls.handlers table allows configuring a lot of features.

It has the following structure:

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODETARGET_WORKSHEETMENU_ORDEREDIT_PARAMETERS
 

For example, here is a configuration for a cell change handler:

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
 s02usp_cashbook3Changes02usp_cashbook3_changePROCEDURE

You can create such tables or views in your schemas copying the xls.handlers table or the xls.view_handlers view if this makes sense.

You can even generate handlers using views.

The table has the following logical sections:

  1. ID
  2. TABLE_SCHEMA, TABLE_NAME, and COLUMN_NAME
  3. EVENT_NAME
  4. HANDLER_SCHEMA, HANDLER_NAME, HANDLER_TYPE, and HANDLER_CODE
  5. TARGET_WORKSHEET, MENU_ORDER, and EDIT_PARAMETERS

The ID is optional for views.

TABLE_SCHEMA, TABLE_NAME, and COLUMN_NAME define an object or a column that is being configured.

EVENT_NAME defines an event or a feature that is being configured.

HANDLER_SCHEMA, HANDLER_NAME, HANDLER_TYPE, and HANDLER_CODE define an object used to handle the event or feature.

TARGET_WORKSHEET tunes the handler.

MENU_ORDER defines the menu item order in the application and context menus.

EDIT_PARAMETERS defines showing or hiding the Edit Parameters dialog box before executing menu items.

EVENT_NAME Values

The EVENT_NAME field allows using about 50 event types.

The first group allows configuring server-side handlers used to handle events or implement features:

These handlers require objects in the HANDLER_SCHEMA, HANDLER_NAME, and HANDLER_TYPE fields.

The second group changes the behavior on the object-level:

The next group changes the column properties:

  • AddHyperlinks
  • BitColumn
  • ConvertFormulas, DoNotConvertFormulas
  • DefaultValue
  • DoNotSelect, DoNotSave, DoNotChange, DoNotSort
  • DataTypeBinary, DataTypeBinary16, DataTypeBit, DataTypeBoolean, DataTypeDate, DataTypeDateTime, DataTypeDateTimeOffset, DataTypeDouble, DataTypeInt, DataTypeGuid, DataTypeString, DataTypeTime, DataTypeTimeSpan
  • Formula, FormulaValue
  • HideByDefault and WhereByDefault
  • SelectPeriod

And there are two event types used by the SaveToDB products internally:

  • License
  • Information

SelectPeriod

SaveToDB and DBEdit allow using date ranges for filters on fields of tables and views.

The products also allow using two procedure parameters as the beginning and the end of the period.

You can customize the behavior for fields and period beginning parameters using the SelectPeriod event type.

The TARGET_SHEET field format: [<pair parameter name>],[<DayOfWeek>],[<Hide>]

The pair parameter name is required for parameters.

The first DayOfWeek must be a number from 0 to 7 or in English. Monday or 1 must be passed if the week is to start on Monday.

Hide options: HideThis, HideNext, HideDays, HideWeeks, HideMonths, HideYears, HideThisMonth

Separators can be commas, semicolons, or spaces.

HANDLER_TYPE Values

The HANDLER_TYPE field value defines a handler type.

SaveToDB, DBEdit, DBGate, and ODataDB support the following types:

  • TABLE, VIEW, PROCEDURE, FUNCTION
  • CODE
  • HTTP
  • MENUSEPARATOR
  • VALUES
  • ATTRIBUTE

Use PROCEDURE, FUNCTION, TABLE, and VIEW for database objects to process events or implement features.

Use CODE for objects based on SQL queries.

Use HTTP for objects based on HTTP commands.

Use MENUSEPARATOR for action menu and context menu separators.

Use VALUES to defines fixed values for the ParameterValues, ValidationList, and SelectionList handlers.
Specify values separated by comma or semicolon in the HANDLER_CODE field.

Use the ATTRIBUTE type instead of the NULL value when other types are not applicable.

SaveToDB and DBEdit Specific Handler Types

SaveToDB and DBEdit support the following types to manage worksheets:

  • SELECTSHEET
  • SHOWSHEETS
  • HIDESHEETS

Use SELECTSHEET for an action to select a sheet specified in the TARGET_WORKSHEET field.
The field can contain a sheet name or a name of a target database object.

Use SHOWSHEETS for an action to show sheets and HIDESHEETS to hide sheets specified in the HANDLER_CODE field.
The HANDLER_CODE field can contain a comma- or semicolon-separated list of sheets or names of database objects.
Also, the TARGET_WORKSHEET field can contain a sheet to select.

SaveToDB Specific Handler Types

The SaveToDB add-in also supports specific types:

  • TEXT
  • MACRO
  • CMD
  • REFRESH
  • RANGE
  • PDF
  • REPORT

Use TEXT for objects based on text file queries.

Use MACRO for Excel VBA macros. See Running Macros.

Use CMD for Windows Shell and CMD commands. See Running CMD Commands.

Use REFRESH for an action to refresh data tables on the worksheets specified in the TARGET_WORKSHEET field, separated by commas.

Use RANGE to define an Excel range used as a data source for the ParameterValues, ValidationList, and SelectionList handlers.
Specify a range or a named cell name in the HANDLER_CODE field.

Use PDF to create a PDF document. You can define target sheets separated by a comma in the TARGET_WORKSHEET column.

Use REPORT to create an Excel report workbook. You can define target sheets separated by a comma in the TARGET_WORKSHEET column.

TARGET_WORKSHEET Values

The TARGET_WORKSHEET field allows tuning the handler features.

The field name is a little bit confusing for some features. However, it came from the first versions when only sheets were supported.

SaveToDB, DBEdit, DBGate, and ODataDB support the following options:

  • _reload
  • _askSave
  • _askReload
  • _commit
  • _notransaction
  • _notnull
  • _keepnull

Use the _reload option to reload the active table after executing the handler.

Use the _askSave option to suggest saving changes before executing the handler.

Use the _askReload option to suggest refreshing data after executing the asynchronous handler.

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

Use the _notransaction option to disable the transaction mode when executing the handler.

Use the _notnull option to prevent adding an empty value to parameter value lists.

Use the _keepnull option to add an empty value to parameter value lists if the value exists in the returned list.

SaveToDB, DBEdit, DBGate, and ODataDB also support the following options for outputs:

  • _none
  • _browser
  • _popup
  • _msgbox
  • _self
  • _new
  • <Sheet name>

Use the _none value to define executing the query in a silent mode.

Use the _browser value to define opening the HTTP query in an external browser.

Use the _popup value to show query results in a popup window that supports HTML and XML output.

Use the _msgbox value to define showing query results in a MessageBox window.

Use the _self value to define reloading the configured (active) object with a new query specified by a handler.

Use the _new value or a sheet name to create or open a worksheet or page with the specified query.

SaveToDB Specific Targets

The SaveToDB add-in additionally supports the following targets:

  • _TopWindow | _LeftWindow | _RightWindow | _BottomWindow
  • _taskpane [ _transpose | _transposeauto]
  • <Sheet names separated by a comma>
  • _saveas [<File name>[;CodePage=<Code page>][;Format=CSV][;Separator=;|,|Tab]]

SaveToDB 7-8 allows specifing an optional window position like _RightWindow for drill-down and master-detail queries.
This feature is supported in Excel 2007-2010 only.

Use the _taskpane value to show query results in a task pane window.

Popup windows and task panes have different behavior.
Popup windows are query-independent. So, users must close popup windows explicitly.
Task panes are related to source worksheets, and the add-in shows and hides task panes itself.

Use the _transpose option to transpose query results (rows become columns, and columns become rows).

This option is useful to show details selected as a single row in a two-column form.

Use the _transposeauto option to transpose single row results only and show multi-row results as is.

The TARGET_WORKSHEET field can contain a list of sheet names to use with the PDF and REPORT types.

Use the _saveas option to export the handler query data to a CSV file in one click from the Actions menu.

Default Output Target

HTTP queries are opened in the default web browser.

Single-cell results are opened in the internal web browser, while other results are shown in a popup window.

For example, you can return HTML or styled XML as a single value to show it in the internal web browser.

Generating Handler Configuration

You can use views to generate the handler configurations.

For example, the following view adds "See Online Help" links for every selectable object in the s02 schema.

CREATE VIEW s02.xl_actions_online_help
AS
SELECT
    t.TABLE_SCHEMA
    , t.TABLE_NAME
    , CAST(NULL AS nvarchar(128)) AS COLUMN_NAME
    , 'Actions' AS EVENT_NAME
    , t.TABLE_SCHEMA AS HANDLER_SCHEMA
    , 'See Online Help' AS HANDLER_NAME
    , 'HTTP' AS HANDLER_TYPE
    , 'https://www.savetodb.com/samples/sample'
        + SUBSTRING(t.TABLE_SCHEMA, 2, 2) + '-' + t.TABLE_NAME
        + CASE WHEN USER_NAME() LIKE 'sample%' THEN '_' + USER_NAME() ELSE '' END AS HANDLER_CODE
    , CAST(NULL AS nvarchar(128)) AS TARGET_WORKSHEET
    , 1 AS MENU_ORDER
    , 0 AS EDIT_PARAMETERS
FROM
    INFORMATION_SCHEMA.TABLES t
WHERE
    t.TABLE_SCHEMA = 's02'
    AND NOT t.TABLE_NAME LIKE 'xl_%'
UNION ALL
SELECT
    t.ROUTINE_SCHEMA AS TABLE_SCHEMA
    , t.ROUTINE_NAME AS TABLE_NAME
    , CAST(NULL AS nvarchar(128)) AS COLUMN_NAME
    , 'Actions' AS EVENT_NAME
    , t.ROUTINE_SCHEMA AS HANDLER_SCHEMA
    , 'See Online Help' AS HANDLER_NAME
    , 'HTTP' AS HANDLER_TYPE
    , 'https://www.savetodb.com/samples/sample'
        + SUBSTRING(t.ROUTINE_SCHEMA, 2, 2) + '-' + t.ROUTINE_NAME
        + CASE WHEN USER_NAME() LIKE 'sample%' THEN '_' + USER_NAME() ELSE '' END AS HANDLER_CODE
    , CAST(NULL AS nvarchar(128)) AS TARGET_WORKSHEET
    , 1 AS MENU_ORDER
    , 0 AS EDIT_PARAMETERS
FROM
    INFORMATION_SCHEMA.ROUTINES t
WHERE
    t.ROUTINE_SCHEMA = 's02'
    AND NOT t.ROUTINE_NAME LIKE 'xl_%'
    AND NOT t.ROUTINE_NAME LIKE '%_insert'
    AND NOT t.ROUTINE_NAME LIKE '%_update'
    AND NOT t.ROUTINE_NAME LIKE '%_delete'
    AND NOT t.ROUTINE_NAME LIKE '%_change'
    AND NOT t.ROUTINE_NAME LIKE '%_merge'

The view selects the same columns as defined in the xls.handlers table, except for ID.

You can use this technique for other configuration tables also.