xls.handlers

The xls.handlers table lets you configure various features effectively.

Table Structure

It has the following structure:

ID	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE	TARGET_WORKSHEET	MENU_ORDER	EDIT_PARAMETERS

Example Configuration

Here’s an example of a configuration for a cell change handler:

ID	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE
	s02	usp_cashbook3		Change	s02	usp_cashbook3_change	PROCEDURE

You can create similar tables or views in your schemas by copying the xls.handlers table or the xls.view_handlers view if it makes sense for your use case.

You can even generate handlers using views.

Logical Sections of the Table

The table consists of the following logical sections:

ID
TABLE_SCHEMA, TABLE_NAME, and COLUMN_NAME
EVENT_NAME
HANDLER_SCHEMA, HANDLER_NAME, HANDLER_TYPE, and HANDLER_CODE
TARGET_WORKSHEET, MENU_ORDER, and EDIT_PARAMETERS

ID is optional for views.
TABLE_SCHEMA, TABLE_NAME, and COLUMN_NAME define the object or column being configured.
EVENT_NAME defines the event or feature being configured.
HANDLER_SCHEMA, HANDLER_NAME, HANDLER_TYPE, and HANDLER_CODE define the object used to handle the event or feature.
TARGET_WORKSHEET customizes the handler.
MENU_ORDER specifies the order of menu items in the application and context menus.
EDIT_PARAMETERS controls the visibility of the Edit Parameters dialog box before executing menu items.

EVENT_NAME Values

The EVENT_NAME field supports around 50 event types.

Server-Side Handlers

The first group configures server-side handlers for events or features:

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

Object-Level Behavior Modifications

The second group modifies behavior at the object level:

Column Properties Modifications

The next group modifies 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

Internal Event Types

There are two event types used internally by the SaveToDB products:

License
Information

SelectPeriod

SaveToDB and DBEdit allow using date ranges for filters on fields of tables and views. You can also use two procedure parameters to define the start and end of the period.

Customize the behavior for fields and period beginning parameters using the SelectPeriod event type.

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

The pair parameter name is required.
The first DayOfWeek must be a number from 0 to 7 or in English. Use Monday or 1 if the week starts on Monday.
Hide options include: HideThis, HideNext, HideDays, HideWeeks, HideMonths, HideYears, HideThisMonth.

Separators can be commas, semicolons, or spaces.

HANDLER_TYPE Values

The HANDLER_TYPE field specifies the 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 that 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 define fixed values for the ParameterValues, ValidationList, and SelectionList handlers. Specify values separated by commas or semicolons in the HANDLER_CODE field.

Use ATTRIBUTE instead of NULL when other types do not apply.

SaveToDB and DBEdit Specific Handler Types

SaveToDB and DBEdit support the following types for managing worksheets:

SELECTSHEET
SHOWSHEETS
HIDESHEETS

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

Use SHOWSHEETS to show sheets and HIDESHEETS to hide sheets specified in the HANDLER_CODE field. The HANDLER_CODE can contain a comma- or semicolon-separated list of sheets or database object names. The TARGET_WORKSHEET field can also specify 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 to refresh data tables on the worksheets specified in the TARGET_WORKSHEET field, separated by commas.

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

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

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

TARGET_WORKSHEET Values

The TARGET_WORKSHEET field customizes handler features. The name can be a bit misleading, as it originated 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 prompt saving changes before executing the handler.
Use the _askReload option to suggest refreshing data after executing the asynchronous handler.
Use the _commit option with cell change handlers to indicate that the handler commits changes to a database.
Use the _notransaction option to disable 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 it exists in the returned list.

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

_none
_browser
_popup
_msgbox
_self
_new
<Sheet name>
Use the _none value for silent query execution.
Use the _browser value to open the HTTP query in an external browser.
Use the _popup value to display query results in a popup window that supports HTML and XML output.
Use the _msgbox value to show query results in a MessageBox window.
Use the _self value to reload 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:

_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 specifying 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 display query results in a task pane window.

Popup windows and task panes behave differently. Popup windows are query-independent and must be closed explicitly by users, while task panes are linked to source worksheets, and the add-in manages their visibility.

Use the _transpose option to transpose query results (rows become columns and vice versa). This option is useful for displaying details selected as a single row in a two-column format.

Use the _transposeauto option to transpose single-row results only, while showing multi-row results as is.

The TARGET_WORKSHEET field can contain a list of sheet names for 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 open in the default web browser. Single-cell results display in the internal web browser, while other results appear 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 handler configurations.

For instance, 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'

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

You can apply this technique to other configuration tables as well.