Зависание системы при импорте данных из файлов Microsoft Excel — классическая проблема, с которой сталкиваются многие разработчики на платформе 1С:Предприятие. Ситуация, когда загрузка всего 10–20 строк намертво «вешает» сеанс пользователя до такой степени, что задачу приходится снимать через диспетчер задач Windows, сигнализирует о грубых архитектурных ошибках в коде обработки.
В этой статье мы подробно проанализируем, почему типовые механизмы загрузки через COM-объект работают медленно, разберем ключевые ошибки позиционирования кода между клиентом и сервером и разработаем максимально быстрое, современное решение без использования сторонних библиотек.
Проанализируем исходный код, ставший причиной проблемы, и пошагово разберем методы оптимизации загрузки.
---
Рассмотрим исходный алгоритм, представленный автором темы. В нем кроются сразу три критические проблемы производительности, каждая из которых способна замедлить работу системы в десятки раз.
Ошибка 1. Вызовы сервера внутри клиентского цикла (Network Roundtrips)
Обратим внимание на тело цикла обработки строк в исходной процедуре:
Для СчСтроки = 2 По ФайлСтрок Цикл
Сотр = СокрЛП(Excel.Cells(СчСтроки, НомерКолонкиСотрудника).Value);
Инфа = СокрЛП(Excel.Cells(СчСтроки, НомерКолонкиДатаВремя).Value);
// ВНИМАНИЕ: Серверный вызов на каждой итерации!
Раб = НайтиСотрПоИмя(Сотр);
НоваяСтрока = Объект.СписокВиновников.Добавить;
НоваяСтрока.Сотрудник = Раб;
// ВНИМАНИЕ: Еще один серверный вызов на каждой итерации!
Структура = ПолучитьДанныеРаба(НоваяСтрока.Сотрудник);
НоваяСтрока.Отдел = Структура.Должность;
НоваяСтрока.ВремяПрихода = ИнФа;
...
КонецЦикла;
Процедура выполняется &НаКлиенте. При этом на каждой строчке файла система совершает два полноценных сетевых обращения к серверу 1С: НайтиСотрПоИмя() и ПолучитьДанныеРаба(). Если в файле содержится хотя бы 100 строк, платформа выполнит 200 последовательных клиент-серверных вызовов. В условиях работы через тонкий клиент по глобальной сети (интернет, VPN) задержка на один сетевой пакет (ping) может составлять от 10 до 150 мс. В итоге простейший цикл растягивается на минуты, создавая иллюзию «мертвого» зависания интерфейса.
Ошибка 2. Построчное обращение к ячейкам через COM-соединение
Использование конструкции Excel.Cells(Строка, Колонка).Value в цикле заставляет операционную систему для каждой ячейки выполнять ресурсоемкий маршалинг данных между процессами 1С и Microsoft Excel. На больших объемах данных это приводит к колоссальным потерям времени.
Ошибка 3. Интерактивные диалоги и утечка процессов EXCEL.EXE
Если в процессе COM-чтения Excel сталкивается с какими-либо внутренними предупреждениями (например, проверка лицензии, битые связи, вопросы о сохранении файла при закрытии), он выводит модальное окно. Поскольку процесс запущен в невидимом режиме, это окно блокирует выполнение кода 1С, оставаясь невидимым для пользователя. Программа зависает навсегда, а в памяти сервера или клиента плодятся «зомби-процессы» EXCEL.EXE.
---
Для достижения максимальной скорости загрузки мы должны полностью отказаться от выполнения бизнес-логики на клиенте и перенести чтение данных на сторону сервера. Наш план оптимизации выглядит следующим образом:
ТабличныйДокумент. Это полностью избавляет нас от зависимости от установленного Microsoft Office (код будет работать даже на серверах под управлением Linux).ПостроительЗапроса.Разберем реализацию этого алгоритма детально.
---
Напишем процедуру выбора файла. Вместо устаревшего и медленного COM-обхода мы подготовим двоичные данные файла и передадим адрес во временном хранилище на сервер.
&НаКлиенте
Процедура ВыбратьИЗагрузитьФайл(Команда)
ПараметрыДиалога = Новый ПараметрыДиалогаПомещенияФайлов;
ПараметрыДиалога.Фильтр = "Файлы Excel (*.xls;*.xlsx)|*.xls;*.xlsx";
ПараметрыДиалога.Заголовок = "Выберите файл для импорта данных";
ОписаниеОповещения = Новый ОписаниеОповещения("ПослеВыбораФайла", ЭтотОбъект);
НачатьПомещениеФайлаНаСервер(ОписаниеОповещения, , , , ПараметрыДиалога, УникальныйИдентификатор);
КонецПроцедуры
&НаКлиенте
Процедура ПослеВыбораФайла(РезультатПомещения, ДополнительныеПараметры) Экспорт
Если РезультатПомещения = Неопределено Или Не РезультатПомещения.ИмяФайлаПомещено Тогда
Возврат;
КонецЕсли;
// Файл успешно загружен в хранилище. Передаем адрес серверной процедуре
ЗагрузитьДанныеНаСервере(РезультатПомещения.Адрес);
КонецПроцедуры
---
Теперь на сервере мы извлекаем двоичные данные, сохраняем их во временный файл и считываем структуру документа. Для этого используем встроенный метод Прочитать объекта ТабличныйДокумент.
&НаСервере
Процедура ЗагрузитьДанныеНаСервере(АдресХранилища)
// Получаем двоичные данные из хранилища
ДвоичныеДанные = ПолучитьИзВременногоХранилища(АдресХранилища);
// Формируем уникальное имя временного файла на сервере
ИмяФайлаНаСервере = ПолучитьИмяВременногоФайла("xlsx");
ДвоичныеДанные.Записать(ИмяФайлаНаСервере);
ТабДок = Новый ТабличныйДокумент;
Попытка
// Читаем Excel напрямую средствами платформы 1С
ТабДок.Прочитать(ИмяФайлаНаСервере, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
УдалитьФайлы(ИмяФайлаНаСервере);
ВызватьИсключение "Не удалось прочитать файл Excel: " + ОписаниеОшибки();
КонецПопытки;
// Удаляем временный файл с диска сервера, он нам больше не нужен
Попытка
УдалитьФайлы(ИмяФайлаНаСервере);
Исключение
// Игнорируем возможные ошибки блокировки файла операционной системой
КонецПопытки;
// Преобразуем ТабличныйДокумент в ТаблицуЗначений для быстрой обработки
ТаблицаДанных = ПолучитьТаблицуИзТабДока(ТабДок);
// Обрабатываем полученные данные пакетным запросом
ОбработатьЗагруженныеДанные(ТаблицаДанных);
КонецПроцедуры
---
Для того чтобы быстро перевести строки ТабличногоДокумента в стандартную ТаблицуЗначений, воспользуемся эффективным приемом с использованием объекта ПостроительЗапроса. Этот метод работает в сотни раз быстрее ручного обхода ячеек в цикле.
&НаСервере
Функция ПолучитьТаблицуИзТабДока(ТабДок)
// Определяем заполненную область данных
ОбластьДанных = ТабДок.Область(1, 1, ТабДок.ВысотаТаблицы, ТабДок.ШиринаТаблицы);
ПЗ = Новый ПостроительЗапроса;
ПЗ.ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьДанных);
ПЗ.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
Попытка
ПЗ.ЗаполнитьНастройки();
ПЗ.Выполнить();
Таблица = ПЗ.Результат.Выгрузить();
Исключение
// Если структура шапки сложная, может потребоваться ручной разбор
Таблица = Новый ТаблицаЗначений;
КонецПопытки;
Возврат Таблица;
КонецФункции
---
Вместо того чтобы искать каждого сотрудника по отдельности с помощью метода НайтиПоНаименованию(), мы выгрузим все имена из таблицы Excel, передадим их в единственный запрос к СУБД и получим готовые ссылки на элементы справочника.
Проанализируем этот важнейший этап:
&НаСервере
Процедура ОбработатьЗагруженныеДанные(ТаблицаДанных)
// Предположим, ПостроительЗапроса автоматически назвал колонки Колонка1, Колонка2 и т.д.
// Переименуем колонки для удобства работы или обратимся по индексам.
// Для надежности перенесем данные в типизированную таблицу.
МассивИмен = ТаблицаДанных.ВыгрузитьКолонку(0); // Первая колонка содержит ФИО сотрудников
// Удаляем пустые значения и дубликаты из массива для оптимального запроса
ОбщегоНазначенияКлиентСервер.СвернутьМассив(МассивИмен);
// Одним запросом выбираем всех подходящих сотрудников и их должности
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Сотрудники.Ссылка КАК Сотрудник,
| Сотрудники.Наименование КАК ФИО,
| Сотрудники.Должность КАК Должность
|ИЗ
| Справочник.Сотрудники КАК Сотрудники
|ГДЕ
| Сотрудники.Наименование В(&МассивИмен)";
Запрос.УстановитьПараметр("МассивИмен", МассивИмен);
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
// Создаем соответствие (кэш в оперативной памяти) для моментального поиска
КэшСотрудников = Новый Соответствие;
Для Каждого СтрокаРез Из РезультатЗапроса Цикл
СтруктураДанных = Новый Структура("Ссылка, Должность", СтрокаРез.Сотрудник, СтрокаРез.Должность);
КэшСотрудников.Вставить(СокрЛП(ВРег(СтрокаРез.ФИО)), СтруктураДанных);
КонецЦикла;
// Очищаем целевую табличную часть перед заполнением
Объект.СписокВиновников.Очистить();
// Теперь обходим таблицу значений локально на сервере без обращений к базе данных и файлам!
Для Каждого СтрокаФайла Из ТаблицаДанных Цикл
ИмяСотрудника = СокрЛП(СтрокаФайла[0]);
Если ПустаяСтрока(ИмяСотрудника) Тогда
Продолжить;
КонецЕсли;
НоваяСтрока = Объект.СписокВиновников.Добавить;
// Поиск в кэше (оперативная память)
ДанныеСотрудника = КэшСотрудников.Получить(ВРег(ИмяСотрудника));
Если ДанныеСотрудника <> Неопределено Тогда
НоваяСтрока.Сотрудник = ДанныеСотрудника.Ссылка;
НоваяСтрока.Отдел = ДанныеСотрудника.Должность;
Иначе
// Если сотрудник не найден в базе данных
НоваяСтрока.Сотрудник = Справочники.Сотрудники.ПустаяСсылка();
НоваяСтрока.Отдел = "Сотрудник не найден в базе!";
КонецЕсли;
// Читаем дату-время и определяем день недели локально
Попытка
НоваяСтрока.ВремяПрихода = Дата(СтрокаФайла[1]);
// Используем системную функцию Формат для получения дня недели
НоваяСтрока.ДеньНедели = Формат(НоваяСтрока.ВремяПрихода, "ДФ=дддд");
Исключение
НоваяСтрока.ВремяПрихода = Дата(1, 1, 1);
НоваяСтрока.ДеньНедели = "Некорректная дата";
КонецПопытки;
КонецЦикла;
КонецПроцедуры
---
В процессе обсуждения на форуме возникла ошибка: Процедура или функция с указанным именем не определена (СтрСоединить) на сервере. Эта проблема характерна для старых релизов платформы 1С:Предприятие 8.3 (ниже версии 8.3.6) или при работе конфигурации в режиме совместимости с более старыми версиями (например, 8.2).
Выясним причину: встроенная функция СтрСоединить() была добавлена в платформу начиная с релиза 8.3.6. Если ваша конфигурация запущена в режиме совместимости, данный метод будет недоступен компилятору.
Чтобы обойти это ограничение, мы можем использовать готовую функцию из Библиотеки Стандартных Подсистем (БСП) либо написать собственный простой аналог для склейки строк из массива.
Вариант с использованием БСП:
Вместо СтрСоединить(Массив, Разделитель) используем следующий вызов:
СтроковыеФункцииКлиентСервер.СтрокаИзМассиваПодстрок(МассивЗаголовков[Кол-1], "_")
Вариант с собственной функцией-аналогом:
Если в вашей конфигурации отсутствует БСП, добавьте в модуль функцию совмещения строк:
Функция ОбъединитьМассивСтрок(МассивИсточник, Разделитель = "_") Экспорт
Результат = "";
Для Каждого Элемент Из МассивИсточник Цикл
Результат = Результат + ?(ПустаяСтрока(Результат), "", Разделитель) + Строка(Элемент);
КонецЦикла;
Возврат Результат;
КонецФункции
---
Мы полностью переработали архитектуру загрузки данных. Давайте сравним показатели старого кода и нового серверного решения:
Было в исходном коде:
Стало после оптимизации:
ТабличныйДокумент (работает на Windows/Linux без необходимости устанавливать Excel).ТаблицуЗначений в один шаг через ПостроительЗапроса.Применяя данные подходы к проектированию интеграций, вы обеспечите стабильную, быструю и отказоустойчивую работу ваших прикладных решений даже на колоссальных объемах входящих файлов.