Каталог решений - Подсистема COMExchange: прямой доступ к EXCEL через ADO

Все истинные арийцы коннектятся через 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.