Excel и 1С в связке: COM, ODBC, табличный документ и обмен файлами

Синхронизировать Excel с 1С можно пятью способами: штатной загрузкой и выгрузкой через табличный документ, COM-соединением (V83.COMConnector из Excel или Excel.Application из 1С), чтением книги через ADO/ODBC, обменом промежуточными файлами CSV/XML/JSON и подключением Excel к 1С по OData. Для разовой загрузки прайса хватит первого. Для отчёта в Excel, который сам обновляется из базы, подходят COM или OData.

Сразу договоритесь, кто главный. Живая двусторонняя синхронизация, где правки из книги сами летят в базу, почти всегда заканчивается задвоениями. Рабочая схема: 1С — источник правды, Excel — витрина или входной шаблон, который проверяют перед загрузкой.

Что выбрать: сравнение способов связи Excel и 1С

Способы отличаются тем, где выполняется код, нужен ли установленный Excel и сколько строк выдержат без тормозов.

Загрузка и выгрузка через табличный документ платформы

Самый надёжный путь. Платформа 8.3 сама читает и пишет .xlsx без установленного Office, поэтому код работает и на клиенте, и на сервере.

Без программирования

1. Выгрузка. Откройте любой отчёт или список, нажмите Ctrl+S или кнопку сохранения, выберите тип «Лист Microsoft Excel 2007 (*.xlsx)». Из списка документов то же самое делает команда «Вывести список » в меню «Ещё»: она открывает табличный документ, который затем сохраняют.
2. Загрузка в справочник. В БП 3.0 и УТ 11 в списке номенклатуры есть команда «Загрузить» (в части релизов она в меню «Ещё»). Программа предложит вставить данные из буфера или выбрать файл, затем сопоставить колонки.
3. Загрузка в документ. В табличной части «Товары» поступления и реализации есть команда загрузки из файла. Позиции, которых нет в справочнике, программа покажет отдельно и предложит создать или сопоставить.

Чтение книги кодом

Для нестандартных шаблонов пишут обработку. Минимальный пример, который читает первый лист и собирает строки в таблицу значений:

ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ИмяФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);

Строки = Новый ТаблицаЗначений;
Строки.Колонки.Добавить("Артикул");
Строки.Колонки.Добавить("Количество");

Для НомерСтроки = 2 По ТабДок.ВысотаТаблицы Цикл // строка 1 — заголовки
    Артикул = СокрЛП(ТабДок.Область(НомерСтроки, 1, НомерСтроки, 1).Текст);
    Если ПустаяСтрока(Артикул) Тогда
        Продолжить;
    КонецЕсли;
    ТекстКол = СтрЗаменить(ТабДок.Область(НомерСтроки, 3, НомерСтроки, 3).Текст, Символы.НПП, "");
    НоваяСтрока = Строки.Добавить();
    НоваяСтрока.Артикул = Артикул;
    НоваяСтрока.Количество = Число(СтрЗаменить(ТекстКол, " ", ""));
КонецЦикла;

Режим Значение возвращает данные ячейки, а не отображаемый текст с форматом. Это важно для дат и чисел с разделителями разрядов. Символы.НПП убирает неразрывные пробелы, которые Excel вставляет в числа при копировании из банк-клиентов.

Запись обратно — одной строкой:

ТабДок.Записать(ИмяФайла, ТипФайлаТабличногоДокумента.XLSX);

Если обработка работает на сервере, а файл выбрал пользователь, сначала передайте его во временное хранилище (НачатьПомещениеФайла на клиенте), а на сервере получите двоичные данные и запишите во временный файл через ПолучитьИмяВременногоФайла("xlsx").

Прямое COM-соединение: Excel и 1С управляют друг другом

COM работает только в Windows и в обе стороны. Excel может подключиться к базе 1С как внешнее соединение, а 1С может запустить Excel и командовать им.

Из Excel в 1С через V83.COMConnector

Сначала зарегистрируйте компоненту comcntr.dll той же версии, что и платформа. Командная строка от имени администратора:

regsvr32 "C:\Program Files\1cv8\8.3.24.1586\bin\comcntr.dll"

Номер версии в пути подставьте свой. Разрядность компоненты должна совпадать с разрядностью Excel: для 32-битного Office регистрируют файл из C:\Program Files (x86)\1cv8\…, для 64-битного — из C:\Program Files\1cv8\…. Разрядность Office видна в Файл → Учётная запись → О программе Excel.

Макрос, который выгружает номенклатуру на лист:

Sub ОбновитьНоменклатуру()
    Dim Connector As Object, Base As Object, Q As Object, Sel As Object
    Dim Data() As Variant, Cnt As Long, i As Long

    Set Connector = CreateObject("V83.COMConnector")
    Set Base = Connector.Connect("File=""C:\Bases\Buh"";Usr=""Обмен"";Pwd=""пароль"";")
    ' клиент-сервер: "Srvr=""srv1c"";Ref=""buh"";Usr=""Обмен"";Pwd=""пароль"";"

    Set Q = Base.NewObject("Query")
    Q.Text = "ВЫБРАТЬ Н.Код КАК Code, Н.Наименование КАК Name, Н.Артикул КАК Sku " & _
             "ИЗ Справочник.Номенклатура КАК Н ГДЕ НЕ Н.ЭтоГруппа"
    Set Sel = Q.Execute().Select()

    Cnt = Sel.Count()
    If Cnt = 0 Then Exit Sub
    ReDim Data(1 To Cnt, 1 To 3)
    i = 0
    Do While Sel.Next()
        i = i + 1
        Data(i, 1) = Sel.Code
        Data(i, 2) = Sel.Name
        Data(i, 3) = Sel.Sku
    Loop

    With ThisWorkbook.Sheets(1)
        .Range("A2:C1048576").ClearContents
        .Range("A2").Resize(Cnt, 3).Value = Data
    End With
End Sub

Что здесь сделано правильно:

• 🔹 поля запроса получили латинские псевдонимы Code, Name, Sku — к ним удобно обращаться из VBA;
• 🔹 данные сначала собираются в массив и пишутся на лист одним присваиванием. Запись по одной ячейке на 5 000 строк занимает минуты, массивом — секунды;
• 🔹 пользователь Обмен заведён отдельно, с правами только на чтение нужных данных и правом внешнего соединения.

Из 1С в Excel через Excel.Application

Обратная схема нужна, когда книга сложная: несколько листов, формулы, сводные таблицы, которые надо заполнить, не ломая оформление.

Excel = Новый COMОбъект("Excel.Application");
Excel.DisplayAlerts = Ложь;
Попытка
    Книга = Excel.Workbooks.Open(ИмяФайла);
    Лист = Книга.Worksheets(1);
    Лист.Cells(2, 5).Value = СуммаОстатка;
    Книга.Save();
    Книга.Close(Ложь);
Исключение
    ЗаписьЖурналаРегистрации("Обмен с Excel", УровеньЖурналаРегистрации.Ошибка, , , ОписаниеОшибки());
КонецПопытки;
Excel.Quit();
Excel = Неопределено;

Вызов Excel.Quit() обязателен в любом исходе. Иначе в диспетчере задач копятся процессы EXCEL.EXE, которые держат файл открытым.

Чтение Excel через ADO и драйверы ODBC

ADO читает лист как таблицу базы данных: SQL-запросом, без запуска Excel. Это быстро и годится для сервера 1С, где Office ставить нельзя. Нужен компонент Microsoft Access Database Engine (провайдер Microsoft.ACE.OLEDB.12.0) той же разрядности, что и процесс, который его вызывает: клиент 1С или рабочий процесс сервера.

Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ИмяФайла
    + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";");

Набор = Соединение.Execute("SELECT * FROM [Лист1$] WHERE [Артикул] IS NOT NULL");
Пока НЕ Набор.EOF Цикл
    Артикул = Набор.Fields("Артикул").Value;
    Цена = Набор.Fields("Цена").Value;
    // обработка строки
    Набор.MoveNext();
КонецЦикла;
Набор.Close();
Соединение.Close();

Параметры строки подключения:

• 📌 HDR=YES — первая строка содержит имена колонок, к полям обращаются по имени;
• 📌 IMEX=1 — смешанные колонки читаются как текст. Без него драйвер угадывает тип по первым строкам и превращает артикулы вроде 00125 в число 125 или в пустоту;
• 📌 [Лист1$] — имя листа со знаком доллара. Диапазон задают так: [Лист1$A1:F500].

Второй вариант — объект конфигурации «Внешние источники данных». Он подключается к книге через ODBC-драйвер Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) и позволяет обращаться к листу прямо в запросах 1С. Это изменение конфигурации, поэтому для типовых баз его добавляют расширением, а не правкой основной конфигурации.

Обмен через CSV, XML и JSON

Когда Excel — лишь промежуточное звено между 1С и другой программой, книгу лучше заменить текстовым файлом. Он не зависит от версии Office, его легко проверить глазами и разобрать любым языком.

Чтение CSV в 1С:

Чтение = Новый ЧтениеТекста(ИмяФайла, КодировкаТекста.UTF8);
Строка = Чтение.ПрочитатьСтроку(); // заголовок
Строка = Чтение.ПрочитатьСтроку();
Пока Строка <> Неопределено Цикл
    Поля = СтрРазделить(Строка, ";", Истина);
    // Поля[0] — артикул, Поля[1] — количество
    Строка = Чтение.ПрочитатьСтроку();
КонецЦикла;
Чтение.Закрыть();

Для JSON в платформе есть ЧтениеJSON и функция ПрочитатьJSON, для XML — ЧтениеXML и XDTO. В Excel такие файлы открывают через Данные → Получить данные → Из файла: Power Query разворачивает вложенные структуры в таблицу и запоминает шаги, так что следующий файл загружается кнопкой «Обновить».

Живая связь: Excel получает данные из 1С по OData

Если база опубликована на веб-сервере (IIS или Apache), платформа отдаёт данные по стандартному протоколу OData. Excel подключается к нему через Данные → Получить данные → Из других источников → Из канала OData. Адрес выглядит так: https://server/buh/odata/standard.odata/.

Порядок действий:

1. Опубликуйте базу на веб-сервере с флажком публикации стандартного интерфейса OData.
2. Укажите, какие объекты доступны по OData. В типовых конфигурациях на БСП это делают в разделе Администрирование → Синхронизация данных по ссылке настройки стандартного интерфейса OData. Там же заводят пользователя для доступа.
3. В Excel выберите нужные справочники и регистры, отфильтруйте их в Power Query и загрузите в таблицу или модель данных.
4. Дальше обновление делается кнопкой «Обновить все» или по расписанию в свойствах подключения.

Этот способ не требует COM, регистрации DLL и лицензии на компьютере аналитика: сеанс открывается на веб-сервере. Ограничение одно: через OData удобно читать таблицы, а сложные отчёты с расчётами всё равно проще собрать в 1С и сохранить в .xlsx.

Ошибки при синхронизации и как ускорить обмен

Автоматизировать Office в службе сервера 1С Microsoft не рекомендует: Excel ждёт рабочий стол пользователя и может зависнуть на диалоговом окне. На сервере используйте табличный документ или ADO.

Что даёт реальный прирост скорости:

1. Читайте и пишите массивом. Через COM обращайтесь не к Cells построчно, а к Range(...).Value целиком.
2. Ищите ссылки одним запросом. Не вызывайте НайтиПоРеквизиту на каждой строке. Соберите все артикулы файла в массив, одним запросом получите номенклатуру и сложите в Соответствие «артикул → ссылка».
3. Записывайте порциями в транзакции. Пачки по 500–1 000 объектов внутри НачатьТранзакцию() / ЗафиксироватьТранзакцию() работают быстрее, чем запись по одному. Но длинная транзакция блокирует таблицы для других пользователей, поэтому большие загрузки запускайте вне рабочего времени.
4. Отключайте пересчёт в Excel. При заполнении книги через COM установите Excel.ScreenUpdating = Ложь и Excel.Calculation = -4135 (ручной пересчёт), а в конце верните -4105 (автоматический).

Частые вопросы