Интеграция с внешними файлами Excel — одна из самых частых задач, с которыми сталкивается разработчик 1С. Будь то первоначальное заполнение каталога товаров, загрузка прайс-листов поставщиков или импорт курсов валют, логика процесса остается схожей. Однако при реализации этого механизма легко совершить архитектурные и синтаксические ошибки.
В рамках этой статьи мы подробно разберем, как правильно реализовать загрузку данных из Excel в табличную часть обработки, исправим классические ошибки позиционирования строк, решим проблему зависших процессов в операционной системе и рассмотрим современные альтернативные подходы, которые работают быстрее и надежнее стандартного COM-соединения.
Проанализируем ситуацию, описанную в первом сообщении темы. Разработчик пытается прочитать строки из файла Excel в цикле и напрямую присвоить их значения реквизитам табличной части обработки:
Объект.ТабличнаяЧасть1.Номенклатура1 = Лист.Cells(НомерСтроки, 1).Value;
Объект.ТабличнаяЧасть1.Курс1 = Лист.Cells(НомерСтроки, 2).Value;
Выясним причину, почему данный код выдает ошибку или не заполняет таблицу. Дело в том, что ТабличнаяЧасть1 — это коллекция строк (объект типа ДанныеФормыКоллекция в управляемом приложении). Мы не можем напрямую обращаться к реквизитам внутри коллекции без указания конкретной строки. Для добавления новой записи в таблицу необходимо использовать метод Добавить(), который возвращает конкретную строку табличной части. Только после этого мы можем заполнять ее реквизиты.
Также обратим внимание на бесконечный цикл Пока Истина Цикл. Если в файле Excel не будет предусмотрено жесткое условие выхода, программа зависнет. Для предотвращения этого мы должны анализировать содержимое ячеек и прерывать цикл, когда строки закончатся.
Рассмотрим исправленный вариант базового алгоритма заполнения строк:
НомерСтроки = 4; // Предположим, что первые 3 строки — это шапка таблицы
Пока Истина Цикл
// Читаем значение первой колонки для проверки на заполненность
ТекстЯчейки = Лист.Cells(НомерСтроки, 1).Text;
// Если ячейка пустая, значит данные закончились, прерываем цикл
Если СокрЛП(ТекстЯчейки) = "" Тогда
Прервать;
КонецЕсли;
// Добавляем новую строку в табличную часть документа или обработки
НоваяСтрока = Объект.TablichnayaChast1.Добавить();
НоваяСтрока.Номенклатура1 = ТекстЯчейки;
НоваяСтрока.Курс1 = Лист.Cells(НомерСтроки, 2).Value;
НомерСтроки = НомерСтроки + 1;
КонецЦикла;
Важно различать свойства Value и Text объекта ячейки Excel. Свойство Value возвращает типизированное значение ячейки (число, дату или булево). Свойство Text всегда возвращает строковое представление того, что отображается на экране. Для проверки текстовых полей, таких как наименование или артикул, безопаснее использовать именно Text.
Кроме того, в коде автора была допущена опечатка в вызове метода завершения работы приложения Excel: Qiut вместо Quit. Из-за этого процесс Excel оставался открытым в памяти компьютера.
В исходном примере кода вся процедура загрузки выполняется в директиве &НаКлиенте. Это серьезная архитектурная ошибка. Выполнение тяжелых операций чтения файлов и заполнения таблиц на стороне клиента приводит к зависанию интерфейса программы, повышенной нагрузке на рабочий компьютер пользователя и полной неработоспособности кода в веб-клиенте.
Посмотрим на правильную схему реализации импорта с использованием клиент-серверного взаимодействия:
Разберем по шагам, как это выглядит в программном коде управляемого приложения 1С:
&НаКлиенте
Процедура ЗагрузитьФайл(Команда)
Если ПустаяСтрока(ПутьКФайлу) Тогда
ПоказатьПредупреждение(, "Пожалуйста, выберите файл для загрузки!");
Возврат;
КонецЕсли;
// Описываем обработчик завершения помещения файла во временное хранилище
ОписаниеОповещения = Новый ОписаниеОповещения("ИмпортФайлаЗавершен", ЭтотОбъект);
// Помещаем файл во временное хранилище
НачатьПомещениеФайлаНаСервер(ОписаниеОповещения, , , , ПутьКФайлу, УникальныйИдентификатор);
КонецПроцедуры
&НаКлиенте
Процедура ИмпортФайлаЗавершен(Результат, АдресХранилища, ВыбранноеИмяФайла, ДополнительныеПараметры) Экспорт
Если Результат Тогда
// Передаем управление серверному методу для разбора данных
ЗагрузитьДанныеИзФайлаНаСервере(АдресХранилища);
ПоказатьОповещениеПользователя("Успешно", , "Данные успешно загружены!");
КонецЕсли;
КонецПроцедуры
При работе с технологией OLE/COM крайне важно гарантировать закрытие приложения Excel в операционной системе. Если во время чтения строк возникнет ошибка (например, неверный формат данных в ячейке), управление передается в блок обработки ошибок, процедура завершается, а процесс EXCEL.EXE остается «висеть» в памяти. Со временем это может привести к перерасходу оперативной памяти сервера.
Проанализируем ситуацию и напишем отказоустойчивую процедуру чтения на сервере с гарантированным уничтожением COM-объектов:
&НаСервере
Процедура ЗагрузитьДанныеИзФайлаНаСервере(АдресХранилища)
// Получаем двоичные данные из временного хранилища
ДвоичныеДанныеФайла = ПолучитьИзВременногоХранилища(АдресХранилища);
// Создаем временный файл на сервере
ИмяВременногоФайла = ПолучитьИмяВременногоФайла("xlsx");
ДвоичныеДанныеФайла.Записать(ИмяВременногоФайла);
Эксель = Неопределено;
Книга = Неопределено;
Попытка
// Инициализируем COM-соединение
Эксель = Новый COMОбъект("Excel.Application");
Эксель.DisplayAlerts = Ложь; // Отключаем предупреждения Excel
Книга = Эксель.WorkBooks.Open(ИмяВременногоФайла);
Лист = Книга.Worksheets(1);
НомерСтроки = 4;
Пока Истина Цикл
НоменклатураТекст = СокрЛП(Лист.Cells(НомерСтроки, 1).Text);
Если НоменклатураТекст = "" Тогда
Прервать;
КонецЕсли;
КурсЧисло = Лист.Cells(НомерСтроки, 2).Value;
НоваяСтрока = Объект.ТабличнаяЧасть1.Добавить();
НоваяСтрока.НоменклатураТекст = НоменклатураТекст;
НоваяСтрока.Курс = ?(ТипЗнч(КурсЧисло) = Тип("Число"), КурсЧисло, 0);
НомерСтроки = НомерСтроки + 1;
КонецЦикла;
// Корректно закрываем книгу и приложение
Книга.Close(Ложь);
Эксель.Quit();
Исключение
// В случае падения обязательно закрываем процессы в памяти
Если Книга <> Неопределено Тогда
Попытка Книга.Close(Ложь); Исключение КонецПопытки;
КонецЕсли;
Если Эксель <> Неопределено Тогда
Попытка Эксель.Quit(); Исключение КонецПопытки;
КонецЕсли;
// Удаляем временный файл
УдалитьФайлы(ИмяВременногоФайла);
// Передаем ошибку выше для отображения пользователю
ВызватьИсключение "Ошибка при разборе файла Excel: " + ОписаниеОошибки();
КонецПопытки;
УдалитьФайлы(ИмяВременногоФайла);
КонецПроцедуры
В сообщениях 5 и 6 участники обсуждения справедливо заметили, что в файле Excel хранится текстовое наименование номенклатуры, тогда как в табличной части 1С реквизит Номенклатура обычно имеет тип ссылки (СправочникСсылка.Номенклатура).
Рассмотрим частую ошибку оптимизации — поиск элементов справочника в цикле загрузки ячеек. Если мы будем выполнять поиск методом НайтиПоНаименованию() на каждую строку Excel, система будет делать тысячи запросов к базе данных, что катастрофически замедлит импорт.
Для оптимизации сопоставления применим метод пакетной обработки данных с использованием объекта Соответствие и запроса к базе данных. Разберем этот алгоритм по шагам:
Посмотрим, как выглядит реализация этого подхода на встроенном языке 1С:
&НаСервере
Процедура ЗагрузитьССопоставлениемНаСервере(АдресХранилища)
ДвоичныеДанныеФайла = ПолучитьИзВременногоХранилища(АдресХранилища);
ИмяВременногоФайла = ПолучитьИмяВременногоФайла("xlsx");
ДвоичныеДанныеФайла.Записать(ИмяВременногоФайла);
// 1. Читаем файл и собираем уникальные наименования
МассивИмен = Новый Массив;
ТаблицаДанныхФайла = Новый ТаблицаЗначений;
ТаблицаДанныхФайла.Колонки.Добавить("Наименование", Новый ОписаниеТипов("Строка"));
ТаблицаДанныхФайла.Колонки.Добавить("Курс", Новый ОписаниеТипов("Число"));
Эксель = Новый COMОбъект("Excel.Application");
Книга = Эксель.WorkBooks.Open(ИмяВременногоФайла);
Лист = Книга.Worksheets(1);
НомерСтроки = 4;
Пока Истина Цикл
Наименование = СокрЛП(Лист.Cells(НомерСтроки, 1).Text);
Если Наименование = "" Тогда
Прервать;
КонецЕсли;
Курс = Лист.Cells(НомерСтроки, 2).Value;
НоваяСтрокаТЗ = ТаблицаДанныхФайла.Добавить();
НоваяСтрокаТЗ.Наименование = Наименование;
НоваяСтрокаТЗ.Курс = ?(ТипЗнч(Курс) = Тип("Число"), Курс, 0);
Если МассивИмен.Найти(Наименование) = Неопределено Тогда
МассивИмен.Добавить(Наименование);
КонецЕсли;
НомерСтроки = НомерСтроки + 1;
КонецЦикла;
Книга.Close(Ложь);
Эксель.Quit();
УдалитьФайлы(ИмяВременногоФайла);
// 2. Делаем ОДИН запрос к базе для сопоставления номенклатуры
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Ссылка КАК Ссылка,
| Номенклатура.Наименование КАК Наименование
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.Наименование В(&МассивИмен)";
Запрос.УстановитьПараметр("МассивИмен", МассивИмен);
РезультатЗапроса = Запрос.Выполнить();
// 3. Заполняем соответствие для быстрого поиска в оперативной памяти
СоответствиеНоменклатуры = Новый Соответствие;
Выборка = РезультатЗапроса.Выбрать();
Пока Выборка.Следующий() Цикл
СоответствиеНоменклатуры.Вставить(Выборка.Наименование, Выборка.Ссылка);
КонецЦикла;
// 4. Заполняем табличную часть обработки из Таблицы Значений
Объект.ТабличнаяЧасть1.Очистить();
Для Каждого СтрокаФайла Из ТаблицаДанныхФайла Цикл
НоваяСтрока = Объект.ТабличнаяЧасть1.Добавить();
НоваяСтрока.Курс1 = СтрокаФайла.Курс;
// Ищем ссылку в созданном соответствии
НайденнаяСсылка = СоответствиеНоменклатуры.Получить(СтрокаФайла.Наименование);
Если НайденнаяСсылка <> Неопределено Тогда
НоваяСтрока.Номенклатура1 = НайденнаяСсылка;
Иначе
// Оставляем пустую ссылку или выполняем логику создания нового элемента
// НоваяСтрока.Номенклатура1 = Справочники.Номенклатура.ПустаяСсылка();
КонецЕсли;
КонецЦикла;
КонецПроцедуры
Такой подход сокращает время выполнения импорта в десятки раз, так как база данных опрашивается всего один раз независимо от количества строк в файле загрузки.
В сообщении 12 предложен отличный современный вариант чтения файлов формата Excel. Использование технологии COM ограничено операционной системой Windows и требует обязательной установки программы MS Excel на сервере или клиентской машине. Если сервер 1С работает под управлением операционной системы Linux, использование COM-соединений становится технически невозможным.
Начиная со средних релизов платформы 1С v8.3, реализована полноценная нативная поддержка чтения файлов форматов XLS, XLSX и ODS без использования внешних библиотек и офисных пакетов. Для этого применяется стандартный объект ТабличныйДокумент.
Рассмотрим, как прочитать файл Excel с помощью встроенных средств платформы:
&НаСервере
Процедура ЧтениеЧерезТабличныйДокументНаСервере(АдресХранилища)
ДвоичныеДанныеФайла = ПолучитьИзВременногоХранилища(АдресХранилища);
ИмяВременногоФайла = ПолучитьИмяВременногоФайла("xlsx");
ДвоичныеДанныеФайла.Записать(ИмяВременногоФайла);
// Создаем объект ТабличныйДокумент и считываем в него файл напрямую
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ИмяВременногоФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
УдалитьФайлы(ИмяВременногоФайла);
ВызватьИсключение "Не удалось прочитать файл! Ошибка: " + ОписаниеОшибки();
КонецПопытки;
УдалитьФайлы(ИмяВременногоФайла);
// Определяем границы заполненной области табличного документа
ВысотаТаблицы = ТабДок.ВысотаТаблицы;
ШиринаТаблицы = ТабДок.ШиринаТаблицы;
// Очищаем табличную часть перед импортом
Объект.ТабличнаяЧасть1.Очистить();
// Читаем строки, начиная со строки данных (например, со 4-й строки)
Для НомерСтроки = 4 По ВысотаТаблицы Цикл
// Получаем текстовое значение первой колонки для проверки
НоменклатураТекст = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, 1).ТекущийРисунок.Имя); // или через Область
НоменклатураТекст = СокрЛП(ТабДок.Область(НомерСтроки, 1).Текст);
Если НоменклатураТекст = "" Тогда
Продолжить; // Или Прервать, если пустая ячейка означает конец таблицы
КонецЕсли;
НоваяСтрока = Объект.ТабличнаяЧасть1.Добавить();
// Получаем значение ячеек напрямую с автоматическим приведением типов
НоваяСтрока.Номенклатура1 = НоменклатураТекст;
ЗначениеКурса = ТабДок.Область(НомерСтроки, 2).СодержитЗначение;
Если ЗначениеКурса Тогда
НоваяСтрока.Курс1 = ТабДок.Область(НомерСтроки, 2).Значение;
Иначе
НоваяСтрока.Курс1 = ВычислитьЧислоИзСтроки(ТабДок.Область(НомерСтроки, 2).Текст);
КонецЕсли;
КонецЦикла;
КонецПроцедуры
Параметр метода чтения СпособЧтенияЗначенийТабличногоДокумента.Значение позволяет считывать числовые данные и даты именно как числа и даты 1С, избегая проблем со сложными разделителями разрядов и форматами дат, принятыми в конкретной операционной системе.
Использование ТабличныйДокумент дает разработчику три важнейших преимущества: