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

01.08.2019 15 Comments

Инструментарий разработчика, Разработка

,

    Снимок.JPG
Когда ВПР недостаточно…

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

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

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

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

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

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

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

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

————————————-UPD————————————————-

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

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

Related Posts

15 Comments

  1. necropunk

    Я эту задачу решал загрузкой табдока в построитель и забирая оттуда готовую ТЗ. Ну, а потом нашел, что в Инструментах Разработчика можно загружать таблицу из экселя прямо в параметры и эта проблема для меня исчезла полностью.

    Reply ↓
  2. olegmedvedev

    (1)

    в Инструментах Разработчика можно загружать таблицу из экселя прямо в параметры

    извиняюсь за оффтоп. а как такое там выполнить можете написать?

    Reply ↓
  3. necropunk

    (2) Консоль запросов, в ней пишете что-то типа

    ВЫБРАТЬ
ТабЭксель.Артикул,
ТабЭксель.Цена
ПОМЕСТИТЬ ВТТаб
ИЗ
&ТЗ КАК ТабЭксель
;
////////////////////////////////////////////////////////////­////////////////////

    Заходите в параметры, нажимаете «Из запроса», появляется параметр ТЗ, тип таблица значений. Дважды щелкаем на словах «(0)Таблица значений» в колонке «Значение», открывается редактор таблицы значений. Сверху есть «Получить» и варианты «из файла», «из MXL» и «из запроса». Выбираем «из MXL», он просит выбрать файл с расширением MXL. Не верим ему, выбираем в типе файлов «Все файлы», выбираем файл Excel (Хорошо бы чтобы он был сделан по проавилам, то есть, в первой строке — названия столбцов, это сводит к минимуму дальнейшую обработку таблицы), он спрашивает хотим ли мы ограничить длину строк, и загружает таблицу. Пока с проблемами загрузки ни разу не сталкивался.

    Reply ↓
  4. necropunk

    (3) Ну, соответственно, в запросе надо будет написать поля, которые есть в таблице.

    Reply ↓
  5. necropunk

    Еще один вариант загрузки из Excel от разработчика подсистемы

    http://forum.infostart.ru/forum9/topic21000/message1739962/#message1739962

    «В консоли запросов выбери тип запроса «ADO», затем выбери драйвер ADO XLS файлов и укажи файл. Дальше в конструкторе запроса (ИР) сделай запрос и дальше результат обрабатывай обработчиками результата.»

    Reply ↓
  6. KazanKokos

    (3) Извиняюсь за оффтоп. Это же для консоли запроса а не в код? В коде какая разница откуда грузить. Грузим в ТЗ, раньше через com а теперь либо ODBC либо Табличный документ и скармливаем запросу. Или чет теперь поменялось?

    Reply ↓
  7. necropunk

    (6) в консоли многим удобнее обрабатывать информацию, я, например, 1С знаю лучше, чем Excel, мне проще подавать данные на вход и уже по ним получать любую нужную информацию.

    А в коде — да, в коде все по прежнему

    Excel = Новый COMОбъект(«Excel.Application»);
Excel.WorkBooks.Open(Имя);
ExcelЛист = Excel.Sheets(НомерЛиста);
Таблица = ExcelЛист.UsedRange.Value.Выгрузить();
Excel.WorkBooks.Close();
    Reply ↓
  8. KazanKokos

    (7) Я раньше тоже так делал. Где не стоял эксель ставил опен офис. Но в последних версиях эту компоненту полностью(включая работу с картинками) заменяет родной «ТабличныйДокумент». Теперь только им пользуюсь. Ого. Про UsedRange не знал. Спасибо ) Надо будет посмотреть такую возможность у табдокумента

    Reply ↓
  9. KazanKokos

    (8)

    Построитель = Новый ПостроительЗапроса;
ТабДок = ЭлементыФормы.ТабДокумент;

ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(«C:хххУвеличенные ставки 2014.mxl»);

Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область(1, 1, ТабДок.ВысотаТаблицы, 7));
Построитель.Выполнить();
ТЗ = Построитель.Результат.Выгрузить();

    Показать

    Reply ↓
  10. necropunk

    (9) А, ну если mxl то все просто, да. Прием с построителем, кстати, начал использовать не так давно, года четыре назад, раньше почему-то пользовался другими сомнительными способами.

    Reply ↓
  11. KazanKokos

    (10) А потому что на УФ выборку данных из динамического списка по другому не сделать. Делаешь там и привыкаешь 🙂 А причем тут мхл? Если колонки правильно названы то и эксель читает и пишет

    Reply ↓
  12. DrAku1a

    Как программисту быстро загрузить данные из Excel

    Отладчик запросов — это умеет, там в редакторе таблицы значений есть два варианта загрузки — из файла (выгруженного ранее процедурой ЗначениеВФайл), и из табличного документа (откроется диалог, в которм будет поле табличного документа, куда нужно вставить данные). Единественное — при загрузке из табличного документа поддерживаются только два типа данных — строка и число.

    Reply ↓
  13. Светлый ум

    взял на вооружение

    Reply ↓
  14. kadild

    (3)

    Сверху есть «Получить»

    Что-то нет такой кнопки, какой версией Консоля запросов пользуетесь? Можете скинуть скрин?

    Reply ↓
  15. necropunk

    (14) Сейчас немного изменилась загрузка, стала более функциональной, но принцип тот же — двойной клик на параметр «ТаблицаЗначений» и открывается отдельная форма загрузки.

    Релиз: Портативные 4.33p

    Reply ↓

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *