При интеграции 1С с Microsoft Excel разработчики часто сталкиваются с критическим падением производительности при считывании информации. Классический сценарий с открытием COM-объекта и чтением файла ячейка за ячейкой в цикле может занимать по полсекунды на операцию. При объеме файла всего в пару тысяч строк загрузка затягивается на десятки минут. Разберем, почему возникает эта проблема, и рассмотрим несколько эффективных способов многократного ускорения загрузки.
Проанализируем механику работы кода, при котором к ячейкам обращаются по очереди через ExcelЛист.Cells(Счетчик, Колонка).Value. Главная причина задержек кроется в особенностях межпроцессного взаимодействия (IPC) операционной системы Windows.
Каждое обращение к свойству или методу COM-объекта через интерфейс IDispatch заставляет операционную систему выполнять маршалинг данных и переключать контекст выполнения между процессом 1С (1cv8.exe или 1cv8c.exe) и процессом Excel (EXCEL.EXE). Если в таблице 1000 строк и 5 колонок, 1С сделает 5000 отдельных переключений контекста. Накладные расходы на эти вызовы многократно превышают время физического извлечения данных из памяти.
Наиболее быстрое решение при сохранении существующей архитектуры на COM — запросить весь заполненный диапазон листа за один единственный вызов. Для этого обратимся к свойству диапазона листа UsedRange и свойству Value2.
Обратим внимание на важное различие между Value и Value2. Свойство Value2 работает существенно быстрее и не искажает данные, так как в отличие от Value оно не пытается принудительно преобразовать значения ячеек в типы даты или валюты, а отдает неформатированные числа с плавающей точкой и строки напрямую во внутренний двумерный массив COMSafeArray.
Рассмотрим по шагам реализацию пакетного считывания с правильной инициализацией и корректным завершением работы процесса Excel:
// Отключаем визуальное отображение и системные предупреждения
ExcelПриложение = Новый COMОбъект("Excel.Application");
ExcelПриложение.Visible = Ложь;
ExcelПриложение.DisplayAlerts = Ложь;
ExcelПриложение.ScreenUpdating = Ложь;
ExcelПриложение.EnableEvents = Ложь;
// Отключаем автоматический пересчет формул
ExcelПриложение.Calculation = -4135; // xlCalculationManual
Попытка
// Открываем книгу в режиме "только чтение"
ExcelКнига = ExcelПриложение.Workbooks.Open(ПутьКФайлуЗагрузки, , Истина);
ExcelЛист = ExcelКнига.Sheets(1);
// Получаем двумерный COMSafeArray всех занятых ячеек за один вызов
// Метод Выгрузить() переводит COM-массив в структуру массивов 1С
МассивДанных = ExcelЛист.UsedRange.Value2.Выгрузить();
ExcelКнига.Close(Ложь);
Исключение
ExcelПриложение.Quit();
ExcelПриложение = Неопределено;
ВызватьИсключение;
КонецПопытки;
// Обязательно корректно закрываем приложение Excel
ExcelПриложение.Quit();
ExcelПриложение = Неопределено;
Посмотрим, как работать с полученными данными. Платформа 1С преобразует двумерный COMSafeArray в фиксированный массив, где элементами являются массивы колонок. Теперь весь обход строк происходит исключительно в оперативной памяти 1С со скоростью сотен тысяч строк в секунду:
КоличествоКолонок = МассивДанных.Количество();
Если КоличествоКолонок > 0 Тогда
КоличествоСтрок = МассивДанных[0].Количество();
// В SafeArray нумерация элементов после метода Выгрузить() начинается с 0
Для ИндексСтроки = 0 По КоличествоСтрок - 1 Цикл
// Считываем нужные колонки текущей строки
Артикул = МассивДанных[0][ИндексСтроки];
Наименование = МассивДанных[1][ИндексСтроки];
// Выполняем обработку считанных значений
КонецЦикла;
КонецЕсли;
Важный момент: если процесс Excel не выгрузить явными командами Workbooks.Close() и Application.Quit() с последующим обнулением переменных присваиванием Неопределено, процесс EXCEL.EXE останется висеть в памяти операционной системы до полного закрытия клиентского сеанса 1С.
Если требуется избавиться от зависимости от установленного Microsoft Office (например, при выполнении кода на 64-разрядном сервере или под управлением Linux), применим встроенный механизм платформы 1С.
Начиная с версии 8.3.6, объект ТабличныйДокумент умеет самостоятельно считывать файлы форматов .xlsx и .xls без сторонних драйверов.
Разберем типичную проблему этого метода: при чтении артикулов, содержащих дефисы или спецсимволы (например, маски вида ххх-хххххх-хх), ячейки могут отображаться искаженными. Это происходит из-за того, что свойство ячейки Текст считывает примененный пользовательский формат отображения Excel, а не само значение. Чтобы получить корректные исходные данные, необходимо считывать именно свойство Значение.
Рассмотрим пример правильного обхода строк через табличный документ:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлуЗагрузки);
ВсегоСтрок = ТабДок.ВысотаТаблицы;
ВсегоКолонок = ТабДок.ШиринаТаблицы;
Для НомерСтроки = 1 По ВсегоСтрок Цикл
ОбластьЯчейки = ТабДок.Область(НомерСтроки, 1);
// Проверяем, содержит ли ячейка чистое значение
Если ОбластьЯчейки.СодержитЗначение Тогда
ЗначениеАртикула = СокрЛП(Строка(ОбластьЯчейки.Значение));
Иначе
ЗначениеАртикула = СокрЛП(ОбластьЯчейки.Текст);
КонецЕсли;
Если ПустаяСтрока(ЗначениеАртикула) Тогда
Продолжить;
КонецЕсли;
// Чтение последующих колонок строки
Наименование = ТабДок.Область(НомерСтроки, 2).Текст;
КонецЦикла;
Ограничение табличного документа: встроенный парсер 1С считывает из файла предварительно рассчитанный кэш значений. Если файл формировался сторонней программой, которая записала формулы, но не рассчитала их результаты, такие ячейки окажутся пустыми.
Любой файл формата .xlsx является стандартным zip-архивом, внутри которого хранятся XML-файлы со структурой книги и данными. Этот способ — самый быстрый из всех существующих на платформе 1С, полностью кроссплатформенный и не зависящий от разрядности сервера.
Проанализируем структуру архива .xlsx:
xl/sharedStrings.xml — общая таблица уникальных строк файла. Большинство текстовых ячеек в Excel хранятся в виде числовых индексов, ссылающихся на этот справочник.xl/worksheets/sheet1.xml — структура конкретного листа с ячейками, их координатами и числовыми значениями либо ссылками на справочник строк.Для реализации разбора используются базовые объекты платформы: ЧтениеZIPФайла и ЧтениеXML. Сначала распаковывается файл общих строк и считывается во внутренний массив 1С, затем потоковым чтением XML обрабатывается файл листа. Этот подход позволяет загружать сотни тысяч строк за считанные секунды даже на серверах под управлением Linux.
UsedRange.Value2.Выгрузить(). Это ускорит загрузку в десятки и сотни раз без переписывания архитектуры.ТабличныйДокумент.Прочитать(), обращаясь к свойствам СодержитЗначение и Значение ячеек.ЧтениеZIPФайла и парсинг XML-структуры листа.