При периодической интеграции и обновлении данных в справочниках (например, в справочнике контрагентов) разработчики часто сталкиваются с файлами объемом в сотни тысяч строк. Попытка прочитать файл Excel размером более 600 000 строк классическим способом через COM-объект обычно приводит к зависанию сеанса на долгие часы или аварийному завершению процесса 1С с дампом памяти. Разберем по шагам, почему возникает эта проблема, как быстро прочитать файл и как оптимизировать последующую синхронизацию данных в базе.
Проанализируем типовую реализацию чтения через COM-объект Excel.Application. При поячеечном обращении к документу вида Worksheet.Cells(СмещениеСтрок, НомерКолонки).Value для файла на 600 000 строк и 8 колонок выполняется около 4,8 миллиона межпроцессных COM-вызовов. Каждый такой вызов требует переключения контекста между процессами операционной системы и маршалинга данных, что создает колоссальные накладные расходы процессора.
Кроме того, выделяются следующие критические факторы:
ТабличныйДокумент.Прочитать() приводит к ошибке нехватки памяти (Out of Memory) и аварийному завершению.Рассмотрим чтение данных из книги Excel как из реляционной базы данных с помощью технологии ADO. В этом сценарии операционная система обращается к файлу через OLE DB / ODBC драйвер. Нам не требуется запускать тяжелый процесс Excel.Application, а обход строк выполняется через объект Recordset по условию достижения конца набора данных без необходимости предварительного расчета точного диапазона ячеек.
Посмотрим на пример программной реализации подключения и чтения данных:
Соединение = Новый COMОбъект("ADODB.Connection");
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ФайлЗагрузки + ";Extended Properties=""Excel 12.0 XML;HDR=YES;IMEX=1;"";";
Попытка
Соединение.Open(СтрокаПодключения);
Исключение
Сообщить("Ошибка открытия файла через ADO: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
Команда.CommandText = "SELECT * FROM [Лист1$]";
НаборЗаписей = Новый COMОбъект("ADODB.Recordset");
НаборЗаписей.Open(Команда);
Пока НЕ НаборЗаписей.EOF Цикл
Наименование = СокрЛП(Строка(НаборЗаписей.Fields("Наименование").Value));
ИНН = СокрЛП(Строка(НаборЗаписей.Fields("ИНН").Value));
// Обработка прочитанных данных порциями
НаборЗаписей.MoveNext();
КонецЦикла;
НаборЗаписей.Close();
Соединение.Close();
Если отказаться от COM по каким-то причинам невозможно, проанализируем способ ускорения выборки за счет чтения диапазонов ячеек пачками, а не по отдельности. Обращение к свойству Value у целого диапазона ячеек возвращает двумерный массив COMSafeArray за один вызов, что сокращает миллионы межпроцессных переключений до нескольких десятков обращений.
Разберем пример чтения строк порциями по 10 000 строк:
РазмерПорции = 10000;
ТекущаяСтрока = НомерПервойСтроки;
Пока ТекущаяСтрока <= ВсегоСтрок Цикл
СтрокаОкончания = Мин(ТекущаяСтрока + РазмерПорции - 1, ВсегоСтрок);
// Считываем весь блок ячеек за один вызов к COM-объекту
Область = Worksheet.Range(Worksheet.Cells(ТекущаяСтрока, 1), Worksheet.Cells(СтрокаОкончания, КоличествоКолонок));
МассивДанных = Область.Value;
// Обрабатываем полученный двумерный массив в памяти 1С
Для ИндексСтроки = 1 По (СтрокаОкончания - ТекущаяСтрока + 1) Цикл
Наименование = СокрЛП(Строка(МассивДанных.GetValue(1, ИндексСтроки)));
ИНН = СокрЛП(Строка(МассивДанных.GetValue(2, ИндексСтроки)));
КонецЦикла;
ТекущаяСтрока = СтрокаОкончания + 1;
КонецЦикла;
Файлы формата .xlsx представляют собой ZIP-архив, содержащий набор XML-файлов. Чтобы прочитать сотни тысяч строк с минимальным расходом оперативной памяти, рассмотрим потоковый разбор. Этот способ полностью автономен, работает как на Windows, так и на Linux-серверах и не требует установки MS Excel или сторонних библиотек.
ЧтениеZipФайла во временный каталог на сервере.ЧтениеXML разбираем файл общих строк xl/sharedStrings.xml, в котором хранятся все текстовые значения книги.ЧтениеXML построчно считываем строки из файла данных конкретного листа xl/worksheets/sheet1.xml.Так как объект ЧтениеXML не загружает структуру документа в память целиком, разбор 600 000 строк выполняется со скоростью чтения текста с диска без риска аварийного завершения процесса по причине переполнения памяти.
Если перед загрузкой исходный файл сохраняется в текстовый формат с разделителями (CSV), то скорость обработки возрастает до максимума. Объект ЧтениеТекста с методом ПрочитатьСтроку() расходует фиксированный минимум оперативной памяти, считывая строки последовательно:
ТекстовыйФайл = Новый ЧтениеТекста(ИмяФайлаCSV, КодировкаТекста.UTF8);
СтрокаФайла = ТекстовыйФайл.ПрочитатьСтроку();
Пока СтрокаФайла <> Неопределено Цикл
МассивЗначений = СтрРазделить(СтрокаФайла, ";");
// Наполнение порции данных для пакетной обработки
СтрокаФайла = ТекстовыйФайл.ПрочитатьСтроку();
КонецЦикла;
ТекстовыйФайл.Закрыть();
Важно помнить: чтение файла — это лишь первая часть задачи. Вторая критическая точка производительности — сопоставление прочитанных данных с объектами в базе данных 1С и их сохранение. Выполнять поиск в цикле через запросы или методы менеджера справочника нельзя, иначе обработка 600 000 записей растянется на десятки часов. Разберем правила оптимизации серверной части:
Справочник.Контрагенты по полям поиска (ИНН, КПП). За одно обращение к СУБД мы получаем массив ссылок, требующих изменения, и список новых контрагентов.Объект.ОбменДанными.Загрузка = Истина;. Это предотвратит вызов тяжелых проверок модулей, подписок на события и блокировку зависимых регистров.НачатьТранзакцию() и ЗафиксироватьТранзакцию(). Это сохраняет стабильный размер журнала транзакций СУБД и исключает длительные монопольные блокировки таблиц.ФоновыеЗадания.Выполнить().