Настройка плагина SaveToDB

Настройка плагина SaveToDB

Настройка на стороне сервера

Конфигурирование плагина выполняется на серверной стороне и позволяет решать следующие задачи:

  • Настроить объекты базы данных, SQL-код, http-запросы, текстовые файлы для подключения через список запросов на ленте.
  • Настроить сохранение данных, полученных из представлений, хранимых процедур, SQL-кода, http-запросов или текстовых файлов.
  • Настроить сохранение данных за счет обработчика событий изменения ячейки Change.
  • Настроить контекстные запросы к базе данных или веб по событию DoubleClick.
  • Настроить логику Master-Details за счет обработчика событий SelectionChange.
  • Настроить контекстные запросы к базе данных или веб, запуск макросов и команд Windows Shell и CMD из контекстного меню Microsoft Excel и меню плагина "Действия" на ленте.
  • Настроить перевод имен объектов, полей и параметров на бизнес-язык пользователей.
  • Настроить списки значений параметров запросов для выбора на ленте и в диалогах запуска запросов.
  • Настроить возможность сохранения и загрузки форматов таблиц Excel из базы данных.
  • Настроить формулы Microsoft Excel в объектах базы данных, включая формулы DDE и RTD (real-time data).

В данном разделе описаны требования со стороны плагина SaveToDB.

В основном, это требования к сигнатуре полей представлений и параметрам процедур.
Плагин определяет конфигурационные представления именно по сигнатуре полей, а не по наименованиям объектов.

Разработчик баз данных свободен в выборе реализации серверной части.

Наиболее быстрый способ добавить настраиваемые возможности в базу данных - воспользоваться готовыми решениями SaveToDB Framework.

Установка с помощью Мастера установки SaveToDB Framework в любую базу данных займет минуту.

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

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

Разделы настройки:

Настройка списка запросов

Введение

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

Это удобно, т.к. в книге Microsoft Excel не нужно делать много листов, а можно динамически переключать запросы на одном листе.

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

Список запросов задается для каждого объекта базы данных в Мастере подключения к базе данных и может быть изменен позже.

После подключения первого запроса пользователь может переключать запросы из "Списка запросов" на ленте.

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

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

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

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

Списки запросов могут содержать таблицы, представления, хранимые процедуры, SQL-код, http-запросы и запросы к текстовым файлам.

Представление конфигурации списка запросов

Представления* списка запросов должны содержать только следующие восемь полей:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_TYPE
  4. TABLE_CODE
  5. INSERT_PROCEDURE
  6. UPDATE_PROCEDURE
  7. DELETE_PROCEDURE
  8. PROCEDURE_TYPE

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

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

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

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

TABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDUREPROCEDURE_TYPE
<Схема таблицы><Имя таблицы>TABLE [<Процедура>
/<SQL-код>]
[<Процедура>
/<SQL-код>]
[<Процедура>
/<SQL-код>]
[PROCEDURE
/CODE]
<Схема представления><Имя представления>VIEW [<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[TABLE
/PROCEDURE
/CODE]
<Схема процедуры><Имя процедуры>PROCEDURE [<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[TABLE
/PROCEDURE
/CODE]
<Схема объекта><Имя SQL-кода>CODE<SQL-код>[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[TABLE
/PROCEDURE
/CODE]
<Схема объекта><Имя http-запроса>HTTP<http-запрос>[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>][<Таблица>][TABLE
/PROCEDURE
/CODE]
<Схема объекта><Имя text-запроса>TEXT<Имя файла>
[;CodePage=<Код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>][<Таблица>][TABLE
/PROCEDURE
/CODE]

Поля TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE и TABLE_CODE относятся к объекту данных. Можно задать шесть типов объектов:

  • Таблица базы данных (TABLE).
  • Представление базы данных (VIEW).
  • Хранимая процедура базы данных (PROCEDURE).
  • Запрос к базе данных - SQL-код (CODE).
  • http-запрос (HTTP).
  • Запрос к текстовому файлу в формате XML, JSON, HTML и CSV (TEXT).

Для последних трех типов запросов код запроса указывается в поле TABLE_CODE, а обязательные схема и наименование в полях TABLE_SCHEMA и TABLE_NAME.
Значение поле TABLE_SCHEMA можно выбрать произвольно. Важно, чтобы оно не пересекалось с имеющимися объектами базы данных.

Поля INSERT_PROCEDURE, UPDATE_PROCEDURE, DELETE_PROCEDURE используются для настройки сохранения изменений в БД и могут быть NULL.

Представление конфигурации списка запросов в формате 3.x-4.x

Плагин также читает представления в формате 3.х-4.х, которые должны содержать только следующие шесть полей:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_TYPE
  4. INSERT_PROCEDURE
  5. UPDATE_PROCEDURE
  6. DELETE_PROCEDURE

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

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

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

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

TABLE_SCHEMATABLE_NAMETABLE_TYPEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDURE
<Схема таблицы><Имя таблицы>TABLE[<Процедура>
/<SQL-код>]
[<Процедура>
/<SQL-код>]
[<Процедура>
/<SQL-код>]
<Схема представления><Имя представления>VIEW[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
<Схема процедуры><Имя процедуры>PROCEDURE[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
<SQL-код><Имя SQL-кода>CODE[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
<http-запрос><Имя http-запроса>HTTP[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>][<Таблица>]
<Имя файла>[;CodePage=<Код>]<Имя text-запроса>TEXT[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>][<Таблица>]

Поля TABLE_SCHEMA, TABLE_NAME и TABLE_TYPE относятся к объекту данных. Можно задать шесть типов объектов:

  • Таблица базы данных (TABLE).
  • Представление базы данных (VIEW).
  • Хранимая процедура базы данных (PROCEDURE).
  • Запрос к базе данных - SQL-код (CODE).
  • http-запрос (HTTP).
  • Запрос к текстовому файлу в формате XML, JSON, HTML и CSV (TEXT).

Для последних трех типов запросов код запроса указывается в поле TABLE_SCHEMA, а обязательное наименование в поле TABLE_NAME.

Поля INSERT_PROCEDURE, UPDATE_PROCEDURE, DELETE_PROCEDURE используются для настройки сохранения изменений в БД и могут быть NULL.

Объекты в виде 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-запросов

http-запрос также может содержать параметры, которые выводятся на ленту.

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

Фактически, url запроса из браузера просто вставляется в поле TABLE_CODE.

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

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

Например:

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, Windows Live, Yahoo и Yahoo API Key.

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

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

<Имя файла>[;CodePage=<Код кодировки>]

Например:

Contacts.csv;CodePage=1251

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

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

Пример:

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

В этом случае, на ленту будут выведены параметры FileName и CodePage.

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

Пример представления списка запросов:

Пример представления конфигурации списка запросов

Создание списков запросов в базе данных

Для создания представлений списков запросов удобно использовать базовое представление списка запросов из состава SaveToDB Framework.

Настройка сохранения изменений

Введение

Плагин SaveToDB позволяет сохранять данные и их изменения из Microsoft Excel в базу данных или веб-сервис OData.

Плагин поддерживает следующие сценарии:

Источник данныхВариант сохраненияЗапуск
Таблица базы данныхСохранение таблиц по умолчаниюКнопка Save
Представление, процедура или SQL-кодСохранение в базовую таблицуКнопка Save
Представление, процедура или SQL-кодСохранение SQL-кодомКнопка Save
Представление, процедура или SQL-кодСохранение хранимыми процедурамиКнопка Save
OData EntitySetСохранение OData по умолчаниюКнопка Save
http-запрос или текстовый файлСлияние в базовую таблицуКнопка Save
http-запрос или текстовый файлСлияние SQL-кодомКнопка Save
http-запрос или текстовый файлСлияние хранимой процедуройКнопка Save
Любой источник данныхСохранение обработчиком событийСобытие Change
Любой источник данныхМастер публикацииЗапуск мастера
Любой источник данныхМастер слиянияЗапуск мастера

Сценарии делятся на три большие группы:

  1. Сохранение по кнопке "Сохранить" (Save).
  2. Сохранение изменений по событию изменения ячейки Change за счет серверного обработчика событий.
  3. Публикация данных или слияние данных с применением соответствующего мастера.

Последние два варианта рассмотрены в соответствующих разделах документации. Ниже рассматривается только первый вариант.

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

  1. Сохранение изменений, включая вставку новых, обновление существующих и удаление удаленных записей.
  2. Слияние данных, включая вставку новых записей и обновление существующих.

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

Варианты реализации сохранения или слияния делятся на следующие группы:

  1. Сохранение данных таблиц или OData EntitySet, работающее по умолчанию.
  2. Сохранение или слияние данных в базовую таблицу встроенными процедурами.
  3. Сохранение или слияние данных за счет хранимых процедур.
  4. Сохранение или слияние данных за счет SQL-кода.

Сохранение данных OData и за счет хранимых процедур требует редакции SaveToDB Standard, Enterprise или пробной версии.

Для редактируемых запросов плагин SaveToDB сохраняет информацию, необходимую для последующей генерации запросов на изменение данных, на скрытых листах книги.
При этом книга может быть закрыта и открыта сколько угодно раз. Листы данных вставляются в книгу при подключении к данным или в настройках плагина.

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

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

Представление конфигурации списка запросов

Представления* списка запросов должны содержать только следующие восемь полей:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_TYPE
  4. TABLE_CODE
  5. INSERT_PROCEDURE
  6. UPDATE_PROCEDURE
  7. DELETE_PROCEDURE
  8. PROCEDURE_TYPE

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

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

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

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

TABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDUREPROCEDURE_TYPE
<Схема таблицы><Имя таблицы>TABLE [<Процедура>
/<SQL-код>]
[<Процедура>
/<SQL-код>]
[<Процедура>
/<SQL-код>]
[PROCEDURE
/CODE]
<Схема представления><Имя представления>VIEW [<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[TABLE
/PROCEDURE
/CODE]
<Схема процедуры><Имя процедуры>PROCEDURE [<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[TABLE
/PROCEDURE
/CODE]
<Схема объекта><Имя SQL-кода>CODE<SQL-код>[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[TABLE
/PROCEDURE
/CODE]
<Схема объекта><Имя http-запроса>HTTP<http-запрос>[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>][<Таблица>][TABLE
/PROCEDURE
/CODE]
<Схема объекта><Имя text-запроса>TEXT<Имя файла>
[;CodePage=<Код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>][<Таблица>][TABLE
/PROCEDURE
/CODE]

Поля TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE и TABLE_CODE относятся к объекту данных.

Поля INSERT_PROCEDURE, UPDATE_PROCEDURE и DELETE_PROCEDURE задают базовую таблицу, процедуры или SQL-код сохранения изменений.

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

Представление конфигурации списка запросов в формате 3.x-4.x

Плагин также читает представления в формате 3.х-4.х, которые должны содержать только следующие шесть полей:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_TYPE
  4. INSERT_PROCEDURE
  5. UPDATE_PROCEDURE
  6. DELETE_PROCEDURE

* Для Microsoft SQL Server Compact используется таблица, а не представление.

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

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

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

TABLE_SCHEMATABLE_NAMETABLE_TYPEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDURE
<Схема таблицы><Имя таблицы>TABLE[<Процедура>
/<SQL-код>]
[<Процедура>
/<SQL-код>]
[<Процедура>
/<SQL-код>]
<Схема представления><Имя представления>VIEW[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
<Схема процедуры><Имя процедуры>PROCEDURE[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
<SQL-код><Имя SQL-кода>CODE[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>
/<Процедура>
/<SQL-код>]
<http-запрос><Имя http-запроса>HTTP[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>][<Таблица>]
<Имя файла>[;CodePage=<Код>]<Имя text-запроса>TEXT[<Таблица>
/<Процедура>
/<SQL-код>]
[<Таблица>][<Таблица>]

Поля TABLE_SCHEMA, TABLE_NAME и TABLE_TYPE относятся к объекту базы данных, для которого выполняется настройка.

Поля INSERT_PROCEDURE, UPDATE_PROCEDURE и DELETE_PROCEDURE задают базовую таблицу, процедуры или SQL-код сохранения изменений.

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

Сохранение изменений в базовую таблицу

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

Например, следующая процедура выводит данные из одной таблицы:

CREATE PROCEDURE [xls].[uspStockHistory]
    @Symbol varchar(5) = 'YHOO'
AS
BEGIN
    SET NOCOUNT ON
    SELECT
        *
    FROM
        dbo.StockTradeHistory th
    WHERE
        th.Symbol = @Symbol
    ORDER BY
        th.[Date] DESC
END

Данные этой процедуры могут быть сохранены в базу данных, если указать в соответствующих полях таблицу dbo.StockTradeHistory.

Если базовая таблица указана для http-запросов или запросов к текстовым файлам, то сохранение выполняется слиянием.

Необходимые поля можно формировать формульными колонками Excel. Например, их исходного поля Name можно получить поля FirstName и LastName для сохранения.

Сохранение изменений с использованием SQL-кода и хранимых процедур

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

При сохранении данных плагин вызывает SQL-код или процедуру INSERT_PROCEDURE для новых записей в таблице Excel, UPDATE_PROCEDURE для измененных и DELETE_PROCEDURE для удаленных.

В режиме слияния данных SQL-код или процедура INSERT_PROCEDURE вызывается для всех записей таблицы. В этом случае логика слияния должна быть реализована в коде.

Значения параметров SQL-кода и хранимых процедур могут формироваться из различных источников. Наиболее приоритетный источник - значения из колонок таблицы Excel с соответствующим параметру наименованием.

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

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

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

Пример представления конфигурации списка запросов

Встроенный запрос списка запросов плагина SaveToDB

Встроенный в плагин Список запросов по умолчанию использует окончания "_insert", "_update", "_delete" в наименованиях процедур для автоматического связывания процедур сохранения данных и базового объекта.
Если базовый объект содержит окончание "_select", то оно отбрасывается. Например, для объекта uspBudget_select автоматически определилась процедура вставки uspBudget_insert.

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

Проверка и отладка конфигурации

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

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

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

Для создания представлений списков запросов удобно использовать базовое представление списка запросов и таблицу конфигурации из состава SaveToDB Framework.

В состав SaveToDB Framework for Microsoft SQL Server также включена процедура генерации процедур INSERT, UPDATE, DELETE.

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

Введение

Плагин 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 и выше.

Представление конфигурации обработчиков событий в формате 3.x-4.x

В целях обеспечения совместимости с предыдущими версиями, поддерживается также вариант представления конфигурации в формате 4.x, которое должно содержать следующие поля:

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

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

TABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPETARGET_WORKSHEETMENU_ORDEREDIT_PARAMETERS
<Схема объекта БД>
или <Тип объекта>
См. таблицу 3.1
<Имя объекта>
См. таблицу 3.1
[<Имя колонки>]Actions
Change
ContextMenu
DoubleClick
SelectionChange
<Схема
обработчика>
См. таблицу 3.2
<Имя
обработчика>
См. таблицу 3.2
<Тип
обработчика>
См. таблицу 3.2
[<Цель вывода>]
См. таблицу 3.3
[<Порядковый
номер
в меню>]
[1 или 0]
Таблица 3.1
TABLE_SCHEMATABLE_NAME
<Схема таблицы><Имя таблицы>
<Схема представления><Имя представления>
<Схема процедуры><Имя процедуры>
CODE<Имя SQL-кода>
HTTP<Имя http-запроса>
TEXT<Имя text-запроса>
Таблица 3.2
HANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPE
<Схема процедуры><Имя процедуры>[PROCEDURE]
<Схема функции><Имя функции>FUNCTION
<Схема таблицы><Имя таблицы>TABLE
<Схема представления><Имя представления>VIEW
<SQL-код><Имя SQL-кода>CODE
<http-запрос><Имя http-запроса>HTTP
<Имя файла>[;CodePage=<Код>]<Имя text-запроса>TEXT
MenuSeparator[MENUSEPARATOR]
<Макрос Excel> [Параметры]<Имя пункта меню>MACRO
<Команда Windows Shell или CMD><Имя пункта меню>CMD
<Имя пункта меню>REFRESH
Таблица 3.3
TARGET_WORKSHEET
<Имя листа> [<Позиция окна>]
_new [<Позиция окна>]
_self
_popup
_browser
_reload
_saveas [<Имя файла>[;CodePage=<Код>]]
<Список листов для REFRESH>
Таблица 3.4
Позиция окна
_TopWindow
_LeftWindow
_RightWindow
_BottomWindow

В версии 4.х код обработчиков событий задается в поле HANDLER_SCHEMA, а в версии 5.0 в поле HANDER_CODE, что делает представление более логичным, и позволяет использовать типы, которые содержат большой текст.

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

Представление конфигурации обработчиков событий в формате 1.x-2.x

В целях обеспечения совместимости с предыдущими версиями, поддерживается также усеченный вариант представления конфигурации:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. COLUMN_NAME
  4. EVENT_NAME
  5. HANDLER_NAME
  6. TARGET_WORKSHEET

Но этот вариант не позволяет настроить новые возможности версии 3.0.

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

Обязательные поля 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] Вставка разделителя пунктов меню

Формат версии 3.0 предполагает указание данных в трех полях, версии 4.0 - в четырех полях.
В целях совместимости с существующими конфигурационными представлениями предыдущих версий 1.x-2.x читаются следующие форматы:

HANDLER_NAMEКомментарий
<Схема>.<Имя>Полное имя существующего объекта базы данных.
<http-запрос>Формат: [<Имя обработчика>=]<url запроса>.

Возможные значения поля 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.

Настройка значений параметров ленты

Введение

Если запрос активной таблицы является хранимой процедурой, SQL-кодом, http-запросом или запросом к текстовому файлу и содержит параметры, то плагин SaveToDB автоматически отображает параметры на ленте.

При изменении параметров запрос перестраивается с новыми значениями.

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

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

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

Разработчик баз данных может задать процедуры, табличные функции, представления, таблицы или SQL-код для формирования списка значений параметров на ленте.

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

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

Это удобно использовать при работе с иерархическими каталогами.

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

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

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. PARAMETER_NAME
  4. SELECT_SCHEMA
  5. SELECT_NAME
  6. SELECT_TYPE
  7. SELECT_CODE

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

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

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

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

TABLE_SCHEMATABLE_NAMEPARAMETER_NAMESELECT_SCHEMASELECT_NAMESELECT_TYPESELECT_CODE
<Схема объекта>
См. таблицу 1.1
<Имя объекта>
См. таблицу 1.1
[<Имя параметра>
или <Имя колонки>]
<Схема объекта запроса>
См. таблицу 1.2
<Имя объекта запроса>
См. таблицу 1.2
<Тип объекта запроса>
См. таблицу 1.2
<SQL-код>
См. таблицу 1.2
Таблица 1.1
TABLE_SCHEMATABLE_NAME
<Схема таблицы><Имя таблицы>
<Схема представления><Имя представления>
<Схема процедуры><Имя процедуры>
<Схема объекта><Имя SQL-кода>
<Схема объекта><Имя http-запроса>
<Схема объекта><Имя text-запроса>
Таблица 1.2
SELECT_SCHEMASELECT_NAMESELECT_TYPESELECT_CODE
<Схема процедуры><Имя процедуры>[PROCEDURE] 
<Схема функции><Имя функции>FUNCTION 
<Схема таблицы><Имя таблицы>TABLE 
<Схема представления><Имя представления>VIEW 
<Схема объекта><Имя SQL-кода>CODE<SQL-код>
<Схема><Имя>RANGE *<Имя или адрес диапазона>
<Схема><Имя>VALUES *<Список значений>

* Типы RANGE и VALUES доступны в SaveToDB 7.1 и выше.

Первые два поля идентифицируют объект базы данных (таблица, представление или хранимая процедура) или объект, настроенный через Список запросов.

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

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

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

Это могут быть хранимые процедуры, табличные функции, представления, таблицы или SQL-код.

Для использования SQL-кода в поле SELECT_CODE следует указать SQL-код, а в поле SELECT_TYPE тип CODE.

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

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

Например:

SELECT DISTINCT CategoryID, CategoryName FROM dbo.Category c WHERE c.ParentCategoryID = @CategoryID

Представление конфигурации в формате 3.х-4.х

В целях обеспечения совместимости с предыдущими версиями, поддерживается также вариант представления конфигурации в формате 4.x, которое должно содержать следующие поля:

  1. SPECIFIC_SCHEMA
  2. SPECIFIC_NAME
  3. PARAMETER_NAME
  4. SELECT_SCHEMA
  5. SELECT_NAME
  6. SELECT_TYPE

* Для Microsoft SQL Server Compact используется таблица, а не представление.

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

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

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

SPECIFIC_SCHEMASPECIFIC_NAMEPARAMETER_NAMESELECT_SCHEMASELECT_NAMESELECT_TYPE
<Схема объекта БД>
или <Тип объекта>
См. таблицу 2.1
<Имя объекта>
См. таблицу 2.1
[<Имя параметра>
или <Имя колонки>]
<Схема объекта запроса>
См. таблицу 2.2
<Имя объекта запроса>
См. таблицу 2.2
<Тип объекта запроса>
См. таблицу 2.2
Таблица 2.1
SPECIFIC_SCHEMASPECIFIC_NAME
<Схема таблицы><Имя таблицы>
<Схема представления><Имя представления>
<Схема процедуры><Имя процедуры>
CODE<Имя SQL-кода>
HTTP<Имя http-запроса>
TEXT<Имя text-запроса>
Таблица 2.2
SELECT_SCHEMASELECT_NAMESELECT_TYPE
<Схема процедуры><Имя процедуры>[PROCEDURE]
<Схема функции><Имя функции>FUNCTION
<Схема таблицы><Имя таблицы>TABLE
<Схема представления><Имя представления>VIEW
<SQL-код><Имя SQL-кода>CODE

Первые два поля идентифицируют объект базы данных (таблица, представление или хранимая процедура) или объект, настроенный через Список запросов.

Использование префикса "SPECIFIC" в наименовании сложилось исторически, когда поддерживались только хранимые процедуры Microsoft SQL Server.
В версии 5.0 "SPECIFIC_" заменено на единообразное для всех конфигурационных таблиц "TABLE_".

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

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

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

Это могут быть хранимые процедуры, табличные функции, представления, таблицы или SQL-код.

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

Пример представления конфигурации:

Пример представления конфигурации списка значений параметров

Запрос uspItem имеет три параметра CategoryID, SubcategoryID и BrandID, которые будут выведены на ленту.
Для каждого параметра заданы процедуры формирования списка значений. Их объявления:

CREATE PROCEDURE [xls13].[uspParameterValues_CategoryID]
CREATE PROCEDURE [xls13].[uspParameterValues_SubcategoryID]
    @CategoryID int = NULL
CREATE PROCEDURE [xls13].[uspParameterValues_BrandID]
    @CategoryID int = NULL
    , @SubcategoryID int = NULL

Процедуры uspParameterValues_SubcategoryID и uspParameterValues_BrandID, формирующие списки значений параметров SubcategoryID и BrandID, имеют параметр CategoryID.
Таким образом, при изменении параметра CategoryID будут запрошены новые списки значений параметров SubcategoryID и BrandID, перестроен запрос таблицы и обновлены данные таблицы Microsoft Excel.

Обновление списков значений в Microsoft Excel

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

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

При обновлении данных таблицы из Microsoft Excel или по кнопке "Обновить" значения не загружаются, чтобы сократить время обновления запроса.

В приведенном выше примере, будут перезагружены списки параметров @Subcategory и @Brand, а список @Category останется неизменным.

Добавление пустого значения в список значений

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

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

Пустое значение (NULL) автоматически добавляется только в списки запросов с контекстными параметрами (@Subcategory и @Brand в примере выше).
Это необходимо, т.к. список значений зависимого параметра должен содержать NULL для отбора всех позиций родительского параметра.

Если же запрос списка значений не содержит параметров, то пустое значение автоматически не добавляется.
Разработчик имеет полный контроль над списком.
Иногда значение NULL должно быть, а иногда - нет. Например, если требуется обязательное указание номера счета.

Это поведение изменено в версии 3.0.

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

Например, это строка в начале запроса типа:

SELECT NULL AS ID, NULL AS Name UNION

Выходные поля процедур запроса списков значений

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

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

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

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

Если в качестве объекта запроса списка значений указана таблица или представление, то запрос выходных полей формируется плагином SaveToDB.

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

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

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

Если полей два или более, то поля выбираются по следующим алгоритмам.

Приоритет выбора поля значений параметра:

  1. Поле с именем параметра.
  2. Поле с именем ID.
  3. Первое поле с типом данных параметра списка значений.

Приоритет выбора поля наименования значения параметра на ленте:

  1. Поле с именем Name, если оно не входит в поле отбора WHERE.
  2. Первое поле с типом данных *CHAR (char, nchar, varchar, nvarchar), если оно не входит в поле отбора WHERE.

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

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

Например:

Представление возвращает поля CategoryID, SubcategoryID, CategoryName и SubcategoryName и используется для выбора значений поля SubcategoryID.

Если у базового запроса таблицы нет параметра CategoryID, то будет сформирован запрос:

SELECT DISTINCT SubcategoryID, CategoryName FROM ...

Если же базовый запрос таблицы содержит параметр CategoryID, то будет сформирован запрос

SELECT DISTINCT SubcategoryID, CategoryName FROM ... WHERE CategoryID = @CategoryID

В обоих случаях первым идет поле CategoryName, как первое поле с типом *CHAR.

Соответственно, для выбора значений поля SubcategoryID поле CategoryName надо или убрать или переместить за поле SubcategoryName.

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

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

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

Введение

Плагин SaveToDB поддерживает перевод имен и описаний объектов базы данных, а также объектов, настроенных через список запросов или обработчики событий:

  • Таблицы, представления, процедуры.
  • SQL-код, http-запросы, запросы к текстовым файлам.
  • Макросы и команды Windows Shell или CMD в обработчиках событий.
  • Поля таблиц и представлений.
  • Параметры процедур и других объектов.

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

Т.е. все элементы интерфейса и данных приложения на Microsoft Excel могут содержать понятные пользователям наименования и описания.

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

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

Перевод и аннотация имен объектов

Для перевода используется представление* базы данных со следующими полями:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. LANGUAGE_NAME
  4. TRANSLATED_NAME
  5. TRANSLATED_DESC
  6. TRANSLATED_COMMENT

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

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

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

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

TABLE_SCHEMATABLE_NAMELANGUAGE_NAMETRANSLATED_NAMETRANSLATED_DESCTRANSLATED_COMMENT
<Схема объекта>
или <Тип объекта>
<Имя объекта><Код языка>[<Переведенное имя>][<Описание>][<Комментарий>]

Варианты значений полей TABLE_SCHEMA и TABLE_NAME:

TABLE_SCHEMATABLE_NAME
<Схема таблицы><Имя таблицы>
<Схема представления><Имя представления>
<Схема процедуры><Имя процедуры>
<Схема функции><Имя функции>
CODE<Имя SQL-кода>
HTTP<Имя http-запроса>
TEXT<Имя text-запроса>
MACRO<Имя пункта меню>
CMD<Имя пункта меню>
REFRESH<Имя пункта меню>

Поля TABLE_SCHEMA и TABLE_NAME определяют объект базы данных или запрос, настроенный через список запросов или обработчики событий.

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

Поле LANGUAGE_NAME должно содержать две первые буквы кода языка.

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

Поля TRANSLATED_NAME, TRANSLATED_DESC и TRANSLATED_COMMENT могут быть выведены на лист в соответствующие именованные ячейки:

  • SaveToDB_Name - переведенное имя объекта
  • SaveToDB_Desc - описание объекта
  • SaveToDB_Comment - комментарий

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

Присвоить ячейке соответствующее имя можно используя пункт меню Excel "Формулы", "Присвоить имя".

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

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

Один из возможных - брать из таблицы перевода. Этот подход используется в примерах и SaveToDB Framework.

Данные перевода объектов загружаются в момент обновления списка запросов или обновления данных и конфигурации запроса.
То есть, если изменены данные перевода в таблице базы данных, то следует обновить список запросов или сам запрос.

Пример представления перевода объектов:

Пример представления конфигурации перевода имен объектов

Перевод и аннотация полей и параметров

Для перевода используется представление* базы данных со следующими полями:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. COLUMN_NAME
  4. LANGUAGE_NAME
  5. TRANSLATED_NAME
  6. TRANSLATED_DESC

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

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

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

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

TABLE_SCHEMATABLE_NAMECOLUMN_NAMELANGUAGE_NAMETRANSLATED_NAMETRANSLATED_DESC
[<Схема объекта БД>
или <Тип объекта>]
[<Имя объекта>]<Имя колонки>
или <Имя параметра>
<Код языка>[<Переведенное имя>]
[<Описание>]

Значения полей TABLE_SCHEMA и TABLE_NAME аналогичны приведенным выше в переводе имен объектов.

Но возможны дополнительные варианты за счет указания NULL в определенном поле:

TABLE_SCHEMATABLE_NAMEИспользование
<Схема объекта БД>
или <Тип объекта>
<Имя объекта>Перевод используется для полей и параметров конкретного объекта
<Схема объекта БД>
или <Тип объекта>
NULLПеревод используется для полей и параметров объектов заданной схемы
NULLNULLПеревод используется для полей и параметров любых объектов

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

Обязательное поле COLUMN_NAME определяет имя колонки или параметра, для которого задается перевод.

Поле LANGUAGE_NAME должно содержать две первые буквы кода языка.

Поле TRANSLATED_NAME используется для перевода заголовков полей в таблицах Excel и отображения в элементах интерфейса.

Поле TRANSLATED_DESC используется в качестве комментариев заголовков таблиц Excel и во всплывающих подсказках для параметров.
Это может быть удобно, если требуется дать расширенное описание назначения колонки таблицы.

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

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

Данные для представления можно получать любым способом.
Один из возможных - брать из таблицы перевода. Этот подход используется в примерах и SaveToDB Framework.

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

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

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

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

Настройка форматов таблиц

Введение

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

Когда текущий запрос закрывается, плагин SaveToDB сохраняет оформление запроса в активной книге, и восстанавливает его, когда запрос активируется снова.

Оформление таблиц включает следующие элементы:

  • Формат ячеек.
  • Условное форматирование.
  • Примененные авто-фильтры.
  • Итоги таблиц.
  • Условия проверки данных.
  • Свойства активного окна.
  • Свойства настройки печати.
  • Колонки с пользовательскими формулами.

Таким образом, пользователь при повторном открытии запроса видит его таким, каким он был в последний момент.

Можно настроить возможность сохранения форматов в базе данных.

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

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

Представление загрузки форматов таблиц

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

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_EXCEL_FORMAT_XML

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

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

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

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

TABLE_SCHEMATABLE_NAMETABLE_EXCEL_FORMAT_XML
<Схема таблицы><Имя таблицы><Формат таблицы Excel>
<Схема представления><Имя представления><Формат таблицы Excel>
<Схема процедуры><Имя процедуры><Формат таблицы Excel>
CODE<Имя SQL-кода><Формат таблицы Excel>
HTTP<Имя http-запроса><Формат таблицы Excel>
TEXT<Имя text-запроса><Формат таблицы Excel>

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

Поле TABLE_EXCEL_FORMAT_XML хранит значения в формате XML и должно позволять хранить значения более 32 килобайт.

Рекомендуемые типы данных поля TABLE_EXCEL_FORMAT_XML:

  • Microsoft SQL Server - XML.
  • Microsoft SQL Server Compact - NTEXT.
  • Oracle Database - NCLOB.
  • IBM DB2 - CLOB(200000).
  • MySQL - MEDIUMTEXT.
  • MariaDB - MEDIUMTEXT.
  • NuoDB - STRING.
  • PostgreSQL - TEXT.

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

Пример представления конфигурации форматов таблиц

Процедура сохранения форматов таблиц

SaveToDB сохраняет и удаляет форматирование таблиц, используя первую хранимую процедуру* со следующей сигнатурой параметров:

  1. Schema
  2. Name
  3. ExcelFormatXML

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

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

Мастер управления форматами таблиц позволяет управлять форматами таблиц.

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

SaveToDB Framework предоставляет готовые решения, которые можно быстро развернуть в базы данных Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, PostgreSQL, NuoDB и SQLite.