Работа с прайс-листами крупных поставщиков часто превращается в техническую проблему для разработчика 1С. Когда файл Excel весит десятки мегабайт и содержит более 100 000 строк, стандартные методы последовательного чтения ячеек через COM-объект показывают крайне низкую производительность — загрузка может длиться часами. В данной статье мы разберем наиболее эффективные способы решения этой задачи, проанализируем причины медленной работы и научимся использовать современные инструменты платформы 1С для ускорения процесса в десятки раз.
Рассмотрим типичную ошибку начинающих разработчиков: использование Excel.Application для обхода каждой ячейки в цикле. Каждый вызов Cells(i, j).Value — это отдельное обращение через механизм COM, которое требует значительных ресурсов операционной системы. При 100 000 строк и 20 колонках программа должна выполнить 2 миллиона таких обращений. Добавим к этому затраты на отрисовку интерфейса Excel, если он не скрыт, и получим время ожидания более 1.5 часов.
Один из самых быстрых способов превратить данные из Excel в таблицу значений 1С — это использование метода ТабличныйДокумент.Прочитать(). Этот метод реализован на уровне платформы и считывает данные напрямую в память, минуя графический интерфейс Excel. Разберем по шагам, как реализовать этот алгоритм.
Для начала нам необходимо прочитать файл в объект ТабличныйДокумент, затем выделить область данных и с помощью объекта ПостроительОтчета мгновенно выгрузить её в таблицу значений.
&НаСервере
Функция ЗагрузкаНоменклатуры_ПрочитатьФайл(ИмяФайла)
// Создаем объект табличного документа
ТабДокумент = Новый ТабличныйДокумент;
// Читаем файл Excel напрямую
ТабДокумент.Прочитать(ИмяФайла);
// Определяем границы заполненной области
ВсегоСтрок = ТабДокумент.ВысотаТаблицы;
ВсегоКолонок = ТабДокумент.ШиринаТаблицы;
// Выделяем область данных
Область = ТабДокумент.Область(1, 1, ВсегоСтрок, ВсегоКолонок);
// Используем ОписаниеИсточникаДанных для связи области и построителя
ИсточникДанных = Новый ОписаниеИсточникаДанных(Область);
ПостроительОтчета = Новый ПостроительОтчета;
ПостроительОтчета.ИсточникДанных = ИсточникДанных;
// Выполняем построение и выгружаем результат в Таблицу Значений
ПостроительОтчета.Выполнить();
ТабЗначений = ПостроительОтчета.Результат.Выгрузить();
Возврат ТабЗначений;
КонецФункции
Проанализируем этот код: использование ПостроителяОтчета позволяет избежать циклов при заполнении таблицы значений. Платформа делает это внутренними методами, что сокращает время загрузки 100 000 строк до нескольких секунд.
Загрузить данные в таблицу значений (ТЗ) — это только половина дела. Нам необходимо сопоставить эти данные с существующим справочником Номенклатура и регистрами цен. Если мы будем искать каждую позицию методом Найти() в ТЗ на 100 000 строк, мы снова столкнемся с деградацией производительности.
Рассмотрим правильный подход: использование Таблицы Значений как временной таблицы в запросе. Для этого выполним следующие действия:
ЦеныНоменклатуры.Пример подготовки таблицы значений с индексами:
// Перед использованием ТЗ в запросе или поиске обязательно индексируем ее
ТабЗначений.Колонки.Артикул.Имя = "Артикул";
ТабЗначений.Индексы.Добавить("Артикул");
Важный момент: при работе в клиент-серверном варианте передачу больших объемов данных нужно минимизировать. Рекомендуется помещать файл во временное хранилище на клиенте и выполнять всю обработку на стороне сервера.
Если файл Excel имеет сложную структуру или вы хотите работать с ним как с базой данных, не загружая его целиком в память, рассмотрим использование ADODB.Connection. Этот метод позволяет выполнять SQL-запросы к листам Excel.
Выясним преимущество этого подхода: мы можем выбрать только нужные колонки еще на этапе чтения файла. Это экономит память сервера. Для работы потребуется провайдер Microsoft.ACE.OLEDB.12.0.
Connection = Новый COMОбъект("ADODB.Connection");
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ИмяФайла + ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";";
Connection.Open(ConnectionString);
Recordset = Новый COMОбъект("ADODB.Recordset");
// Выбираем только Артикул и Цену, фильтруя пустые строки
ТекстЗапроса = "SELECT Артикул, Цена FROM [Лист1$] WHERE Артикул IS NOT NULL";
Recordset.Open(ТекстЗапроса, Connection);
Пока НЕ Recordset.EOF Цикл
// Обработка данных построчно
Recordset.MoveNext();
КонецЦикла;
Когда данные сопоставлены, наступает этап записи. Создание 100 000 записей цен по одной — крайне долгий процесс из-за транзакционных издержек. Проанализируем рекомендации по ускорению записи:
Если даже ТабличныйДокумент не справляется (например, файл содержит миллионы строк), рассмотрим вариант сохранения Excel в формат CSV. Чтение текстового файла через объект ЧтениеТекста в 1С происходит практически мгновенно. 100 000 строк в формате CSV можно прочитать за 1-2 секунды, так как 1С не тратит время на разбор структуры XML, стилей и форматирования ячеек, которые присутствуют в XLSX.
Также не забывайте про корректное завершение работы с COM-объектами. Если вы все же используете COM, обязательно обнуляйте ссылки:
ExcelОбъект.Quit();
ExcelОбъект = Неопределено;
Подведем итог: для быстрой загрузки 100 000 строк оптимальным выбором будет метод ТабличныйДокумент.Прочитать() в сочетании с ПостроителемОтчета и последующей обработкой полученной ТЗ в запросе с использованием индексов. Это сократит время обработки с полутора часов до пары минут, обеспечив стабильную работу системы даже на средних по мощности серверах.