В любой крупной или динамично развивающейся компании экономисты, финансовые аналитики и менеджеры часто создают сложные, многостраничные отчеты в формате Excel. В этих файлах содержится огромное количество формул, перекрестных ссылок, графиков и специфических расчетов. Переносить такие отчеты напрямую в 1С в виде стандартных отчетов зачастую не имеет смысла: требования руководства к оформлению и структуре таблиц меняются еженедельно, а в самом отчете могут использоваться внешние данные, которых в принципе нет в информационной системе.
Вручную копировать данные из 1С в Excel — процесс трудоемкий и чреватый ошибками. Возникает логичная задача: настроить систему так, чтобы пользователь мог открыть свой рабочий файл Excel, нажать кнопку «Обновить», и таблицы автоматически заполнились актуальными данными из 1С. Для полной автоматизации рутинного труда также применяется платформа для создания программных роботов One RPA, позволяющая настроить сценарии переноса информации без написания кода. Рассмотрим подробнее самые эффективные и современные способы решения этой задачи.
Это самый элегантный способ, который практически не требует написания сложного кода ни на стороне 1С, ни на стороне Excel. Он базируется на использовании встроенного механизма OData (автоматического REST-сервиса 1С) и мощного инструмента импорта данных в Excel — Power Query. Для быстрой подготовки адресов и параметров обращений подойдёт генератор запросов OData к 1С.
Разберем по шагам, как настроить эту связку:
МассивИмен = Новый Массив;
МассивИмен.Добавить("InformationRegister.ЦеныНоменклатуры");
МассивИмен.Добавить("Catalog.Номенклатура");
МассивИмен.Добавить("AccumulationRegister.Продажи.Обороты");
УстановитьСоставИспользованияOData(МассивИмен);
http://имя_сервера/имя_базы/odata/standard.odata/.Теперь пользователь может строить любые формулы типа ВПР (VLOOKUP) или СУММЕСЛИ, ссылаясь на этот лист. При нажатии кнопки «Обновить все» на вкладке «Данные» Excel сам обратится к 1С через веб-сервер, заберет свежие данные и обновит все зависимые формулы и диаграммы.
Если стандартный интерфейс OData кажется вам избыточным или небезопасным (так как он предоставляет доступ к сырым таблицам БД), отличным решением будет разработка собственных легких HTTP-сервисов на стороне 1С. Они будут отдавать подготовленные, предварительно сгруппированные данные в формате JSON или CSV. Для ускорения разработки таких интерфейсов можно использовать конструктор API для обмена данными в формате JSON.
Проанализируем ситуацию: SOAP-сервисы (классические Web-сервисы 1С) возвращают тяжелый XML, который сложно разбирать в Excel без макросов. HTTP-сервисы лишены этого недостатка.
Пример реализации метода GET в HTTP-сервисе 1С для получения остатков товаров:
Функция ПолучитьОстаткиТоваров(Запрос)
Ответ = Новый HTTPСервисОтвет(200);
Ответ.Заголовки.Вставить("Content-Type", "application/json; charset=utf-8");
// Формируем запрос к данным 1С
ТекстЗапроса =
"ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Номенклатура.Наименование КАК Товар,
| ТоварыНаСкладахОстатки.Склад.Наименование КАК Склад,
| ТоварыНаСкладахОстатки.ВНаличииОстаток КАК Остаток
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыНаСкладахОстатки";
Запрос1С = Новый Запрос(ТекстЗапроса);
ТаблицаДанных = Запрос1С.Выполнить().Выгрузить();
// Конвертируем таблицу в массив структур для JSON
МассивДанных = Новый Массив;
Для Каждого Строка Из ТаблицаДанных Цикл
СтруктураСтроки = Новый Структура("Product, Warehouse, Quantity", Строка.Товар, Строка.Склад, Строка.Остаток);
МассивДанных.Добавить(СтруктураСтроки);
КонецЦикла;
// Записываем JSON в тело ответа
ЗаписьJSON = Новый ЗаписьJSON;
ЗаписьJSON.УстановитьСтроку();
ЗаписатьJSON(ЗаписьJSON, МассивДанных);
СтрокаJSON = ЗаписьJSON.Закрыть();
Ответ.УстановитьТелоИзСтроки(СтрокаJSON, КодировкаТекста.UTF8, ИспользованиеByteOrderMark.НеИспользовать);
Возврат Ответ;
КонецФункции
В Excel получение этих данных настраивается за пару минут: Данные -> Получить данные -> Из других источников -> Из интернета. Указываем URL-адрес нашего HTTP-сервиса. Power Query автоматически распознает формат JSON, преобразует его в таблицу, и мы получаем готовый к использованию массив данных. Для контроля точности полученного массива можно автоматически сравнивать данные между базами 1С при помощи инструментов автоматической выверки.
Выясним преимущества этого метода: если база данных 1С работает под управлением MS SQL Server или PostgreSQL, мы можем настроить прямое чтение таблиц СУБД из Excel. Это обеспечивает колоссальную скорость работы даже на миллионах строк данных.
Однако здесь программисту придется столкнуться с трудностями. Платформа 1С хранит данные в таблицах СУБД под замаскированными именами (например, справочник контрагентов может называться _Reference112, а его реквизит ИНН — _Fld2104). Чтобы построить корректный SQL-запрос, необходимо выяснить соответствие метаданных физическим таблицам СУБД.
Сделать это можно с помощью встроенного метода 1С:
СтруктураХранения = ПолучитьСтруктуруХраненияБазыДанных();
// Полученную таблицу можно выгрузить и проанализировать реальные имена таблиц и полей
Важное предупреждение: Прямые запросы к рабочей базе данных SQL могут вызывать блокировки таблиц и снижать производительность работы активных пользователей 1С. Для безопасной работы рекомендуется настроить репликацию базы данных SQL (например, механизм Always On в MS SQL Server) и отправлять тяжелые запросы из Excel на сервер-реплику, предназначенный только для чтения.
Рассмотрим сценарий, когда ИТ-служба категорически запрещает внешним пользователям и сторонним программам напрямую подключаться к базе данных (неважно, через веб-сервисы или SQL) во избежание деградации производительности в рабочее время.
В этом случае отличной альтернативой выступает создание регламентных заданий в 1С, которые по расписанию (например, каждый час или ночью) формируют плоские таблицы с нужными аналитиками и сохраняют их в сетевую папку компании в формате CSV или XLSX. Чтобы гарантировать стабильность работы системы, удобно автоматизировать обслуживание и обновление баз 1С по расписанию в «тихом» режиме.
Пример серверного кода регламентного задания для выгрузки данных:
Процедура ВыгрузитьПродажиДляАналитиков() Экспорт
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ПродажиОбороты.Период КАК Дата,
| ПродажиОбороты.Номенклатура.Наименование КАК Товар,
| ПродажиОбороты.КоличествоОборот КАК Количество,
| ПродажиОбороты.СуммаОборот КАК Сумма
|ИЗ
| РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода,,) КАК ПродажиОбороты";
Запрос.УстановитьПараметр("НачалоПериода", НачалоГода(ТекущаяДата()));
Запрос.УстановитьПараметр("КонецПериода", КонецДня(ТекущаяДата()));
РезультатЗапроса = Запрос.Выполнить();
// Создаем табличный документ для сохранения в CSV или Excel
ТабДок = Новый ТабличныйДокумент;
Построитель = Новый ПостроительОтчета;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(РезультатЗапроса);
Построитель.Вывести(ТабДок);
ПутьКФайлу = "\\server-share\data\sales_data.csv";
ТабДок.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.CSV);
КонецПроцедуры
Пользователи в своих Excel-файлах настраивают связи с этим файлом sales_data.csv через тот же Power Query. Нагрузка на рабочую базу 1С при этом сводится к нулю, так как пользователи работают со статичным файлом на сетевом диске, который обновляется сервером 1С в фоновом режиме.
Этот метод упомянут в обсуждении на форуме (сообщение 1). На компьютере пользователя пишется макрос на VBA, который запускает фоновый процесс 1С, авторизуется под учетной записью пользователя, выполняет запрос и выгружает результат в ячейки текущего листа.
Пример кода макроса на VBA в Excel:
Sub GetDataFrom1C()
Dim Connector As Object
Dim Connection As Object
Dim Query As Object
Dim QueryResult As Object
' Создаем COM-объект для подключения к 1С
Set Connector = CreateObject("V83.COMConnector")
' Подключаемся к базе данных (вариант для клиент-серверной базы)
Set Connection = Connector.Connect("Srvr=""My1CServer"";Ref=""My1CBase"";Usr=""ExcelUser"";Pwd=""Password123"";")
' Создаем объект запроса внутри 1С
Set Query = Connection.NewObject("Query")
Query.Text = "ВЫБРАТЬ Номенклатура.Наименование КАК Имя, Код ИЗ Справочник.Номенклатура ГДЕ НЕ ЭтоГруппа"
Set QueryResult = Query.Execute().Unload()
' Переносим данные на лист Excel
Dim Row As Long
Row = 1
Do Until QueryResult.EOF()
Cells(Row, 1).Value = QueryResult.Get(Row - 1).Имя
Cells(Row, 2).Value = QueryResult.Get(Row - 1).Код
Row = Row + 1
Loop
MsgBox "Данные успешно обновлены!", vbInformation
End Sub
Недостатки COM-соединения:
Выбор оптимального архитектурного решения зависит от потребностей вашего бизнеса и возможностей ИТ-инфраструктуры: