Как быстро и оптимально загрузить данные из Excel в 1С?

Программист 1С v8.3 (Управляемые формы) IT и автоматизация бизнеса
← На главную

Загрузка данных из электронных таблиц Microsoft Excel — одна из самых частых задач в практике программиста 1С. Исторически сложилось, что наиболее популярным методом автоматизации этой задачи стало использование COM-соединения. Однако этот способ имеет множество критических недостатков: низкая скорость работы, жесткая зависимость от операционной системы Windows и установленного пакета MS Office, а также нестабильность при работе в многопоточном режиме на сервере.

В этой статье мы подробно разберем, почему от классического COM-соединения стоит отказаться, и рассмотрим современные, гораздо более производительные альтернативы загрузки файлов Excel в систему 1С:Предприятие.

Способ 1. Использование встроенного метода «Прочитать()» табличного документа

Начиная с современных версий платформы 1С v8.3, разработчикам доступен мощный встроенный механизм прямого чтения файлов XLSX. Объект ТабличныйДокумент умеет самостоятельно открывать и считывать файлы без привлечения сторонних библиотек и установленного MS Excel.

Для реализации этого метода мы используем следующий базовый код:


ТабДок = Новый ТабличныйДокумент;
Попытка
    ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
    ВызватьИсключение "Не удалось прочитать файл! Ошибка: " + ОписаниеОшибки();
КонецПопытки;

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

Давайте рассмотрим, как это реализовать на практике:


// Создаем построитель запроса
Построитель = Новый ПостроительЗапроса;
// Передаем всю заполненную область табличного документа в качестве источника данных
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область());
Построитель.Выполнить();

// Выгружаем данные в готовую Таблицу Значений одним движением
ТаблицаДанных = Построитель.Результат.Выгрузить();

Этот способ работает в десятки раз быстрее обычного перебора строк. К тому же он является полностью кроссплатформенным и прекрасно отрабатывает на серверах под управлением ОС Linux.

Способ 2. Подключение к Excel через ADO (ActiveX Data Objects)

Если загрузка выполняется в среде ОС Windows, отличным скоростным решением является использование ADO. При этом подходе файл Excel рассматривается системой как полноценная база данных, а листы книги — как таблицы, к которым можно обращаться с помощью классических SQL-запросов.

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


Соединение = Новый COMОбъект("ADODB.Connection");
Recordset = Новый COMОбъект("ADODB.Recordset");

// Формируем строку подключения для провайдера ACE OLEDB
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";

Попытка
    Соединение.Open(СтрокаПодключения);
Исключение
    Предупреждение("Ошибка подключения к ADO: " + ОписаниеОшибки());
    Возврат;
КонецПопытки;

// Выполняем SQL-запрос к первому листу (обратите внимание на знак $ в названии листа)
ТекстЗапроса = "SELECT * FROM [Лист1$]";
Recordset.Open(ТекстЗапроса, Соединение, 1, 1);

// Выгружаем рекордсет в таблицу значений 1С
// (Для обхода рекордсета можно использовать цикл Пока Recordset.EOF = 0)
Пока Recordset.EOF() = 0 Цикл
    // Обработка полей, например: ЗначениеКолонки = Recordset.Fields(0).Value;
    Recordset.MoveNext();
КонецЦикла;

Recordset.Close();
Соединение.Close();

Важная особенность метода ADO: Этот способ обеспечивает высокую скорость за счет автоматической типизации данных драйвером. Однако помните о системном параметре реестра Windows TypeGuessRows. По умолчанию драйвер ADO сканирует только первые 8 строк, чтобы определить тип данных в колонке. Если в начале колонки идут числа, а далее текст — ADO может вернуть пустые значения (NULL) для текстовых ячеек. Использование параметра IMEX=1 в строке подключения минимизирует эту проблему, переводя смешанные колонки в текстовый тип.

Способ 3. Предварительная конвертация файла в формат CSV

Для файлов огромного объема (сотни тысяч или миллионы строк) самым практичным и быстрым решением остается перевод данных в простой текстовый формат CSV (Comma-Separated Values). Текстовые файлы считываются платформой 1С практически мгновенно.

Если исходный файл передается пользователем в формате Excel, мы можем программно конвертировать его в CSV с помощью метода SaveAs. Проанализируем пример такой конвертации:


Excel = Новый COMОбъект("Excel.Application");
Excel.DisplayAlerts = Ложь; // Отключаем предупреждения системы
Книга = Excel.Workbooks.Open(ПутьКФайлу);

// Параметр 6 соответствует формату xlCSV (сохранение с разделителями запятыми/точками с запятой)
Книга.SaveAs(ПутьКВыходномуФайлуCSV, 6);
Книга.Close(Ложь);
Excel.Quit();

После получения CSV-файла мы можем прочитать его стандартными встроенными средствами 1С, такими как ЧтениеТекста, разбивая строки по символу-разделителю. Это обеспечивает рекордную скорость обработки огромных массивов информации.

Способ 4. Прямой разбор XLSX-архива (Парсинг XML)

Файлы с расширением .xlsx представляют собой стандартные ZIP-архивы, внутри которых находятся XML-файлы со всеми данными книги. Этот метод дает абсолютную независимость от операционной системы и стороннего ПО.

Давайте разберем по шагам логику этого процесса:

  1. Используем объект ЧтениеZipФайла для распаковки файла во временную директорию.
  2. Внутри каталога находим файл общего списка строк: xl/sharedStrings.xml. Считываем его с помощью объекта ЧтениеXML, чтобы составить массив всех уникальных текстовых значений файла.
  3. Переходим к файлу структуры конкретного листа, например: xl/worksheets/sheet1.xml, и последовательно считываем индексы строк и ссылки на массив уникальных строк.

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

Способ 5. Оптимизация классического COM-соединения

Если переписать архитектуру решения невозможно и вам приходится использовать Excel.Application, обязательно примените ключевое правило оптимизации COM-вызовов: избегайте обращений к единичным ячейкам в цикле.

Вместо медленного построчного чтения воспользуйтесь свойством UsedRange.Value. Оно позволяет за одно обращение выгрузить данные всего листа в многомерный массив оперативной памяти 1С:


Excel = Новый COMОбъект("Excel.Application");
Книга = Excel.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Sheets(1);

// Одним COM-вызовом получаем двумерный массив COMSafeArray со всеми данными листа
МассивДанных = Лист.UsedRange.Value;

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

// Теперь мы можем быстро обойти этот массив стандартными средствами 1С
// Это в десятки раз быстрее, чем вызовы Лист.Cells(Row, Col).Value в цикле

Подведем итоги

Для выбора оптимального инструмента загрузки данных из Excel руководствуйтесь следующими правилами:

← На главную