Статьи

Статьи

Статьи

Продвинутые возможности:

Статьи для разработчиков:

Работа со сводными таблицами

Плагин SaveToDB добавляет следующие возможности для работы со сводными таблицами:

  • Редактирование значений базовой таблицы Excel из сводной таблицы.
  • Обновление и сохранение изменений базовой таблицы на другом листе элементами управления на ленте.
  • Сохранение и восстановление видов сводных таблиц с помощью элементов управления группы "Вид таблицы".
  • Сохранение видов сводных таблиц в базе данных и загрузка из базы данных видов таблиц пользователями.
  • Замена стандартного действия Excel по двойному клику на таблице, создания нового листа, на отбор записей в базовой таблице.

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

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

Пример использования сводных таблиц в качестве бюджетных форм

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

Budget Diagram

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

Data table

Обратите внимание, что параметры процедуры (DepartmentCode, ItemCode, Date) отображены на ленте и позволяют менять параметры запроса.

Пользователи могут создать сводную таблицу типа этой (статьи по департаментам для выбранной даты):

Pivot table Items * Departments

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

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

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

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

Обратите внимание, что на ленте отображаются те же элементы управления, что и для базовой таблицы.
Вы можно изменять параметры, обновлять данные и сохранять изменения.

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

  1. Отбирает записи базовой таблицы по значениям полей и фильтров сводной таблицы и параметрам запроса.
  2. Если таблица содержит только одну строку, то плагин меняет значение в ней.
    Если таблица не содержит отобранных строк, то плагин добавляет строку с новыми значениями.
    Если таблица содержит более одной строки, то плагин не изменяет значения в строке.
  3. Обновляет сводную таблицу.

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

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

К примеру, двойной клик на ячейке Direct Sales, SALES покажет только одну запись базовой таблицы:

Filtered data

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

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

Pivot table Items * Dates
Pivot table Departments * Dates

Виды сводных таблиц

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

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

Важно! Виды таблиц запроса и виды сводных таблиц хранятся в единой записи.
Не изменяйте виды таблиц в разных книгах.

 

Работа с полями и курсорами

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

Такие ячейки должны иметь имена в специальном формате: field_<имя таблицы>_<имя поля>.

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

Пробелы в имени поля следует опустить.

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

Затем можно удалить неиспользуемые ячейки. Используйте после этого пункт "Очистить ошибочные именованные ячейки" мастера форм.

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

Строка курсора может быть изменена с помощью макросов VBA за счет вызова методов плагина типа MoveNext, MovePrevious, MoveFirst, MoveLast.

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

Идея проста. Ячейка должна иметь два имени:

  • field_<имя мастер таблицы>_<имя поля>
  • <имя параметра дочерней таблицы>

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

Затем плагин обновляет дочерний запрос, т.к. изменилось значение его параметра (за счет именованной ячейки).

См. подробности о создании параметров в разделе Группа "Параметры запроса".

Эта техника может быть повторена на листе несколько раз.

Ниже приведен пример использования описанных возможностей:

Example of working with cursors and fields.

Таблица CustomerIndex является родительской для таблицы Customers.

Таблица Customers является родительской для таблицы Orders.

Таблица Orders является родительской для таблицы OrderDetails.

Во все три таблицы добавлены курсоры.

Таблицы Customers и Orders имеют скрытые колонки. Значения этих колонок отображаются в полях формы.

Это можно видеть в активной ячейке с именем field_Customers_City.

Вы можете попробовать этот пример в папке Northwind примеров для Microsoft SQL Server и Microsoft SQL Server Compact.

 

Работа с изображениями

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

Плагин отображает изображения, когда курсор переходит на новую строку.

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

Если Вы закрыли панель задач, то ее можно отобразить с помощью пункта меню Настройки, Показать панель задач.

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

Разработчики баз данных могут создать обработчики событий SelectionChange для отображения изображения для любого запроса.
Достаточно указать значение _TaskPane в поле TARGET_WORKSHEET и вернуть изображение в качестве результата запроса.

Панели задач зависят от активного листа.

Пример отображения фотографий из базы данных:

Example of showing photos from database tables

Вы можете попробовать этот пример в папке Northwind примеров для Microsoft SQL Server и Microsoft SQL Server Compact.

Плагин SaveToDB также позволяет отображать изображения из Интернет, если строка таблица содержит ссылки на файлы с расширениями jpg, jpeg, gif или png.

Например, следующая таблица содержит колонку скрытых ссылок на фотографии в LinkedIn, типа

https://media.licdn.com/media/p/7/000/253/05b/308dd6e.jpg: 

Example of showing photos from the web

Вы можете найти этот пример в папке Примеры для Web, Пример загрузки фотографий.

Плагин сохраняет загруженные изображения во временном каталоге.

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

Управление окнами

Плагин добавляет в Microsoft Excel следующие возможности по управлению окнами:

  • Возможность авто-активации связанных окон.
  • Возможность авто-упорядочивания окон.

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

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

Зависимые окна восстанавливаются в том виде, в котором они были до переключения.

Листы в основном и зависимых окнах могут иметь разные настройки окон.

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

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

При стандартном режиме упорядочивания Microsoft Excel, режим сбрасывается при перемещении границ.

Пример упорядоченных окон:

Пример управления окнами в Microsoft Excel

Пример отключения окон 2 и 3, которые относятся к листу "Лист1", при переходе на "Лист2" в первом окне:

Пример управления окнами в Microsoft Excel

Если вернуться на "Лист1", то снова будут активированы окна, показанные на первом снимке экрана.

Управление авто-фильтрами

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

Можно вводить конкретные значения или использовать подстановочные знаки ? и *, а также знаки >, <, <>.
Например, <>*AA* - отберет все строки, которые не содержат AA.
При удалении значения в ячейке над таблицей авто-фильтр колонки сбрасывается.

Ниже показан пример отбора записей по акционерным обществам за счет *АО* и суммами более 100 тысяч рублей.

Пример управления авто-фильтрами в Microsoft Excel

Календарь для ввода дат

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

Календарь, вызываемый по двойному клику в Microsoft Excel

В отличие от других реализаций календарей, изменение в ячейке можно отменить (Ctrl-Z).

Работа с внешними ключами

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

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

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

Схема базы данных

Таблица dbo38.Payment содержит поля внешних ключей из таблиц dbo38.Account, dbo38.Company и dbo38.Item.

Для создания формы в Excel можно добавить обработчики ValidationList для таблицы dbo38.Payment:

Настройка списка значений

В результате, таблица в Excel выглядит следующим образом:

Таблица в Excel со списками значений

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

Вы можете использовать этот подход для представлений и хранимых процедур.
См. Руководство разработчика 6.5 для Microsoft SQL Server в SaveToDB SDK, которое содержит пример работы с представлениями и процедурами.

Когда пользователь выбирает ячейку, у которой есть список проверки данных, плагин отображает диалоговое окно List Editor (Список значений):

Список значений

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

Значения также можно фильтровать. Достаточно, набрать несколько букв из необходимого слова.

Использование параметров

Настройка параметров запросов

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

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

Для остальных типов запросов параметры можно настроить за счет форматных вставок в тексте запросе.

Текст запросов таких типов указывается в полях TABLE_CODE списка запросов, HANDLER_CODE обработчиков событий и SELECT_CODE объектов запроса списка значений.

Форматы настройки параметров:

Тип запросаКод типаФормат параметраПримеры
SQL-код Microsoft SQL Server,
Microsoft SQL Server Compact, SQLite
CODE@Parameter[=DefaultValue]SELECT * FROM Sales.Contacts WHERE Name = @Name
SELECT * FROM Sales.Contacts WHERE Name = @Name=ABC
SQL-код Oracle Database,
IBM DB2, MySQL, MariaDB, PostgreSQL, NuoDB
CODE:Parameter[=DefaultValue]SELECT * FROM SALES.CONTACTS WHERE NAME = :Name
SELECT * FROM SALES.CONTACTS WHERE NAME = :Name=ABC
http-запросHTTPПо стандарту httphttp://www.google.com/finance/historical?q=GOOG
http-запросHTTP{Parameter[=DefaultValue]}http://www.google.com/finance/historical?q={Symbol=GOOG}
https://www.google.com/search?as_q={Query}
Запрос к тестовому файлуTEXT{Parameter[=DefaultValue]}{FileName};CodePage=1251
{FileName};CodePage={CodePage=1251}
Макрос ExcelMACRO{Parameter[=DefaultValue]}SayHello {Name=World}
Sheet1.SayHello {FirstName}, {LastName}
Команда Windows Shell или CMDCMD{Parameter[=DefaultValue]}{FileName}
notepad.exe {FileName}
dir {Mask=*.*}
cmd /c dir {Mask=*.*}
cmd /k dir *.*
mailto:{Email}&subject=Thanks for the connection&body=Hello {FirstName},%0A

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

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

Если пользователь может выбирать значение параметра (на ленте или из диалога), то списки таких значений можно настроить.

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

Источники значений параметров запросов

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

Источник значенияОбласть действияКомментарий
Одноименная колонка таблицыВсе типы запросовИспользуется имя колонки объекта до перевода наименования
Одноименный параметр на лентеВсе типы запросовНа ленте отображаются параметры запроса активной таблицы на листе 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Обработчик событий ChangeКоличество измененных ячеек.
ChangedCellIndex, changed_cell_indexОбработчик событий ChangeНомер запроса в группе запросов обработки изменений.
TransactionID, transaction_idВсе типы запросовGUID группы операций.
TransactionStartTime, transaction_start_timeВсе типы запросовВремя UTC группы операций.
WindowsUserName, windows_user_nameВсе типы запросовИмя пользователя Windows.
SaveToDbVersion, savetodb_versionВсе типы запросовВерсия плагина SaveToDB.
MergeDate, merge_dateСохранение слияниемДата и время формирования запроса слияния данных. Позволяет определить наличие данных в последнем слиянии.

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

Использование именованных ячеек

Именованные ячейки Excel, обновляемые плагином

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

Имя ячейкиКомментарий
SaveToDB_ObjectОбъект активного запроса
SaveToDB_NameЗначение поля TRANSLATED_NAME из представлений перевода имен объектов для объекта запроса и выбранного языка данных
SaveToDB_DescЗначение поля TRANSLATED_DESC из представлений перевода имен объектов для объекта запроса и выбранного языка данных
SaveToDB_CommentЗначение поля TRANSLATED_COMMENT из представлений перевода имен объектов для объекта запроса и выбранного языка данных
SaveToDB_CommandTextCommandText активного запроса
SaveToDB_ConnectionStringConnectionString активного запроса
SaveToDB_ElapsedMillisecondsВремя выполнения запроса в миллисекундах
<Parameter>Значение параметра. Значения параметров также изменяются при изменении значений ячеек.
<Parameter>__nameИмя значения параметра (два подчеркивания).

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

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

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

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

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

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

Использование формул Microsoft Excel

Настройка формул Excel для запросов базы данных

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

Такие колонки действуют только в книге пользователя.

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

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

Например, для вычисления суммы в Microsoft Excel может использоваться формула '=[@Price]*[@Qty]'.

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

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

Основное правило - использование английских наименований функций и запятых для разделения параметров.

До момента преобразования текста в формулу на экране может появляться непосредственно сам текст формул.

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

Например, формат "0;-0;;" отключает вывод нулевых и строковых значений.

Настройка DDE-формул

Формулы в запросах можно формировать для каждой строки, что позволяет их использовать для работы с DDE или RTD (Real-Time Data).

Настройку формул DDE гораздо проще выполнять в SQL, чем макросами.

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

Пример подхода:

  1. В рабочей книге Excel есть таблица с полями Symbol и Comment. Необходимо добавить DDE-формулы из торговой платформы Thinkorswim (TOS).
  2. Создаем базу данных в рабочем каталоге книги и таблицу WatchList с помощью Мастера публикации.
  3. Устанавливаем в новую базу данных SaveToDB Framework с помощью Мастера установки SaveToDB Framework.
  4. Генерируем книгу настройки приложения с помощью Мастера создания книги настройки.
  5. В книге настройки в таблицу QueryList добавляем исходную таблицу и запрос с формулами DDE за счет SQL-кода (см. ниже).
  6. Подключаем запрос WatchList TOS на новый лист рабочей книги с помощью Мастера подключения.

В итоге, мы получим таблицу с DDE-формулами. Данные в этой таблице можно добавлять, удалять, изменять (поля Symbol и Comment).

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

Конфигурация запросов в таблице QueryList:

IDTABLE_SCHEMATABLE_NAMETABLE_TYPEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDURE
1 WatchListTABLE   
2SELECT
Symbol
, '=TOS|LAST!' + Symbol AS Last
, '=TOS|NET_CHANGE!' + Symbol As NetChange
, '=TOS|PERCENT_CHANGE!' + Symbol As Change
, '=TOS|HIGH!' + Symbol As High
, '=TOS|LOW!' + Symbol As Low
, '=SUBSTITUTE(TOS|VOLUME!' + Symbol+'," ","")+0' As Volume
, Comment
FROM
WatchList
WatchList TOSCODEWatchListWatchListWatchList

SQL-код указывается в поле TABLE_SCHEMA. В поле TABLE_TYPE следует указать тип CODE.

За счет указания таблицы WatchList в полях INSERT_PROCEDURE, UPDATE_PROCEDURE, DELETE_PROCEDURE наш запрос является редактируемым, и все изменения сохраняются в базовую таблицу WatchList.

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

Возможные шаги дальнейшего развития приложения: