Шаги:
1. Сперва откроем основную форму обработки и в командную панель добавим свою команду (кнопку) для открытия нашей формы с настройками подключения (свойства на рисунке справа)
Также создаем действие для нашей команды
Процедура КоманднаяПанельФормыВнешниеИсточникиДанных(Кнопка)
Если мФормаВнешнихИсточников.Открыта() Тогда
мФормаВнешнихИсточников.Закрыть();
Иначе
мФормаВнешнихИсточников.Открыть();
КонецЕсли;
КонецПроцедуры
"мФормаВнешнихИсточников" это переменная модуля формы
Из переменной "мФормаПараметров" сделаем экспортную, добавив "Экспорт" в конец.
С модулем основной формы пока что все.
2. Теперь добавим новые необходимые нам формы в обработку
Сразу же для формы "ФормаТаблицаСДаннымиВнешнегоИсточника" добавим реквизит формы с типом "ТаблицаЗначений"
и выведем на форму со стандартной командной панелью,
больше ничего с этой формой не делаем, оставляем в покое.
Переходим в форму "ФормаВнешнихИсточников", так она должна выглядеть в итоге,
добавляем реквизит формы "ПараметрыПодключений" с типом "ТаблицаЗначений"
Можно сразу добавить в модуль формы глобальные переменные, в начало и конец модуля
Добавим на форму командную панель для табличного поля (без автозаполнения, см. рис.), сначала команд не будет совсем, будем поочередно добавлять, так она выглядит в итоге.
Добавляем кнопку добавления на ком. панель (рис.),
с действием "КнопкаДобавитьИсточник",
Процедура КнопкаДобавитьИсточник(Кнопка)
// Открыть диалог выбора файла .xls|.xlsx|.mxl
Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Диалог.Заголовок = "Выберите файл";
Диалог.Фильтр = "Файлы excel (*.xls, *.xlsx)|*.xls;*.xlsx|Табличный документ (*.mxl)|*.mxl";
Если Диалог.Выбрать() Тогда
ПутьКФайлу = Диалог.ПолноеИмяФайла;
ТабДокумент = Новый ТабличныйДокумент;
Попытка
ТабДокумент.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщить(ОписаниеОшибки());
КонецПопытки;
Если ТабДокумент.Области.Количество() = 0 Тогда
Сообщить("Не найдено ни одной области данных для выборки в выбранном документе.");
Возврат;
КонецЕсли;
Если ПараметрыПодключений.Найти(ПутьКФайлу, "ПутьКФайлу") <> Неопределено Тогда
Предупреждение("Файл с таким расположением пути уже присутствует в списке.
|Сначала удалите предыдущий.");
Возврат;
КонецЕсли;
ПостроительЗапроса = Новый ПостроительЗапроса;
Попытка
ПостроительЗапроса.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДокумент.Область());
ПостроительЗапроса.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
ПостроительЗапроса.ЗаполнитьНастройки();
ПостроительЗапроса.Выполнить();
Результат = ПолучитьНормализованнуюТаблицу(ПостроительЗапроса.Результат.Выгрузить());
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;
Массив = Новый Массив;
Массив.Добавить("Все области данных"); // всегда по умолчанию
Для Каждого Область Из ТабДокумент.Области Цикл
Массив.Добавить(Область.Имя);
КонецЦикла;
НовоеИмя = ПодобратьУникальноеИмяПодключения("ИмяПодключения");
СоответствиеИменИОбластейИсточников.Вставить(НовоеИмя, Массив);
НоваяСтрока = ПараметрыПодключений.Добавить();
НоваяСтрока.Имя = НовоеИмя;
НоваяСтрока.ПутьКФайлу = ПутьКФайлу;
НоваяСтрока.ОбластьВыборки = Массив[0];
НоваяСтрока.Данные = Результат;
НоваяСтрока.Просмотр = "Просмотр";
// добавление в форму параметров
Параметры = ЭтаФорма.ВладелецФормы.мФормаПараметров.Параметры;
СтрокаПараметров = Параметры.Найти(НовоеИмя,"ИмяПараметра");
Если СтрокаПараметров = Неопределено Тогда
СтрокаПараметров = Параметры.Добавить();
СтрокаПараметров.ИмяПараметра = НовоеИмя;
СтрокаПараметров.ЗначениеПараметра = Результат;
КонецЕсли;
ЭлементыФормы.ПараметрыПодключений.ТекущаяСтрока = НоваяСтрока;
ЭлементыФормы.ПараметрыПодключений.ТекущаяКолонка = ЭлементыФормы.ПараметрыПодключений.Колонки.Имя;
ЭлементыФормы.ПараметрыПодключений.ИзменитьСтроку();
КонецЕсли;
КонецПроцедуры
добавляем следующую командную кнопку "Удалить", выбираем стандартное системное действие удаления, остальные стандартные команды по желанию.
Для табличного поля "ПараметрыПодключений" выставляем свойства и события как на рис.,
Код для события "ПараметрыПодключенийВыбор"
Процедура ПараметрыПодключенийВыбор(Элемент, ВыбраннаяСтрока, Колонка, СтандартнаяОбработка)
Если Колонка.Имя = "Просмотр" Тогда
Если мФормаПросмотраДанных.Открыта() Тогда
мФормаПросмотраДанных.Закрыть();
Иначе
// загрузим данные в таблицу на форме
мФормаПросмотраДанных.ЭлементыФормы.ТаблицаСДанными.Значение = ВыбраннаяСтрока.Данные;
мФормаПросмотраДанных.ЭлементыФормы.ТаблицаСДанными.СоздатьКолонки();
мФормаПросмотраДанных.Открыть();
КонецЕсли;
КонецЕсли;
КонецПроцедуры
Код для события "ПараметрыПодключенийПередУдалением"
Процедура ПараметрыПодключенийПередУдалением(Элемент, Отказ)
СоответствиеИменИОбластейИсточников.Удалить(Элемент.ТекущаяСтрока.Имя);
КонецПроцедуры
Код для события "ПараметрыПодключенийПриНачалеРедактирования"
Процедура ПараметрыПодключенийПриНачалеРедактирования(Элемент, НоваяСтрока, Копирование)
Если Элемент.ТекущаяКолонка.Имя = "Имя" Тогда
ПредыдущееИмяПодключения = Элемент.ТекущаяСтрока.Имя;
КонецЕсли;
КонецПроцедуры
далее, добавляем колонки в таблицу:
- Колонка "Имя", тип "Строка", событие - "ОкончаниеВводаТекста"
Код для события "ПараметрыПодключенийИмяОкончаниеВводаТекста"
Процедура ПараметрыПодключенийИмяОкончаниеВводаТекста(Элемент, Текст, Значение, СтандартнаяОбработка)
Если ПредыдущееИмяПодключения <> Текст Тогда
// Проверить уникальность имени
Если ПараметрыПодключений.Найти(Текст, "Имя") <> Неопределено Тогда
СтандартнаяОбработка = Ложь;
Предупреждение("Такое имя уже используется.");
Значение = ПредыдущееИмяПодключения;
ЭлементыФормы.ПараметрыПодключений.ИзменитьСтроку();
Возврат;
КонецЕсли;
СоответствиеИменИОбластейИсточников.Вставить(Текст, СоответствиеИменИОбластейИсточников[ПредыдущееИмяПодключения]);
СоответствиеИменИОбластейИсточников.Удалить(ПредыдущееИмяПодключения);
КонецЕсли;
КонецПроцедуры
- Колонка "ПутьКФайлу", тип "Строка",
- Колонка "ОбластьВыборки", тип "Строка", КнопкаСпискаВыбора "Истина", событие - "ПараметрыПодключенийОбластьВыборкиПриИзменении" и "ПараметрыПодключенийОбластьВыборкиНачалоВыбораИзСписка"
Код для события "ПараметрыПодключенийОбластьВыборкиПриИзменении"
Процедура ПараметрыПодключенийОбластьВыборкиПриИзменении(Элемент)
ЭлементыФормы.ПараметрыПодключений.ЗакончитьРедактированиеСтроки(Ложь);
Если мФормаПросмотраДанных.Открыта() Тогда
мФормаПросмотраДанных.Закрыть();
КонецЕсли;
// Заново сформировать данные построителем
ТабДокумент = Новый ТабличныйДокумент;
Попытка
ТабДокумент.Прочитать(ЭлементыФормы.ПараметрыПодключений.ТекущаяСтрока.ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщить(ОписаниеОшибки());
КонецПопытки;
ОбластьСтрокой = ЭлементыФормы.ПараметрыПодключений.ТекущаяСтрока.ОбластьВыборки;
ТекущаяОбласть = Неопределено;
Если ОбластьСтрокой = "Все области данных" Тогда
ТекущаяОбласть = ТабДокумент.Область();
Иначе
ТекущаяОбласть = ТабДокумент.Область(ОбластьСтрокой);
КонецЕсли;
ПостроительЗапроса = Новый ПостроительЗапроса;
Попытка
ПостроительЗапроса.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТекущаяОбласть);
ПостроительЗапроса.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
ПостроительЗапроса.ЗаполнитьНастройки();
ПостроительЗапроса.Выполнить();
Результат = ПолучитьНормализованнуюТаблицу(ПостроительЗапроса.Результат.Выгрузить());
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;
ЭлементыФормы.ПараметрыПодключений.ТекущаяСтрока.Данные = Результат;
КонецПроцедуры
Код для события "ПараметрыПодключенийОбластьВыборкиНачалоВыбораИзСписка"
Процедура ПараметрыПодключенийОбластьВыборкиНачалоВыбораИзСписка(Элемент, СтандартнаяОбработка)
МассивОбластейИсточника = СоответствиеИменИОбластейИсточников[ЭлементыФормы.ПараметрыПодключений.ТекущаяСтрока.Имя];
// Заполнить массив областями из соответствия
ЭлементыФормы.ПараметрыПодключений.Колонки.ОбластьВыборки.ЭлементУправления.СписокВыбора.ЗагрузитьЗначения(МассивОбластейИсточника);
КонецПроцедуры
- Колонка "Просмотр", тип "Строка", Гиперссылка "Истина", РежимРедактирования "Непосредственно".
Полный листинг модуля формы внешних источников
Перем СоответствиеИменИОбластейИсточников;
Перем ПредыдущееИмяПодключения;
Перем мФормаПросмотраДанных;
Процедура КнопкаДобавитьИсточник(Кнопка)
// Открыть диалог выбора файла .xls|.xlsx|.mxl
Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Диалог.Заголовок = "Выберите файл";
Диалог.Фильтр = "Файлы excel (*.xls, *.xlsx)|*.xls;*.xlsx|Табличный документ (*.mxl)|*.mxl";
Если Диалог.Выбрать() Тогда
ПутьКФайлу = Диалог.ПолноеИмяФайла;
ТабДокумент = Новый ТабличныйДокумент;
Попытка
ТабДокумент.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщить(ОписаниеОшибки());
КонецПопытки;
Если ТабДокумент.Области.Количество() = 0 Тогда
Сообщить("Не найдено ни одной области данных для выборки в выбранном документе.");
Возврат;
КонецЕсли;
Если ПараметрыПодключений.Найти(ПутьКФайлу, "ПутьКФайлу") <> Неопределено Тогда
Предупреждение("Файл с таким расположением пути уже присутствует в списке.
|Сначала удалите предыдущий.");
Возврат;
КонецЕсли;
ПостроительЗапроса = Новый ПостроительЗапроса;
Попытка
ПостроительЗапроса.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДокумент.Область());
ПостроительЗапроса.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
ПостроительЗапроса.ЗаполнитьНастройки();
ПостроительЗапроса.Выполнить();
Результат = ПолучитьНормализованнуюТаблицу(ПостроительЗапроса.Результат.Выгрузить());
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;
Массив = Новый Массив;
Массив.Добавить("Все области данных"); // всегда по умолчанию
Для Каждого Область Из ТабДокумент.Области Цикл
Массив.Добавить(Область.Имя);
КонецЦикла;
НовоеИмя = ПодобратьУникальноеИмяПодключения("ИмяПодключения");
СоответствиеИменИОбластейИсточников.Вставить(НовоеИмя, Массив);
НоваяСтрока = ПараметрыПодключений.Добавить();
НоваяСтрока.Имя = НовоеИмя;
НоваяСтрока.ПутьКФайлу = ПутьКФайлу;
НоваяСтрока.ОбластьВыборки = Массив[0];
НоваяСтрока.Данные = Результат;
НоваяСтрока.Просмотр = "Просмотр";
// добавление в форму параметров
Параметры = ЭтаФорма.ВладелецФормы.мФормаПараметров.Параметры;
СтрокаПараметров = Параметры.Найти(НовоеИмя,"ИмяПараметра");
Если СтрокаПараметров = Неопределено Тогда
СтрокаПараметров = Параметры.Добавить();
СтрокаПараметров.ИмяПараметра = НовоеИмя;
СтрокаПараметров.ЗначениеПараметра = Результат;
КонецЕсли;
ЭлементыФормы.ПараметрыПодключений.ТекущаяСтрока = НоваяСтрока;
ЭлементыФормы.ПараметрыПодключений.ТекущаяКолонка = ЭлементыФормы.ПараметрыПодключений.Колонки.Имя;
ЭлементыФормы.ПараметрыПодключений.ИзменитьСтроку();
КонецЕсли;
КонецПроцедуры
Функция ПолучитьНормализованнуюТаблицу(Источник)
// Скопируем вручную исходную таблицу в новую без типов Null
НормализованнаяКопия = Новый ТаблицаЗначений;
Для Каждого Колонка Из Источник.Колонки Цикл
МассивТипов = Колонка.ТипЗначения.Типы();
НоваяКолонка = НормализованнаяКопия.Колонки.Добавить(
Колонка.Имя,
Новый ОписаниеТипов(Колонка.ТипЗначения,, "Null"),
Колонка.Заголовок,
Колонка.Ширина);
КонецЦикла;
Для Каждого Строка Из Источник Цикл
ЗаполнитьЗначенияСвойств(НормализованнаяКопия.Добавить(), Строка);
КонецЦикла;
Источник.Очистить();
Возврат НормализованнаяКопия;
КонецФункции
Функция ПодобратьУникальноеИмяПодключения(ШаблонСтрока, Знач НомерПопыткиПоиска = 0)
ПопыткаПоиска = НомерПопыткиПоиска;
ПопыткаПоискаСтрокой = ?(ПопыткаПоиска = 0, "", Строка(ПопыткаПоиска));
Если ПараметрыПодключений.Найти(ШаблонСтрока + ПопыткаПоискаСтрокой, "Имя") <> Неопределено Тогда
ПопыткаПоиска = ПопыткаПоиска + 1;
ПопыткаПоискаСтрокой = Строка(ПопыткаПоиска);
Возврат ПодобратьУникальноеИмяПодключения(ШаблонСтрока, ПопыткаПоиска);
КонецЕсли;
Возврат ШаблонСтрока + ПопыткаПоискаСтрокой;
КонецФункции
Процедура ПараметрыПодключенийОбластьВыборкиНачалоВыбораИзСписка(Элемент, СтандартнаяОбработка)
МассивОбластейИсточника = СоответствиеИменИОбластейИсточников[ЭлементыФормы.ПараметрыПодключений.ТекущаяСтрока.Имя];
// Заполнить массив областями из соответствия
ЭлементыФормы.ПараметрыПодключений.Колонки.ОбластьВыборки.ЭлементУправления.СписокВыбора.ЗагрузитьЗначения(МассивОбластейИсточника);
КонецПроцедуры
Процедура ПараметрыПодключенийОбластьВыборкиПриИзменении(Элемент)
ЭлементыФормы.ПараметрыПодключений.ЗакончитьРедактированиеСтроки(Ложь);
Если мФормаПросмотраДанных.Открыта() Тогда
мФормаПросмотраДанных.Закрыть();
КонецЕсли;
// Заново сформировать данные построителем
ТабДокумент = Новый ТабличныйДокумент;
Попытка
ТабДокумент.Прочитать(ЭлементыФормы.ПараметрыПодключений.ТекущаяСтрока.ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщить(ОписаниеОшибки());
КонецПопытки;
ОбластьСтрокой = ЭлементыФормы.ПараметрыПодключений.ТекущаяСтрока.ОбластьВыборки;
ТекущаяОбласть = Неопределено;
Если ОбластьСтрокой = "Все области данных" Тогда
ТекущаяОбласть = ТабДокумент.Область();
Иначе
ТекущаяОбласть = ТабДокумент.Область(ОбластьСтрокой);
КонецЕсли;
ПостроительЗапроса = Новый ПостроительЗапроса;
Попытка
ПостроительЗапроса.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТекущаяОбласть);
ПостроительЗапроса.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
ПостроительЗапроса.ЗаполнитьНастройки();
ПостроительЗапроса.Выполнить();
Результат = ПолучитьНормализованнуюТаблицу(ПостроительЗапроса.Результат.Выгрузить());
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;
ЭлементыФормы.ПараметрыПодключений.ТекущаяСтрока.Данные = Результат;
КонецПроцедуры
Процедура ПередОткрытием(Отказ, СтандартнаяОбработка)
Если ПараметрыПодключений.Колонки.Найти("Данные") = Неопределено Тогда
ПараметрыПодключений.Колонки.Добавить("Данные", Новый ОписаниеТипов("ТаблицаЗначений")); // для хранения загруженных данных
КонецЕсли;
КонецПроцедуры
Процедура ПараметрыПодключенийИмяОкончаниеВводаТекста(Элемент, Текст, Значение, СтандартнаяОбработка)
Если ПредыдущееИмяПодключения <> Текст Тогда
// Проверить уникальность имени
Если ПараметрыПодключений.Найти(Текст, "Имя") <> Неопределено Тогда
СтандартнаяОбработка = Ложь;
Предупреждение("Такое имя уже используется.");
Значение = ПредыдущееИмяПодключения;
ЭлементыФормы.ПараметрыПодключений.ИзменитьСтроку();
Возврат;
КонецЕсли;
СоответствиеИменИОбластейИсточников.Вставить(Текст, СоответствиеИменИОбластейИсточников[ПредыдущееИмяПодключения]);
СоответствиеИменИОбластейИсточников.Удалить(ПредыдущееИмяПодключения);
КонецЕсли;
КонецПроцедуры
Процедура ПараметрыПодключенийПриНачалеРедактирования(Элемент, НоваяСтрока, Копирование)
Если Элемент.ТекущаяКолонка.Имя = "Имя" Тогда
ПредыдущееИмяПодключения = Элемент.ТекущаяСтрока.Имя;
КонецЕсли;
КонецПроцедуры
Процедура ПараметрыПодключенийВыбор(Элемент, ВыбраннаяСтрока, Колонка, СтандартнаяОбработка)
Если Колонка.Имя = "Просмотр" Тогда
Если мФормаПросмотраДанных.Открыта() Тогда
мФормаПросмотраДанных.Закрыть();
Иначе
// загрузим данные в таблицу на форме
мФормаПросмотраДанных.ЭлементыФормы.ТаблицаСДанными.Значение = ВыбраннаяСтрока.Данные;
мФормаПросмотраДанных.ЭлементыФормы.ТаблицаСДанными.СоздатьКолонки();
мФормаПросмотраДанных.Открыть();
КонецЕсли;
КонецЕсли;
КонецПроцедуры
Процедура ПараметрыПодключенийПередУдалением(Элемент, Отказ)
СоответствиеИменИОбластейИсточников.Удалить(Элемент.ТекущаяСтрока.Имя);
КонецПроцедуры
// Ключ - имя подключения, Значение - массив с областями источника подключения
СоответствиеИменИОбластейИсточников = Новый Соответствие;
ПредыдущееИмяПодключения = "";
мФормаПросмотраДанных = ОбработкаОбъект.ПолучитьФорму("ФормаТаблицаСданнымиВнешнегоИсточника", ЭтаФорма);
3. Финальная часть, возвращаемся в основную форму обработки, добавляем оставшийся код в процедуру "ВыполнитьЗапрос()",
весь код процедуры "ВыполнитьЗапрос()":
Процедура ВыполнитьЗапрос()
вСохранитьЗапросТекущейСтроки();
ОбъектЗапрос = Новый Запрос;
Для каждого СтрокаПараметров Из мФормаПараметров.Параметры Цикл
Если СтрокаПараметров.ЭтоВыражение Тогда
ОбъектЗапрос.УстановитьПараметр(СтрокаПараметров.ИмяПараметра, Вычислить(СтрокаПараметров.ЗначениеПараметра));
Иначе
ОбъектЗапрос.УстановитьПараметр(СтрокаПараметров.ИмяПараметра, СтрокаПараметров.ЗначениеПараметра);
// mairon меанир
// найти имя параметра в форме внешних источников, проверить тип на "ТаблицаЗначений" и установить данные параметра
Если мФормаВнешнихИсточников.ПараметрыПодключений.Найти(СтрокаПараметров.ИмяПараметра, "Имя") <> Неопределено
И СтрокаПараметров.ЗначениеПараметра = "ТаблицаЗначений" Тогда
ОбъектЗапрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
ОбъектЗапрос.УстановитьПараметр(СтрокаПараметров.ИмяПараметра,
мФормаВнешнихИсточников.ПараметрыПодключений.Найти(СтрокаПараметров.ИмяПараметра, "Имя").Данные);
КонецЕсли;
КонецЕсли;
КонецЦикла;
ОбъектЗапрос.Текст = СтрЗаменить(вПолучитьТекстЗапроса(Истина), "|", "");
Если ПустаяСтрока(ОбъектЗапрос.Текст) Тогда
Предупреждение("Не заполнен текст запроса!", 30);
Возврат;
КонецЕсли;
ДатаНачала = ТекущаяДата();
Попытка
мРезЗапроса = ОбъектЗапрос.Выполнить();
ДатаКонцаВыполнения = ТекущаяДата();
мТаблицаЗагружена = Ложь;
мСводнаяТаблицаЗагружена = Ложь;
вЗагрузитьРезультат();
ДатаКонца = ТекущаяДата();
Исключение
Предупреждение(Сред(ОписаниеОшибки(),69));
КонецПопытки;
КонецПроцедуры // ВыполнитьЗапрос()
Описание работы и нюансы:
для файлов .mxl необходимо иметь имя области, хотя бы одно (имя области можно назначить прямо в режиме предприятия)
для файлов excel области - это листы,
первой строчкой всегда определяются названия колонок, потом данные.
При добавлении подключения формируется уникальное Имя подключения, - это название параметра запроса, которое необходимо использовать в кач-ве параметра таблицы значений, т.е имя подключения должно совпадать с именем параметра в форме параметров запроса.
по гиперссылке в колонке "просмотр" можно смотреть какие данные были загружены и править их при необходимости (вручную прямо в таблице, отсюда они попадут в запрос).
Далее, необходимо сформировать шаблон текста запроса для вытягивания данных из параметра таблицы значений:
ВЫБРАТЬ
*
ПОМЕСТИТЬ ВТ
ИЗ
&ИмяПодключения КАК ИмяПодключения
Доработка и тестирование производилось на платформе версии 8.3.18.
