При разработке обработок загрузки данных из табличных файлов Excel в прикладные объекты 1С:Предприятия часто возникают логические ошибки в циклах обхода ячеек. Типичная ситуация: обработка создает нужное количество документов, но во все созданные объекты переносятся реквизиты исключительно из самой последней строки файла. Разберем причины возникновения этой ошибки, детально проанализируем код и рассмотрим правильные варианты реализации загрузки.
Проанализируем исходный фрагмент кода, в котором выполняется обход строк и колонок таблицы через COM-объект Excel.Application:
Для НС = 1 по ФайлСтрок Цикл
ДокОстаток = Документы.ВводНачальныхОстатковОС.СоздатьДокумент();
// ... заполнение шапки ...
Для Каждого СтрокаМассив из МассивКолонок Цикл
ТекущееЗначение = ОбъектExcel.Cells(ФайлСтрок, СтрокаМассив.НомерКолонки).Text;
ИмяКолонки = ОбъектExcel.Cells(1, СтрокаМассив.НомерКолонки).Text;
// ... обработка значений ...
КонецЦикла;
КонецЦикла;
Выясним причину некорректного поведения. В цикле обхода строк переменная НС выступает в роли счетчика текущей строки. Однако при чтении значения ячейки в выражении ОбъектExcel.Cells(ФайлСтрок, СтрокаМассив.НомерКолонки).Text вместо счетчика НС передается переменная ФайлСтрок, содержащая общее количество строк на листе. В результате на каждой итерации внешнего цикла считываются данные только из самой последней строки таблицы Excel.
Помимо неверной переменной строки, при детальном рассмотрении алгоритма обнаруживается еще несколько грубых ошибок, влияющих на корректность и производительность:
ДокОстаток.Записать() расположен во внутреннем цикле перебора колонок. Это приводит к тому, что документ перезаписывается в базе данных столько раз, сколько колонок обрабатывается в строке. Вызов метода записи должен выполняться строго один раз после полного заполнения документа.Для НС = 1 по ФайлСтрок. Так как первая строка содержит наименования колонок, чтение фактических данных необходимо начинать со второй строки: Для НС = 2 по ФайлСтрок.Если ТекущееЗначение <> Строка(Справочники.ОсновныеСредства.НайтиПоНаименованию(...)) является ошибочной. Метод НайтиПоНаименованию возвращает ссылку на элемент либо пустую ссылку типа СправочникСсылка. Проверять наличие найденного элемента необходимо через метод .Пустая().Если и Иначе, создавая лишнюю нагрузку на СУБД. Результат поиска следует сохранять в отдельную переменную.Соберем все исправления в единый рабочий алгоритм:
// Обход строк данных, начиная со второй (пропуская заголовок)
Для НС = 2 По ФайлСтрок Цикл
ДокОстаток = Документы.ВводНачальныхОстатковОС.СоздатьДокумент();
ДокОстаток.Организация = Организация;
ДокОстаток.Дата = Дата('20171231');
ДокОстаток.ИФО = ИФО;
ДокОстаток.КФО = КФО;
ДокОстаток.ОтражатьВБухгалтерскомУчете = Истина;
ДокОстаток.СчетУчета = СчетУчета;
ИО = ДокОстаток.ИнвентарныеОбъекты.Добавить();
ИО.СостояниеОС = СостояниеОС;
СИА = ДокОстаток.СтоимостьИАмортизация.Добавить();
СИА.ПорядокПогашенияСтоимости = Перечисления.ПорядокПогашенияСтоимостиОС.НачислениеАмортизации;
Для Каждого СтрокаМассив Из МассивКолонок Цикл
// Считываем значение ячейки из текущей строки НС
ТекущееЗначение = СокрЛП(ОбъектExcel.Cells(НС, СтрокаМассив.НомерКолонки).Text);
ИмяКолонки = СтрокаМассив.НазваниеКолонки;
Если ИмяКолонки = "Основноесредство" Тогда
НайденноеОС = Справочники.ОсновныеСредства.НайтиПоНаименованию(ТекущееЗначение, Истина);
Если НайденноеОС.Пустая() Тогда
НовоеОС = Справочники.ОсновныеСредства.СоздатьЭлемент();
НовоеОС.Наименование = ТекущееЗначение;
НовоеОС.ВидНФА = Перечисления.ВидыНФА.ОС;
НовоеОС.Организация = Организация;
НовоеОС.Записать();
ДокОстаток.ОсновноеСредство = НовоеОС.Ссылка;
Иначе
ДокОстаток.ОсновноеСредство = НайденноеОС;
КонецЕсли;
ИначеЕсли ИмяКолонки = "Количество" Тогда
ЧислоЗначение = Число(СтрЗаменить(ТекущееЗначение, " ", ""));
ДокОстаток.ПервоначальноеКоличество = ЧислоЗначение;
ИО.Количество = ЧислоЗначение;
ИначеЕсли ИмяКолонки = "Балансоваястоимость" Тогда
ЧислоЗначение = Число(СтрЗаменить(СтрЗаменить(ТекущееЗначение, " ", ""), ",", "."));
ДокОстаток.ПервоначальнаяСтоимость = ЧислоЗначение;
ИО.Стоимость = ЧислоЗначение;
ИначеЕсли ИмяКолонки = "Суммаамортизации" Тогда
ЧислоЗначение = Число(СтрЗаменить(СтрЗаменить(ТекущееЗначение, " ", ""), ",", "."));
ДокОстаток.ПервоначальнаяСуммаАмортизации = ЧислоЗначение;
ИО.СуммаАмортизации = ЧислоЗначение;
КонецЕсли;
КонецЦикла;
// Запись документа выполняется один раз после обработки всех колонок
ДокОстаток.Записать(РежимЗаписиДокумента.Запись);
КонецЦикла;
Использование COM-объекта Excel.Application на стороне сервера несет в себе серьезные риски: несовместимость с серверами на Linux, зависимость от установленного пакета MS Office, зависание процессов EXCEL.EXE и проблемы с правами доступа службы 1С. Платформа 1С:Предприятие предоставляет встроенный инструмент — объект ТабличныйДокумент, который умеет напрямую считывать файлы форматов .xls и .xlsx без сторонних библиотек.
Рассмотрим реализацию чтения файла с помощью табличного документа:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(Путь, СпособЧтенияЗначенийТабличногоДокумента.Текст);
ВсегоСтрок = ТабДок.ВысотаТаблицы;
ВсегоКолонок = ТабДок.ШиринаТаблицы;
// Создаем карту соответствия номеров колонок
СоответствиеКолонок = Новый Соответствие;
Для НомерКолонки = 1 По ВсегоКолонок Цикл
ИмяКолонки = СокрЛП(ТабДок.ПолучитьОбласть(1, НомерКолонки).Текст);
Если ЗначениеЗаполнено(ИмяКолонки) Тогда
СоответствиеКолонок.Вставить(ИмяКолонки, НомерКолонки);
КонецЕсли;
КонецЦикла;
// Обход строк данных
Для НомерСтроки = 2 По ВсегоСтрок Цикл
КолонкаОС = СоответствиеКолонок["Основное средство"];
Если КолонкаОС = Неопределено Тогда
Продолжить;
КонецЕсли;
ЗначениеОС = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, КолонкаОС).Текст);
Если ПустаяСтрока(ЗначениеОС) Тогда
Продолжить;
КонецЕсли;
ДокОстаток = Документы.ВводНачальныхОстатковОС.СоздатьДокумент();
ДокОстаток.Организация = Организация;
ДокОстаток.Дата = Дата('20171231');
ДокОстаток.ОтражатьВБухгалтерскомУчете = Истина;
НайденноеОС = Справочники.ОсновныеСредства.НайтиПоНаименованию(ЗначениеОС, Истина);
Если НайденноеОС.Пустая() Тогда
НовоеОС = Справочники.ОсновныеСредства.СоздатьЭлемент();
НовоеОС.Наименование = ЗначениеОС;
НовоеОС.ВидНФА = Перечисления.ВидыНФА.ОС;
НовоеОС.Организация = Организация;
НовоеОС.Записать();
ДокОстаток.ОсновноеСредство = НовоеОС.Ссылка;
Иначе
ДокОстаток.ОсновноеСредство = НайденноеОС;
КонецЕсли;
// Запись сформированного документа
ДокОстаток.Записать(РежимЗаписиДокумента.Запись);
КонецЦикла;
Чтение через ТабличныйДокумент работает значительно быстрее COM-соединения, не оставляет неуправляемых фоновых процессов в операционной системе и полностью кроссплатформенно.