Довольно часто в практике разработчика 1С возникает задача, когда необходимо сопоставить данные из внешнего файла (например, спецификацию или прайс-лист в формате Excel) с объектами, уже существующими в базе данных. Основная сложность заключается в том, чтобы сделать это эффективно, не создавая лишних объектов в метаданных (справочников или регистров сведений) и не выполняя запросы в цикле, что крайне негативно сказывается на производительности. В этой статье мы подробно разберем, как объединить эти два источника данных с помощью механизма временных таблиц.
Для того чтобы данные из файла «встретились» с данными из информационной базы в рамках одного запроса, нам необходимо пройти через несколько этапов. Разберем наш план действий:
ТабличныйДокумент.ТабличныйДокумент в объект ТаблицаЗначений.Первым делом нам нужно извлечь данные из файла. В платформе 8.3 предусмотрен удобный метод Прочитать(), который позволяет работать с файлами .xlsx без необходимости устанавливать Microsoft Office на стороне сервера. Проанализируем пример кода:
ТабДокумент = Новый ТабличныйДокумент;
Попытка
ТабДокумент.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщить("Не удалось прочитать файл: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
Использование параметра СпособЧтенияЗначенийТабличногоДокумента.Значение позволяет нам считывать именно значения (числа, даты), а не просто текст, отображаемый в ячейке, что упрощает дальнейшую обработку.
Самый быстрый способ превратить область табличного документа в таблицу значений — использовать ПостроительОтчета. Этот объект умеет автоматически формировать структуру колонок на основе данных. Рассмотрим подробнее, как это реализовать:
// Определяем область, которую будем загружать (вся заполненная часть листа)
ПоследняяСтрока = ТабДокумент.ВысотаТаблицы;
ПоследняяКолонка = ТабДокумент.ШиринаТаблицы;
ОбластьЯчеек = ТабДокумент.Область(1, 1, ПоследняяСтрока, ПоследняяКолонка);
// Используем описание источника данных для построителя
ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьЯчеек);
ПостроительОтчета = Новый ПостроительОтчета;
ПостроительОтчета.ИсточникДанных = ИсточникДанных;
// Выгружаем результат в таблицу значений
ПостроительОтчета.Выполнить();
ТабЗначений = ПостроительОтчета.Результат.Выгрузить();
Важное замечание: При таком способе названия колонок в ТабЗначений будут соответствовать заголовкам из первой строки файла Excel. Если заголовки содержат пробелы или спецсимволы, 1С заменит их на допустимые для имен колонок символы.
Это критически важный шаг, который часто пропускают новички. Запрос в 1С не может корректно работать с таблицей значений, если у ее колонок не задан тип данных (в этом случае тип считается «Произвольным»). Если мы попытаемся передать такую таблицу в запрос, система выдаст ошибку при попытке поместить ее во временную таблицу.
Выясним, как правильно задать типы данных программно:
// Создаем массив для описания типов
МассивТипСтрока = Новый Массив;
МассивТипСтрока.Добавить(Тип("Строка"));
ОписаниеТипаСтрока = Новый ОписаниеТипов(МассивТипСтрока, , Новый КвалификаторыСтроки(100));
МассивТипЧисло = Новый Массив;
МассивТипЧисло.Добавить(Тип("Число"));
ОписаниеТипаЧисло = Новый ОписаниеТипов(МассивТипЧисло, , , Новый КвалификаторыЧисла(15, 2));
// Принудительно устанавливаем типы для колонок
ТабЗначений.Колонки.Колонка1.ТипЗначения = ОписаниеТипаСтрока; // Например, Артикул
ТабЗначений.Колонки.Колонка2.ТипЗначения = ОписаниеТипаЧисло; // Например, Цена
Теперь, когда у нас есть подготовленная ТаблицаЗначений, мы можем передать ее в запрос. Чтобы «соединить» ее с данными базы данных, мы будем использовать временную таблицу. Рассмотрим структуру такого запроса:
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
// Шаг 1: Помещаем внешние данные во временную таблицу
Запрос.Текст =
"ВЫБРАТЬ
| ВнешнийИсточник.Артикул КАК Артикул,
| ВнешнийИсточник.Цена КАК ЦенаИзФайла
|ПОМЕСТИТЬ ВТ_ВнешниеДанные
|ИЗ
| &ВнешнийИсточник КАК ВнешнийИсточник";
Запрос.УстановитьПараметр("ВнешнийИсточник", ТабЗначений);
Запрос.Выполнить();
// Шаг 2: Выполняем соединение с данными БД
Запрос.Текст =
"ВЫБРАТЬ
| ВТ_ВнешниеДанные.Артикул КАК Артикул,
| ВТ_ВнешниеДанные.ЦенаИзФайла КАК ЦенаИзФайла,
| ЕСТЬNULL(Номенклатура.Ссылка, ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)) КАК НоменклатураСсылка,
| ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК ЦенаВБазе
|ИЗ
| ВТ_ВнешниеДанные КАК ВТ_ВнешниеДанные
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура
| ПО ВТ_ВнешниеДанные.Артикул = Номенклатура.Артикул
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныНоменклатурыСрезПоследних
| ПО Номенклатура.Ссылка = ЦеныНоменклатурыСрезПоследних.Номенклатура";
РезультатЗапроса = Запрос.Выполнить();
ТаблицаРезультат = РезультатЗапроса.Выгрузить();
Проанализируем ситуацию: мы использовали ЛЕВОЕ СОЕДИНЕНИЕ. Это позволяет нам увидеть все строки из нашего файла Excel, даже если соответствующая номенклатура не была найдена в базе данных. С помощью функции ЕСТЬNULL мы корректно обрабатываем ситуации отсутствия данных, заменяя их на пустые ссылки или нули.
Если в вашем файле Excel содержатся тысячи строк, работа запроса может замедлиться. Чтобы этого избежать, всегда индексируйте временные таблицы по полям связи. В нашем примере это поле Артикул. Добавим индекс в текст первого запроса:
// ... код создания временной таблицы ...
"ВЫБРАТЬ
| ВнешнийИсточник.Артикул КАК Артикул,
| ВнешнийИсточник.Цена КАК ЦенаИзФайла
|ПОМЕСТИТЬ ВТ_ВнешниеДанные
|ИЗ
| &ВнешнийИсточник КАК ВнешнийИсточник
|ИНДЕКСИРОВАТЬ ПО
| Артикул";
// ... продолжение запроса ...
Важные рекомендации:
ВЫБРАТЬ РАЗЛИЧНЫЕ при помещении данных во временную таблицу.ТаблицаЗначений не может быть передана с клиента на сервер напрямую — передавайте ее как результат функции, выполняемой на сервере.ADO, так как это потребляет значительно меньше оперативной памяти по сравнению с ТабличнымДокументом.Таким образом, мы решили задачу объединения данных без изменения метаданных конфигурации. Этот метод является «золотым стандартом» при разработке обработок загрузки данных, так как он обеспечивает высокую скорость работы и гибкость настройки условий сопоставления объектов.