Оптимизация загрузки Excel-файлов

Введение

В современной бизнес-среде работа с Excel-файлами стала неотъемлемой частью повседневной деятельности. Однако при обработке массивов данных большого объёма возникают серьёзные проблемы с производительностью системы.

Исходная ситуация

На проекте каждый месяц загружают Excel-файл, содержащего около 40 000 строк для загрузки цен товаров. Первоначальное время обработки составляло 2,5 часа, что существенно замедляло работу системы.

Анализ проблем производительности

Исходный код загрузки выглядел следующим образом:

НомерСтроки = 2;

Пока ЗначениеЗаполнено(Sheet.Cells(НомерСтроки, 1).Value) Цикл
    НоваяСтрока = ТаблицаПрайса.Добавить();
    НоваяСтрока.КодНСИ = Sheet.Cells(НомерСтроки, СтолбецКодНСИ).Value;
    НоваяСтрока.Цена = Sheet.Cells(НомерСтроки, СтолбецЦена).Value;
    НаименованиеВидаЦен = Sheet.Cells(НомерСтроки, СтолбецВидыЦен).Value;
    НоваяСтрока.ВидЦены = ВидЦенаПоНаименованию(НаименованиеВидаЦена);
КонецЦикла;

Функция ВидЦенаПоНаименованию(НаименованиеВидаЦена)
Возврат Справочники.ВидыЦен.НайтиПоНаименованию(НаименованиеВидаЦена);
КонецФункции

Выявленные проблемы

1. Последовательное чтение ячеек методом Sheet.Cells(НомерСтроки, НомерСтолбца).Value:
   Время чтения одной ячейки — 0,03 секунды.
   При 120 000 ячеек (40 000 строк × 3 колонки) общее время — 60 минут.
2. Проверка заполненности каждой ячейки на каждой итерации — 20 минут.
3. Поиск по наименованию в справочнике:
   Каждый запрос занимает 0,09 секунды, при 40 000 строк — 60 минут.

Решения и оптимизация

1. Чтение колонок таблицы вместо последовательного чтения каждой ячейки:
   Sheet.Range(Sheet.Cells(НачальнаяСтрока,СтолбецЦеноваяГруппа), Sheet.Cells(КоличествоСтрок, СтолбецЦеноваяГруппа)).Value.Выгрузить()
   Время чтения столбца — 0,1 секунды.
2. Определение количества строк через метод:
   Sheet.Cells(1,1).SpecialCells(11).Row
   Время чтения — 0,04 секунды.
3. Оптимизация поиска через использование соответствий:
• Время поиска в соответствии — 0,000015 секунды.
  
• Сокращение времени с 20 минут до 0,6 секунды.

Итоговый оптимизированный код

КоличествоСтрок = Sheet.Cells(1,1).SpecialCells(11).Row;

МассивКодНСИ = Sheet.Range(Sheet.Cells(2,СтолбецКодНСИ), Sheet.Cells(КоличествоСтрок, СтолбецКодНСИ)).Value.Выгрузить();
МассивКодНСИ = МассивКодНСИ[0];

МассивЦена = Sheet.Range(Sheet.Cells(2,СтолбецЦена), Sheet.Cells(КоличествоСтрок, СтолбецЦена)).Value.Выгрузить();
МассивЦена = МассивЦена[0];

МассивВидЦены = Sheet.Range(Sheet.Cells(2,СтолбецВидыЦен), Sheet.Cells(КоличествоСтрок, СтолбецВидыЦен)).Value.Выгрузить();
МассивВидЦены = МассивВидЦены[0];

Счетчик = 0;

СоответствиеВидовЦен = СоответствиеВидовЦен();

Пока Счетчик <> КоличествоСтрок Цикл
    НоваяСтрока = ТаблицаПрайса.Добавить();
    НоваяСтрока.КодНСИ = МассивКодНСИ[Счетчик];
    НоваяСтрока.Цена = МассивЦена[Счетчик];
    НаименованиеВидаЦен = МассивВидЦены[Счетчик];
    НоваяСтрока.ВидЦены = СоответствиеВидовЦен.Получить(НаименованиеВидаЦен);
    Счетчик = Счетчик + 1;
КонецЦикла;

Функция СоответствиеВидовЦен()
    СоответствиеВидовЦен = Новый Соответствие;

    Запрос = Новый Запрос;
    Запрос.Текст =
        "ВЫБРАТЬ
        | ВидыЦен.Ссылка КАК Ссылка,
        | ВидыЦен.Наименование КАК Наименование
        |ИЗ
        | Справочник.ВидыЦен КАК ВидыЦен";

    РезультатЗапроса = Запрос.Выполнить();
    ВыборкаДетальныеЗаписи = РезультатЗапроса.Выбрать();

    Пока ВыборкаДетальныеЗаписи.Следующий() Цикл
        СоответствиеВидовЦен.Вставить(ВыборкаДетальныеЗаписи.Наименование, ВыборкаДетальныеЗаписи.Ссылка);
    КонецЦикла;

    Возврат СоответствиеВидовЦен;
КонецФункции

Результаты оптимизации

Ключевые достижения:

• Время обработки сократилось с 2,5 часа до 1 минуты
• Ускорение процесса в 150 раз
• Существенное снижение нагрузки на систему

Выводы

Предложенные методы оптимизации могут быть успешно применены в любых проектах, где требуется:

• Обработка больших Excel-файлов
• Массовая загрузка данных, как для постоянной загрузки, так и для разовой миграции данных

Такой подход позволяет значительно повысить производительность системы при минимальных изменениях в архитектуре решения.