gifts2017

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

Опубликовал Юрий Осипов (yuraos) в раздел Обмен - Обмен через DBF

На примере этой, очень популярной, темы наглядно продемонстрированы
нетривиальные возможности консоли запросов в составе подсистемы:
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.

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Юрий Осипов (yuraos) 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) 21.01.13 09:20
(2) Чем же, Angeros, поясните пожалуста?
4. Доржи Балбаров (Angeros) 21.01.13 09:40
прямой доступ к EXCEL через ADO - описывает избитую тему про обмен с екселем через адо. А у вас какаято обработка на подобии консоли запросов...
5. Юрий Осипов (yuraos) 21.01.13 10:25
(4) Angeros, ну дык она и осуществляет прямой доступ к ЕКСЕЛЮ через ADO ...
... наглядным образом ;)
6. Юрий Осипов (yuraos) 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;"

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