Configuring Event Handlers

Configuring Event Handlers

The SaveToDB add-in allows processing Excel events like CellChange, SelectionChange, or DoubleClick on the server-side.

So, you may update database data on cell changes or implement master-detail interfaces and drill-down queries.

The SaveToDB add-in also allows customizing application behavior on the server-side.

You configure these features using event handler configurations.

You may install SaveToDB Framework into your database and use the xls.handlers table, or to create your own as described below.

Configuration Specification

SaveToDB reads the event handler configuration from tables and views with the following fields:

  1. An optional primary key column like 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

* The add-in does not use the TABLE_SCHEMA and HANDLER_SCHEMA field values with Microsoft SQL Server Compact and SQLite.

TABLE_SCHEMA and TABLE_NAME define schemas and names of configured database or code-based objects.

COLUMN_NAME specifies a column name. Set the NULL value to apply the handler to the entire table.

EVENT_NAME specifies an event name. See details below.

HANDLER_SCHEMA and HANDLER_NAME define schemas and names of database or code-based objects used to process events.

HANDLER_TYPE defines a handler object type. See details below.

HANDLER_CODE contains a code-based object code or additional parameters like a field list to select from tables and views.

TARGET_WORKSHEET defines an output target. See details below.

MENU_ORDER allows sorting menu items. Use integer values.

EDIT_PARAMETERS allows changing the default behavior for launching menu items: 1 - confirm parameters, 0 - run without confirmation.

Event Types

The EVENT_NAME field may contain the following values:

  • Actions, ContextMenu
  • Change
  • DoubleClick
  • SelectionChange
  • ConvertFormulas, DoNotConvertFormulas
  • DoNotSelect, DoNotSave, DoNotChange, ProtectRows, DoNotAddValidation
  • Formula, FormulaValue
  • AddStateColumn
  • DefaultListObject
  • SyncParameter
  • ManyToMany, DoNotAddManyToMany
  • KeepFormulas, DoNotKeepFormulas
  • ParameterValues
  • ValidationList, SelectionList

Action-Based Handlers

The SaveToDB add-in adds items of the Actions type to the Actions menu and items of the ContextMenu type to the Excel context menu.

The add-in executes the handlers of the Change type on cell change, DoubleClick type on double-click, and the SelectionChange type on selection change.

Usually, you implement such handlers as procedures or SQL codes. The add-in passes values of the current table row as parameters.

Declarative Handlers

Handlers of this group configure the add-in behavior only.

ConvertFormulas handlers force converting text formulas from a database to Excel formulas in the specified column. See Using Formulas.

DoNotConvertFormulas handlers prevent converting text formulas from a database to Excel formulas in the specified column.

DoNotSelect handlers prevent selecting the column in the Connection Wizard (however, users may edit the query in Excel manually).

DoNotSave handlers exclude the specified column from the INSERT and UPDATE statements used to save changes or prevent saving changes for the entire table.

The add-in prevents changes in columns marked with the DoNotChange event type.

Also, the add-in prevents adding or deleting rows in tables marked with the ProtectRows event type.

DoNotAddValidation handlers prevent adding automatic validation lists.

The add-in replaces column values with an Excel formula specified in the HANDLER_CODE field for the Formula handlers.

The add-in replaces column values with values calculated by a formula specified in the HANDLER_CODE field for the FormulaValues handlers.

You may use Excel formulas or special built-in formulas: =DomainUserName() and =UserName().

The AddStateColumn handler defines adding the _State_ column to an Excel table. Users may set 0 in this column to delete rows.

The DefaultListObject handler defines the default table on a worksheet. The add-in shows ribbon elements for such tables even the active cell is outside of the table.

The SyncParameter handler links parameters for two objects like an @order_id parameter of order_header and header_details objects.
Specify opposite handlers for both objects.

Use the ManyToMany handler to mark fields of foreign keys in tables with many-to-many relationships. The add-in activates a special mode to edit such relations.

The add-in adds ManyToMany handlers automatically when it detects many-to-many relationships. Use the DoNotAddManyToMany handler to disable this feature for the specified table.

Use the KeepFormulas handler to specify a COLUMN_NAME column used to save and restore user formulas.
Other users get updated formulas after reload too. For example, finance managers can see formulas used to calculate budget values in budget requests.

Use the DoNotKeepFormulas handler to disable saving formulas for the entire table or only for the column specified in the COLUMN_NAME field.
Disable columns, for example, to always have actual values in Excel.

Parameter Values

ParameterValues items configure queries used to populate ribbon parameter value lists.

SaveToDB 8 allows using event handler configuration for this. The previous versions use separate tables.

See details in Parameter Value Lists.

Validation and Selection Lists

The ValidationList handlers define queries used as a source of Excel validation lists.

If the query returns a single column, the add-in just loads values into the hidden SaveToDB_Lists worksheet and create a validation list for the specified column.

If the query returns two or more columns, the add-in additionally converts loaded column values of the underlying table to names.

The SelectionList handlers allow selecting values using the List Editor task pane and do not modify the underlying table.

Handler Types

The HANDLER_TYPE field may contain the following values:

  • PROCEDURE, FUNCTION, TABLE, VIEW
  • CODE, HTTP, TEXT
  • MENUSEPARATOR, MACRO, CMD, REFRESH
  • RANGE, VALUES
  • PDF, REPORT
  • SELECTSHEET
  • SHOWSHEETS, HIDESHEETS

Use the PROCEDURE, FUNCTION, TABLE, and VIEW values for database objects used to process events.

Use the CODE, HTTP, and TEXT values for code-based objects.

Use the MENUSEPARATOR type to define menu separators of the Actions menu and the Excel context menu.

You may use a non-unique name like MenuSeparator for such items in the HANDLER_NAME field. However, specify different values in the MENU_ORDER field.

The MACRO type defines an Excel VBA macro. Place the name of the macro and its optional parameters into the HANDLER_CODE field.

The CMD type defines a Windows Shell or CMD commands. Place the command and its optional parameters into the HANDLER_CODE field.

The REFRESH type defines a command used to refresh data tables on the worksheets specified in the TARGET_WORKSHEET field, separated by commas.

For example, this feature allows refreshing data tables used as sources of validation lists.

The MENUSEPARATOR, MACRO, CMD, and REFRESH types are applicable for Actions and ContextMenu event handlers only.

The RANGE type defines 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.

The VALUES type defines values used as a data source for the ParameterValues, ValidationList, and SelectionList handlers.
Specify values separated by comma or semicolon in the HANDLER_CODE field.

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

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

Use the SELECTSHEET type 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 the SHOWSHEETS type to show sheets and the HIDESHEETS type to hide sheets specified in the HANDLER_CODE field.
The 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.

.

Output Targets

The TARGET_WORKSHEET field may contain the following values:

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

Use a sheet name and an optional window position for drill-down and master-detail queries that load data to worksheets.

Specify sheet names to use with the PDF and REPORT types.

The _new value defines creating a new worksheet.

The _self value defines reloading the configured object with a new query specified by a handler.

The _none value defines executing the query in a silent mode.

The _browser value defines opening the HTTP query in a browser.

The _popup value defines showing query results in a popup window.

The _msgbox value defines showing query results in a MessageBox window.

The _taskpane value defines showing query results in a task pane window.

Popup windows and task panes have a 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.

The _transpose option defines transposing 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.

The _transposeauto option defines transposing single row results only.

Use the _reload option to reload the source table after executing the query.

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

Default Behavior

The add-in opens HTTP queries in the default web browser.

The add-in shows single-cell results in the internal web browser and other results in the popup windows.

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

Handler Parameters

Handlers may have parameters to get values from table cells, named cells, ribbon parameters.

See Using parameters.

Using Parameter Values in Context Menu Item Names

You may include parameter values into handler names of non-database objects using the {ParameterName} form.

For example: Open website - {Website}

Translating Context and Action Menu Items

You may translate handler names to multiple languages using the TRANSLATED_NAME field of the object name translation configuration.

Customizing Queries from Tables and Views

You may customize queries from tables and views using the HANDLER_CODE field.

You may specify comma-separated field names to select.

For example:

id, name

Also, you may use the following modifiers as field name prefixes:

+ORDER BY ASC
-ORDER BY DESC
@WHERE

For example, if you add the following code to the HANDLER_CODE field for the dbo.view_subcategories view:

id,+name,@category

The add-in will generate the following SQL code:

SELECT [id], [name] FROM [dbo].[view_subcategories] WHERE [category] = @category ORDER BY [name]

As you may see, this is a light-way alternative to using SQL codes described below.

Using SQL Code

You may use SQL codes in the HANDLER_CODE field.

For example:

SELECT * FROM dbo.StockTradeHistory th WHERE th.Symbol = @Symbol

See Code-Based Objects.

Using HTTP Queries

You may use HTTP queries in the HANDLER_CODE field.

For example:

http://www.google.com/finance/historical?q={Symbol=AAPL}

Use HTTP query handlers to add documentation links to the Actions menu.

See Code-Based Objects.

Using Text File Queries

You may specify file names and code pages can in the HANDER_CODE field in the following format:

<File name>[;CodePage=<Code page>]
For example:
Contacts.csv;CodePage=65001

See Code-Based Objects.

Running Macros

You may configure the add-in to launch Excel macros. This way gives additional benefits:

  • All possible user actions are located in two menus: the Excel context menu and the SaveToDB Actions menu.
  • Macros are linked to specific queries and are available immediately after connecting data.
  • Macros can have input parameters from different value sources as described above.

Macros can be located in active workbooks or in separate macro workbooks that can be updated independently.

Specify macros in the HANDLER_CODE field in the following format:

<Excel macro> [<Parameter1>[, <Parameter2>[, ...]]

Macros may use context values in the following format: {ParameterName=DefaultValue} or simply {ParameterName}

For example:

SayHello "World"
SayHello {Name=World}
Sheet1.SayHello {FirstName}, {LastName}
MacroWorkbook.xlsm!SayHello {FirstName}, {LastName}

Note that a macro sheet name is an internal Excel name, not a user visible name.
You may check macro names using the Excel dialog box for launching macros (Alt-F8).

Important! SaveToDB does not check macros. Run macros from trusted sources only!

Running Windows Shell and CMD Commands

SaveToDB allows loading and refreshing data from text files in the following formats: XML, JSON, HTML, and CSV.

You may use Windows Shell and CMD commands for loading, preparing, or checking such files.

Specify commands in the HANDLER_CODE field in the following format:

<Command> [<Parameter1>[ <Parameter2>[ ...]]

Commands can use context values in the following format: {ParameterName=DefaultValue} or simply {ParameterName}

Command examples:

{FileName}
notepad.exe {FileName}
dir *.*
cmd /c dir {Mask=*.*}
cmd /k dir {Mask=*.*}
sayhello.cmd {FirstName}
mailto:{Email}&subject=Thanks for the connection&body=Hello {FirstName},%0A

The latest example shows how to launch a new email using the mailto: command.
The mailto: handlers are added to menus for non-empty recipients only.

The cmd /c mode is used to execute a command and to close the window after execution, and the cmd /k mode is used to leave the window opened.

The working directory is the active workbook directory if the workbook has been already saved.

Important! SaveToDB does not check commands. Run commands from trusted sources only!

Refreshing Queries using REFRESH Handlers

Microsoft Excel allows validating user input. See Data, Data Tools, Data Validation.

Values from database queries can be used as validation lists.

For example, a contact category list can be used for the validation of category column values in a contact list.

In addition, such validation lists should be updated regularly and consistently.

You may use the REFRESH handler added to the Actions menu. Specify the worksheets that contain validation lists in the TARGET_WORKSHEET field.

It is a good practice to place the validation lists on one or two hidden sheets.

Checking and Debugging Configuration

Use the Workbook Information dialog box to check the loaded configurations.

Use also logging SQL queries using the Options dialog box.

Creating Database Configuration Objects

You may create the configuration tables or views yourself.

This is useful when your application must create the configuration dynamically.

In other cases, just install SaveToDB Framework to your database using the Application Installer.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.