Как загрузить данные из Excel в таблицу значений 1С

Загрузка данных из Excel в таблицу значений — одна из самых частых задач при доработке конфигураций 1С: перенос номенклатуры, цен, остатков, контрагентов и других списков из внешних файлов. Таблица значений здесь выступает промежуточным буфером: данные сначала считываются из файла, проверяются, а затем записываются в справочники или документы.

Существует три рабочих способа: чтение через COM-объект Excel.Application, загрузка через объект ТабличныйДокумент и использование готовых типовых обработок. Выбор зависит от того, где выполняется код (на клиенте или сервере), установлен ли Excel на машине и какой объём данных нужно обработать.

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

Какой способ загрузки выбрать

Перед написанием кода определите три условия: где исполняется код (толстый клиент, тонкий клиент, сервер), есть ли установленный Microsoft Excel на машине и каков размер файла. От этого зависит допустимый метод.

Чтение через ТабличныйДокумент — самый переносимый вариант: платформа сама разбирает форматы XLS, XLSX и ODS, Excel не нужен, код работает на сервере и в веб-клиенте. Ограничение — на очень больших файлах (сотни тысяч строк) чтение заметно медленнее и требует больше памяти.

COM-подключение к Excel.Application даёт высокую скорость и доступ к вычисляемым формулам, но работает только на Windows с установленным Office и обычно только на клиенте. На сервере 1С под Linux этот способ недоступен в принципе.

Сравнение способов загрузки

ТабличныйДокумент.Прочитать()

Работает везде, Excel не нужен. Подходит для файлов до нескольких десятков тысяч строк. Медленнее на больших объёмах.

COM: Excel.Application

Быстро на больших файлах, доступ к формулам и листам. Требует Windows и установленного Excel, обычно только клиент.

Типовые обработки (ЗагрузкаДанныхИзФайла)

Готовый интерфейс сопоставления колонок из БСП. Не требует программирования, но загружает сразу в объекты, а не в произвольную таблицу значений.

Подготовка файла и структуры таблицы значений

Любая загрузка начинается с описания структуры приёмника. Создайте таблицу значений и явно добавьте колонки с типами — это избавит от проблем с датами, числами и строками разной длины при последующей записи в базу.

Файл Excel приведите к простому виду: одна строка заголовков, с первой строки данных — только значения, без объединённых ячеек, промежуточных итогов и пустых колонок внутри диапазона. Объединённые ячейки при чтении дают значение только в первой ячейке диапазона, остальные будут пустыми — это частая причина «потери» данных.

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

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

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

Последовательность: создать ТабличныйДокумент, вызвать метод Прочитать() с путём к файлу, определить количество строк и колонок через область, затем в цикле перенести значения в таблицу значений. Первая строка обычно пропускается, если в ней заголовки.

1. 1
   Создайте объект ТабличныйДокумент и вызовите Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение)
2. 2
   Определите границы: ТабДок.ВысотаТаблицы и ТабДок.ШиринаТаблицы
3. 3
   Пройдите циклом по строкам, начиная со второй, и читайте ячейки через ТабДок.Область(НомерСтроки, НомерКолонки).Текст или .Значение
4. 4
   Добавляйте строку в таблицу значений и приводите значения к типам колонок
5. 5
   Проверьте количество загруженных строк и значения в нескольких контрольных строках

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

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

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

Загрузка через COM-объект Excel

Если код выполняется на клиенте Windows с установленным Excel, чтение через COM-объект обычно быстрее и даёт доступ к нескольким листам, формулам и форматам. Объект создаётся через COMСоединение с «Excel.Application».

Критично завершать работу корректно: закрыть книгу, вызвать Quit() и обнулить переменные. Иначе в памяти останется процесс EXCEL.EXE, который удерживает файл и мешает его повторному открытию. Открывайте книгу в режиме только чтения и с отключёнными диалогами, чтобы Excel не ждал реакции пользователя.

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

Для НомерСтроки = 2 По ВсегоСтрок Цикл
НоваяСтрока = ТЗ.Добавить();
НоваяСтрока.Артикул = СокрЛП(Строка(Лист.Cells(НомерСтроки, 1).Value));
НоваяСтрока.Цена = Лист.Cells(НомерСтроки, 3).Value;
КонецЦикла;

Книга.Close(Ложь);
Excel.Quit();
Excel = Неопределено;

Загрузка на сервере и в файловом режиме

В клиент-серверном варианте код обычно выполняется на сервере, а файл выбирает пользователь на своём компьютере. Путь «C:\Загрузка\файл.xlsx» на сервере недоступен — файл нужно сначала передать. Стандартная схема: поместить файл во временное хранилище на клиенте, на сервере извлечь его во временный файл и уже его прочитать.

Для этого используются методы НачатьПомещениеФайлаВоВременноеХранилище (или ПоместитьФайл в толстом клиенте) на клиенте и ПолучитьИзВременногоХранилища на сервере. Полученные двоичные данные записываются во временный файл с расширением исходного файла — расширение важно, потому что по нему платформа определяет формат при чтении табличного документа.

Сохраняйте расширение исходного файла при записи во временный файл: ПолучитьИмяВременногоФайла("xlsx"). Если записать XLSX-файл с расширением tmp, метод Прочитать() не распознает формат и выдаст ошибку или пустой документ.

Проверка результата и типичные ошибки

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

Коды и артикулы с ведущими нулями — классическая проблема: Excel хранит их как числа и нули теряет. Решение — заранее форматировать такие колонки в Excel как текст или восстанавливать нули программно по известной длине кода.

Контрольные точки

Как проверить результат загрузки

Количество строк

Норма

Совпадает с числом строк данных в файле (без заголовка)

Тревожный признак

Меньше — потеряны строки; больше — захвачены пустые или служебные строки

Числа и цены

Норма

Дробная часть сохранена, суммы сходятся с файлом

Тревожный признак

Числа обрезаны до целых или разделитель тысяч стал частью числа

Коды и артикулы

Норма

Ведущие нули на месте, длина кода как в файле

Тревожный признак

Нули потеряны, код преобразован в число

Даты

Норма

День и месяц не перепутаны, формат соответствует файлу

Тревожный признак

Даты интерпретированы в американском формате (месяц/день) вместо российского

Альтернативы: типовые обработки и CSV

Если дорабатывать конфигурацию нельзя, используйте типовую обработку загрузки данных из табличного документа (в конфигурациях на БСП она встроена, для старых конфигураций существует универсальная обработка с диска ИТС). Она позволяет сопоставить колонки файла реквизитам объекта через интерфейс и сразу записать данные в справочник или табличную часть.

Ещё одна альтернатива — сохранить лист Excel в формат CSV и читать его как текстовый файл через ЧтениеТекста с разбором по разделителю. Это самый быстрый и не требовательный к ресурсам способ, но CSV не хранит типы данных и ломается, если в значениях встречается символ разделителя или перевод строки.

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

Можно ли загрузить Excel без установленного Microsoft Office?

Да. Способ с объектом ТабличныйДокумент не требует Excel: платформа сама разбирает форматы XLS, XLSX и ODS. Достаточно вызвать метод Прочитать() и перебрать ячейки. Это единственный вариант для сервера на Linux.

Почему при загрузке теряются ведущие нули в артикулах?

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

Как загрузить файл, если код выполняется на сервере?

Передайте файл с клиента во временное хранилище (НачатьПомещениеФайлаВоВременноеХранилище), на сервере извлеките двоичные данные, запишите их во временный файл с исходным расширением и прочитайте через ТабличныйДокумент. Локальный путь клиента на сервере недоступен.

Что делать, если файл очень большой и загрузка зависает?

Чтение через ТабличныйДокумент на файлах в сотни тысяч строк требует много памяти. Варианты: читать через COM-объект Excel на клиенте, сохранить файл в CSV и разбирать текстом порциями, либо разбить исходный файл на части. Также выводите индикатор прогресса, чтобы пользователь видел, что процесс идёт.

Как загрузить данные с конкретного листа книги Excel?

Через COM-объект лист выбирается явно: Книга.Worksheets(ИмяЛиста) или по номеру. При чтении через ТабличныйДокумент платформа читает лист по умолчанию; если нужен другой лист без COM, проще сохранить его отдельным файлом или использовать расширенные библиотеки чтения XLSX.

Можно ли сразу записать данные в справочник, минуя таблицу значений?

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