IE 2018

Запросы с использованием данных табличных файлов на примере xls

Программирование - Инструментарий

Эксель Excel запрос консоль запросов консоль запросов для управляемого приложения.

11
Когда ВПР недостаточно...

Представим себе ситуацию, при которой Вам надо совместить данные из файла excell и действующей базы на 1С. Впринципе можно выгрузить данные из 1С в тот же Эксель и "ПроВПРить". Но во-первых, нам могут понадобиться более продвинутые средства получения данных, чем ВПР, во-вторых, для сложных конструкций это долго, а в третьих, для порядочного 1С-ника это ФИ (не зря же мы изучали язык запросов! да и конструктор запросов- вещь крутая). Но сосредоточимся на главном - это получение данных из файла Эксель(или другого табличного файла, можно и какого-нибудь csv) и использование их в запросе.

Я использую следующую методику:

1) Сначала читаем этот файл и загоняем данные в таблицу значений (ТЗ).

2) Передаем полученную ТЗ как параметр запроса и создаем на основе нее временную таблицу (ВТ).

3) Используем полученную ВТ для дальнейших манипуляций с запросами (через 2-ой пакет запросов).

Для удобства я доработал Консоль запросов (для управляемых форм), чтобы она выполняла первые 2 пункта на автомате, читая файл и дописывая запрос до нужного нам вида. Первая строка таблицы используется как заголовки(Она обязательно должна быть!). Нам останется лишь в конструкторе запросов добавить второй пакет и в нем уже получить нужные нам выборки. В параметрах Вы можете назначить типы данных. Так же для функционала добавил исполняемый код для строк выборки. Все параметры и код сохраняются в файл вместе с запросом.

Обработку прилагаю, можете посмотреть, что да как сделано, и использовать вместо обычной консоли запросов. Или не использовать. Если обработка окажется полезной, доработаю её под другие типы файлов(csv например).

Чтобы добавить несколько таблиц, надо переименовать параметр с Таб например не Таб2 в параметрах и в самом запросе в первом пакете. так же переименовать временную таблицу, тогда после прочтения другого файла, будет добавлен новый пакет запросов и новый параметр.

-------------------------------------UPD-------------------------------------------------

Поработал с обработкой и допилил её до вполне юзабельного на мой взгляд вида:

  •  добавлены типы читаемых файлов, теперь: xls,xlsx,ods,mxl.
  • файлы теперь читаются быстрее и не требуют установленного Excel.
  • таблица по-нормальному подставляется в параметр, это особенно важно когда надо отредактировать типы данных полей (щелкнув на редактирование параметра).
  • для повторного выполнения запроса не надо каждый раз заново читать файл.
  • добавил поле для произвольного кода для обработки выборки(сохраняется вместе с запросом) .
  • добавил возможность использовать параметры в исполняемом коде.
  • добавил возможность запускать код в фоновом задании.
  • добавил возможность добавлять параметры из строки: в отладке для нужного параметра выполняете ЗначениеВСтрокуВнутр() и получившуюся строку вставляете по кнопке "из строки".
11

Скачать файлы

Наименование Файл Версия Размер
Консоль запросов для управляемого приложения с чтением табличных файлов и обработчиком результата
.epf 101,88Kb
05.06.18
14
.epf 1.52 101,88Kb 14 Скачать

См. также

Комментарии
Избранное Подписка Сортировка: Древо
1. necropunk 5 06.02.17 12:16 Сейчас в теме
Я эту задачу решал загрузкой табдока в построитель и забирая оттуда готовую ТЗ. Ну, а потом нашел, что в Инструментах Разработчика можно загружать таблицу из экселя прямо в параметры и эта проблема для меня исчезла полностью.
2. olgerd666 41 06.02.17 12:26 Сейчас в теме
(1)
в Инструментах Разработчика можно загружать таблицу из экселя прямо в параметры

извиняюсь за оффтоп. а как такое там выполнить можете написать?
3. necropunk 5 06.02.17 15:51 Сейчас в теме
(2) Консоль запросов, в ней пишете что-то типа
ВЫБРАТЬ
	ТабЭксель.Артикул,
	ТабЭксель.Цена
ПОМЕСТИТЬ ВТТаб
ИЗ
	&ТЗ КАК ТабЭксель
;
////////////////////////////////////////////////////////////­////////////////////


Заходите в параметры, нажимаете "Из запроса", появляется параметр ТЗ, тип таблица значений. Дважды щелкаем на словах "(0)Таблица значений" в колонке "Значение", открывается редактор таблицы значений. Сверху есть "Получить" и варианты "из файла", "из MXL" и "из запроса". Выбираем "из MXL", он просит выбрать файл с расширением MXL. Не верим ему, выбираем в типе файлов "Все файлы", выбираем файл Excel (Хорошо бы чтобы он был сделан по проавилам, то есть, в первой строке - названия столбцов, это сводит к минимуму дальнейшую обработку таблицы), он спрашивает хотим ли мы ограничить длину строк, и загружает таблицу. Пока с проблемами загрузки ни разу не сталкивался.
svilsa; abadonna83; yurii_host; fancy; olgerd666; +5 Ответить
4. necropunk 5 06.02.17 15:52 Сейчас в теме
(3) Ну, соответственно, в запросе надо будет написать поля, которые есть в таблице.
olgerd666; +1 Ответить
6. KazanKokos 7 07.02.17 11:26 Сейчас в теме
(3) Извиняюсь за оффтоп. Это же для консоли запроса а не в код? В коде какая разница откуда грузить. Грузим в ТЗ, раньше через com а теперь либо ODBC либо Табличный документ и скармливаем запросу. Или чет теперь поменялось?
7. necropunk 5 07.02.17 11:29 Сейчас в теме
(6) в консоли многим удобнее обрабатывать информацию, я, например, 1С знаю лучше, чем Excel, мне проще подавать данные на вход и уже по ним получать любую нужную информацию.
А в коде - да, в коде все по прежнему

Excel = Новый COMОбъект("Excel.Application");
Excel.WorkBooks.Open(Имя);
ExcelЛист = Excel.Sheets(НомерЛиста);
Таблица = ExcelЛист.UsedRange.Value.Выгрузить();
Excel.WorkBooks.Close();
8. KazanKokos 7 07.02.17 11:37 Сейчас в теме
(7) Я раньше тоже так делал. Где не стоял эксель ставил опен офис. Но в последних версиях эту компоненту полностью(включая работу с картинками) заменяет родной "ТабличныйДокумент". Теперь только им пользуюсь. Ого. Про UsedRange не знал. Спасибо ) Надо будет посмотреть такую возможность у табдокумента
9. KazanKokos 7 07.02.17 11:41 Сейчас в теме
(8)
Построитель = Новый ПостроительЗапроса;
    ТабДок = ЭлементыФормы.ТабДокумент;
    
    ТабДок = Новый ТабличныйДокумент;
    ТабДок.Прочитать("C:\ххх\Увеличенные ставки 2014.mxl");
    
    Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область(1, 1, ТабДок.ВысотаТаблицы, 7));
    Построитель.Выполнить();
    ТЗ = Построитель.Результат.Выгрузить();
Показать
10. necropunk 5 07.02.17 12:01 Сейчас в теме
(9) А, ну если mxl то все просто, да. Прием с построителем, кстати, начал использовать не так давно, года четыре назад, раньше почему-то пользовался другими сомнительными способами.
11. KazanKokos 7 07.02.17 12:07 Сейчас в теме
(10) А потому что на УФ выборку данных из динамического списка по другому не сделать. Делаешь там и привыкаешь :) А причем тут мхл? Если колонки правильно названы то и эксель читает и пишет
14. kadild 16.02.18 23:48 Сейчас в теме
(3)
Сверху есть "Получить"

Что-то нет такой кнопки, какой версией Консоля запросов пользуетесь? Можете скинуть скрин?
15. necropunk 5 19.02.18 10:49 Сейчас в теме
(14) Сейчас немного изменилась загрузка, стала более функциональной, но принцип тот же - двойной клик на параметр "ТаблицаЗначений" и открывается отдельная форма загрузки.
Релиз: Портативные 4.33p
Прикрепленные файлы:
5. necropunk 5 07.02.17 11:21 Сейчас в теме
Еще один вариант загрузки из Excel от разработчика подсистемы
http://forum.infostart.ru/forum9/topic21000/message1739962/#message1739962
"В консоли запросов выбери тип запроса "ADO", затем выбери драйвер ADO XLS файлов и укажи файл. Дальше в конструкторе запроса (ИР) сделай запрос и дальше результат обрабатывай обработчиками результата."
12. DrAku1a 1289 10.02.17 16:14 Сейчас в теме
Как программисту быстро загрузить данные из Excel
Отладчик запросов - это умеет, там в редакторе таблицы значений есть два варианта загрузки - из файла (выгруженного ранее процедурой ЗначениеВФайл), и из табличного документа (откроется диалог, в которм будет поле табличного документа, куда нужно вставить данные). Единественное - при загрузке из табличного документа поддерживаются только два типа данных - строка и число.
13. Светлый ум 218 05.06.17 11:25 Сейчас в теме
Оставьте свое сообщение