Интеграция с внешними файлами — одна из самых частых задач в практике программиста 1С. Загрузка данных из таблиц Excel в документы (например, в табличную часть «Товары» документа «Поступление товаров и услуг») кажется простой процедурой, однако здесь кроется множество неочевидных логических и архитектурных ошибок. В рамках этой статьи мы подробно разберем, как реализовать отбор данных по контрагенту на этапе чтения файла, исправим ошибку «заполнения только последней строки» и выясним, почему не переносятся такие реквизиты, как ставки НДС и счета учета.
Рассмотрим исходную задачу: у нас есть файл Excel, содержащий более 200 строк. Нам необходимо создать документ Поступление (акты, накладные) и заполнить его табличную часть «Товары» строками из файла. Но загружать нужно не все строки подряд, а только те, у которых контрагент совпадает с контрагентом, выбранным на форме обработки в 1С (реквизит Объект.Контрагент).
При первой попытке написания кода автор столкнулся со следующими проблемами:
excel.exe.Проанализируем эти ошибки и найдем их элегантное решение.
---
Обратим внимание на фрагмент кода, который изначально использовался для обхода строк таблицы Excel:
Для СтрокаОтсчета = 2 по КоличествоСтрок Цикл
Если не ПустаяСтрока(НаименованиеКонтрагента) Тогда
Если НаименованиеКонтрагента = Excel.Cells(СтрокаОтсчета, 23).Text Тогда
НСтр = НовыйДокумент.Товары.Добавить();
КонецЕсли;
КонецЕсли;
Для каждого СтрокаМассив из МассивКолонок Цикл
ТекущееЗначение = Excel.Cells(СтрокаОтсчета, СтрокаМассив.НомерКолонки).Value;
// Здесь шло заполнение реквизитов созданной строки: НСтр.Номенклатура = ...
КонецЦикла;
КонецЦикла;
Выясним причину такого поведения платформы. Метод Добавить() вызывается строго внутри условия, когда контрагент в файле совпадает с отбором. Но цикл по колонкам (заполнение реквизитов) выполняется всегда, вне зависимости от того, сработало условие или нет!
Что происходит в памяти программы? Когда условие по контрагенту выполняется впервые, создается новая строка НСтр. На следующей итерации цикла контрагент может не совпасть — новая строка в документе 1С не создается. Однако переменная НСтр по-прежнему хранит ссылку на ранее созданную строчку! В результате цикл по колонкам выполняется для неподходящей строки Excel и просто перезаписывает данные в той же самой строке 1С. К концу работы алгоритма мы получаем одну единственную строку, заполненную значениями из последней строки файла Excel.
Правильное решение: использовать оператор Продолжить сразу после начала цикла по строкам Excel, если строка не удовлетворяет условию отбора. В этом случае выполнение текущей итерации прерывается, и программа мгновенно переходит к следующей строке файла.
Для СтрокаОтсчета = 2 по КоличествоСтрок Цикл
// Считываем контрагента из 23-й колонки Excel
КонтрагентИзФайла = Excel.Cells(СтрокаОтсчета, 23).Text;
// Если контрагент не совпадает с выбранным на форме — пропускаем строку
Если КонтрагентИзФайла <> НаименованиеКонтрагента Тогда
Продолжить;
КонецЕсли;
// Сюда алгоритм дойдет только при совпадении контрагентов
НСтр = НовыйДокумент.Товары.Добавить();
// Заполняем реквизиты строки
Для каждого СтрокаМассив из МассивКолонок Цикл
// Код заполнения...
КонецЦикла;
КонецЦикла;
---
Перейдем ко второй проблеме. Автор пожаловался, что при попытке заполнить ставку НДС и счет учета данные поля остаются пустыми. Посмотрим на строки кода:
ИначеЕсли ИмяКолонки = "Ставка НДС" Тогда
НСтр.СтавкаНДС = Перечисления.СтавкиНДС.БезНДС;
ИначеЕсли ИмяКолонки = "Ссылка.Счет учета расчетов по авансам" Тогда
НСтр.СчетУчета = ТекущееЗначение;
Разберем детально, почему этот код не работает:
ИмяБезПробелов = СтрЗаменить(ИмяКолонки, " ", "");. Таким образом, в массиве колонок название хранится в виде "СтавкаНДС". Сравнение ИмяКолонки = "Ставка НДС" (с пробелом) никогда не вернет истину. Сравнивать нужно с переменной, из которой удалены пробелы."41.01"). Но реквизит табличной части СчетУчета в 1С имеет ссылочный тип данных — ПланСчетовСсылка.Хозрасчетный. Платформа 1С не умеет автоматически сопоставлять строку со счетом. Нам необходимо программно найти счет в плане счетов по его коду.Справочники.СтавкиНДС. Прямая попытка записать туда значение перечисления (даже если оно существует) приведет к тому, что поле останется незаполненным.Правильное решение: приведем типы данных из Excel к типам данных 1С:
// Ищем счет учета в плане счетов по коду, полученному из Excel
НайденныйСчет = ПланыСчетов.Хозрасчетный.НайтиПоКоду(ТекущееЗначение);
Если Не НайденныйСчет.Пустая() Тогда
НСтр.СчетУчета = НайденныйСчет;
КонецЕсли;
// Ищем ставку НДС в справочнике по наименованию
СтавкаНДССсылка = Справочники.СтавкиНДС.НайтиПоНаименованию("Без НДС", Истина);
Если ЗначениеЗаполнено(СтавкаНДССсылка) Тогда
НСтр.СтавкаНДС = СтавкаНДССсылка;
КонецЕсли;
---
Использование COM-объекта Excel.Application — классический, но крайне устаревший и медленный способ работы. На каждую ячейку тратится время на межпроцессное взаимодействие между 1С и Excel. Более того, если ваша база 1С работает на сервере под управлением Linux, COM-объекты там работать не будут в принципе.
Мы настоятельно рекомендуем использовать современный механизм платформы — чтение файлов Excel напрямую через встроенный объект 1С ТабличныйДокумент. Этот способ работает без установленного MS Excel на компьютере, выполняется на сервере и отрабатывает в десятки раз быстрее.
Рассмотрим по шагам, как переписать алгоритм загрузки с использованием табличного документа:
ТабДок = Новый ТабличныйДокумент;
Попытка
// Читаем файл Excel напрямую средствами платформы 1С
ТабДок.Прочитать(Объект.ФайлExcel, СпособЧтенияТабличногоДокумента.Excel);
Исключение
ВызватьИсключение "Не удалось прочитать файл Excel! Ошибка: " + ОписаниеОшибки();
КонецПопытки;
КоличествоСтрок = ТабДок.ВысотаТаблицы;
// Предположим, что колонки в файле распределены стандартно:
// Колонка 23 - Контрагент
// Колонка 1 - Номенклатура (текст)
// Колонка 2 - Количество (число)
// Колонка 3 - Цена (число)
// Колонка 4 - Код счета учета (например, "41.01")
НовыйДокумент = Документы.ПоступлениеТоваровУслуг.СоздатьДокумент();
НовыйДокумент.Дата = ТекущаяДата();
НовыйДокумент.Контрагент = Объект.Контрагент;
НовыйДокумент.Организация = Объект.Организация;
НовыйДокумент.Склад = Объект.Склад;
НовыйДокумент.ВидОперации = Объект.ВидОперации;
НаименованиеКонтрагента = Объект.Контрагент.Наименование;
Для НомерСтроки = 2 По КоличествоСтрок Цикл
// Получаем текст контрагента из ячейки Excel
КонтрагентТекст = ТабДок.ПолучитьОбласть(НомерСтроки, 23, НомерСтроки, 23).ТекущийОбласть.Текст;
// Фильтруем строки по контрагенту
Если СокрЛП(КонтрагентТекст) <> СокрЛП(НаименованиеКонтрагента) Тогда
Продолжить;
КонецЕсли;
// Добавляем строку в табличную часть "Товары"
НСтр = НовыйДокумент.Товары.Добавить();
// Читаем номенклатуру
НоменклатураТекст = ТабДок.ПолучитьОбласть(НомерСтроки, 1, НомерСтроки, 1).ТекущийОбласть.Текст;
НСтр.Номенклатура = Справочники.Номенклатура.НайтиПоНаименованию(НоменклатураТекст, Истина);
// Читаем количество и цену
НСтр.Количество = Число(ТабДок.ПолучитьОбласть(НомерСтроки, 2, НомерСтроки, 2).ТекущийОбласть.Текст);
НСтр.Цена = Число(ТабДок.ПолучитьОбласть(НомерСтроки, 3, НомерСтроки, 3).ТекущийОбласть.Текст);
// Читаем счет учета и преобразуем его в ссылку
СчетУчетаТекст = ТабДок.ПолучитьОбласть(НомерСтроки, 4, НомерСтроки, 4).ТекущийОбласть.Текст;
НСтр.СчетУчета = ПланыСчетов.Хозрасчетный.НайтиПоКоду(СчетУчетаТекст);
// Заполняем Ставку НДС из справочника
НСтр.СтавкаНДС = Справочники.СтавкиНДС.НайтиПоНаименованию("Без НДС");
// Рассчитываем сумму
НСтр.Сумма = НСтр.Количество * НСтр.Цена;
КонецЦикла;
НовыйДокумент.Записать(РежимЗаписиДокумента.Запись);
Сообщить("Успешно создан документ: " + НовыйДокумент.Ссылка);
---
Если по каким-то причинам вы обязаны использовать именно COM-объект, крайне важно гарантировать закрытие процесса excel.exe в операционной системе. Если этого не сделать, при любой непредвиденной ошибке в цикле процесс останется висеть в памяти. С каждым запуском обработки свободная память на сервере будет уменьшаться, что в итоге приведет к зависанию всей системы.
Для предотвращения этой проблемы всегда оборачивайте работу с внешними приложениями в блок Попытка ... Исключение, а в секции завершения обязательно выполняйте деструктуризацию объекта:
Excel = Неопределено;
Попытка
Excel = Новый COMОбъект("Excel.Application");
Excel.Workbooks.Open(Объект.ФайлExcel);
// ... весь ваш код загрузки и циклы обработки ...
// Корректное закрытие в конце успешного выполнения
Excel.Quit();
Excel = Неопределено;
Исключение
// Если произошел сбой в коде, закрываем Excel принудительно
Если Excel <> Неопределено Тогда
Excel.DisplayAlerts = 0;
Excel.Quit();
Excel = Неопределено;
КонецЕсли;
// Передаем ошибку дальше для анализа программистом
ВызватьИсключение КраткоеПредставлениеОшибки(ИнформацияОбОшибке());
КонецПопытки;
Применение такой конструкции гарантирует стабильность операционной системы сервера приложений 1С и исключает утечки оперативной памяти.