В процессе разработки отчетов и обработок в 1С часто возникает задача обогащения данных, полученных из базы, информацией из внешних источников, например, из файлов Microsoft Excel. Рассмотрим типичный случай: у нас есть обработка, которая с помощью запроса формирует таблицу с данными о продажах и остатках товаров. Нам необходимо добавить в эту таблицу еще одну колонку, «Нормативный день», и заполнить ее значениями из Excel-файла, сопоставляя товары по наименованию.
Дополнительно разберем связанную проблему, возникшую у автора: как в одном запросе получить данные о продажах за прошлый год и при этом увидеть актуальные остатки товаров на текущую дату, а не на конец прошлого года.
Основная идея заключается в том, чтобы сначала выполнить основной запрос и получить данные из информационной базы, а затем, вторым шагом, прочитать Excel-файл и обновить полученную таблицу, заполнив новую колонку.
Проанализируем и реализуем пошаговый алгоритм, предложенный на форуме.
Прежде всего, в реквизитах обработки необходимо предусмотреть саму таблицу для хранения данных и колонку для импортируемых значений. Допустим, у нас есть реквизит обработки РезультатОбработки с типом ТаблицаЗначений. В него мы выгружаем результат запроса. Нам нужно убедиться, что в этой таблице есть колонка "НормативныйДень" с числовым типом.
Если результат запроса выгружается в таблицу значений, добавить колонку можно методом Вставить() объекта Колонки.
// ... после выполнения запроса
ТаблицаРезультат = РезультатЗапроса.Выгрузить();
// Добавляем новую колонку, если ее еще нет
Если ТаблицаРезультат.Колонки.Найти("НормативныйДень") = Неопределено Тогда
ТаблицаРезультат.Колонки.Вставить("НормативныйДень", Новый ОписаниеТипов("Число"));
КонецЕсли;
На форме обработки создадим команду (кнопку), при нажатии на которую будет открываться диалог выбора файла. Этот код выполняется на клиенте.
&НаКлиенте
Процедура ЗагрузитьИзФайла(Команда)
// Открываем диалог выбора файла
Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Диалог.Фильтр = "Файл Excel (*.xlsx)|*.xlsx|Файл Excel 97-2003 (*.xls)|*.xls";
Диалог.МножественныйВыбор = Ложь;
Диалог.Заголовок = "Выберите файл с нормативными днями";
Оповещение = Новый ОписаниеОповещения("ПослеВыбораФайла", ЭтотОбъект);
Диалог.Показать(Оповещение);
КонецПроцедуры
&НаКлиенте
Процедура ПослеВыбораФайла(ВыбранныеФайлы, ДополнительныеПараметры) Экспорт
Если ВыбранныеФайлы = Неопределено Или ВыбранныеФайлы.Количество() = 0 Тогда
Возврат;
КонецЕсли;
ИмяФайла = ВыбранныеФайлы[0];
// Вызываем серверную процедуру для чтения и обработки файла
ПрочитатьИЗаполнитьНаСервере(ИмяФайла);
КонецПроцедуры
На сервере мы прочитаем выбранный файл в объект ТабличныйДокумент. Этот способ универсален и не требует установленного на сервере Microsoft Excel.
Для эффективного сопоставления данных считаем информацию из Excel в промежуточную структуру данных — Соответствие. Ключом будет наименование товара, а значением — нормативный день. Это позволит избежать медленных вложенных циклов при больших объемах данных.
&НаСервере
Процедура ПрочитатьИЗаполнитьНаСервере(ПутьКФайлу)
// 1. Читаем данные из Excel в промежуточную структуру
ДанныеИзФайла = Новый Соответствие;
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу);
// Предполагаем, что в Excel данные в первых двух колонках, начиная со второй строки
// Колонка 1 - Наименование товара
// Колонка 2 - Нормативный день
Для НомерСтроки = 2 По ТабДок.ВысотаТаблицы Цикл
НаименованиеТовара = СокрЛП(ТабДок.Область(НомерСтроки, 1).Текст);
ЗначениеНормы = ТабДок.Область(НомерСтроки, 2).Значение;
Если НЕ ПустаяСтрока(НаименованиеТовара) Тогда
// Если значение нормы не число, можно его пропустить или установить 0
Попытка
ЧисловоеЗначение = Число(ЗначениеНормы);
ДанныеИзФайла.Вставить(НаименованиеТовара, ЧисловоеЗначение);
Исключение
// Обработка ошибки, если в ячейке не число
КонецПопытки;
КонецЕсли;
КонецЦикла;
// 2. Обновляем основную таблицу данными из файла
ОбновитьТаблицуРезультатов(ДанныеИзФайла);
КонецПроцедуры
Теперь, имея данные из файла в удобной структуре, обойдем основную таблицу нашей обработки (РезультатОбработки) и заполним новую колонку.
&НаСервере
Процедура ОбновитьТаблицуРезультатов(ДанныеИзФайла)
// Объект.РезультатОбработки - это реквизит формы типа ТаблицаЗначений
Для Каждого СтрокаТаблицы Из Объект.РезультатОбработки Цикл
// Ищем товар из нашей таблицы в данных, загруженных из файла
НаименованиеТовара = СтрокаТаблицы.Товар.Наименование;
ЗначениеНормы = ДанныеИзФайла.Получить(НаименованиеТовара);
// Если нашли соответствие, записываем значение
Если ЗначениеНормы <> Неопределено Тогда
СтрокаТаблицы.НормативныйДень = ЗначениеНормы;
Иначе
СтрокаТаблицы.НормативныйДень = 0; // Или оставляем пустым
КонецЕсли;
КонецЦикла;
КонецПроцедуры
Важный момент: сопоставление по наименованию может быть ненадежным. Если в Excel "Яблоко Голден", а в 1С "Яблоко 'Голден'", прямое сравнение строк не сработает. В идеале, в Excel-файл нужно добавлять уникальный идентификатор товара (артикул или код), по которому и производить поиск.
Рассмотрим вторую проблему: запрос возвращает остатки на прошлую дату вместо текущей. Проанализируем исходный фрагмент запроса.
...
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(&НачПериодГод, &КонПериодГод, Период, Склад = &Склад) КАК ПродажиОбороты
ПО Товары.Ссылка = ПродажиОбороты.Товар
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Остатки.Остатки(&КонПериод, Склад = &Склад) КАК ОстаткиОстатки
ПО Товары.Ссылка = ОстаткиОстатки.Товар
...
Причина проблемы в том, что для виртуальной таблицы Остатки.Остатки используется параметр &КонПериод. Судя по коду установки параметров, эта переменная содержит дату из прошлого года, которая используется для расчета продаж.
// Авторский код, где устанавливаются параметры
Запрос.УстановитьПараметр("КонПериодГод", (КонПериод-(60*60*24*365)));
Запрос.УстановитьПараметр("НачПериодГод", (НачПериод-(60*60*24*365)));
...
Запрос.УстановитьПараметр("КонПериод", КонПериод);
Виртуальные таблицы регистра — это, по сути, независимые подзапросы. Каждая из них использует те параметры, которые ей переданы. Чтобы получить остатки на текущую дату, необходимо передать в таблицу остатков именно ее, а не дату из прошлого.
Для этого введем новый параметр в запрос, например, &АктуальнаяДата.
Исправленный текст запроса:
ВЫБРАТЬ
СУММА(ЕСТЬNULL(ПродажиОбороты.КоличествоОборот, 0)) КАК ПрошлыйГод,
ЕСТЬNULL(ОстаткиОстатки.КоличествоОстаток, 0) КАК Остаток,
Товары.Ссылка КАК Товар
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(&НачПериодГод, &КонПериодГод, , Склад = &Склад) КАК ПродажиОбороты
ПО Товары.Ссылка = ПродажиОбороты.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&АктуальнаяДата, Склад = &Склад) КАК ОстаткиОстатки
ПО Товары.Ссылка = ОстаткиОстатки.Номенклатура
СГРУППИРОВАТЬ ПО
Товары.Ссылка,
ЕСТЬNULL(ОстаткиОстатки.КоличествоОстаток, 0)
Примечание: в оригинальном запросе была ошибка в группировке (группировка по агрегируемому полю КолОборот). В исправленном варианте мы группируемся по товару и полю остатков, которое не агрегируется.
Теперь перед выполнением запроса нужно установить значение для нового параметра:
// ...
Запрос.УстановитьПараметр("Склад", Склад);
// Устанавливаем актуальную дату для получения остатков
Запрос.УстановитьПараметр("АктуальнаяДата", ТекущаяДата());
// Устанавливаем параметры для периода продаж прошлого года
Запрос.УстановитьПараметр("КонПериодГод", ДобавитьМесяц(КонПериод, -12)); // Более корректный способ
Запрос.УстановитьПараметр("НачПериодГод", ДобавитьМесяц(НачПериод, -12)); // расчета прошлогодней даты
//...
РезультатЗапроса = Запрос.Выполнить();
Таким образом, мы разделили параметры для разных виртуальных таблиц: Продажи.Обороты будет использовать даты прошлого года для расчета оборотов, а ТоварыНаСкладах.Остатки — актуальную дату для получения остатков, что полностью соответствует поставленной задаче.