Приветствуем вас! В практике каждого разработчика 1С периодически возникает задача: загрузить данные из внешнего файла Excel и на их основе обновить реквизиты в уже существующих документах базы данных (например, при вводе начальных остатков или корректировке аналитики). На первый взгляд, задача кажется тривиальной. Однако, когда объем обрабатываемых данных составляет от 1 000 до 10 000 документов, а каждый документ содержит одну или несколько табличных частей, неоптимальный алгоритм может превратить пятиминутную операцию в многочасовое зависание системы.
В этой статье мы подробно и по шагам разберем, как сделать этот процесс максимально быстрым, корректным и профессиональным с точки зрения стандартов разработки «1С:Предприятие».
Прежде чем обновлять документы, данные из файла Excel необходимо прочитать в оперативную память 1С — как правило, в объект типа ТаблицаЗначений. Проанализируем существующие методы чтения, выясним их плюсы и минусы, а также выберем наиболее производительные.
Метод 1. Кроссплатформенный Native-метод (Чтение через Табличный документ)
Начиная с версии платформы 1С 8.3.6, система умеет читать файлы формата XLSX напрямую, без использования внешних библиотек и установленного Microsoft Office. Это идеальный вариант для работы в клиент-серверном варианте, особенно если сервер 1С работает под управлением ОС Linux. Прочитав файл в ТабличныйДокумент, мы можем быстро преобразовать его в ТаблицаЗначений с помощью объекта ПостроительЗапроса. Этот способ исключает медленный поэлементный обход ячеек.
Рассмотрим подробнее программный код этого метода:
ТабДок = Новый ТабличныйДокумент;
// Читаем файл напрямую в табличный документ на стороне сервера
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
// Используем ПостроительЗапроса для моментальной конвертации в ТЗ
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область());
Построитель.ЗаполнитьНастройки();
Построитель.Выполнить();
ТаблицаДанных = Построитель.Результат.Выгрузить();
Этот метод работает чрезвычайно быстро и стабильно, поскольку вся обработка файлов XML (из которых состоит XLSX) происходит на уровне платформы в оптимизированном бинарном коде.
Метод 2. Оптимизированный COM-объект (Variant Array)
Обычный поэлементный обход ячеек через COM-соединение Excel.Application (например, циклическое обращение к Лист.Cells(Строка, Колонка).Value) — это самый медленный и нестабильный способ. Причина кроется в накладных расходах на межпроцессное взаимодействие (IPC) между процессом 1С и внешним приложением Excel при каждом обращении к ячейке. На 10 000 строк это превращается в десятки тысяч медленных вызовов.
Если обойтись без COM-объекта по каким-то причинам нельзя, мы можем оптимизировать чтение, забрав весь диапазон данных за одно обращение в виде многомерного массива (Variant Array). Выясним, как это реализовать:
ExcelПриложение = Новый COMОбъект("Excel.Application");
Книга = ExcelПриложение.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Sheets(1);
// Получаем все данные заполненного диапазона в один массив за один вызов
МассивДанных = Лист.UsedRange.Value.Выгрузить();
Книга.Close(Ложь);
ExcelПриложение.Quit();
После получения массива мы обходим его в оперативной памяти средствами самой 1С, что работает в десятки раз быстрее, чем прямое чтение ячеек через интерфейс COM.
Метод 3. Чтение через ADO (ActiveX Data Objects)
Этот способ позволяет работать с файлом Excel как со сторонней реляционной базой данных с помощью стандартных SQL-запросов. Он очень быстр, но требует наличия установленного провайдера Microsoft Access Database Engine на сервере или компьютере пользователя (что исключает кроссплатформенность).
Посмотрим на пример строки подключения и запроса:
Соединение = Новый COMОбъект("ADODB.Connection");
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";
Соединение.Open(СтрокаПодключения);
RecordSet = Новый COMОбъект("ADODB.Recordset");
// Обращаемся к Листу1 как к таблице базы данных
ЗапросSQL = "SELECT [Артикул], [Цена], [Количество] FROM [Лист1$]";
RecordSet.Open(ЗапросSQL, Соединение);
// Далее загружаем полученный RecordSet в ТаблицуЗначений 1С
Параметр HDR=YES указывает на то, что первая строка листа содержит заголовки колонок, к которым мы можем обращаться в тексте SQL-запроса.
Предположим, мы успешно прочитали файл Excel и сформировали в памяти 1С объект ТаблицаЗначений (ТЗ) с уникальными ключевыми полями связи (например, НазваниеОС и ИнвентНомер) и новыми значениями реквизитов, которые необходимо записать.
Как поступить правильнее с точки зрения архитектуры СУБД?
НайтиПоРеквизиту()). Делать поисковые запросы к СУБД в цикле — это грубая ошибка, которая мгновенно снижает производительность системы из-за постоянных одиночных обращений к базе данных.Для того чтобы использовать ТаблицаЗначений в запросе как виртуальную таблицу, у её колонок обязательно должны быть жестко определены типы данных. Опишем структуру нашей ТЗ:
ТЗ_Данные = Новый ТаблицаЗначений;
ТЗ_Данные.Колонки.Добавить("НазваниеОС", Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(150)));
ТЗ_Данные.Колонки.Добавить("ИнвентНомер", Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(50)));
ТЗ_Данные.Колонки.Добавить("Амортизация", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
// Заполняем ТЗ_Данные строками из файла Excel...
Теперь спроектируем запрос, который связывает нашу ТЗ с документами ввода остатков и их табличными частями. Допустим, нам необходимо изменить значение реквизита в табличной части (в которой всегда содержится одна строка):
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ВремТаблица.НазваниеОС КАК НазваниеОС,
| ВремТаблица.ИнвентНомер КАК ИнвентНомер,
| ВремТаблица.Амортизация КАК НоваяАмортизация,
| Док.Ссылка КАК ДокументСсылка,
| ТЧ_Амортизация.Амортизация КАК ТекущаяАмортизация
|ИЗ
| &ВнешнийИсточник КАК ВремТаблица
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ВводНачальныхОстатков КАК Док
| ПО Док.РеквизитНазвание = ВремТаблица.НазваниеОС
| И Док.РеквизитИнвентНомер = ВремТаблица.ИнвентНомер
| ЛЕВОЕ СОЕДИНЕНИЕ Документ.ВводНачальныхОстатков.ОС КАК ТЧ_Амортизация
| ПО ТЧ_Амортизация.Ссылка = Док.Ссылка
| И ТЧ_Амортизация.НомерСтроки = 1
|ГДЕ
| // Отсекаем строки, в которых значения уже совпадают с новыми
| ТЧ_Амортизация.Амортизация <> ВремТаблица.Амортизация";
Запрос.УстановитьПараметр("ВнешнийИсточник", ТЗ_Данные);
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
Обратим особое внимание на условие в блоке ГДЕ ТЧ_Амортизация.Амортизация <> ВремТаблица.Амортизация. Это критически важное условие, обеспечивающее принцип идемпотентности (проверку на избыточность записи). Если в документе уже установлена нужная сумма амортизации, мы исключаем его из выборки. Это убережет базу данных от лишней и самой дорогостоящей операции — перезаписи объекта на диск.
Физическая перезапись измененного документа на диск СУБД — это самая ресурсоемкая операция. Чтобы ускорить ее в десятки раз, мы должны применить три правила оптимизации:
1. Использование режима загрузки данных
Перед вызовом метода Записать() у объекта документа необходимо установить флаг ОбменДанными.Загрузка = Истина. Это отключает проверки проведения, подписки на события и всю тяжелую прикладную бизнес-логику конфигурации, которая не должна срабатывать при простом техническом исправлении реквизитов.
2. Проведение без лишней нагрузки
Если документ уже был проведен, нам нужно сохранить его статус проведения. Записывать объект нужно в режиме РежимЗаписиДокумента.Запись, если изменяются чисто информационные реквизиты, не влияющие на регистры. Если же изменения влияют на движения, записываем в режиме РежимЗаписиДокумента.Проведение, но благодаря установленному свойству Загрузка = Истина проведение выполнится максимально быстро и без блокирующих проверок.
3. Пакетные транзакции
Запись каждого документа по отдельности инициирует индивидуальную транзакцию в СУБД. Это создает колоссальную нагрузку на дисковую подсистему сервера из-за постоянных операций подтверждения (Commit). Однако объединение всех 10 000 документов в одну гигантскую транзакцию — тоже плохая идея, поскольку это намертво заблокирует таблицы базы данных для других пользователей на все время работы обработки. Оптимальное решение — разбивать запись на пакеты (например, по 500–1000 документов в одной транзакции).
Разберем готовый шаблон кода, реализующий пакетную обработку измененных документов:
СчетчикДокументов = 0;
РазмерПакетнойТранзакции = 500;
НачатьТранзакцию();
Попытка
Для Каждого СтрокаВыборки Из РезультатЗапроса Цикл
// Получаем объект документа для изменения
ДокОбъект = СтрокаВыборки.ДокументСсылка.ПолучитьОбъект();
// Проверяем наличие строк в табличной части
Если ДокОбъект.ОС.Количество() > 0 Тогда
// Меняем реквизит в первой строке табличной части
ДокОбъект.ОС[0].Амортизация = СтрокаВыборки.НоваяАмортизация;
КонецЕсли;
// Включаем режим загрузки для отключения лишних проверок и триггеров
ДокОбъект.ОбменДанными.Загрузка = Истина;
// Сохраняем документ в зависимости от его исходного статуса проведения
Если ДокОбъект.Проведен Тогда
ДокОбъект.Записать(РежимЗаписиДокумента.Проведение);
Иначе
ДокОбъект.Записать(РежимЗаписиДокумента.Запись);
КонецЕсли;
СчетчикДокументов = СчетчикДокументов + 1;
// Разделяем транзакцию на пакеты по 500 документов
Если СчетчикДокументов % РазмерПакетнойТранзакции = 0 Тогда
ЗафиксироватьТранзакцию();
НачатьТранзакцию();
КонецЕсли;
КонецЦикла;
// Фиксируем остаток транзакции, если он есть
Если ТранзакцияАктивна() Тогда
ЗафиксироватьТранзакцию();
КонецЕсли;
Сообщить("Обработка успешно завершена! Изменено документов: " + СчетчикДокументов);
Исключение
// В случае ошибки откатываем все незафиксированные изменения пакета
Если ТранзакцияАктивна() Тогда
ОтменитьТранзакцию();
КонецЕсли;
ЗаписьЖурналаРегистрации("ЗагрузкаExcel", УровеньЖурналаРегистрации.Ошибка,,, ПодробноеПредставлениеОшибки(ИнформацияОбОшибке()));
ВызватьИсключение "Произошла ошибка при обновлении документов. Текущий пакет изменений откачен. Подробности записаны в Журнал регистрации.";
КонецПопытки;
Мы детально проанализировали процесс загрузки данных из внешнего файла Excel и последующего обновления существующих документов в 1С. Использование кроссплатформенного чтения табличного документа, типизация ТаблицаЗначений для обработки в запросе, сопоставление данных на сервере за одно обращение и пакетная запись документов с отключением бизнес-логики через ОбменДанными.Загрузка = Истина позволяют сократить время выполнения задачи с нескольких часов до нескольких секунд.
Применяйте эти подходы в своей ежедневной работе, чтобы ваши решения всегда оставались быстрыми, надежными и соответствовали лучшим стандартам разработки на платформе «1С:Предприятие 8»!