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

02.03.17

Интеграция - Загрузка и выгрузка в Excel

Надстройка "Активные таблицы" это мини система компоновки данных для MS Excel, где формирование таблиц производится путем выполнением SQL команд. Без использования формул и макросов можно обрабатывать большие массивы данных и формировать таблицы или отчеты заданной структуры. Все настройки (конфигурация) хранятся на скрытом листе рабочей книги. Обработка данных может быть активирована интерактивно через меню, по горячим клавишам, через изменение ячеек листа или по нажатию командных кнопок на листе книги.

Скачать файлы

Наименование Файл Версия Размер
Надстройка Активные Таблицы
.zip 4,20Mb
12
.zip 1.07 4,20Mb 12 Скачать
Видеоинструкции
.zip 11,13Mb
10
.zip 1.0 11,13Mb 10 Скачать

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

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

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

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

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

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

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

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

Быстрое создание текста запроса 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с.

Для получения данных из 1С Предприятия необходимо:

1.Создать строку соединения с 1С предприятием.
Перейти на панель Источники данных. Нажать кнопку Создать соединение с 1С.
Заполнить настройки соединения с необходимой базой данных 1С.
  
 
2.В запросе выбрать имя созданного источника данных 1С.
3.Вставить запрос из конфигуратора 1С в поле ввода запроса. Запрос может быть создан только вручную. Встроенный конструктор запроса при com-соединение с 1С не используется. 
4.Заменить параметры запроса на макропараметры.

Заключение

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

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

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

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

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

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

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

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

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

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

Активные таблицы ActiveTables SQL Excel ADO Report Query Tables Таблицы Отчет

См. также

Загрузка номенклатуры из Excel в УТ11, КА 2, ERP 2, Розница 2. Дополнительные реквизиты и сведения, характеристики, картинки, цены, остатки

Загрузка и выгрузка в Excel Розничная торговля Логистика, склад и ТМЦ Ценообразование, анализ цен Прайсы Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Розница 2 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

Загрузка из файлов xls, xlsx, ods, csv, mxl в УТ11, КА 2, ERP 2, Розница 2. Задействованы все возможности конфигурации - заполнение реквизитов номенклатуры, дополнительных реквизитов и сведений, характеристики, доп.реквизиты и сведения характеристик. Дополнительные обработки для расширения возможностей.

10560 руб.

29.10.2014    209814    620    524    

438

Загрузка номенклатуры c картинками (несколько потоков одновременно) и сопутствующими данными в базу и любые документы из yml, xls, xlsx, xlsm, ods, ots, csv для УТ 10.3, УТ 11 (все), БП 3, КА 2, ERP 2, УНФ 1.6/3.0, Розница 2

Загрузка и выгрузка в Excel Логистика, склад и ТМЦ Ценообразование, анализ цен Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Платные (руб)

Эволюция не стоит на месте - новая удобная версия функциональной обработки для Вашего бизнеса! Что же Вы получаете? Удобный и интуитивно понятный интерфейс с 3-мя этапами работы. 2 режима - автоматический и ручной. Чтение XLSX, XLSM, CSV, XML/YML форматов без офиса, на любом сервере! Визуальное связывание колонок файла и реквизитов простым перетаскиванием колонок. Создание или обновление номенклатуры с иерархией, характеристик, доп. реквизитов, упаковок, загрузка практически неограниченного количества картинок на одну номенклатуру (с возможностью загрузки в несколько потоков одновременно), с хранением в томах или в базе. Загрузка номенклатуры поставщиков или поиск по их данным номенклатуры. Загрузка доп. реквизитов в характеристики. Загрузка штрихкодов с генерацией новых. Создание элементов справочников и ПВХ "на лету" для выбранных реквизитов. (Обновление от 11.12.2023, версия 9.5 - 9.9)

13200 руб.

20.11.2015    150379    365    375    

499

Маркетплейсный загрузчик для 12-ти маркетплейсов в "БП 3", "УТ 11", "КА 2", ERP, УНФ

Загрузка и выгрузка в Excel Маркетплейсы Платформа 1С v8.3 Бухгалтерский учет 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Россия Бухгалтерский учет Управленческий учет Платные (руб)

Реальный помощник, с помощью которого Вы сделаете необходимые документы для Wildberries, OZON, ЯндексМаркет, Мегамаркет, Aliexpress, "Детский мир", Казань-Экспресс, "Леруа Мерлен", ЭНФАНТА (Акушерство), ЛаМода, Летуаль, "Твой дом" в документы "Отчет комиссионера (агента) о продажах" и другие, может работать в "Бухгалтерия 3", "Бухгалтерия 3 КОРП", УТ 11, УНФ, КА 2, ERP. Возможность подключить любые маркетплейсы. Анализ продаж ОЗОН. 30 дней БЕСПЛАТНОГО пользования!

1800 руб.

12.08.2021    31793    234    63    

117

Распознавание и загрузка сканов в 1С "одним нажатием": УПД, ТОРГ-12, накладные, счета, номенклатура, заказы и т.д.

Загрузка и выгрузка в Excel Документооборот и делопроизводство (СЭД) Учет документов Распознавание документов и образов Управляемые формы 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление холдингом 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная программа для распознавания ЛЮБЫХ СКАНОВ ИЛИ ФОТО ТОВАРНЫХ ДОКУМЕНТОВ в 1С. Не требует указания параметров и предварительной настройки. Просто выбираете файл (PDF, JPG, DOC, XLS, HTML и т.д.) выбираете документ 1С и нажимаете кнопку "Распознать и загрузить".

5400 руб.

04.06.2019    101126    296    173    

312

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

Загрузка и выгрузка в Excel Маркетплейсы Платформа 1С v8.3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Загрузка данных отчета о реализации товаров из сервиса СберМегаМаркет для конфигурации: Бухгалтерия предприятия, редакция 3.0; Управление торговлей, редакция 11 и Управление нашей фирмой, редакция 3.0 в документ "Отчет комиссионера (агента) о продажах".

3600 руб.

07.06.2022    14947    78    0    

59

Загрузка документов и номенклатуры из Excel в 1С "одним нажатием": УПД, ТОРГ-12, отчеты маркетплейсов, заказы, счета, прайсы

Загрузка и выгрузка в Excel Оптовая торговля Платформа 1С v8.3 Управляемые формы Платформа 1C v8.2 Конфигурации 1cv8 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная обработка для загрузки документов из Excel в 1С. Не требует указания параметров (номера колонок, номер первой строки таблицы и т.д.) и предварительной настройки. Просто выбираете файл Excel, документ 1С и нажимаете кнопку "Загрузить". Обработка сама находит таблицу в файле Excel, необходимые для загрузки данные в ней (номенклатура, количество, НДС, цена, сумма) и загружает ее в 1С. Вместе с номенклатурой может найти контрагента, номер и дату документа, штрих-коды, серии ГТД, страну и т.д. Распознает документы ЛЮБОЙ ФОРМЫ (УПД, ТОРГ-12, заказ, отчет комиссионера и т.д.). Не требует MS Office. Для поиска таблиц используются методы эвристического поиска. Загружает только то, что нужно, т.е. пропускает повторы шапки таблицы, заголовки, промежуточные итоги, подписи и т.д. Содержит модуль работы с электронной почтой и api-загрузчик отчетов о продажах маркетплейсов.

5000 руб.

09.11.2016    214602    921    886    

938
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Aleksey.Bochkov 3659 21.12.14 08:02 Сейчас в теме
Работа, конечно, проделана огромная, но стоило ли?
На рынке довольно много бесплатных и платных решений.
QlikView (ИМХО, одно из лучших решений), например, в бесплатной версии позволяет легко интегрироваться с различными источниками данных и быстро строить красивые и отзывчивые отчеты.
Почему решили свое написать?
Silenser; +1 Ответить
2. PowerBoy 3347 22.12.14 07:26 Сейчас в теме
Работа, конечно, проделана огромная, но стоило ли?
На рынке довольно много бесплатных и платных решений.
QlikView (ИМХО, одно из лучших решений), например, в бесплатной версии позволяет легко интегрироваться с различными источниками данных и быстро строить красивые и отзывчивые отчеты.
Почему решили свое написать?


Моя надстройка - это не столько генератор отчетов, сколько чисто утилитарный инструмент обработки данных, созданный в Excel(VBA) и для поддержки Excel приложений, т.е. помимо формул или макросов Вы можете выполнять SQL команды (не только Select, но Update, Delete и т.д) которые помогут обработать большие массивы данных. Для примера: Большую таблицу данных неудобно обрабатывать формулами, трудно копировать, требуется много оперативной памяти, придется программировать макрос. Обычная группировка с суммированием в макросе займет не меньше страницы текста с обязательными циклами обработки и немалым временем на тестирование. Эта же задача может быть решена одной SQL строчкой (Select ... Group by..). причем и отработает она быстрей.

ps. Посмотрел QlikView - система бизнес-анализа, с OLAP сервером, своими скриптами загрузки и обработки, вообщем, только на освоение этой штуки год примерно нужен.
Оставьте свое сообщение