Настройка обработки событий

Настройка обработки событий

Введение

Плагин SaveToDB позволяет программировать поведение приложений Microsoft Excel, используя объекты базы данных.

Поддерживается:

  • Обработка события изменения ячейки Change серверными хранимыми процедурами или с использованием SQL-кода.
  • Запуск детализирующих запросов в окно, браузер или на лист по событию двойного щелчка DoubleClick.
  • Обновление зависимых запросов по событию изменения активной ячейки SelectionChange.
  • Выполнение действий из контекстного меню Microsoft Excel и меню "Действия" на ленте плагина SaveToDB.

За счет обработки событий Excel можно:

  • изменять любые данные базы данных из Microsoft Excel при изменении ячеек,
  • защищать определенные колонки, строки или ячейки от изменений,
  • реализовывать уточняющие запросы,
  • реализовывать логику Master-Details и т.д.

За счет настройки контекстного меню и меню "Действия" можно:

  • выполнять запросы к базе данных, включая хранимые процедуры и SQL-код,
  • открывать http-запросы в браузере, всплывающем окне или на листе,
  • открывать текстовые файлы во всплывающем окне или на листе,
  • запускать макросы,
  • запускать команды Windows и CMD,
  • обновлять запросы к базе данных на указанных листах (листы справочников),
  • управлять параметрами активного запроса (например, для быстрого включения наиболее частых запросов).

Пункты контекстного меню и меню "Действия" могут быть переведены на бизнес-язык пользователей за счет возможностей перевода имен объектов.

Параметры обработчиков событий могут быть также переведены за счет возможностей перевода имен полей и параметров.

Возможные значения параметров обработчиков событий можно настроить за счет списков значений параметров.

Представление конфигурации обработчиков событий

Конфигурация обработчиков событий задается представлением*, которое должно содержать следующие поля:

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

* Для Microsoft SQL Server Compact используются таблицы, а не представления. Начиная с версии 7.0, также могут использоваться таблицы, а не представления.

Первым полем представления может быть поле идентификатора.

Плагин SaveToDB определяет конфигурационные представления по сигнатуре полей.

Можно использовать любую логику формирования представления, главное, чтобы оно содержало требуемый набор полей.

Формат представления:

TABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODETARGET_WORKSHEETMENU_ORDEREDIT_PARAMETERS
<Схема объекта>
См. таблицу 2.1
<Имя объекта>
См. таблицу 2.1
[<Имя колонки>]Actions
Change
ContextMenu
DoubleClick
SelectionChange
ConvertFormulas (v7.2)
DoNotConvertFormulas (v7.2)
DoNotSelect (v6.2)
DoNotSave (v6.2)
DoNotChange (v6.2)
ProtectRows (v6.9)
Formula (v6.2)
FormulaValue (v6.2)
ValidationList (v6.2)
SelectionList (v6.2)
<Схема
обработчика>
См. таблицу 2.2
<Имя
обработчика>
См. таблицу 2.2
<Тип
обработчика>
См. таблицу 2.2
<Код
обработчика>
См. таблицу 2.2
[<Цель вывода>]
См. таблицу 2.3
[<Порядковый
номер
в меню>]
[1 или 0]
Таблица 2.1
TABLE_SCHEMATABLE_NAME
<Схема таблицы><Имя таблицы>
<Схема представления><Имя представления>
<Схема процедуры><Имя процедуры>
<Схема объекта><Имя SQL-кода>
<Схема объекта><Имя http-запроса>
<Схема объекта><Имя text-запроса>
Таблица 2.2
HANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHADLER_CODE
<Схема процедуры><Имя процедуры>[PROCEDURE] 
<Схема функции><Имя функции>FUNCTION 
<Схема таблицы><Имя таблицы>TABLE 
<Схема представления><Имя представления>VIEW 
<Схема объекта><Имя SQL-кода>CODE<SQL-код>
<Схема объекта><Имя http-запроса>HTTP<http-запрос>
<Схема объекта><Имя text-запроса>TEXT<Имя файла>[;CodePage=<Код>]
<Схема объекта><Имя пункта меню>MACRO<Макрос Excel> [Параметры]
<Схема объекта><Имя пункта меню>CMD<Команда Windows Shell или CMD>
 <Имя обработчика >RANGE (v7.1)<Имя или адрес диапазон>
 <Имя обработчика >VALUES (v7.1)<Список значений>
 <Имя пункта меню>REFRESH 
 MenuSeparator[MENUSEPARATOR] 
Таблица 2.3
TARGET_WORKSHEET
<Имя листа> [<Позиция окна>]
_new [<Позиция окна>]
_self
_none
_msgbox
_popup
_taskpane
_browser
_reload
_transpose
_transposeauto
_saveas [<Имя файла>[;CodePage=<Код>]]
<Список листов для REFRESH>
Таблица 2.4
Позиция окна
_TopWindow
_LeftWindow
_RightWindow
_BottomWindow

Поля TABLE_SCHEMA и HANDLER_SCHEMA игнорируются в SQLite и Microsoft SQL Server Compact.

* Типы событий доступны только в SaveToDB 6.2 и выше. ProtectRows доступен в SaveToDB 6.9 и выше.

** Типы обработчиков RANGE и VALUES сочетаются только с типами ValidationList и SelectionList, и доступны в SaveToDB 7.1 и выше.

Описание полей представления конфигурации

Обязательные поля TABLE_SCHEMA и TABLE_NAME задают объект запроса на листе Excel, для которого определен обработчик. См. таблицу 2.1.

Поле COLUMN_NAME задает поле таблицы. Если значение равно NULL, то обработчик используется для всех полей таблицы.

Возможные значения обязательного поля EVENT_NAME:

EVENT_NAMEКомментарий
ActionsПункт меню "Действия"
ContextMenuПункт контекстного меню Excel
ChangeОбработчик события изменения ячейки
DoubleClickОбработчик события двойного клика
SelectionChangeОбработчик события изменения активной ячейки
DoNotSelect (v6.2)Обработчик блокирует выбор поля для вставки в Excel в Мастере подключения к данным.
DoNotSave (v6.2)Обработчик отключает сохранение значений колонки в базу данных.
DoNotChange (v6.2)Обработчик блокирует изменение значений колонки.
ProtectRows (v6.9)Обработчик блокирует добавление или удаление строк.
Formula (v6.2)Обработчик заменяет значения колонки на формулы, указанные в поле HANDLER_CODE.
FormulaValue (v6.2)Обработчик заменяет значения в колонке значениями, которые рассчитываются по формуле в колонке HANDLER_CODE.
Можно использовать специальные формулы =DomainUserName() и =UserName().
ValidationList (v6.2)Обработчик загружает значения из указанного объекта обработчика на скрытый лист SaveToDB_Lists и создает список проверки для указанной колонки.
Используйте пункт "Обновить, Обновить данные и конфигурацию" для обновления списков значений из базы данных.
SelectionList (v6.2)Обработчик загружает значения из указанного объекта обработчика на скрытый лист SaveToDB_Lists. Список отображается в панели выбора значений из списка.
Используйте пункт "Обновить, Обновить данные и конфигурацию" для обновления списков значений из базы данных.

* Типы событий доступны только в SaveToDB 6.2 и выше.

Возможные варианты значений полей HANDLER_SCHEMA, HANDLER_NAME и HANDLER_TYPE:

HANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODEКомментарий
<Схема процедуры><Имя процедуры>[PROCEDURE] Выполнение процедуры базы данных
<Схема функции><Имя функции>FUNCTION Выполнение функции база данных
<Схема таблицы><Имя таблицы>TABLE Запрос из таблицы базы данных
<Схема представления><Имя представления>VIEW Запрос из представления базы данных
<Схема объекта><Имя SQL-кода>CODE<SQL-код>Выполнение SQL-кода
<Схема объекта><Имя http-запроса>HTTP<http-запрос>Выполнение http-запроса
<Схема объекта><Имя text-запроса>TEXT<Имя файла>[;CodePage=<Код>]Запрос к текстовому файлу
<Схема объекта><Имя пункта меню>MACRO<Макрос Excel> [Параметры]Выполнение макроса Excel
<Схема объекта><Имя пункта меню>CMD<Команда Windows Shell или CMD>Выполнение команды Windows Shell или CMD
 <Имя пункта меню>REFRESH Обновление запросов на указанных листах
 MenuSeparator[MENUSEPARATOR] Вставка разделителя пунктов меню

Возможные значения поля TARGET_WORKSHEET:

TARGET_WORKSHEETКомментарий
<Имя листа> [<Позиция окна>]Результат будет выведен на указанный лист. Если он не существует, то лист будет создан.
_new [<Позиция окна>]Результат будет выведен в новую таблицу на новом листе, которая будет использоваться в дальнейшем.
_selfРезультат заменит таблицу, из которой вызван обработчик.
_noneРезультат вывода игнорируется и окно не создается.
_msgboxРезультат будет выведен в стандартное окно (версия 6.9 и выше).
_popupРезультат будет выведен во всплывающее окно.
_taskpaneРезультат будет выведен на панели задач.
_browserРезультат будет выведен во внешний (для http-запросов) или внутренний браузер.
_transposeПервая строка результата вывода в окно будет преобразована в вид Поле - Значение.
_transposeautoЕдинственная строка результата вывода в окно будет преобразована в вид Поле - Значение.
_reloadРезультаты обработчика используются для установки значений параметров текущего запроса с последующей перезагрузкой данных.
Новые значения параметров считываются из одноименных колонок в первой строке результата.
_saveas [<Имя файла>[;CodePage=<Код>]]
[;Format=CSV][;Separator=;/,/Tab]]
Результат обработчика будет сохранен в текстовый файл.
Поддерживается только CSV формат. Поддерживаются следующие разделители: запятая, точка с запятой, Tab.
<Список листов>В поле задается список листов для обновления запросов в режиме REFRESH.
NULLhttp-запросы выводятся в браузер.
Для серверных обработчиков:
если обработчик выводит результаты в таблицу, то он будет выведен во всплывающее окно;
если результат содержит только одну ячейку, то результат будет выведен во встроенный HTML-браузер.

Поле TARGET_WORKSHEET для листов Excel и значения _new может содержать дополнительные значения (через пробел):

Позиция окнаКомментарий
_RightWindowВывод в правое окно Excel.
_TopWindowВывод в верхнее окно Excel.
_LeftWindowВывод в левое окно Excel.
_BottomWindowВывод в нижнее окно Excel.

Указание окон для обработчиков событий SelectionChange позволяют реализовать логику Master-Details.

Окно активируется в двух случаях:

  1. Целевой лист вывода не существует и создается в первый раз.
  2. Целевой лист вывода существует, и лист активного запроса открыт в нормальном, а не распахнутом окне.

Если же лист вывода уже существует и активный лист открыт в распахнутом окне, то обработчик не выполняется.
Это позволяет отменить режим Master-Details по необходимости. Если же перевести окно в нормальный режим, то зависимые запросы начнут обновляться при изменении активной строки таблицы.

Поле MENU_ORDER позволяет сортировать пункты контекстного меню и меню действий.

Поле EDIT_PARAMETERS позволяет изменить поведение по умолчанию запуска диалога ввода параметров запроса.

Диалог ввода параметров запроса позволяет задать или изменить параметры запроса.
Это важно для реализации многих задач, например, создания новых позиций таблиц или изменения наименований.

По умолчанию, диалог запускается только для обработчиков меню "Действия".
Для контекстного меню, по умолчанию, запрос выполняется сразу, без запроса параметров.

Логика в этом следующая: контекстное меню используется для запуска уточняющих запросов, а меню "Действия" для выполнения различных действий.

Пример представления

(Показано в формате 4.х)

Комментарии к примеру:

  • Обработчики событий заданы для хранимой процедуры xls11.uspCalendar.
  • Обработчики событий действуют для всех полей таблицы, т.к. имена полей не заданы (поле COLUMN_NAME скрыто и содержит NULL).
  • Обработчики ContextMenu будут показаны в меню в указанном порядке.
  • Процедура uspExcelEvent_DoubleClick_xls11_uspCalendar используется для обработки нескольких событий.
  • Результаты процедуры uspExcelEvent_DoubleClick_xls11_uspCalendar для события SelectionChange будут выводиться на лист в правом окне.
  • Результаты обработчика Google Search будут открыты в браузере. Код http-запроса указан в поле HANDLER_SCHEMA, а тип HTTP указан в поле HANDLER_TYPE.

Параметры обработчиков событий

Обработчики событий могут получать значения параметров из следующих данных:

Источник значенияКомментарий
Одноименная колонка таблицыИспользуется имя колонки объекта до перевода наименования
Одноименный параметр на лентеНа ленте отображаются параметры запроса активной таблицы на листе Excel.
Параметры синхронизируются с именованными ячейками Excel.
Параметры с предопределенными именами (см. ниже) не выводятся на ленту.
Одноименная именованная ячейка ExcelЗначение именованной ячейки Excel
Одноименный параметр запроса активной таблицыНапример, обработчики событий запроса EXEC dbo.uspCustomers @ManagerID=101
могут использовать значение параметра ManagerID
Предопределенные параметры: 
WorkbookName, workbook_nameИмя активной книги не включая каталог
WorkbookPath, workbook_pathКаталог активной рабочей книги. Значение является пустым для новой книги.
SheetName, sheet_nameИмя активного листа
DataLanguage, data_languageЗначение содержит код языка данных, выбранного в настройках плагина
TableName, table_nameИмя объекта запроса
EventName, event_nameЗначения определяются типами из колонки EVENT_NAME таблицы 2
ColumnName, column_nameИмя активной колонки
CellValue, cell_valueЗначение активной ячейки. Используется текстовый тип для параметра, и преобразовывайте в нужный тип в SQL-коде.
CellAddress, cell_addressАдрес активной ячейки.
CellFormula, cell_formulaФормула активной ячейки в формате Range.Formula.
ChangedCellCount, changed_cell_countКоличество измененных ячеек.
ChangedCellIndex, changed_cell_indexНомер запроса в группе запросов обработки изменений.
TransactionID, transaction_idGUID группы операций.
TransactionStartTime, transaction_start_timeВремя UTC группы операций.
WindowsUserName, windows_user_nameИмя пользователя Windows.
SaveToDbVersion, savetodb_versionВерсия плагина SaveToDB.

Примечание: источники указаны в порядке убывания приоритета, значение колонки таблицы имеет наиболее высокий приоритет.

Таким образом, в дополнение к имеющимся в таблице данным, обработчики событий могут определить лист, таблицу, тип события, из какой колонки вызвано событие и значение активной ячейки.

За счет использования значений TableName, ColumnName и CellValue можно реализовывать универсальные обработчики.

Имена обработчиков событий для контекстного меню и меню "Действия"

Имена обработчиков могут быть переведены на бизнес-язык пользователей.
Это удобно, т.к. показываются понятные имена, а не имена объектов из базы данных.

См. Настройка перевода данных.

Также, иногда удобно указать в имени обработчика значения, с которыми он будет вызван.

Для этого можно использовать вставку в имени обработчика {ParameterName}.

Например:

Google Finance {Symbol}

Обработчики событий в виде SQL-кода

SQL-код может содержать параметры в виде:

  • @ParameterName для Microsoft SQL Server, Microsoft SQL Server Compact и SQLite.
  • :ParameterName для Oracle Database, IBM DB2, MySQL, MariaDB, PostgreSQL и NuoDB.

Например:

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

Обработчики событий в виде http-запросов

Можно использовать стандартный формат параметров url: ?ParameterName1=Value1&ParameterName2=Value2...

Но, этот формат не удобен, т.к. имена полей в таблице должны совпадать с именами параметров http-запроса.

В качестве альтернативы, можно переопределить параметры в виде: {ParameterName=Value} или просто {ParameterName}.

Например:

http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20%28%22{Symbol=YHOO}
%22%29&diagnostics=false&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys

В примере используется единственный параметр Symbol со значением по умолчанию YHOO.

http-запросы могут осуществляться к корпоративным или публичным веб-страницам, веб-сервисам или веб-сервисам OData.

Поддерживаются форматы XML, JSON, HTML и CSV.

При выводе на лист табличные данные извлекаются интеллектуальными алгоритмами, которые не требуют настройки.

Если какая-либо страница разбирается неправильно, вы можете обратиться в службу поддержки. Мы постараемся исправить ошибку.

Если веб-данные предоставляются только авторизованным пользователям, то данные авторизации будут запрошены в процессе подключения.

Поддерживаются следующие методы авторизации: Windows Forms, Basic, OAuth 1.0, OAuth 2.0.

Поддерживаются следующие провайдеры OAuth: Google, Facebook, LinkedIn, Twitter, Microsoft Azure Marketplace, Windows Live, Yahoo.

http-запросы можно использовать в меню "Действия" для вывода развернутой документации к активной таблице.

http-запросы можно использовать для открытия веб-сайтов или страниц из таблиц Excel.

Например, если таблица содержит поля Website и ProfileURL, то можно создать обработчики для открытия сайта и профиля:

EVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
ContextMenuhttp32Open website - {Website}HTTP{Website}
ContextMenuhttp32Open profile - {ProfileURL}HTTP{ProfileURL}

В контекстное меню пункты будут добавлены только для непустых полей.

Если веб-сайт указан без http://, то обработчик следует указать в виде http://{Website}

Имена обработчиков можно переводить с помощью перевода имен объектов. Например:

TABLE_SCHEMATABLE_NAMELANGUAGE_NAMETRANSLATED_NAMETRANSLATED_DESCTRANSLATED_COMMENT
http32Open website - {Website}enOpen website - {Website}  
http32Open profile - {ProfileURL}enOpen profile - {ProfileURL}  
http32Open website - {Website}ruОткрыть веб-сайт - {Website}  
http32Open profile - {ProfileURL}ruОткрыть профиль - {ProfileURL}  

Конечно, можно задать локализованные имена сразу в поле HANDLER_NAME, и по необходимости переводить их на другие языки.

Но первый подход лучше, т.к. TABLE_SCHEMA и TABLE_NAME используются во многих конфигурационных представлениях и должны быть "постоянными".
А за счет таблицы перевода, их наименования для пользователей можно менять без каких-либо последствий.

Обработчики событий в виде запросов к текстовым файлам

Имя файла и кодировка задаются в поле HANDER_CODE представления конфигурации списка запросов в формате:

<Имя файла>[;CodePage=<Код кодировки>]
Например:
Contacts.csv;CodePage=1251

Имя файла может быть указано относительно каталога рабочей книги.
Это позволяет переносить файлы данных вместе с книгой в другой каталог без изменения запросов.

В запросе можно указать параметры в виде: {ParameterName=DefaultValue}

Пример:

{FileName=Contacts.csv};CodePage={CodePage=1251}

В этом случае, параметры FileName и CodePage определяются из контекста запуска обработчика событий и могут быть изменены пользователем, если это разрешено.

Запуск макросов

Плагин SaveToDB удобно использовать для запуска макросов:

  • Все возможные действия пользователей сосредоточены в двух меню - контекстном меню Excel и меню "Действия" плагина SaveToDB.
  • Макросы настраиваются для каждого запроса и становятся доступны после подключения к данным, без дополнительных действий.
  • Макросы могут получать входные параметры из различных источников, как описано выше.

Макросы могут находиться в активной книге или же в специальной книге или книгах макросов, которые обновляются отдельно в рамках компании.

Формат запуска:

<Макрос Excel> [Параметр[, Параметр[, ...]]

где параметры могут быть указаны в формате {ParameterName=Value} или просто {ParameterName}.

Например:

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

При указании листа следует учитывать, что следует использовать встроенное имя листа Excel, а не имя листа для пользователей.

Требуемое имя макроса можно проверить через диалог запуска макросов (Alt-F8).

Важно! Плагин SaveToDB не проверяет текст макросов. Запускайте макросы только из доверенных источников!

Запуск команд Windows Shell и CMD

Плагин SaveToDB позволяет обновлять данные из текстовых файлов в форматах XML, JSON, HTML и CSV.

Для загрузки, получения, предварительной обработки или проверки этих файлов удобно использовать команды Windows Shell или CMD.

Формат запуска:

<Команда> [Параметр[ Параметр[ ...]]

где параметры могут быть указаны в формате {ParameterName=Value} или просто {ParameterName}.

Например:

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

Последний пример mailto: позволяет запустить создание нового письма указанному адресату с указанной темой и текстом.
Обработчик добавляется в меню, если поле получателя не является пустым.

Запуск команды в режиме cmd /c закрывает окно после выполнения, а cmd /k оставляет окно открытым.

Рабочим каталогом запуска команд является каталог активной рабочей книги, если она уже сохранена на диск.

Важно! Плагин SaveToDB не проверяет текст команды. Запускайте команды только из доверенных источников!

Обновление запросов в режиме REFRESH

Для проверки введенных пользователями значений удобно использовать возможность Excel Данные, Проверка данных.

В качестве источника возможных значений можно использовать значения из запроса к базе данных.

Например, список категорий контактов может использоваться для проверки значений поля категории в списке контактов.

При этом возникает необходимость обновления таких справочников, желательно всех одновременно.
В то же время, желательно обновить только справочники, а не запросы пользователя.

Для этого подходит обработчик событий REFRESH, который можно добавить в меню "Действия".

В поле TARGET_WORKSHEET следует указать перечень листов, на которых следует обновить запросы.

Удобно собрать все справочники на одном-двух скрытых листах, чтобы пользователь даже не видел их.

Проверка и отладка обработчиков

Проверить загруженные в книгу обработчики событий можно на вкладке "Обработчики событий" диалога "Информация о книге" меню "Помощь".

Создание конфигурации обработчиков событий в базе данных

В составе SaveToDB SDK можно найти готовые примеры конфигурации обработчиков,
а с помощью SaveToDB Framework можно быстро добавить эту функциональность в базы данных
Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, PostgreSQL, NuoDB и SQLite.

Этот веб-сайт использует куки. Продолжая использовать веб-сайт, Вы принимаете условия Cookie Policy.