Разработка и Конфигурация · 11 мин
Как прочитать XLS файл в 1С
Способы чтения XLS и XLSX файлов в 1С: табличный документ, COM-объект Excel, ADODB. Примеры кода, типичные ошибки и советы по обработке больших файлов.
Чтение данных из Excel-файлов — одна из самых частых задач при загрузке номенклатуры, цен, остатков и контрагентов в 1С. Формат XLS (старый бинарный формат Excel 97–2003) и XLSX (современный формат на основе XML) читаются по-разному, и выбор способа зависит от версии платформы, объёма файла и того, есть ли на компьютере установленный Microsoft Excel.
В 1С есть три рабочих подхода: встроенный объект «ТабличныйДокумент», который умеет открывать Excel-файлы без сторонних программ; COM-подключение к приложению Excel; и чтение через ADODB как из базы данных. Первый способ — самый простой и предпочтительный в большинстве случаев, особенно в клиент-серверном режиме и на Linux-сервере, где Excel отсутствует.
Ниже разобраны все три метода с готовыми примерами кода на встроенном языке 1С, ограничения каждого подхода, типичные ошибки и способы проверить, что данные прочитались корректно.
Выбор способа чтения: что учесть до написания кода
Прежде чем писать обработку, ответьте на три вопроса. Первый — где будет выполняться код: на клиенте, на сервере Windows или на сервере Linux. COM-объект Excel работает только там, где установлен сам Excel, то есть практически никогда на сервере. Второй вопрос — объём файла: ТабличныйДокумент при файлах в десятки тысяч строк может работать медленно и потреблять много памяти. Третий — нужны ли только значения ячеек или также формулы, цвета, объединённые ячейки.
Для типовой загрузки прайса или номенклатуры почти всегда достаточно ТабличногоДокумента. COM-подключение оправдано, когда файл содержит сложную структуру с макросами или когда нужно читать вычисленные значения формул строго так, как их показывает Excel. ADODB выбирают для потокового чтения больших однородных таблиц.
Сравнение способов чтения XLS
Работает везде, включая Linux-сервер и веб-клиент. Не требует Excel. Ограничение: медленно на очень больших файлах, читает только значения.
Полный доступ к формулам, листам, форматированию. Ограничение: требует установленный Excel, работает только на Windows, оставляет процесс EXCEL.EXE при ошибках.
Быстрое чтение больших однородных таблиц как SQL-запросом. Ограничение: требует провайдера данных, проблемы с определением типов колонок, только Windows.
Чтение через ТабличныйДокумент — универсальный способ
Объект ТабличныйДокумент умеет открывать файлы форматов XLS, XLSX, MXL и некоторые другие. Это встроенный механизм платформы, поэтому никаких внешних компонентов не требуется. Файл сначала нужно получить на той стороне, где выполняется код: при работе на сервере файл передаётся с клиента через временное хранилище.
Базовый пример чтения файла и обхода ячеек:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать("C:\Данные\Прайс.xls", СпособЧтенияЗначенийТабличногоДокумента.Значение);
ВысотаТаблицы = ТабДок.ВысотаТаблицы;
ШиринаТаблицы = ТабДок.ШиринаТаблицы;
Для НомерСтроки = 2 По ВысотаТаблицы Цикл
Наименование = ТабДок.Область("R" + НомерСтроки + "C1").Текст;
ЦенаСтрокой = ТабДок.Область("R" + НомерСтроки + "C3").Текст;
// далее — преобразование и запись в справочник
КонецЦикла;
Обратите внимание на параметр СпособЧтенияЗначенийТабличногоДокумента.Значение — он указывает платформе читать вычисленные значения ячеек, а не исходные формулы. Адресация ячеек идёт в формате «R{строка}C{колонка}», а свойство Текст возвращает содержимое в виде строки, которую затем нужно привести к числу или дате.
Как передать файл на сервер в клиент-серверном режиме
В тонком клиенте файловая система клиента недоступна серверу, поэтому типовая схема такая: на клиенте пользователь выбирает файл через диалог, файл помещается во временное хранилище, на сервере извлекается во временный файл, читается, после чего временный файл удаляется.
// На клиенте
НачатьПомещениеФайла(Новый ОписаниеОповещения("ПослеПомещения", ЭтотОбъект),
, ПутьКФайлу, Ложь, УникальныйИдентификатор);
// На сервере
ДвоичныеДанные = ПолучитьИзВременногоХранилища(АдресХранилища);
ИмяВременногоФайла = ПолучитьИмяВременногоФайла("xls");
ДвоичныеДанные.Записать(ИмяВременногоФайла);
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ИмяВременногоФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);
УдалитьФайлы(ИмяВременногоФайла);
Тип файла при помещении во временное хранилище не сохраняется, поэтому расширение временного файла задавайте явно — иначе платформа может не распознать формат при чтении. Для XLSX указывайте «xlsx», для старого формата — «xls».
Чтение через COM-объект Excel
Если на рабочем месте установлен Microsoft Excel, можно подключиться к нему как к COM-серверу. Этот способ даёт доступ к именам листов, формулам, объединённым ячейкам и форматированию. Код выполняется только на клиенте Windows (или на сервере Windows с установленным Office, что не рекомендуется по соображениям стабильности).
Попытка
Excel = Новый COMОбъект("Excel.Application");
Исключение
Сообщить("Excel не установлен: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
Excel.Visible = Ложь;
Excel.DisplayAlerts = Ложь;
Книга = Excel.Workbooks.Open("C:\Данные\Прайс.xls");
Лист = Книга.Sheets(1);
ПоследняяСтрока = Лист.Cells(Лист.Rows.Count, 1).End(-4162).Row; // xlUp
Для НомерСтроки = 2 По ПоследняяСтрока Цикл
Наименование = Лист.Cells(НомерСтроки, 1).Value;
Цена = Лист.Cells(НомерСтроки, 3).Value;
КонецЦикла;
Книга.Close(Ложь);
Excel.Quit();
Excel = Неопределено;
Критически важно закрывать книгу и завершать приложение Excel, иначе в памяти останется процесс EXCEL.EXE, который будет удерживать файл и накапливаться при каждом запуске обработки. Оберните работу в Попытка/Исключение и в блоке Исключение также вызывайте Quit, если объект был создан.
Чтение через ADODB для больших файлов
Когда файл содержит десятки тысяч однородных строк, быстрее прочитать его как таблицу базы данных через ADODB-запрос. Провайдер Microsoft.ACE.OLEDB (или устаревший Jet) трактует лист Excel как таблицу, а диапазон ячеек — как источник данных.
Соединение = Новый COMОбъект("ADODB.Connection");
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Данные\Прайс.xls;"
+ "Extended Properties=""Excel 8.0;HDR=YES""";
Соединение.Open(СтрокаПодключения);
НаборЗаписей = Соединение.Execute("SELECT * FROM [Лист1$A1:C50000]");
Пока НЕ НаборЗаписей.EOF Цикл
Наименование = НаборЗаписей.Fields(0).Value;
НаборЗаписей.MoveNext();
КонецЦикла;
НаборЗаписей.Close();
Соединение.Close();
Параметр HDR=YES означает, что первая строка — заголовки колонок. Главная ловушка метода: провайдер определяет тип колонки по первым строкам, и если в числовой колонке встретится текст, значение вернётся как Null. Для смешанных колонок добавляйте в Extended Properties параметр IMEX=1.
Обработка значений: числа, даты и пустые ячейки
Реальные Excel-файлы редко бывают чистыми: в числах встречаются неразрывные пробелы и символы валюты, даты записаны текстом, пустые ячейки перемежаются с данными. Перед записью в базу каждое значение нужно нормализовать.
Функция СтрокаВЧисло(Знач Строка)
Строка = СтрЗаменить(Строка, Символ(160), ""); // неразрывный пробел
Строка = СтрЗаменить(Строка, " ", "");
Строка = СтрЗаменить(Строка, ",", ".");
ОписаниеЧисла = Новый ОписаниеТипов("Число");
Возврат ОписаниеЧисла.ПривестиЗначение(Строка);
КонецФункции
Пустые строки в середине файла встречаются постоянно — например, строки-разделители категорий товаров. Проверяйте ключевое поле (обычно наименование или артикул) на пустоту и пропускайте такие строки, но ведите счётчик пропущенных, чтобы в конце показать пользователю статистику: сколько строк прочитано, сколько загружено, сколько отклонено и почему.
Проверка результата и ограничения методов
После разработки обработки проверьте её на реальном файле поставщика, а не на идеальном тестовом примере. Контрольные точки: число загруженных строк совпадает с числом строк в файле минус заголовок и пустые; суммы по числовым колонкам сходятся с итогами в Excel; даты не «переехали» (проблема форматов ДД.ММ.ГГГГ против ММ.ДД.ГГГГ).
Как проверить результат
Загружено столько строк, сколько заполненных строк в файле
Часть строк потеряна или загружены пустые дубликаты
Совпадает с итогом в Excel
Расхождение — проверяйте преобразование чисел и разделитель
Даты соответствуют исходным
День и месяц перепутаны местами или дата стала строкой
Помните об ограничениях. ТабличныйДокумент читает только первый лист файла и не даёт доступа к формулам. COM-способ не работает без установленного Excel и в 64-битном серверном контексте может конфликтовать с разрядностью Office. ADODB требует установленного провайдера соответствующей разрядности. Для файлов XLSX в веб-клиенте без расширения работы с файлами единственный путь — передача на сервер и чтение ТабличнымДокументом.
Что важно запомнить
Для большинства задач используйте ТабличныйДокумент с методом Прочитать — это кроссплатформенно и не требует Excel. COM-объект нужен только для формул и сложной структуры, ADODB — для очень больших таблиц. Всегда нормализуйте значения, работайте в транзакции и проверяйте итоги после загрузки.
Частые вопросы
Можно ли прочитать XLSX на сервере 1С под Linux?
Да. Объект ТабличныйДокумент с методом Прочитать работает на сервере Linux без каких-либо дополнительных компонентов. Файл нужно предварительно передать на сервер через временное хранилище, записать во временный файл с расширением xlsx и прочитать. COM и ADODB на Linux недоступны.
Почему ТабличныйДокумент не открывает мой файл XLS?
Частые причины: файл на самом деле имеет другой формат (например, переименованный CSV или HTML-таблица, сохранённая с расширением .xls), файл повреждён или защищён паролем. Попробуйте открыть файл в Excel и пересохранить его в стандартном формате «Книга Excel 97–2003» или XLSX.
Как прочитать не первый, а второй лист книги Excel?
ТабличныйДокумент читает только первый лист. Если нужен другой лист, либо используйте COM-объект Excel (Книга.Sheets(2)), либо попросите поставщика данных присылать файл с нужным листом первым, либо программно пересохраните книгу через COM с нужным листом на первой позиции.
Числа из Excel загружаются как нули — в чём причина?
Почти всегда дело в формате строки: неразрывные пробелы между разрядами, запятая вместо точки, символ валюты. Функция Число() в 1С не умеет игнорировать такие символы. Очищайте строку через СтрЗаменить и приводите значение через ОписаниеТипов, как показано в разделе про обработку значений.
Файл очень большой, обработка зависает — что делать?
Определите реальную границу данных и не обходите пустые строки. Выводите прогресс через ДлительныеОперации или обработку ожидания, читайте файл порциями. Если ТабличныйДокумент всё равно не справляется, перейдите на чтение через ADODB — оно существенно быстрее на однородных таблицах.
Как загрузить данные в фоне, не блокируя работу пользователя?
Используйте фоновые задания: поместите файл во временное хранилище, передайте адрес в фоновое задание, которое прочитает файл и запишет данные. Для управляемых форм удобен механизм длительных операций из БСП (ДлительныеОперации.ВыполнитьВФоне) с выводом прогресса и отчётом о результатах.