Загрузка из Excel в 1С нужна почти в каждой компании: поставщик прислал прайс, менеджер собрал заказ в таблице, при переходе с другой программы надо перенести список товаров. В 1С:Предприятии 8.3 для этого есть штатные инструменты, а когда их не хватает, пишут небольшую обработку. Ниже — что можно сделать без программиста и как устроен собственный код загрузки: чтение файла, сопоставление номенклатуры по артикулу, заполнение табличной части документа.
В типовых конфигурациях на Библиотеке стандартных подсистем (БСП) — Бухгалтерии 3.0, УТ 11, КА 2, ERP — есть механизм «Загрузка данных из файла». Команда «Загрузить» или «Загрузить из файла» находится в списке справочника (например, номенклатуры) или над табличной частью документа, иногда в меню «Ещё»; название и место зависят от конфигурации.
Работает механизм так:
Для разовой загрузки номенклатуры из Excel в 1С этого обычно хватает; так же загружают цены и строки накладной. Для объектов без штатной команды администраторы берут универсальную обработку «Загрузка данных из табличного документа» с диска ИТС. Перед любой массовой загрузкой сделайте резервную копию базы.
| Способ | Подходит, когда | Ограничения |
|---|---|---|
| Штатная загрузка из файла (БСП) | разово загрузить товары, цены, строки документа | фиксированный набор полей, сопоставление каждый раз проверяется вручную |
| Универсальная обработка с ИТС | нужно заполнить объект, для которого штатной команды нет | пишет прямо в базу: ошибка в настройке портит данные |
| Своя обработка | файлы приходят регулярно в одном формате, нужны проверки и особые правила поиска | нужен программист; при смене формата обработку дорабатывают |
| Обмен без Excel | данные регулярно идут с сайта или маркетплейса | требует настройки интеграции |
Если таблицы каждую неделю выгружают с вашего сайта или из личного кабинета маркетплейса, Excel в этой цепочке лишний: надёжнее настроить обмен 1С с сайтом или интеграцию с маркетплейсами.
Своя обработка для загрузки данных из Excel в 1С нужна, когда формат файла постоянный, а правила сложнее штатных. В управляемом приложении схема такая:
ТабличныйДокумент.Ключевой метод — ТабличныйДокумент.Прочитать(). С платформы 8.3.6 он читает не только собственный формат mxl, но и XLS, XLSX и ODS, причём Microsoft Excel для этого не нужен. Второй параметр появился в той же версии — это значение перечисления СпособЧтенияЗначенийТабличногоДокумента: при варианте Текст всё читается строками, при варианте Значение числа и даты приходят типизированными, и «1 234,50» не приходится разбирать вручную.
Пример — для модуля формы документа с табличной частью «Товары» (колонки Номенклатура, Количество, Цена, Сумма) и справочника «Номенклатура» с реквизитом «Артикул». Добавьте на форму команду ЗагрузитьИзExcel и поправьте имена под свою конфигурацию.
// Модуль формы документа с табличной частью «Товары»
// (колонки Номенклатура, Количество, Цена, Сумма).
// Платформа 1С:Предприятие 8.3.15 и выше, управляемая форма.
// Режим совместимости конфигурации — не ниже 8.3.15 (или «Не использовать»).
// В первой строке файла — заголовки: Артикул, Наименование, Количество, Цена.
// Имена справочника, реквизитов и табличной части замените на свои.
&НаКлиенте
Процедура ЗагрузитьИзExcel(Команда)
ПараметрыДиалога = Новый ПараметрыДиалогаПомещенияФайлов;
ПараметрыДиалога.Заголовок = НСтр("ru = 'Выберите файл с товарами'");
ПараметрыДиалога.Фильтр = НСтр("ru = 'Таблицы Excel и OpenDocument'")
+ " (*.xlsx;*.xls;*.ods)|*.xlsx;*.xls;*.ods";
Оповещение = Новый ОписаниеОповещения("ЗагрузитьИзExcelПослеПомещенияФайла", ЭтотОбъект);
НачатьПомещениеФайлаНаСервер(Оповещение, , , , ПараметрыДиалога, УникальныйИдентификатор);
КонецПроцедуры
&НаКлиенте
Процедура ЗагрузитьИзExcelПослеПомещенияФайла(ОписаниеФайла, ДополнительныеПараметры) Экспорт
Если ОписаниеФайла = Неопределено Или ОписаниеФайла.ПомещениеФайлаОтменено Тогда
Возврат;
КонецЕсли;
Загружено = ЗагрузитьИзExcelНаСервере(ОписаниеФайла.Адрес, ОписаниеФайла.СсылкаНаФайл.Имя);
Если Загружено > 0 Тогда
Модифицированность = Истина;
КонецЕсли;
ПоказатьОповещениеПользователя(НСтр("ru = 'Загрузка из Excel'"), ,
СтрШаблон(НСтр("ru = 'Добавлено строк: %1'"), Загружено));
КонецПроцедуры
&НаСервере
Функция ЗагрузитьИзExcelНаСервере(АдресФайла, ИмяФайла)
Расширение = НРег(Сред(Новый Файл(ИмяФайла).Расширение, 2));
Если Расширение <> "xlsx" И Расширение <> "xls" И Расширение <> "ods" Тогда
УдалитьИзВременногоХранилища(АдресФайла);
ВызватьИсключение НСтр("ru = 'Выберите файл в формате xlsx, xls или ods.'");
КонецЕсли;
ТабДок = ПрочитатьТабличныйДокумент(АдресФайла, Расширение);
// Номера колонок ищем по заголовкам, а не по буквам A, B, C
Колонки = КолонкиПоЗаголовкам(ТабДок);
КолАртикул = Колонки["АРТИКУЛ"];
КолНаименование = Колонки["НАИМЕНОВАНИЕ"];
КолКоличество = Колонки["КОЛИЧЕСТВО"];
КолЦена = Колонки["ЦЕНА"];
Если КолКоличество = Неопределено Или КолЦена = Неопределено
Или (КолАртикул = Неопределено И КолНаименование = Неопределено) Тогда
ВызватьИсключение НСтр("ru = 'В первой строке файла нужны заголовки Количество, Цена и хотя бы один из двух: Артикул или Наименование.'");
КонецЕсли;
// 1. Переносим строки файла в таблицу значений
СтрокиФайла = Новый ТаблицаЗначений;
СтрокиФайла.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число"));
СтрокиФайла.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка"));
СтрокиФайла.Колонки.Добавить("Наименование", Новый ОписаниеТипов("Строка"));
СтрокиФайла.Колонки.Добавить("Количество");
СтрокиФайла.Колонки.Добавить("Цена");
Для НомерСтроки = 2 По ТабДок.ВысотаТаблицы Цикл
Артикул = ТекстЯчейки(ТабДок, НомерСтроки, КолАртикул);
Наименование = ТекстЯчейки(ТабДок, НомерСтроки, КолНаименование);
Если Артикул = "" И Наименование = "" Тогда
Продолжить; // пустая строка
КонецЕсли;
// Строка «Итого:» или «Всего:» внизу таблицы. Сравниваем текст целиком,
// иначе выпадет товар вроде «Итоговая ведомость (бланк)»
ТекстСтроки = ВРег(СтрЗаменить(СокрЛП(Артикул + " " + Наименование), ":", ""));
Если ТекстСтроки = "ИТОГО" Или ТекстСтроки = "ВСЕГО" Тогда
Продолжить;
КонецЕсли;
СтрокаФайла = СтрокиФайла.Добавить();
СтрокаФайла.НомерСтроки = НомерСтроки;
СтрокаФайла.Артикул = Артикул;
СтрокаФайла.Наименование = Наименование;
СтрокаФайла.Количество = ЧислоЯчейки(ТабДок, НомерСтроки, КолКоличество);
СтрокаФайла.Цена = ЧислоЯчейки(ТабДок, НомерСтроки, КолЦена);
КонецЦикла;
// 2. Сопоставляем номенклатуру одним пакетным запросом
Сопоставление = СопоставитьНоменклатуру(СтрокиФайла);
// 3. Заполняем табличную часть и копим ошибки
Ошибки = Новый Массив;
Загружено = 0;
Для Каждого СтрокаФайла Из СтрокиФайла Цикл
Номенклатура = Неопределено;
Если СтрокаФайла.Артикул <> "" Тогда
Номенклатура = Сопоставление.ПоАртикулу[ВРег(СтрокаФайла.Артикул)];
ИначеЕсли СтрокаФайла.Наименование <> "" Тогда
// По наименованию ищем только строки без артикула:
// неизвестный артикул — повод для сообщения, а не для подбора по имени
Номенклатура = Сопоставление.ПоНаименованию[ВРег(СтрокаФайла.Наименование)];
КонецЕсли;
ТоварВФайле = СокрЛП(СтрокаФайла.Артикул + " " + СтрокаФайла.Наименование);
Если Номенклатура = Неопределено Тогда
Ошибки.Добавить(СтрШаблон(НСтр("ru = 'Строка %1: не найден товар «%2»'"),
СтрокаФайла.НомерСтроки, ТоварВФайле));
Продолжить;
ИначеЕсли Номенклатура = Null Тогда
Ошибки.Добавить(СтрШаблон(НСтр("ru = 'Строка %1: товару «%2» соответствует несколько элементов справочника'"),
СтрокаФайла.НомерСтроки, ТоварВФайле));
Продолжить;
ИначеЕсли СтрокаФайла.Количество = Неопределено Или СтрокаФайла.Цена = Неопределено Тогда
Ошибки.Добавить(СтрШаблон(НСтр("ru = 'Строка %1: количество или цена не распознаны как число'"),
СтрокаФайла.НомерСтроки));
Продолжить;
ИначеЕсли СтрокаФайла.Количество <= 0 Тогда
Ошибки.Добавить(СтрШаблон(НСтр("ru = 'Строка %1: количество не указано или отрицательное'"),
СтрокаФайла.НомерСтроки));
Продолжить;
ИначеЕсли СтрокаФайла.Цена <= 0 Тогда
// Если нулевая цена у вас допустима, замените ошибку предупреждением
Ошибки.Добавить(СтрШаблон(НСтр("ru = 'Строка %1: цена не указана или отрицательная'"),
СтрокаФайла.НомерСтроки));
Продолжить;
КонецЕсли;
НоваяСтрока = Объект.Товары.Добавить();
НоваяСтрока.Номенклатура = Номенклатура;
НоваяСтрока.Количество = СтрокаФайла.Количество;
НоваяСтрока.Цена = СтрокаФайла.Цена;
НоваяСтрока.Сумма = НоваяСтрока.Количество * НоваяСтрока.Цена;
// В типовой конфигурации здесь вызовите её штатный пересчёт строки (НДС, упаковки и т.п.)
Загружено = Загружено + 1;
КонецЦикла;
Для Каждого ТекстОшибки Из Ошибки Цикл
Сообщение = Новый СообщениеПользователю;
Сообщение.Текст = ТекстОшибки;
Сообщение.Сообщить();
КонецЦикла;
Возврат Загружено;
КонецФункции
&НаСервереБезКонтекста
Функция ПрочитатьТабличныйДокумент(АдресФайла, Расширение)
ДвоичныеДанные = ПолучитьИзВременногоХранилища(АдресФайла);
УдалитьИзВременногоХранилища(АдресФайла);
Если ТипЗнч(ДвоичныеДанные) <> Тип("ДвоичныеДанные") Тогда
ВызватьИсключение НСтр("ru = 'Файл не найден во временном хранилище. Повторите загрузку.'");
КонецЕсли;
// Прочитать() определяет формат по расширению, поэтому сохраняем его
ИмяВременногоФайла = ПолучитьИмяВременногоФайла(Расширение);
ДвоичныеДанные.Записать(ИмяВременногоФайла);
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ИмяВременногоФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
ТекстОшибки = КраткоеПредставлениеОшибки(ИнформацияОбОшибке());
УдалитьФайлы(ИмяВременногоФайла);
ВызватьИсключение СтрШаблон(НСтр("ru = 'Не удалось прочитать файл: %1'"), ТекстОшибки);
КонецПопытки;
УдалитьФайлы(ИмяВременногоФайла);
Возврат ТабДок;
КонецФункции
&НаСервереБезКонтекста
Функция КолонкиПоЗаголовкам(ТабДок)
// Ключ — заголовок в верхнем регистре, значение — номер колонки
Колонки = Новый Соответствие;
Для НомерКолонки = 1 По ТабДок.ШиринаТаблицы Цикл
Заголовок = ВРег(ТекстЯчейки(ТабДок, 1, НомерКолонки));
Если Заголовок <> "" И Колонки[Заголовок] = Неопределено Тогда
Колонки.Вставить(Заголовок, НомерКолонки);
КонецЕсли;
КонецЦикла;
Возврат Колонки;
КонецФункции
&НаСервереБезКонтекста
Функция СопоставитьНоменклатуру(СтрокиФайла)
Артикулы = Новый Массив;
Наименования = Новый Массив;
Для Каждого СтрокаФайла Из СтрокиФайла Цикл
// Наименования собираем только у строк без артикула
Если СтрокаФайла.Артикул <> "" Тогда
Артикулы.Добавить(СтрокаФайла.Артикул);
ИначеЕсли СтрокаФайла.Наименование <> "" Тогда
Наименования.Добавить(СтрокаФайла.Наименование);
КонецЕсли;
КонецЦикла;
// Условие НЕ ЭтоГруппа уберите, если справочник без групп
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Ссылка КАК Ссылка,
| Номенклатура.Артикул КАК Ключ
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.Артикул В (&Артикулы)
| И НЕ Номенклатура.ЭтоГруппа
| И НЕ Номенклатура.ПометкаУдаления
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Номенклатура.Ссылка КАК Ссылка,
| Номенклатура.Наименование КАК Ключ
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.Наименование В (&Наименования)
| И НЕ Номенклатура.ЭтоГруппа
| И НЕ Номенклатура.ПометкаУдаления";
Запрос.УстановитьПараметр("Артикулы", Артикулы);
Запрос.УстановитьПараметр("Наименования", Наименования);
Результаты = Запрос.ВыполнитьПакет();
Возврат Новый Структура("ПоАртикулу, ПоНаименованию",
СоответствиеПоКлючу(Результаты[0]), СоответствиеПоКлючу(Результаты[1]));
КонецФункции
&НаСервереБезКонтекста
Функция СоответствиеПоКлючу(РезультатЗапроса)
// Если один ключ у нескольких элементов, кладём Null: такую строку не угадываем
Соответствие = Новый Соответствие;
Выборка = РезультатЗапроса.Выбрать();
Пока Выборка.Следующий() Цикл
Ключ = ВРег(СокрЛП(Выборка.Ключ));
Если Соответствие[Ключ] = Неопределено Тогда
Соответствие.Вставить(Ключ, Выборка.Ссылка);
Иначе
Соответствие.Вставить(Ключ, Null);
КонецЕсли;
КонецЦикла;
Возврат Соответствие;
КонецФункции
&НаСервереБезКонтекста
Функция ЗначениеЯчейки(ТабДок, НомерСтроки, НомерКолонки)
Если НомерКолонки = Неопределено Тогда
Возврат Неопределено;
КонецЕсли;
Область = ТабДок.Область(НомерСтроки, НомерКолонки, НомерСтроки, НомерКолонки);
Если Область.СодержитЗначение Тогда
Возврат Область.Значение;
КонецЕсли;
Возврат Область.Текст;
КонецФункции
&НаСервереБезКонтекста
Функция ТекстЯчейки(ТабДок, НомерСтроки, НомерКолонки)
Значение = ЗначениеЯчейки(ТабДок, НомерСтроки, НомерКолонки);
Если ТипЗнч(Значение) = Тип("Число") Тогда
Возврат Формат(Значение, "ЧГ=0"); // без пробелов между разрядами
КонецЕсли;
Возврат СокрЛП(СтрЗаменить(Строка(Значение), Символы.НПП, " "));
КонецФункции
&НаСервереБезКонтекста
Функция ЧислоЯчейки(ТабДок, НомерСтроки, НомерКолонки)
Значение = ЗначениеЯчейки(ТабДок, НомерСтроки, НомерКолонки);
Если ТипЗнч(Значение) = Тип("Число") Тогда
Возврат Значение;
КонецЕсли;
// Текст вида «1 234,50»: убираем пробелы, запятую меняем на точку
Текст = СтрЗаменить(Строка(Значение), Символы.НПП, "");
Текст = СтрЗаменить(СтрЗаменить(Текст, " ", ""), ",", ".");
Если Текст = "" Тогда
Возврат 0;
КонецЕсли;
Попытка
Возврат Число(Текст);
Исключение
Возврат Неопределено; // строку отметим как ошибочную
КонецПопытки;
КонецФункцииНачатьПомещениеФайлаНаСервер показывает диалог и сразу кладёт файл во временное хранилище, привязанное к УникальныйИдентификатор формы. Метод асинхронный, работает в веб-клиенте без модальных окон; в обработчик приходят адрес в хранилище и ссылка на файл с его именем.Прочитать() получает имя файла и определяет формат по расширению, поэтому временный файл создаётся через ПолучитьИмяВременногоФайла(Расширение). После чтения файл удаляется с диска, а данные — из хранилища.КолонкиПоЗаголовкам строит соответствие «заголовок → номер колонки», поэтому перестановка колонок загрузку не ломает. Если нет колонки количества, цены или обеих колонок для поиска товара, пользователь сразу получит сообщение.Значение у области ячейки установлено свойство СодержитЗначение, а само значение лежит в свойстве Значение. Числовой артикул переводим в строку через Формат(…, "ЧГ=0"): Строка() вставила бы неразрывный пробел между разрядами, и «125000» стал бы «125 000».На платформе 8.3.6–8.3.14 или при таком режиме совместимости конфигурации файл передают методом НачатьПомещениеФайла. Серверная часть не меняется:
// Клиентская часть для платформы (или режима совместимости) 8.3.6–8.3.14
&НаКлиенте
Процедура ЗагрузитьИзExcel(Команда)
Оповещение = Новый ОписаниеОповещения("ЗагрузитьИзExcelЗавершение", ЭтотОбъект);
НачатьПомещениеФайла(Оповещение, , , Истина, УникальныйИдентификатор);
КонецПроцедуры
&НаКлиенте
Процедура ЗагрузитьИзExcelЗавершение(Результат, Адрес, ВыбранноеИмяФайла, ДополнительныеПараметры) Экспорт
Если Не Результат Тогда
Возврат; // пользователь закрыл диалог
КонецЕсли;
Загружено = ЗагрузитьИзExcelНаСервере(Адрес, ВыбранноеИмяФайла);
Если Загружено > 0 Тогда
Модифицированность = Истина;
КонецЕсли;
КонецПроцедурыВ 8.3.18 и новее (режим совместимости тоже не ниже 8.3.18) вместо обработчиков оповещения можно использовать асинхронную функцию ПоместитьФайлНаСерверАсинх вместе с Ждать — логика та же.
Прайс-лист загружается по той же схеме, но писать цены напрямую в регистр сведений не стоит. Для своих цен в типовых есть документ «Установка цен номенклатуры», для закупочных в УТ 11, КА 2 и ERP — «Регистрация цен поставщика». Создайте документ программно, заполните и проведите — останется история изменений, а ошибку можно будет отменить.
Загрузка документов из Excel, когда в одном файле несколько накладных, отличается группировкой: строки собираются по номеру документа или контрагенту, и на каждую группу создаётся свой документ. Записывайте и проводите их порциями, а не одной транзакцией на весь файл, иначе остальные пользователи будут ждать на блокировках.
В старых примерах файл открывают через Новый COMОбъект("Excel.Application"). На сервере это плохая идея:
Без Excel такой код падает с ошибкой «Ошибка при вызове конструктора (COMОбъект)» и пояснением «Недопустимая строка с указанием класса». У ТабличныйДокумент.Прочитать() этих проблем нет, так что COM оправдан разве что на платформе ниже 8.3.6.
&НаКлиенте. Всё, что работает с базой, должно выполняться на сервере.ПоместитьФайл или ДиалогВыбораФайла.Выбрать() в конфигурации с отключённой модальностью. Решение — асинхронные методы из примера.ЧислоЯчейки перехватывает это исключение и помечает строку как ошибочную.Неопределено. Данных по адресу уже нет: файл помещён без УникальныйИдентификатор формы (такие данные платформа удаляет после ближайшего серверного вызова), форма-владелец закрыта или адрес читают повторно после УдалитьИзВременногоХранилища. Помещайте файл с идентификатором формы и забирайте данные один раз.КодСимвола().ДлительныеОперации.ВыполнитьВФоне) и записывайте объекты порциями. Данных формы в фоне нет: разбор переносят в общий модуль, а результат возвращают через временное хранилище.Нет, с 8.3.6 платформа сама читает xlsx, xls и ods. Excel нужен только загрузкам через COM, и это повод их переписать.
Штатная загрузка из файла умеет создавать элементы для несопоставленных строк. В своей обработке это делается через Справочники.Номенклатура.СоздатьЭлемент(), но перед записью нужно заполнить обязательные реквизиты — вид номенклатуры, единицу измерения, ставку НДС. Их набор зависит от конфигурации.
Можно, но это другой путь: CSV читают через ЧтениеТекста и делят строки на поля. СтрРазделить подходит, только если в значениях нет кавычек, «;» и переносов строк, иначе нужен разбор с учётом кавычек. Следите за кодировкой: обычный CSV из Excel сохраняется в Windows-1251, вариант «CSV UTF-8» — в UTF-8. Разделитель при русских региональных настройках — точка с запятой.
Регламентное задание берёт файл из сетевой папки, доступной учётной записи сервера 1С, или скачивает его HTTP-запросом. Функции разбора (КолонкиПоЗаголовкам, СопоставитьНоменклатуру и вспомогательные) переносят из модуля формы в общий модуль. Файл читают сразу по пути через ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение), а документ заполняют как объект и записывают методом Записать().
Сопоставлять по наименованию ненадёжно: у поставщика «Кабель ВВГнг 3х2,5», у вас «ВВГнг-LS 3*2.5». Правильнее один раз завести таблицу соответствий «код поставщика → наша номенклатура» (в ряде типовых конфигураций для этого есть справочник номенклатуры поставщиков) и искать по ней.
Если загрузка из Excel нужна регулярно, файлы приходят от разных поставщиков в разных форматах или данные надо разнести сразу по нескольким документам, подгонять универсальные инструменты дольше, чем сделать обработку под ваш файл. Её разрабатывают в рамках услуги «Отчёты и обработки 1С». Опишите задачу и приложите пример файла через форму «Разместить задание» — оценка бесплатная, ответ в течение рабочего дня.
Станьте частью сообщества!
Войдите или зарегистрируйтесь, и вы сможете участвовать в обсуждениях.