Как правильно загрузить данные из Excel в 1С и заполнить связанные справочники

← На главную

Загрузка данных из внешних файлов — одна из самых частых задач программиста 1С. Однако при реализации этой функции часто возникают типичные проблемы: создание пустых элементов, пропуски реквизитов или невозможность корректно привязать данные к существующим справочникам. В рамках данной статьи мы подробно разберем процесс загрузки данных на примере справочника Номенклатура, а также выясним, как правильно организовать создание связанных объектов, таких как Фирмы и Типы продукции.

Постановка задачи и выбор технологии

Представим ситуацию: у нас есть файл Excel, содержащий колонки с кодом товара, наименованием, типом продукции, названием фирмы, единицей измерения и количеством в коробке. Нам необходимо не просто создать новые элементы в справочнике Номенклатура, но и корректно заполнить в нем ссылки на другие справочники. Рассмотрим наиболее распространенный метод реализации — использование COM-объекта.

Шаг 1. Организация интерфейса и выбор файла

Прежде чем приступать к чтению данных на сервере, нам нужно предоставить пользователю возможность выбрать файл на его локальном компьютере. Для этого на клиенте используется объект ДиалогВыбораФайла. Важно помнить, что в веб-клиенте или тонком клиенте для работы с файлами требуется подключение расширения. Проанализируем пример кода:


&НаКлиенте
Процедура Загрузить(Команда)
    РасширениеПодключено = ПодключитьРасширениеРаботыСФайлами();
    Если Не РасширениеПодключено Тогда
        УстановитьРасширениеРаботыСФайлами();
        РасширениеПодключено = ПодключитьРасширениеРаботыСФайлами();
    КонецЕсли;

    Если РасширениеПодключено Тогда
        ДиалогВыбораФайла = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
        ДиалогВыбораФайла.Фильтр = "Файлы Excel (*.xls,*.xlsx) |*.xls*";
        ДиалогВыбораФайла.Заголовок = "Выберите файл для загрузки";
        
        Если ДиалогВыбораФайла.Выбрать() Тогда
            ОбработкаExcel(ДиалогВыбораФайла.ПолноеИмяФайла);
        КонецЕсли;
    Иначе
        Сообщить("Расширение работы с файлами не доступно!");
    КонецЕсли;
КонецПроцедуры

Здесь мы используем метод ПодключитьРасширениеРаботыСФайлами(), чтобы обеспечить стабильную работу в различных браузерах и типах клиентов 1С.

Шаг 2. Инициализация COM-объекта и чтение структуры файла

Перейдем на серверную часть. Нам необходимо создать объект Excel.Application, открыть выбранный файл и определить границы данных. Рассмотрим, как правильно вычислить количество строк и колонок, учитывая разные версии формата Excel.


&НаСервере
Процедура ОбработкаExcel(Файл)
    Попытка
        Excel = Новый COMОбъект("Excel.Application");
        Excel.Workbooks.Open(СокрЛП(Файл));
    Исключение
        Сообщить("Не удалось открыть файл Excel. Возможно, Excel не установлен на сервере.");
        Возврат;
    КонецПопытки;

    Excel.ActiveWorkbook.Worksheets(1).Activate;
    
    // Определяем количество строк и колонок через SpecialCells(11)
    ФайлСтрок = Excel.Cells(1,1).SpecialCells(11).Row;
    ФайлКолонок = Excel.Cells(1,1).SpecialCells(11).Column;

    // Задаем номера колонок в соответствии с файлом
    НомерКолонкиКода = 1;
    НомерКолонкиНаименования = 2;
    НомерКолонкиТип = 3;
    НомерКолонкиФирма = 4;
    // ... и так далее

Обратите внимание на использование метода SpecialCells(11). Это универсальный способ найти последнюю заполненную ячейку на листе, что гораздо надежнее, чем просто перебор фиксированного диапазона.

Шаг 3. Логика создания связанных объектов

Одной из главных ошибок при загрузке является попытка создать основной объект (Товар) раньше, чем будут созданы его составляющие (Фирма или Тип продукции). Рассмотрим правильную последовательность действий внутри цикла обхода строк:

Сначала мы ищем или создаем вспомогательные элементы справочников ТипПродукции и Фирмы.
Сохраняем полученные ссылки в переменные.
Только после этого создаем или обновляем элемент справочника Номенклатура, подставляя в него уже готовые ссылки.

Разберем реализацию этого алгоритма, добавив обязательную проверку на заполненность данных методом ЗначениеЗаполнено(), чтобы избежать появления «пустых» записей в базе:


Для СчСтроки = 2 по ФайлСтрок Цикл
    // Читаем значения из ячеек
    КодТовара = СокрЛП(Excel.Cells(СчСтроки, НомерКолонкиКода).Value);
    НаименованиеТовара = СокрЛП(Excel.Cells(СчСтроки, НомерКолонкиНаименования).Value);
    ТипТовараНаименование = СокрЛП(Excel.Cells(СчСтроки, НомерКолонкиТип).Value);
    ФирмаНаименование = СокрЛП(Excel.Cells(СчСтроки, НомерКолонкиФирма).Value);

    // 1. Работаем с Типом Продукции
    СсылкаТипПродукции = Справочники.ТипПродукции.НайтиПоНаименованию(ТипТовараНаименование, Истина);
    Если СсылкаТипПродукции.Пустая() И ЗначениеЗаполнено(ТипТовараНаименование) Тогда
        НовыйТип = Справочники.ТипПродукции.СоздатьЭлемент();
        НовыйТип.Наименование = ТипТовараНаименование;
        НовыйТип.Записать();
        СсылкаТипПродукции = НовыйТип.Ссылка;
    КонецЕсли;

    // 2. Работаем с Фирмой
    СсылкаФирма = Справочники.Фирмы.НайтиПоНаименованию(ФирмаНаименование, Истина);
    Если СсылкаФирма.Пустая() И ЗначениеЗаполнено(ФирмаНаименование) Тогда
        НоваяФирма = Справочники.Фирмы.СоздатьЭлемент();
        НоваяФирма.Наименование = ФирмаНаименование;
        НоваяФирма.Записать();
        СсылкаФирма = НоваяФирма.Ссылка;
    КонецЕсли;

    // 3. Работаем с основным товаром
    ТоварОбъект = Справочники.Номенклатура.НайтиПоКоду(КодТовара).ПолучитьОбъект();
    Если ТоварОбъект = Неопределено Тогда
        ТоварОбъект = Справочники.Номенклатура.СоздатьЭлемент();
        ТоварОбъект.Код = КодТовара;
    КонецЕсли;

    Если ЗначениеЗаполнено(НаименованиеТовара) Тогда
        ТоварОбъект.Наименование = НаименованиеТовара;
        ТоварОбъект.ТипПродукции = СсылкаТипПродукции; // Передаем ссылку
        ТоварОбъект.Фирма = СсылкаФирма;             // Передаем ссылку
        ТоварОбъект.Записать();
    КонецЕсли;
КонецЦикла;

Шаг 4. Важные нюансы и оптимизация

При работе с большими объемами данных через COMОбъект следует учитывать несколько критических моментов. Проанализируем их подробнее, чтобы ваша обработка работала стабильно:

Завершение процессов: После завершения загрузки обязательно нужно закрыть книгу и выйти из Excel командами Excel.ActiveWorkbook.Close() и Excel.Quit(). Если этого не сделать, в диспетчере задач сервера будут скапливаться «брошенные» процессы EXCEL.EXE, что со временем приведет к нехватке оперативной памяти.
Транзакции: Для ускорения записи тысяч элементов рекомендуется использовать метод НачатьТранзакцию() перед циклом и ЗафиксироватьТранзакцию() после него. Это сократит количество обращений к диску в десятки раз.
Поиск данных: Метод НайтиПоНаименованию работает медленнее, чем НайтиПоКоду. Если в файле есть уникальные идентификаторы (например, артикул), лучше использовать их. Также не забывайте про второй параметр в методе поиска (Истина), который означает поиск по полному соответствию.
Режим загрузки: При программном создании элементов полезно устанавливать свойство ТоварОбъект.ОбменДанными.Загрузка = Истина. Это позволит избежать лишних проверок в модуле объекта и ускорит запись.

Альтернативный взгляд: отказ от COM

Выясним причину, по которой многие разработчики отказываются от Excel.Application. Данный метод требует установленного Microsoft Office на сервере, что часто невозможно в облачных сервисах или на Linux-серверах 1С. Рассмотрим современную альтернативу — использование объекта ТабличныйДокумент.

Вы можете прочитать файл .xlsx напрямую средствами 1С:


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

Этот метод работает значительно быстрее, не требует лицензии Excel и позволяет обращаться к ячейкам как к элементам таблицы 1С через ТабДок.Область(Строка, Колонка).Текст.

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