Активные Таблицы — надстройка MS Excel для обработки данных SQL запросами и командами.

Решаемые задачи

Создание интерактивных отчетов.

Сбор данных и объединение из нескольких таблиц на разных листах книги в одну общую таблицу.

Разделение таблицы на несколько таблиц с переносом данных на разные листы книги.

Создание интерактивных форм ввода.

Нормализация неструктурированных данных для формирования сводных таблиц.

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

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

Быстрое создание текста запроса SQL встроенным конструктором запросов. 

Получение данных из баз данных 1С через COM-соединение.

Импорт и экспорт данных из внешних источников и др..

Техническая реализация

Для чтения и записи данных в книге Excel используется технология ADO.

В книге Excel можно несколькими способами ссылаться на таблицу (или диапазон):

1. Имя листа, а затем знак доллара (например, [Лист1$] или [Мой лист$]). Таблица таким образом состоит из всего используемого диапазона листа.
    Select * from [Sheet1$]
2. Диапазон с определенным именем (например, [«Table1»]).
    Select * from Table1
3. Диапазон с конкретного адреса (например, [Лист1$ A1: B10]).
   Select * from [Sheet1$A1:B10]

В книгах Excel первая строка диапазона считается строкой заголовка (строка имен полей) по умолчанию. Если диапазон не содержит заголовков, можно выключить опцию «Учитывать заголовки таблиц» в настройках запроса. В этом случае имена полей будут созданы автоматически (где F1 будет имя первого поля, F2 будет имя второго поля и так далее).

При создании новой активной таблицы, необходимо определить куда выводить данные результатов запросов этой таблицы. В настройках таблицы общие задать лист или именованную область. Если имя листа не задано, новый лист будет создаваться при каждом выводе таблицы. Имя листа должно заканчиваться знаком $. Перед выводом таблицы лист или область вывода будут очищены. Режим очистки можно задать настройкой Очистить область вывода. Таблицу можно вывести несколько раз, например на разные листы книги. Количество раз выводить таблицу устанавливается в настройке Выполнить вывод таблицы раз. В тексте запроса, а также в имени листа вывода таблицы подставить в необходимых местах макропараметр счетчика вывода таблицы {#}. Если данные будут выводится на лист можно также задать отступы строк и столбцов. Если данные будут выводится в область, она после вывода изменит размер под новые выведенные данные. Установив настройку таблицы режим вставки, новые строки области вывода будут вставлены перед выводом, а значения ячеек ниже области сдвинутся. Настройка полное смещение задает вставку целой строки или столбца. Настройка таблицы выводить заголовки определяет будут ли выведены заголовки полей, которые берутся из первого активного запроса таблицы. 

В формировании таблицы участвуют только те запросы у которых установлен флажок активности. Также надо задать настройку учитывать заголовки таблиц в источнике данных. Если флажок включен, то имена колонок будут браться из первой строки области источника, иначе имена полей будут заданы как F1, F2 .. и т.д. Вывод данных производится последовательным выполнением активных запросов, данные следующего выполняемого запроса выводятся под предыдущими выведенными данными. В настройках запроса можно задать вывод данных справа от предыдущих данных, включив флажок настройки присоединить данные. Так же можно изменить и ориентацию вывода записей с вертикальной на горизонтальную изменив настройку запроса горизонтальный вывод, при этом заголовки таблицы будут выводится сверху вниз. Если установить настройку вывод по именам ячеек, то данные будут выведены в именованные ячейки, у которых имена совпадают с именами полей запроса, при этом данные берутся только из первой записи результата запроса. Если необходимо, что бы колонка таблицы содержала формулу Excel, необходимо установит флажок в настройках форматировать текст в выражение. Имя поля запроса с формулой должно быть текстовым и начинаться со знака равно (=). Формула должна быть написана по английски и иметь стиль ссылок R1C1. Для использования русских формул используйте два знака (==).

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

Если данные берутся с нескольких листов одинаковой структуры, можно использовать один запрос для выборки данных, достаточно выполнить его несколько раз, задав количество раз в настройке повторить запрос. В тексте запроса при этом подставить в необходимых местах макропараметр счетчика вывода запроса {$}. Также этот прием можно использовать, если необходимо собрать данные из разных однотипных файлов, макропараметр {$} при этом подставляется в строку подключения источника данных запроса. В поле настройки количества раз задается число раз выполнения запроса, а также через запятую можно задать шаг увеличения этого счетчика, так же в этом поле можно использовать макропараметры.

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

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

Макропараметры

Макропараметр — это вычисляемое выражение, значение которого подставляется в текст SQL запроса непосредственно в месте его расположения. Макропараметры должны выделяться фигурными скобками и могут быть вложенными друг в друга.
{1+1} => 2
{b2} => Значение ячейки b2
{«текст»} => {текст}
{/комментарий/} => пусто

Существуют несколько типов макропараметров:

Ссылка на именованную ячейку. {ИмяЯчейки}
Возвращает значение по заданному имени ячейки листа. Именованная ячейка может находится на любом листе книги.
select * from temp where field = {Товар}

Ссылка на именованную область. {ИмяОбласти}
Возвращает список значений, разделенных запятыми по заданному имени области листа. Именованная область может находится на любом листе книги.
select * from temp where field in ({Товары})

Ссылка на умную таблицу. {ИмяУмнойТаблицы}
Возвращает адрес области умной таблицы.
select * from [Лист1${Список}]

Абсолютная ссылка. {$Строка,$Столбец}
Возвращает значение ячейки текущего листа по строке и столбцу.
select * from temp where field = {$5,$1}

Другие макропараметры смотрите в справке к надстройке.

Активные области, выпадающие списки и меню активных таблиц.

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

Интеграция с 1с.

 

Заключение

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

Планируется добавить в следующих версиях: 

Поддержку MS Office 2003.  — сделал.

Поддержку внешних источников данных.  — сделал.

Инсталлятор надстройки. — сделал.

Справку в формате pdf. — сделал.

Версию в формате COM-Надстройки. — сделал.

Стартер надстройки. — сделал.

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

Интеграция с 1с по Com-соединению. — сделал.