Загрузка данных из xlsx в 1С: табличный документ, COM, ADO и XML

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

Ниже код для каждого способа, проверка структуры файла, приведение типов ячеек и приёмы, которые ускоряют загрузку больших таблиц в десятки раз. Примеры написаны для управляемых форм и платформы 8.3.2x; это механизм платформы, поэтому он одинаков в БП, УТ, ЗУП и любой другой конфигурации.

Какой способ чтения Excel выбрать

Методов четыре. Они различаются не синтаксисом, а тем, что должно стоять на компьютере, где выполняется код.

💡 Если код выполняется на сервере 1С, Excel там почти наверняка не установлен, а запуск Excel из-под службы — отдельная головная боль. Для серверной загрузки выбирайте табличный документ или XML.

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

Метод Прочитать у объекта ТабличныйДокумент появился в платформе 8.3.6. Он понимает форматы xlsx, xls, ods и mxl. Второй параметр задаёт, что попадёт в ячейки:

• 🔹 СпособЧтенияЗначенийТабличногоДокумента.Значение — типизированные значения (число, дата, строка);
• 🔹 СпособЧтенияЗначенийТабличногоДокумента.Текст — то, что видно на экране, с форматированием.

Для загрузки данных почти всегда нужен первый вариант.

Шаг 1. Передать файл с клиента на сервер

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

&НаКлиенте
Процедура ЗагрузитьИзExcel(Команда)
    Оповещение = Новый ОписаниеОповещения("ПослеПомещенияФайла", ЭтотОбъект);
    НачатьПомещениеФайла(Оповещение, , , Истина, УникальныйИдентификатор);
КонецПроцедуры

&НаКлиенте
Процедура ПослеПомещенияФайла(Результат, Адрес, ВыбранноеИмяФайла, ДополнительныеПараметры) Экспорт
    Если Не Результат Тогда
        Возврат;
    КонецЕсли;
    ЗагрузитьНаСервере(Адрес);
КонецПроцедуры

Шаг 2. Прочитать файл и обойти строки

На сервере двоичные данные записывают во временный файл и открывают:

&НаСервере
Процедура ЗагрузитьНаСервере(Адрес)
    ДвоичныеДанные = ПолучитьИзВременногоХранилища(Адрес);
    ИмяФайла = ПолучитьИмяВременногоФайла("xlsx");
    ДвоичныеДанные.Записать(ИмяФайла);

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

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

Цикл начинается со второй строки, потому что в первой обычно заголовки. ВысотаТаблицы и ШиринаТаблицы возвращают размер заполненной области.

💡 В конфигурациях на БСП вместо ПолучитьИмяВременногоФайла используйте ФайловаяСистема.ВременноеИмяФайла("xlsx") — так рекомендует библиотека, а удаление временных файлов проходит аккуратнее.

Шаг 3. Превратить лист в таблицу значений

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

Область = ТабДок.Область(1, 1, ТабДок.ВысотаТаблицы, ТабДок.ШиринаТаблицы);
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(Область);
Построитель.Выполнить();
ТаблицаДанных = Построитель.Результат.Выгрузить();

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

Как прочитать Excel в 1С через COM-объект

Классический способ: 1С запускает Excel как внешнее приложение и обращается к его объектной модели. Работает только в Windows, где установлен Microsoft Excel. На сервере его запускают редко: нужен Excel на сервере и права службы 1С на запуск COM.

Excel = Новый COMОбъект("Excel.Application");
Excel.DisplayAlerts = Ложь;
Попытка
    Книга = Excel.Workbooks.Open(ПутьКФайлу, 0, Истина); // без обновления связей, только чтение
    Лист = Книга.Worksheets(1);
    ПоследняяСтрока = Лист.Cells(Лист.Rows.Count, 1).End(-4162).Row; // -4162 = xlUp
    Для НомерСтроки = 2 По ПоследняяСтрока Цикл
        Артикул = СокрЛП(Лист.Cells(НомерСтроки, 1).Text);
        Количество = Лист.Cells(НомерСтроки, 3).Value;
        // обработка строки
    КонецЦикла;
    Книга.Close(Ложь);
Исключение
    ТекстОшибки = ОписаниеОшибки();
    Excel.Quit();
    ВызватьИсключение ТекстОшибки;
КонецПопытки;
Excel.Quit();

Разница между свойствами ячейки:

• 📌 Value — значение с типом: число, дата, строка;
• 📌 Text — строка так, как она отображается на экране (с разделителями разрядов и форматом даты);
• 📌 Formula — формула, если она есть.

Лист можно взять по имени: Книга.Worksheets("Остатки"). Количество листов — Книга.Worksheets.Count.

Вызов Quit обязателен

Если не вызвать Excel.Quit() в ветке исключения, процесс EXCEL.EXE останется висеть в памяти. После нескольких неудачных загрузок в диспетчере задач их десятки, а файл заблокирован для открытия. Поэтому Quit стоит и в Исключение, и после блока.

Быстрое чтение всей области за один вызов

Каждое обращение к Cells — межпроцессный вызов. На 20 000 строк и 10 колонок это 200 000 вызовов и минуты ожидания. Приём практиков — забрать всю область одним обращением:

Данные = Лист.UsedRange.Value; // COMSafeArray
МассивДанных = Данные.Выгрузить();

Выгрузить() превращает COMSafeArray в массив массивов 1С, и дальше обход идёт в памяти без обращений к Excel. Порядок вложенности (сначала строки или сначала колонки) проверьте один раз на тестовом файле из двух строк и трёх колонок. И учтите: UsedRange начинается с первой непустой ячейки, а не обязательно с A1.

Чтение через ADO и разбор xlsx как XML

ADO: Excel как таблица базы данных

Провайдер Microsoft ACE OLEDB позволяет выполнить к листу SQL-запрос. Excel не запускается, но провайдер должен быть установлен, причём той же разрядности, что и клиент или сервер 1С.

СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу
    + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open(СтрокаПодключения);
Выборка = Новый COMОбъект("ADODB.Recordset");
Выборка.Open("SELECT * FROM [Лист1$]", Соединение);
Пока Не Выборка.EOF Цикл
    Артикул = Выборка.Fields(0).Value;
    Количество = Выборка.Fields(2).Value;
    // обработка строки
    Выборка.MoveNext();
КонецЦикла;
Выборка.Close();
Соединение.Close();

Параметры строки подключения:

• 🔸 HDR=YES — первая строка содержит заголовки и в данные не попадает;
• 🔸 IMEX=1 — смешанные колонки читаются как текст.

XLSX как ZIP-архив с XML

Файл xlsx — это ZIP-архив. Его можно распаковать объектом ЧтениеZipФайла и прочитать XML-файлы через ЧтениеXML или DOM. Внешние компоненты не нужны, код работает на любой ОС.

Что лежит внутри:

Логика разбора:

1. Прочитайте sharedStrings.xml в массив: каждый элемент si — одна строка словаря.
2. В файле листа обойдите элементы row, внутри них — c. Атрибут r даёт адрес ячейки (B7), значение лежит в дочернем v.
3. Если у ячейки атрибут t="s", в v записан не текст, а индекс в словаре строк (с нуля).
4. Даты хранятся числом дней. Их пересчитывают по формуле Дата(1899, 12, 30) + Число * 86400: число 45000 даёт 15.03.2023.

Способ трудоёмкий, зато даёт полный контроль. Его берут, когда табличный документ спотыкается на «кривом» файле из сторонней программы.

Проверка структуры файла и обработка ошибок при импорте

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

Искать колонки по заголовку, а не по номеру

Вместо жёсткого «артикул в первой колонке» найдите номера колонок по тексту первой строки:

Колонки = Новый Соответствие;
Для НомерКолонки = 1 По ТабДок.ШиринаТаблицы Цикл
    Заголовок = НРег(СокрЛП(ТабДок.Область(1, НомерКолонки, 1, НомерКолонки).Текст));
    Колонки.Вставить(Заголовок, НомерКолонки);
КонецЦикла;

Обязательные = СтрРазделить("артикул,наименование,количество", ",");
Для Каждого Имя Из Обязательные Цикл
    Если Колонки.Получить(Имя) = Неопределено Тогда
        ВызватьИсключение СтрШаблон("В файле нет колонки «%1»", Имя);
    КонецЕсли;
КонецЦикла;

Собирать ошибки по строкам, а не падать на первой

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

Что стоит проверять в каждой строке:

• ✅ обязательные поля не пустые;
• ✅ количество и цена приводятся к числу и не отрицательные;
• ✅ дата в разумном диапазоне;
• ✅ ИНН из 10 или 12 цифр;
• ✅ артикул найден в справочнике.

Кодировка, спецсимволы и типы данных при чтении Excel

С кодировкой у xlsx проблем нет: внутри архива XML в UTF-8, и все три способа отдают кириллицу корректно. Кракозябры появляются в двух случаях:

• 📄 файл на самом деле CSV с расширением .xls, который выгрузила банковская или складская программа;
• 📄 старый xls из очень старой программы с нестандартной кодовой страницей.

CSV читайте как текст с явной кодировкой: Новый ЧтениеТекста(ПутьКФайлу, КодировкаТекста.UTF8) или Новый ЧтениеТекста(ПутьКФайлу, "windows-1251"). Строку делите на поля через СтрРазделить(Строка, ";"). Если в полях есть точка с запятой внутри кавычек, простого деления недостаточно — разбирайте кавычки отдельно.

Невидимые символы в ячейках

Самые частые причины «не нашёл артикул, хотя он есть»:

• 🔍 неразрывный пробел (Символы.НПП) — Excel ставит его как разделитель разрядов;
• 🔍 пробелы в начале и конце — убирает СокрЛП;
• 🔍 перевод строки внутри ячейки (Символы.ПС) после Alt+Enter;
• 🔍 латинские буквы, похожие на кириллические: А, С, Е, О, Р в артикулах.

Функция, которая приводит текст ячейки к числу:

Функция ЧислоИзЯчейки(Знач Текст)
    Текст = СтрЗаменить(Текст, Символы.НПП, "");
    Текст = СтрЗаменить(Текст, " ", "");
    Текст = СтрЗаменить(Текст, ",", ".");
    Если ПустаяСтрока(Текст) Тогда
        Возврат 0;
    КонецЕсли;
    Попытка
        Возврат Число(Текст);
    Исключение
        Возврат Неопределено; // строка не число — пометить ошибку в строке
    КонецПопытки;
КонецФункции

Даты и ведущие нули

Дата в формате дд.мм.гггг, пришедшая текстом, разбирается так: Части = СтрРазделить(Текст, "."), затем Дата(Число(Части[2]), Число(Части[1]), Число(Части[0])) внутри Попытка. Если ячейка прочитана как значение, проверьте ТипЗнч(Значение) = Тип("Дата") — тогда разбирать ничего не нужно.

ИНН, коды и артикулы с ведущими нулями Excel хранит числом и нули теряет. ИНН 0123456789 превращается в девятизначный 123456789. Надёжное решение — формат «Текстовый» для этих колонок ещё в шаблоне файла. Если файл уже пришёл, дополняйте нулями слева до 10 или 12 знаков, но только когда длина однозначно указывает на потерю.

Как ускорить загрузку больших файлов Excel в 1С

На 50 000 строках разница между «в лоб» и «правильно» — минуты против десятков минут. Узкие места почти всегда одни и те же.

Один запрос вместо тысячи поисков

Соберите все артикулы из файла в массив и одним запросом получите ссылки:

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

Поиск внутри цикла превращается в НоменклатураПоАртикулу.Получить(Артикул) — обращение к памяти вместо запроса к базе.

Фоновое задание для длинной загрузки

Если загрузка идёт дольше минуты, вынесите её в фоновое задание. В конфигурациях на БСП для этого есть модуль ДлительныеОперации: форма не блокируется, пользователь видит прогресс, а обрыв клиента не прерывает загрузку.

Пробный прогон на копии

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

Частые вопросы о чтении Excel в 1С