Подсистема "COMExchange": прямой доступ к EXCEL через ADO

21.01.13

Интеграция - Файловый обмен (TXT, XML, DBF), FTP

На примере этой, очень популярной, темы наглядно продемонстрированы
нетривиальные возможности консоли запросов в составе подсистемы:
1. использование событий выполнения запроса (в том числе для запуска на выполнение других запросов)
2. запуск на выполнение запроса в цикле с подстановкой значений параметров из таблицы значений.

 

Все истинные арийцы коннектятся через ADO.
Поэтому в порочящих их связях незаметны ...
(пьяный бред Мюллера после победы) 

Прямой доступ к файлам EXCEL очень популярная, если не порядком избитая, тема. 
Есть множество публикаций, посвящённой этой теме.
Целью же этой статьи является не сказать что-то нового в этом деле, а наглядно продемонстрировать некоторые
нетривиальные возможности консоли запросов в составе подсистемы "COMExchange".

Для этой цели возьмём пример обсуждаемый ll13 в его замечательной статье и немного усложним.
Выгрузим в EXCEL номенклатуру с ценами в разрезе характеристик и видов цен.
Сделаем все это в консоли запросов  и после выгрузки наглядно покажем её результат, а именно
считаем лист EXCEL с выгруженными данными в консоли и откроем саму книгу EXCEL в его родном приложении.

Демонстрационный пример (сохранённый запрос), реализующий поставленную задачу,
можно взять из файла !НоменклатураЦеныВЭксельADO.rar  в архиве комплекта поставки подсистемы.

Этот пример работает в типовых конфигурациях УТ-10.3, УПП-1.2, УПП-1.3 и УТ-11.0.
В последнем случае 1С надо запускать в толстом клиенте в режиме обычного приложения.

Для работы демонстрационного примера необходимо:
1. Установить подсистему "COMExchange";
2. Выполнить начальное заполнение объектов подсистемы в специальной форме. Для примера потребуются стандартные
    («предопределённые»)  узлы плана обмена «Обмен данными COM» с кодами 'ExcelJet' и 'Excel';
3. Открыть в консоли запросов сохранённый запрос из архива файла поставки и выполнить запрос «ЭксельADO»
    из дерева списка запросов (Рис.1, Рис.2).
    При этом в диалоге выбора файла потребуется выбрать путь к файлу EXCEL (лучше выбрать имя нового файла).

Комментарии к запросам примера:
1. Запрос 1С «ЭксельADO»  (Рис.1, Рис.2): 
    Текст и параметры этого запроса не обрабатываются. Его события выполнения используются
    для программного запуска на выполнение  других запросов из дерева списка и показа результата
    выгрузки (смотри ниже комментарии к событиям запросов);

2. Запросы 1С «ВыбратьНоменклатуруЦены» и «ВыбратьНоменклатуруЦены(УТ11 (Рис.3):
    Выбирают из информационной базы данные, требуемые для выгрузки в файл EXCEL.
    Первый используется в конфигурациях УТ-10.3, УПП-1.2, УПП-1.3, последний в УТ-11.0;

3. Запрос ADO «ЭксельСоздатьЛист» (Рис.4):
Выполняет соединение ADO с файлом EXCEL, создаёт рабочий лист («таблицу») с именем «Прайс»;

4. Запрос ADO «ЭксельЗаполнитьЛист» (Рис.5):
    Выполняет вставку строк в рабочий лист. Выполняется в цикле с подстановкой параметров из таблицы значений. 
    В качестве таблицы параметров запросу передаётся полученная из базы 1С выборка данных в виде таблицы значений.

5. Запрос ADO «ЭксельПрочитатьЛист» (Рис.6): 
    Считывает из файла EXCEL выгруженные в него данные для показа результата выгрузки в консоли.

Комментарии к событиям выполнения запроса «ЭксельADO»:
1. Событие «Перед запросом» (Рис.1):
    Отключается обработка текста и параметров запроса (СтандартнаяОбработка = Ложь).
    Проверяется имя и версия конфигурации для определения имени запроса для выборки данных из базы. 
    Если конфигурация непредусмотренная – выводится сообщение с установкой статуса отказа (Отказ = Истина), в противном случае
    выполняется запрос «ВыбратьНоменклатуруЦены» или «ВыбратьНоменклатуруЦены(УТ11 для выборки данных  из базы 1С.
    Затем запрос «ЭксельСоздатьЛист» выполняет подключение к файлу EXCEL и создаёт рабочий лист «Прайс» для выгрузки данных.
    Соединению ADO присваивается имя "ЭксельADO" для использования в других запросах ADO. 
    Затем с помощью функции тзПараметрыУстановить() выборка данных из базы 1С устанавливается для запроса «ЭксельЗаполнитьЛист»
    в качестве таблицы параметров для его выполнения в цикле. После чего этот запрос выполняет заполнение рабочего листа. 
    Ну и в завершении всего запрос «ЭксельПрочитатьЛист» считывает выгруженные данные для показа в консоли.

2. Событие «После запроса» (Рис.2):
    Используется для открытия файла EXCEL в его родном приложении.
    Для этого сначала закрываем соединение ADO с файлом с помощью процедуры ЗакрытьСоединениеADO(),
    передав ей через аргумент имя соединения. Затем открываем файл EXCEL с помощью функции ЗапросПутьOpen().
    В качестве первого аргумента функции передаётся имя запроса открывшего файл  EXCEL.
    При установке соединения через структуру с опциями запроса назад возвращается путь к открытому файлу. 
    В качестве второго аргумента функции передаётся код узла плана обмена «Обмен данными COM» ,
    который, используя путь к файлу в опциях запроса, пытается его открыть. 
    В данном случае это стандартный узел 'Excel' для COM-соединения с приложением MS Excel.

Примечания:
1) Указанные выше процедуры и функции определены в модуле объекта обработки консоли и довольно подробно описаны в её описании.
    Для их вставки в текст модуля обработчика можно воспользоваться меню сервисной кнопки «f(x)» текстового поля (Рис.7).

2) Для доступа к EXCEL через ADO в примере использован узел плана обмена «Excel-Jet.OLEDB».
    В принципе тот же результат получается при использовании узла «Excel-Ace.OLEDB»
    Но в этом случае, если созданный файл будет иметь расширение *.xlsx, то могут возникнуть проблемы с открытием этого файла в EXCEL
    Например, EXCEL-2010 со стандартными настройками отказывается открывать такой файл и его приходится переименовывать в *.xls.

См. также

SALE! 10%

[ED3] Обмен для ERP 2.5, КА 2.5, УТ 11.5 БП 3.0, Розница, УНФ и других с EnterpriseData (универсальный формат обмена), правила обмена

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

Правила в универсальном формате обмена для ERP 2.5, КА 2.5, УТ 11.5, БП 3.0, Розница, УНФ, для последних версий конфигураций. Ссылки на другие конфигурации в описании публикации. Правила совместимы со всеми другими версиями конфигураций новыми и старыми, поддерживающими обмен в формате EnterpriseData. Не требуется синхронного обновления правил после обновления другой конфигурации, участвующей в обмене. Типовой обмен через планы обмена кнопкой Синхронизация вручную или автоматически по расписанию, или вручную обработкой.

25080 руб.

12.06.2017    134626    719    291    

387

Перенос данных из УПП 1.3 в БП 3.0. Переносятся документы (обороты за период), справочная информация и остатки

Обмен между базами 1C Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:Управление производственным предприятием 1С:Бухгалтерия 3.0 Россия Бухгалтерский учет Управленческий учет Платные (руб)

Перенос данных из 1С:Управление производственным предприятием 1.3 в 1С:Бухгалтерия предприятия 3.0 с помощью правил обмена. Переносятся остатки, документы (обороты за период), справочная информация. Правила проверены на конфигурациях УПП 1.3 (1.3.223.x) и БП 3.0 (3.0.149.x). Правила подходят для версии ПРОФ и КОРП.

28000 руб.

15.12.2021    20103    133    38    

90

SALE! 10%

Перенос данных из ERP 2 / КА 2 в ЗУП 3

Обмен между базами 1C Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:ERP Управление предприятием 2 1С:Комплексная автоматизация 2.х 1С:Зарплата и Управление Персоналом 3.x Россия Бухгалтерский учет Управленческий учет Платные (руб)

Обработка позволяет не только перенести все документы, справочную информацию и начальные остатки из ERP 2 или КА 2 в ЗУП 3, но и организовать регулярный перенос данных между программами 1С:ERP 2 / КА 2 и 1С:ЗУП 3. Вы можете выбрать период отбора данных и установить фильтр по организациям, чтобы выгружать только необходимую информацию. Более того, перенос оперативно обновляется при выходе новых релизов программы 1С, так что вы всегда будете иметь самую актуальную версию обработки.

48278 43450 руб.

03.12.2020    34051    80    57    

78

SALE! 10%

Перенос данных из ERP 2 / КА 2 / УТ 11 в БП 3.0

Обмен между базами 1C Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Россия Платные (руб)

Перенос позволяет настроить собственный обмен данными между указанными программами, альтернативный предлагаемому фирмой 1С. Перенос данных осуществляется из 1С:ERP 2 / 1С:КА 2 / 1С:УТ 11 в 1С:БП 3.0. Правила обмена оперативно обновляются при выходе новых релизов программы 1С, так что вы всегда будете иметь самую актуальную версию обработки.

38500 34650 руб.

15.04.2019    68207    176    136    

108

Перенос данных из Парус 10 в ЗГУ ред.3

Внешние источники данных Кадровый учет Файловый обмен (TXT, XML, DBF), FTP Обмен между базами 1C Платформа 1С v8.3 Сложные периодические расчеты 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)

Обработка позволяет перенести кадровую информацию и данные по заработной плате, фактических удержаниях, НДФЛ, вычетах, страховых взносах из базы Парус 10 учреждений в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (ЗГУ) и начать с ней работать с любого месяца года.

60000 руб.

05.10.2022    9159    9    8    

10

Загрузка номенклатуры из 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    209837    620    524    

438

Перенос данных из УТ 10.3 в УТ 11.5. Переносятся документы (обороты за период), справочная информация и остатки

Обмен между базами 1C Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 Оперативный учет 1С:Управление торговлей 10 1С:Управление торговлей 11 Россия Управленческий учет Платные (руб)

Перенос данных из 1С:Управление торговлей 10.3 в 1С:Управление торговлей 11.5 с помощью правил обмена. Переносятся остатки, документы (обороты за период), справочная информация. Правила проверены на конфигурациях УТ 10.3 (10.3.87.x) и УТ 11.5 (11.5.16.x).

28000 руб.

23.07.2020    46032    194    64    

153

Перенос данных из Парус 8 в ЗГУ 3

Зарплата Внешние источники данных Бюджетный учет Платформа 1С v8.3 Сложные периодические расчеты 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)

Обработка позволяет перенести кадровую информацию и данные по заработной плате, фактических удержаниях, НДФЛ, вычетах, страховых взносах из базы Парус 8 учреждений в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (ЗГУ) и начать с ней работать с любого месяца года.

84000 руб.

19.08.2020    22357    18    1    

21
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. yuraos 991 21.01.13 07:36 Сейчас в теме
Привет, Всем доброго времени суток!!!

Сразу выкладываю подправленную версию консоли.

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

Но что-то малость прокосячился ...
... это все наследие 1с-77:
там вместо НЕ ЗначениеЗаполнено() нечто с противоположным смыслом - ПустоеЗначение()
;)

Выложенная консоль будет более удобна тем,
кто вместо провайдера Microsoft.Jet.OLEDB.4.0
захочет попробывать Microsoft.ACE.OLEDB.12.0
выбрав из плана обмена соответствующий узел.
2. Angeros 21.01.13 08:45 Сейчас в теме
Как то название темы несколько выбивается из контекста содержания, или это только мне так кажется?!
3. yuraos 991 21.01.13 09:20 Сейчас в теме
(2) Чем же, Angeros, поясните пожалуста?
4. Angeros 21.01.13 09:40 Сейчас в теме
прямой доступ к EXCEL через ADO - описывает избитую тему про обмен с екселем через адо. А у вас какаято обработка на подобии консоли запросов...
5. yuraos 991 21.01.13 10:25 Сейчас в теме
(4) Angeros, ну дык она и осуществляет прямой доступ к ЕКСЕЛЮ через ADO ...
... наглядным образом ;)
6. yuraos 991 24.02.13 11:20 Сейчас в теме
Хочется еще раз поблагодарить MiCe за оказавшееся ценным замечание.

По невнимательности в строке соединения
стандартных настроек "ADO-подключение к файлу XLS ч/з ACE.OLEDB-12.0",
используемых для "предопределенного" узла "ExcelAce"
было пропущено неприметное словечко "Xml".

Как раз из-за этого Excel-2012 и отказывался узнавать
созданный провайдером при выгрузке данных файл с расширением *.xlsx.
Поскольку фактический формат созданного файла другой - "бинарная" рабочая книга *.xlsb.

Более правильной для расширения файла *.xlsx будет строка соединения
Provider=Microsoft.ACE.OLEDB.12.0; Data Source="!Путь!"; Extended Properties="Excel 12.0 Xml; HDR=Yes;"

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