Импорт Excel в 1С: от файла до таблицы значений в коде

Самый короткий путь из Excel в таблицу значений — прочитать файл в ТабличныйДокумент методом Прочитать(), пройти по ячейкам и заполнить ТаблицаЗначений. Excel на компьютере или сервере для этого не нужен. COM-объект Excel.Application оставляйте на случай, когда формат файла табличный документ не берёт.

Дальше всё зависит от задачи. Нужно разово залить прайс в справочник — хватит готовой обработки загрузки. Нужен регулярный импорт с проверками и протоколом — пишите свою процедуру. Ниже оба варианта и то, что обычно ломается по дороге.

Что проверить в файле до загрузки

Девять проблем из десяти возникают не в коде, а в самом файле. Пять минут на подготовку экономят вечер отладки.

• 📄 Формат. Берите .xlsx или .xls. Файлы .xlsb и .xlsm табличный документ читает хуже, пересохраните их в .xlsx.
• 🧱 Шапка в одну строку. Объединённые ячейки в шапке и данных ломают привязку колонок. Уберите объединение, названия колонок пусть будут в первой строке.
• 🔢 Числа как числа. Если в ячейке «1 250,00» лежит текст (зелёный уголок в углу ячейки), в 1С придёт строка. Сложите колонку формулой СУММ — если она даёт 0, значит, это текст.
• 📅 Даты как даты. Строка «05.03.24» и настоящая дата Excel читаются по-разному. Проверьте формат ячеек.
• 🗂 Один лист — одна таблица. Итоги, подписи под таблицей и пустые строки между блоками удалите или вынесите на другой лист.
• 🔑 Ключ для сопоставления. Артикул, ИНН, штрихкод, код — то, по чему вы будете искать существующие элементы. Наименование как ключ ненадёжно: двойные пробелы и разный регистр дадут дубли.

Загружайте сначала на копии базы или на небольшой выборке из 10–20 строк. Если пишете документы, сначала оцените результат без проведения.

Готовая обработка: когда код писать не нужно

Если данные нужно один раз загрузить в справочник или табличную часть, ищите готовые инструменты. В конфигурациях на БСП в списке справочника есть команда загрузки данных из файла, обычно она живёт в меню Ещё над списком. В части конфигураций в документах с табличной частью есть кнопка подбора или загрузки из файла.

Кроме того, на диске ИТС и в распространённых сборках обработок давно ходит универсальная обработка «Загрузка данных из табличного документа». Принцип у неё один:

1. Открываете файл или вставляете данные в табличный документ обработки.
2. Выбираете, куда грузить: справочник, документ, регистр сведений .
3. Указываете, какая колонка Excel соответствует какому реквизиту.
4. Задаёте режим: создавать новые, обновлять найденные, пропускать существующие.
5. Запускаете загрузку и смотрите протокол.

Плюс готового инструмента — скорость. Минус — он ничего не знает про ваши правила: склеить артикул и бренд, пересчитать единицы, подставить склад по умолчанию. Как только такие правила появились, переходите к своему коду.

Чтение xlsx в таблицу значений через табличный документ

Это основной приём. Файл читается платформой без Excel, поэтому работает на сервере, в веб-клиенте и на машинах без офиса.

Сначала создаёте таблицу значений с нужными колонками и типами. Типы задавайте сразу: позже они пригодятся для запроса и приведения значений.

Функция ПрочитатьФайлВТаблицу(ПутьКФайлу) Экспорт

	ТаблицаДанных = Новый ТаблицаЗначений;
	ТаблицаДанных.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка"));
	ТаблицаДанных.Колонки.Добавить("Наименование", Новый ОписаниеТипов("Строка"));
	ТаблицаДанных.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число"));
	ТаблицаДанных.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число"));

	ТабДок = Новый ТабличныйДокумент;
	ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Текст);

	ТипЧисло = Новый ОписаниеТипов("Число");

	Для НомерСтроки = 2 По ТабДок.ВысотаТаблицы Цикл // первая строка — шапка

		Артикул = СокрЛП(ТабДок.Область(НомерСтроки, 1, НомерСтроки, 1).Текст);
		Если НЕ ЗначениеЗаполнено(Артикул) Тогда
			Продолжить; // пустую строку пропускаем
		КонецЕсли;

		ТекстКоличества = ТабДок.Область(НомерСтроки, 3, НомерСтроки, 3).Текст;
		ТекстКоличества = СтрЗаменить(ТекстКоличества, Символы.НПП, "");
		ТекстКоличества = СтрЗаменить(ТекстКоличества, " ", "");

		НоваяСтрока = ТаблицаДанных.Добавить();
		НоваяСтрока.Артикул = Артикул;
		НоваяСтрока.Наименование = СокрЛП(ТабДок.Область(НомерСтроки, 2, НомерСтроки, 2).Текст);
		НоваяСтрока.Количество = ТипЧисло.ПривестиЗначение(ТекстКоличества);
		НоваяСтрока.НомерСтроки = НомерСтроки;

	КонецЦикла;

	Возврат ТаблицаДанных;

КонецФункции

Что здесь важно:

• СпособЧтенияЗначенийТабличногоДокумента.Текст отдаёт содержимое так, как его видит пользователь. Для чисел и дат это бывает удобнее, но формат отображения придётся разбирать. Режим Значение возвращает типизированные значения: числа приходят числами, даты датами.
• Номер исходной строки сохраняйте в колонке. Он понадобится в протоколе ошибок: «строка 47: артикул не найден».
• ПривестиЗначение молча превращает мусор в 0. Если в колонке количества может оказаться текст, проверяйте значение отдельно и пишите ошибку в протокол.

Файл на клиенте для серверного чтения сначала нужно передать на сервер: через диалог выбора файла с помещением во временное хранилище, а на сервере сохранить из хранилища во временный файл. Читать путь C:\Users\... с сервера нельзя, сервер его просто не видит.

Чтение через COM-объект Excel

Если файл содержит что-то, что табличный документ не осилил, остаётся Excel через COM. Работает только в тонком клиенте и на сервере с установленным Excel под Windows.

Excel = Новый COMОбъект("Excel.Application");
Excel.DisplayAlerts = Ложь;
Книга = Excel.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Worksheets(1);
ПоследняяСтрока = Лист.UsedRange.Rows.Count;

Для НомерСтроки = 2 По ПоследняяСтрока Цикл
	Артикул = Лист.Cells(НомерСтроки, 1).Value;
	// ... заполнение таблицы значений
КонецЦикла;

Книга.Close(Ложь);
Excel.Quit();

Читать ячейки по одной через COM медленно: несколько тысяч строк — уже заметные минуты. И обязательно закрывайте Excel.Quit(), в том числе при ошибке, иначе в диспетчере задач копятся невидимые процессы EXCEL.EXE.

Как сопоставить колонки с реквизитами 1С

Прямая привязка «колонка 3 — количество» держится до первого поставщика, который поменяет порядок колонок. Устойчивее искать колонки по названию шапки или дать пользователю самому указать соответствие.

Рабочая схема выглядит так:

1. Читаете первую строку и записываете в соответствие «название колонки → номер».
2. Сравниваете названия без учёта регистра и лишних пробелов, допускаете синонимы («Кол-во», «Количество», «Qty»).
3. Если обязательной колонки нет, останавливаетесь до начала загрузки и сообщаете, чего не хватает.

Значения-ссылки (единица измерения, контрагент, склад) в файле приходят текстом. Превращать их в ссылки нужно одним из способов:

• Справочники.Контрагенты.НайтиПоРеквизиту("ИНН", Значение) — для поиска по реквизиту;
• НайтиПоКоду() и НайтиПоНаименованию(Значение, Истина) — по коду и точному наименованию;
• запрос с таблицей значений в качестве внешней таблицы — для больших файлов.

Для файла на тысячи строк вызывать поиск в цикле плохая идея: каждый вызов — отдельное обращение к базе. Передайте таблицу значений в запрос параметром и соедините с нужным справочником одним запросом:

Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
|	Данные.Артикул КАК Артикул,
|	Данные.НомерСтроки КАК НомерСтроки,
|	Номенклатура.Ссылка КАК Номенклатура
|ИЗ
|	&Данные КАК Данные
|		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура
|		ПО Данные.Артикул = Номенклатура.Артикул";
Запрос.УстановитьПараметр("Данные", ТаблицаДанных);
Результат = Запрос.Выполнить().Выгрузить();

Строки, где Номенклатура пустая, — кандидаты на создание или на ошибку в протоколе. Решение зависит от режима загрузки.

Мини-пример: прайс на 3000 позиций

Поставщик присылает прайс из трёх колонок: артикул, наименование, цена. Читаете файл табличным документом, получаете 3000 строк в таблице значений. Одним запросом сопоставляете артикулы с номенклатурой: допустим, 2850 нашлись, 150 нет. Для найденных обновляете цену, для остальных создаёте новые элементы или выводите их в протокол отдельным списком для ручной проверки. Вся загрузка занимает секунды, а не минуты, потому что в цикле нет ни одного обращения к базе за ссылкой.

Загрузка в табличные части документов

Таблица значений и табличная часть устроены похоже, поэтому перенос короткий:

Документ = Документы.ПоступлениеТоваровУслуг.СоздатьДокумент();
Документ.Дата = ТекущаяДатаСеанса();
// шапка: организация, контрагент, склад
Документ.Товары.Загрузить(ТаблицаДляДокумента);
Документ.Записать(РежимЗаписиДокумента.Запись);

Метод Загрузить() сопоставляет колонки по именам: колонка Номенклатура попадает в реквизит с тем же именем, остальные игнорируются. Поэтому имена колонок вашей таблицы должны совпадать с именами реквизитов табличной части, а типы — с типами реквизитов. Если реквизит принимает ссылку, а в колонке строка, значение не сохранится.

Что стоит учесть при загрузке документов:

• 📌 Шапка. Организацию, склад, договор и валюту в файле обычно нет. Берите значения по умолчанию или спрашивайте пользователя на форме обработки.
• 📌 Пересчёт. После Загрузить() суммы и НДС сами не пересчитываются. Заполните их в коде или вызовите процедуры пересчёта вашей конфигурации.
• 📌 Проведение. Сначала запись в режиме записи, и только потом, когда протокол чистый, проведение. Проведение сотен документов в одной транзакции откатывается целиком при одной ошибке.
• 📌 Размер пакета. Для файла на 50 тысяч строк делайте по документу на 200–500 строк. Огромный документ открывается долго, и работать с ним неудобно.

Если загрузка должна идти регулярно

Когда файл приходит каждый день, ручная обработка надоедает. Типичная схема автоматизации:

1. Файл попадает в сетевую папку или на почту.
2. Регламентное задание раз в N минут смотрит папку.
3. Новый файл читается, проверяется, загружается; результат пишется в журнал.
4. Обработанный файл переносится в подпапку «Готово» или «Ошибки».

Что нужно предусмотреть:

• Регламентное задание работает на сервере, поэтому читать файл надо серверным способом: Прочитать() по пути, доступному серверу. COM для фоновых заданий не годится — у задания нет интерактивной сессии и Excel может зависнуть.
• Защита от повторной загрузки. Храните имя файла и его контрольную сумму (ХешированиеДанных с алгоритмом MD5 или SHA1) в регистре сведений. Файл с тем же хешем пропускайте.
• Незаконченный файл. Если поставщик ещё копирует файл, 1С прочитает половину. Проверяйте, что размер файла не менялся последние минуты, либо договоритесь, чтобы файл появлялся под временным расширением и переименовывался после копирования.
• Права. Учётная запись, от имени которой работает служба сервера 1С, должна иметь доступ к папке. Частая причина «у меня работает, а из задания нет» именно в этом.

Протокол загрузки и разбор ошибок

Загрузка без протокола — гадание. Сделайте таблицу значений с колонками «Номер строки файла», «Статус», «Сообщение» и выводите её пользователю после загрузки. Минимум полей — номер строки и конкретная причина: не «ошибка в данных», а «строка 47: артикул A-1002 не найден в номенклатуре».

Режимы, которые стоит дать пользователю:

• Только проверить — прогон без записи, в протоколе все проблемы разом.
• Пропускать ошибочные строки — загрузить годные, ошибочные вывести отдельно.
• Остановиться на первой ошибке — для критичных документов.

Принцип: ошибки одной строки не должны валить весь файл молча, но и записывать «наполовину» тоже нельзя. Выбирайте осознанно, что для вашего случая хуже.

Частые вопросы