Чтение и импорт данных из файлов Excel (в форматах .xls, .xlsx) — одна из самых частых и при этом ресурсоемких задач в практике разработчика 1С. Существует множество подходов к ее реализации: классический COM-объект, встроенный Табличный документ 1С, прямой парсинг XML-архива и даже внешние скрипты. Каждый из этих способов имеет свои достоинства, скрытые проблемы производительности и ограничения совместимости.
В этой статье мы подробно разберем, что возвращает популярный метод загрузки данных через COM-диапазон ячеек, как оптимизировать чтение встроенным Табличным документом 1С до предела его возможностей, рассмотрим кроссплатформенные варианты загрузки на операционных системах семейства Linux, а также коснемся нюансов импорта формул.
Рассмотрим код, который часто вызывает вопросы у разработчиков при первой реализации прямой работы с Microsoft Excel через COM:
Excel = Новый COMОбъект("Excel.Application");
Excel.Application.Workbooks.Open(Объект.ПутьКФайлу);
Excel.DisplayAlerts = 0;
ExcelSheet = Excel.Sheets(1);
ВсегоСтрок = ExcelSheet.Cells.SpecialCells(11).Row;
ВсегоКолонок = ExcelSheet.Cells.SpecialCells(11).Column;
ExcelSheetRange = ExcelSheet.Range(ExcelSheet.Cells(НачСтрока, 1), ExcelSheet.Cells(КонСтрока, ВсегоКолонок));
Данные = ExcelSheetRange.Value.Выгрузить();
Выясним причину, почему этот код работает в десятки раз быстрее обычного построчного обхода ячеек в цикле. Основная проблема классического чтения ячеек через COM-соединение заключается в так называемом "маршалинге" (внутрипроцессном и межпроцессном взаимодействии). Когда мы обращаемся к каждой отдельной ячейке вида ExcelSheet.Cells(Строка, Колонка).Value в цикле 1С, платформа выполняет отдельный ресурсоемкий COM-вызов для каждого обращения. Если в файле 50 000 строк и 20 колонок, это потребует 1 000 000 вызовов, что приведет к катастрофическому падению скорости и зависанию системы.
Метод обращения к диапазону ExcelSheetRange.Value позволяет решить эту проблему кардинально. Вместо последовательного опроса каждой ячейки мы за один COM-запрос забираем из оперативной памяти процесса Microsoft Excel весь двумерный массив данных. Этот массив передается в 1С в виде COM-объекта типа COMSafeArray.
При вызове метода Выгрузить() у объекта COMSafeArray платформа 1С автоматически конвертирует его в двумерный массив 1С. Однако здесь кроется важнейшая техническая особенность, о которой необходимо знать каждому разработчику: происходит автоматическое транспонирование индексов массива.
В полученном двумерном массиве первым измерением (первым индексом) становятся колонки, а вторым — строки. То есть обращение к данным выглядит следующим образом:
ЗначениеЯчейки = Данные[ИндексКолонки][ИндексСтроки];
Зная эту особенность, мы можем применить архитектурно красивый и невероятно быстрый прием загрузки данных непосредственно в объект ТаблицаЗначений. Вместо циклического перебора строк и колонок для заполнения таблицы ячейка за ячейкой, мы можем воспользоваться методом ЗагрузитьКолонку() объекта ТаблицаЗначений. Данный метод работает на уровне платформы и выполняет копирование целого массива данных в колонку таблицы за доли миллисекунды. Рассмотрим пример такой реализации:
// Создаем результирующую Таблицу Значений
ТЗ = Новый ТаблицаЗначений;
// Динамически создаем колонки в ТЗ
Для НомерКолонки = 1 По ВсегоКолонок Цикл
ТЗ.Колонки.Добавить("Колонка_" + НомерКолонки);
КонецЦикла;
// Быстро заполняем Таблицу Значений данными из полученного двумерного массива
Для ИндексКолонки = 0 По ВсегоКолонок - 1 Цикл
// Передаем массив значений всей колонки целиком в соответствующую колонку ТЗ
ТЗ.ЗагрузитьКолонку(Данные[ИндексКолонки], ИндексКолонки);
КонецЦикла;
Этот подход позволяет загрузить файл размером в десятки тысяч строк менее чем за 2–3 секунды. Единственным ограничением данного метода является необходимость наличия установленного Microsoft Excel на стороне компьютера, где выполняется данный код (клиентский компьютер или Windows-сервер 1С).
Что делать, если мы работаем на сервере без установленного пакета Microsoft Office или хотим отказаться от COM-технологий в пользу стабильности системы? Платформа 1С предлагает встроенный инструмент — ТабличныйДокумент, который умеет "нативно" считывать файлы Excel методом Прочитать().
Многие разработчики критикуют этот метод за медлительность, однако в 99% случаев виноват не сам метод чтения файла платформой, а последующий неоптимальный алгоритм обхода ячеек табличного документа. Если выполнять обход через ТабДок.Область(Строка, Колонка).Текст, то скорость импорта будет такой же низкой, как и при построчном COM-обходе.
Проанализируем крайне эффективное решение этой проблемы с использованием недокументированной возможности объекта ПостроительЗапроса. Построитель запроса способен использовать в качестве источника данных область табличного документа и мгновенно конвертировать ее в полноценную таблицу значений без каких-либо циклов.
Разберем этот процесс по шагам:
// Инициализируем и читаем файл в Табличный Документ
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(Объект.ПутьКФайлу);
// Используем ПостроительЗапроса для быстрой конвертации
Построитель = Новый ПостроительЗапроса;
// Создаем описание источника данных на основе всей области табличного документа
ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область());
Построитель.ИсточникДанных = ИсточникДанных;
// Заполняем настройки построителя (он автоматически определит структуру колонок)
Построитель.ЗаполнитьНастройки();
// Выполняем построение и выгружаем результат в Таблицу Значений
Построитель.Выполнить();
ТаблицаИзExcel = Построитель.Результат.Выгрузить();
Преимущества данного метода:
В современных реалиях, когда сервера 1С все чаще разворачиваются на базе операционных систем семейства Linux, классические COM-соединения становятся недоступными. Хотя метод с использованием ТабличногоДокумента решает большинство задач, на сверхбольших объемах данных (сотни тысяч строк) он может потреблять значительный объем оперативной памяти сервера. В таких случаях программисты используют альтернативные низкоуровневые или внешние подходы.
Вариант А: Прямой парсинг структуры ZIP/XML (OpenXML)
Современный файл формата .xlsx представляет собой обычный ZIP-архив. Если переименовать расширение файла в .zip и распаковать его, мы обнаружим древовидную структуру XML-файлов. Данные ячеек хранятся в файлах листов (например, xl/worksheets/sheet1.xml), а текстовые значения (строки) вынесены в отдельный оптимизированный словарь общего пользования xl/sharedStrings.xml для экономии места.
С помощью встроенных объектов 1С ЧтениеZipФайла и последовательного чтения через ЧтениеXML мы можем распаковать документ во временный каталог и быстро считать XML-структуру напрямую. Последовательное чтение XML (SAX-парсер) практически не загружает оперативную память, так как не строит дерево элементов DOM в памяти, а читает файл "потоком". Этот способ считается самым производительным на платформе 1С для Linux-систем при обработке гигантских файлов.
Вариант Б: Использование внешних утилит и скриптов на Python
Еще один популярный подход в Linux-среде — вызов легковесных скриптов на Python через системную команду ЗапуститьПриложение. Python имеет чрезвычайно быстрые и оптимизированные библиотеки для работы с большими данными, такие как pandas или openpyxl.
Скрипт на стороне операционной системы считывает исходный файл Excel за секунды, выполняет необходимую первичную фильтрацию и сохраняет данные в простой текстовый формат .csv или структурированный .json. Затем платформа 1С мгновенно считывает готовый текстовый файл стандартными средствами чтения текста. Это позволяет перенести тяжелые вычислительные процессы по парсингу структуры Excel из рабочего процесса 1С в отдельный изолированный системный поток.
При организации импорта данных важно четко разделять задачи: нужно ли нам получить итоговый результат вычислений или же саму математическую формулу, записанную в ячейке Excel.
Особенности импорта формул:
Range.Value в COM-объекте, Excel возвращает в 1С уже вычисленное значение ячейки. Если в ячейке была прописана сложная формула вычисления скидки или суммирования диапазона, результатом которой стало число 1500, в 1С придет именно готовое число 1500.Value не поможет. В этом случае в COM-объекте следует обращаться к свойствам ячеек Formula или FormulaLocal (последнее возвращает формулу с локализованными именами функций на языке пользователя, например, "СУММ" вместо "SUM"). При этом пакетное чтение через диапазон придется усложнить или отказаться от него в пользу детального анализа.ТабличныйДокумент при вызове метода Прочитать() также импортирует только статические вычисленные текстовые представления ячеек. Сами формулы Excel внутрь табличного документа 1С не переносятся и не могут быть вычислены силами самой платформы без интеграции с движком таблиц.Подводя итог, выбор технологии импорта должен основываться на архитектуре вашего решения. Если вы работаете в Windows-окружении и вам важна максимальная скорость "в один клик" — используйте связку COM-диапазона и COMSafeArray. Если перед вами стоит задача построения надежного кроссплатформенного кода, не зависящего от установленного офисного ПО, — лучшим решением станет использование встроенного ТабличногоДокумента в связке с ПостроителемЗапроса.