При автоматизации загрузки данных из электронных таблиц Excel часто возникает ситуация, когда при простом считывании ячеек числовые данные загружаются корректно, а поля ссылочного типа (например, Номенклатура или Склад) остаются пустыми. Это происходит потому, что из Excel считывается текстовое наименование, а реквизит 1С ожидает ссылку на объект справочника.
Рассмотрим подробно, как правильно организовать обход ячеек Excel, сопоставить строки документа со ссылочными реквизитами и какие существуют альтернативные способы решения этой задачи.
Проанализируем классический подход, использующий COM-подключение к MS Excel. Для правильной загрузки необходимо выполнять сопоставление ячеек файловой таблицы с реквизитами строки документа 1С.
Разберем основные шаги реализации:
COMОбъект("Excel.Application") и выбираем нужный лист книги.НайтиПоНаименованию().Рассмотрим пример кода процедур клиент-серверного взаимодействия:
&НаКлиенте
Процедура Загрузить(Команда)
Если Не ЗначениеЗаполнено(Объект.ИмяФайла) Тогда
ВызватьИсключение "Укажите файл для загрузки!";
КонецЕсли;
ЗагрузитьДанныеНаСервере(Объект.ИмяФайла);
КонецПроцедуры
&НаСервере
Процедура ЗагрузитьДанныеНаСервере(ИмяФайла)
Попытка
Excel = Новый COMОбъект("Excel.Application");
Исключение
Сообщить("Не удалось подключить COMОбъект Excel.Application.");
Возврат;
КонецПопытки;
Excel.Workbooks.Open(ИмяФайла);
Excel.Sheets(1).Select();
// Определяем количество строк в документе Excel
ФайлСтрок = Excel.Cells(1, 1).SpecialCells(11).Row;
ДокументОбъект = Документы.ВводНачальныхОстатков.СоздатьДокумент();
ДокументОбъект.Дата = ТекущаяДата();
// Проходим по строкам (например, со 2-й строки, если 1-я — заголовок)
Для НомерСтроки = 2 По ФайлСтрок Цикл
ТекстНоменклатуры = СокрЛП(Excel.Cells(НомерСтроки, 1).Value);
ТекстСклада = СокрЛП(Excel.Cells(НомерСтроки, 2).Value);
ЗначениеКоличество = Excel.Cells(НомерСтроки, 3).Value;
// Поиск элементов в справочниках по наименованию
СсылкаНоменклатура = Справочники.Номенклатура.НайтиПоНаименованию(ТекстНоменклатуры);
СсылкаСклад = Справочники.Склады.НайтиПоНаименованию(ТекстСклада);
Если Не ЗначениеЗаполнено(СсылкаНоменклатура) Тогда
Сообщить("Строка " + НомерСтроки + ": Номенклатура """ + ТекстНоменклатуры + """ не найдена!");
Продолжить;
КонецЕсли;
НоваяСтрока = ДокументОбъект.Товары.Добавить();
НоваяСтрока.Номенклатура = СсылкаНоменклатура;
НоваяСтрока.Склад = СсылкаСклад;
НоваяСтрока.Количество = Число(ЗначениеКоличество);
КонецЦикла;
ДокументОбъект.Записать(РежимЗаписиДокумента.Запись);
// Обязательно закрываем файл и завершаем процесс Excel
Excel.ActiveWorkbook.Close(Ложь);
Excel.Quit();
Excel = Неопределено;
КонецПроцедуры
Работа через COM имеет ряд недостатков: требует установленного MS Office, зависит от разрядности платформы и не работает на серверах под управлением Linux. Современные релизы платформы 1С позволяют прочитать файл Excel непосредственно в объект ТабличныйДокумент без использования сторонних программ.
Выясним, как реализовать этот способ по шагам:
ТабличныйДокумент.Прочитать().
&НаКлиенте
Процедура ЗагрузитьИзФайла(Команда)
Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Диалог.Фильтр = "Файлы Excel (*.xlsx;*.xls)|*.xlsx;*.xls";
Если Диалог.Выбрать() Тогда
АдресХранилища = "";
ПоместитьФайл(АдресХранилища, Диалог.ПолноеИмяФайла, , Ложь, ЭтаФорма.УникальныйИдентификатор);
ЗагрузитьИзТабДокаНаСервере(АдресХранилища);
КонецЕсли;
КонецПроцедуры
&НаСервере
Процедура ЗагрузитьИзТабДокаНаСервере(АдресХранилища)
ДвоичныеДанные = ПолучитьИзВременногоХранилища(АдресХранилища);
ИмяФайла = ПолучитьИмяВременногоФайла("xlsx");
ДвоичныеДанные.Записать(ИмяФайла);
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ИмяФайла); // Загружаем лист прямо в ТабличныйДокумент
УдалитьФайлы(ИмяФайла);
ВысотаТаблицы = ТабДок.ВысотаТаблицы;
ДокументОбъект = Документы.ВводНачальныхОстатков.СоздатьДокумент();
ДокументОбъект.Дата = ТекущаяДата();
Для НомерСтроки = 2 По ВысотаТаблицы Цикл
ТекстНоменклатуры = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, 1).ТекущийОбъект.Текст);
ТекстСклада = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, 2).ТекущийОбъект.Текст);
ЗначениеКоличество = ТабДок.ПолучитьОбласть(НомерСтроки, 3).Значение;
СсылкаНоменклатура = Справочники.Номенклатура.НайтиПоНаименованию(ТекстНоменклатуры);
СсылкаСклад = Справочники.Склады.НайтиПоНаименованию(ТекстСклада);
Если ЗначениеЗаполнено(СсылкаНоменклатура) Тогда
СтрокаТЧ = ДокументОбъект.Товары.Добавить();
СтрокаТЧ.Номенклатура = СсылкаНоменклатура;
СтрокаТЧ.Склад = СсылкаСклад;
СтрокаТЧ.Количество = Число(ЗначениеКоличество);
КонецЕсли;
КонецЦикла;
ДокументОбъект.Записать(РежимЗаписиДокумента.Запись);
КонецПроцедуры
Excel.Application обязательно вызывайте метод Excel.Quit() и обнуляйте переменную, иначе процесс excel.exe останется висеть в памяти сервера.СокрЛП() для усечения невидимых пробелов в начале и конце строк. При выгрузках из внешних систем также рекомендуется заменять неразрывные пробелы: СтрЗаменить(Строка, Символы.НПП, " ").НайтиПоНаименованию() возвращает первое совпадение. По возможности производите поиск по уникальным атрибутам (Артикул, Код или Штрихкод).