Форум пользователей Visio

Форум по вопросам применения и программирования в Visio
Текущее время: 28 мар 2024, 21:18

Часовой пояс: UTC + 3 часа [ Летнее время ]


Правила форума


При размещении файлов предпочтительным является формат vsd (а не vsdx/vsdm)
Размещая ваши вложения на форуме не используйте имена файлов содержащих кириллицу, в противном случае файл будет иметь имя .<расширение файла> !

Для форматирования ваших сообщений используйте BBCodes, описание используемых на форуме BBCodes.



Начать новую тему Ответить на тему  [ Сообщений: 56 ]  На страницу Пред.  1, 2, 3  След.
Автор Сообщение
 Заголовок сообщения: Visio + Excel Power Query
СообщениеДобавлено: 10 мар 2019, 12:48 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Сегодня, впервые, я хочу не задать вопрос, а поделиться одним полезным открытием. Сразу оговорюсь, что данный пост будет понятен тем, кто знаком с Power Query и, знакомых со структурой vsdx файла, как набора файлов XML. Но всё же стоит напомнить что такое Power Query (далее PQ) - это стандартная надстройка Excel, которая по умолчанию поставляется с версией 2016, и позволяет получить данные практически из любого источника, и изменить их так как надо (раньше она так и называлась Get & Transform).
Всё началось с того, что наткнулся на статью, как можно извлечь данные из zip архива с помощью PQ (http://sql10.blogspot.com/2016/06/readi ... ery-m.html). И тут я вспомнил, что vsdx файл - это по сути zip архив, который содержит набор xml файлов, и решил попробовать, решение, указанное выше, как результат - получилось, PQ увидел все XML файлы внутри vsdx файла, но увидел их как Binary файлы. Но у PQ есть функция Text.FromBinary, которая, собственно, извлекает весь текст любого файла (в нашем случае XML). Далее, можно напрямую работать с текстом, но можно пойти чуть дальше. PQ так же умеет работать с XML документами и разбивать их структуру на таблицы с помощью функций Xml.Document или Xml.Tables. Ну а далее - дело техники, вашего умения работать с PQ и вашего воображения, так как PQ это мощнейший инструмент, который удивляет меня всё больше и больше.
В качестве примера прикладываю Zip архив с двумя файлами Visio и Excel. Последний содержит данные, которые подключены к файлу visio, другие страницы содержат таблицы, которые отображают все шейпы на всех страницах, которые содержат пользовательские данные, а вторая - шейпы с данными, которые имеют связь с данными. Если в visio добавить ещё шейпов с данными, сохранить документ, перейти в Excel и обновить все данные (на вкладке данные), то таблицы обновятся - и всё это без единой строчки на VBA.
Таким образом, получаем мощный (масштабируемый, так как PQ может обрабатывать сразу несколько файлов) и гибкий инструмент для извлечения данных из visio.
Данным постом я не предлагаю готового решения, я лишь хотел показать, что есть такая возможность извлечения данных. Надеюсь будет кому-то полезно.


Вложения:
Комментарий к файлу: На вкладке Путь укажите путь к папке с файлом Visio
Example.zip [102.04 Кб]
Скачиваний: 189
Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 19:02 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Что же вы так, я ярлыков ни на кого не навешиваю. Просто все зависит от желания и приоритетов. И я не считаю, что PQ это для "обычных" пользователей.


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 19:08 
Не в сети
Administrator

Зарегистрирован: 30 авг 2009, 11:02
Сообщений: 2253
Очков репутации: 100626

Добавить очки репутацииУменьшить очки репутации
Цитата:
Тут уже надо задуматься кто быстрее справится vba или PQ

Но ведь операция перебора все равно никуда не денется. Только в VBA она присутствует явно (в пользовательском коде), а в Power Query неявно (где-то в движке спрятана). В зависимости от степени оптимизации кода она может выполняться чуть быстрее или медленнее. Но и только. Природу не обманешь.
Цитата:
Если, становится профи, то времени не меньше, а если чисто получить результат, то на много меньше. Мне понадобилось пару дней, а vba я изучал гораздо дольше

И это тоже не совсем корректно...
VBA все равно на каком-то уровне развития становится необходимостью. То есть он нужен не для отдельной задачи, а как один из базовых инструментов. А PQ - только под технологию. То есть в подавляющем большинстве случаев перед пользователем не возникает задача выбора изучаемого языка (один или другой). Вопрос будет стоять по-другому - стоит ли изучать ли PQ в довесок к VBA или ограничиться VBA.
Возможно, стоит.
Возможно даже, стоит написать нечто вроде методички - как и какие данные лучше всего доставать из Visio с помощью PQ. Или что-то вроде типовых примеров-заготовок.


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 19:20 
Не в сети
Administrator

Зарегистрирован: 30 авг 2009, 11:02
Сообщений: 2253
Очков репутации: 100626

Добавить очки репутацииУменьшить очки репутации
Цитата:
И я не считаю, что PQ это для "обычных" пользователей.

А почему?
Технология отчетов в Visio явно слабовата. А нужны они не так уж редко.
И если PQ будет востребована в других приложениях, то и в Visio ее могли бы применять "обычные" пользователи. Ну или "почти обычные" :)


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 19:48 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
Machestro писал(а):
Что же вы так
Шутка !
Machestro писал(а):
все зависит от желания и приоритетов
скорее тут все зависит от выполняемых вами задач!
Machestro писал(а):
я не считаю, что PQ это для "обычных" пользователей.
вот тут я согласен, не уверен что много пользователей использует штатное связывание с данными, и еще меньше "анализирует" потом какой шейп с какой строкой, какого источника данных связан!

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 19:51 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Возможно, стоит поправить. PQ может и стоит знать "обычному" пользователю, но ведь в нашем случае ещё нужно понимать xml структуру документа, а вот это уже перебор...


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 20:22 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
Machestro писал(а):
PQ может и стоит знать "обычному" пользователю, но ведь в нашем случае ещё нужно понимать xml структуру документа, а вот это уже перебор...
похоже вы первый в мире "обычный пользователь" которому это нужно :shock:
пару лет назад в одной из веток мы пытались представить себе психологический портрет "обычного посетителя нашего форума"
bzinchenko в сообщении #11055 писал(а):
Уверен, что основную аудиторию форума составляют гости. Человек зашел, что-то углядел. Почитал. Нашел ответ (или не нашел) и ушел. Категоря (1) крайне ленива, тороплива и регистриться массово вряд ли будет.
Surrogate в сообщении #11056 писал(а):
если человек ушел не найдя ответа. значит не сильно обламывается от того, что не узнал того что искал
не уверен? что среди них найдется масса знатоков PQ или просто желающих изучить его изучить !

если кто-то глубинно разберется в этом вопросе, напишет чОтким мануал возможно всем пользователям захочется изучать язык запросов M и структуру xml
Изображение
я буду только рад :mrgreen:

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 20:34 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Целенаправленно изучать PQ может и не стоит, но знать что это такое стоит. Тем более в версии 2019 Microsoft очень глубоко запрятали стандартный (старый) инструмент загрузки данных. Теперь загрузка данных даже из обычного csv по умолчанию идёт через PQ


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 20:37 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
Machestro писал(а):
Тем более в версии 2019 Microsoft очень глубоко запрятали стандартный (старый) инструмент загрузки данных
это что за старый инструмент такой ? сказать по правде я совсем не загружаю никаких данных, но не видел где в Visio сидит PQ ?
Изображение

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 20:38 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Не, я про Excel


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 20:43 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
Machestro писал(а):
Не, я про Excel
а где в быстром импорте прячется PQ ?
Изображение
с точки зрения простого пользователя. или какой старый механизм имеется в виду ?

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 20:44 
Не в сети
Administrator

Зарегистрирован: 30 авг 2009, 11:02
Сообщений: 2253
Очков репутации: 100626

Добавить очки репутацииУменьшить очки репутации
Цитата:
но ведь в нашем случае ещё нужно понимать xml структуру документа, а вот это уже перебор...

Ну, не совсем...
Вот написали же функцию для доступа к файлам в архиве. В ней не нужно разбираться. Скопировал и пользуйся, архив уже открыт.
Можно написать еще несколько типовых для Visio запросов. Например, выбор всех Custom Properties для всех шейпов на всех страницах. И пользователю достаточно скопировать запрос, да фильтры подстроить. И никакой структуры документа.
А кто выбивается из типовых запросов, пусть смотрит структуру.
Да и количество запросов можно понемногу наращивать.


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 20:46 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
PQ есть только в Excel. Я про него и пишу. Там такое древнее окошко появлялось (Wizard), вот теперь его заменили на PQ


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 20:51 
Не в сети
Administrator

Зарегистрирован: 30 авг 2009, 11:02
Сообщений: 2253
Очков репутации: 100626

Добавить очки репутацииУменьшить очки репутации
Кстати, послезавтра David Parker на вебинар приглашает
Цитата:
Now that you have read my white paper on integrating Visio with Power BI, I would like you to attend a free webinar that I will be presenting on 14th March 2019.
In this session, you will:
Learn how you can seamlessly integrate your work in Visio and Power BI.
Get useful tips on preparing diagrams for Power BI and modelling data for use with Visio.
Find out how easy it is to apply labels and colouring to Visio diagrams in Power BI.
Best wishes,
David J Parker


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 мар 2019, 21:17 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
Tumanov писал(а):
послезавтра David Parker на вебинар приглашает

наверно содержимое будет не сильно отличаться от его доклада на Ignite 2018

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 14 мар 2019, 21:58 
Не в сети
Administrator

Зарегистрирован: 30 авг 2009, 11:02
Сообщений: 2253
Очков репутации: 100626

Добавить очки репутацииУменьшить очки репутации
Решил посмотреть, как может выглядеть более-менее универсальная функция...
Вот что получилось.
Код:
(VisioDocName,PropSection) =>
let
    Source = File.Contents(VisioDocName),
    Files = UnzipContents(Source),
    Pages = Table.SelectRows(Files, each
            Text.StartsWith([FileName], "visio/pages/page")
            and not Text.EndsWith([FileName], "s.xml")),
    ContentColumn = Table.AddColumn(Pages, "Custom", each Xml.Tables([Content])),
    PageContents = Table.ExpandTableColumn(ContentColumn, "Custom", {"Table"}, {"Table"}),
    ShapesTable = Table.ExpandTableColumn(PageContents, "Table", {"Table"}, {"Table.1"}),
    ShapeTable = Table.ExpandTableColumn(ShapesTable, "Table.1", {"Section", "Attribute:ID"}, {"Section", "ShapeID"}),
    ShapeFiltered = Table.SelectRows(ShapeTable, each ([#"ShapeID"] <> null)),
    ExpandedSections = Table.ExpandTableColumn(ShapeFiltered, "Section", {"Row", "Attribute:N"}, {"Section.Row", "SectionName"}),
    OneSectionOnly = Table.SelectRows(ExpandedSections, each ([#"SectionName"] = PropSection)),
    ExpandedRows = Table.ExpandTableColumn(OneSectionOnly, "Section.Row", {"Cell", "Attribute:N"}, {"Section.Row.Cell", "PropRowName"}),
    Row.Cells = Table.ExpandTableColumn(ExpandedRows, "Section.Row.Cell", {"Attribute:N", "Attribute:V"}, {"PropCellName", "PropVal"}),
    ValueOnly = Table.SelectRows(Row.Cells, each ([#"PropCellName"] = "Value")),
    RemovedCellName = Table.RemoveColumns(ValueOnly,{"PropCellName","Content"}),
    PivotedProperty = Table.Pivot(RemovedCellName, List.Distinct(RemovedCellName[#"PropRowName"]), "PropRowName", "PropVal")
in
    PivotedProperty

При вызове функции она принимает два параметра: полный путь к файлу Visio и имя секции, по которой нужно сделать отчет (например Property или User).
Возвращается табличка с ShapeID и найденными в соответствующей секции свойствами.
В принципе, пользователь может вообще ничего не знать о структуре XML Visio. Задействовал функцию - получил данные. Присутствие Visio на машине не требуется. В отчет собираются данные по всем страницам (в отличие от штатного формирователя отчетов, который работает только с одной страницей).
Обращаться можно, например, вот так:
Код:
let
    Source = fShapePropFromAllPages("C:\Work\PowerQuery\Example\Doc1.vsdx","Property")
in
    Source

То есть в принципе работать можно.
Теперь о впечатлениях... Так как Power Query попытался использовать первый раз, мороки было довольно много. Для парсинга XML он не приспособлен совершенно. Чтобы добраться до нужной таблицы приходится создавать целые цепочки последовательных преобразований. В целом программа получается громоздкая и практически нечитаемая. В общем, технология явно "на любителя".
И тем не менее, мысль о том, что нужно бы подумать над набором "типовых" функций, подобных представленной, не уходит :)


Вложения:
scr.JPG
scr.JPG [ 58.16 Кб | Просмотров: 653 ]
Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 15 мар 2019, 13:34 
Не в сети
Administrator

Зарегистрирован: 30 авг 2009, 11:02
Сообщений: 2253
Очков репутации: 100626

Добавить очки репутацииУменьшить очки репутации
Если вдруг сюда заглянет кто-то из знатоков PQ, хотелось бы получить ответ на вопрос - как с помощью PQ выполнить вот такие преобразования?
Хочу получить таблицу соединений из Visio.
Легко получается таблица Connects, где написано, что коннектор 8 связан началом с шейпом 3.
И коннектор 8 связан концом с шейпом 7.
Хотелось бы пары строк связать в одну, где: "Коннектор 8 ведет от шейпа 3 в шейп 7".
Если бы было одно поле, можно использовать Table.Pivot. А тут как?


Вложения:
t2.JPG
t2.JPG [ 70.13 Кб | Просмотров: 675 ]
Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 15 мар 2019, 13:46 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Tumanov писал(а):
Хотелось бы пары строк связать в одну, где: "Коннектор 8 ведет от шейпа 3 в шейп 7".

Я конечно не знаток, но подумать можно. Только не совсем понял как должна выглядеть финальная ячейка (или серия ячеек). Можете подробно написать, что должно получиться?
Думаю, что можно как-то через группировку, а затем слияния ячеек. если будут файлы с примерами могу попробовать.


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 15 мар 2019, 14:02 
Не в сети
Administrator

Зарегистрирован: 30 авг 2009, 11:02
Сообщений: 2253
Очков репутации: 100626

Добавить очки репутацииУменьшить очки репутации
В исходной таблице каждая строка описывает одно соединение.
Первая строка - соединение конца коннектора 8 с шейпом 7.
Вторая - соединение начала коннектора 8 с шейпом 3.
Но мне хочется видеть, что шейп 3 связан с шейпом 7 с помощью коннектора 8, в одной строке. Поэтому в выходной таблице должн быть 7 колонок.
Номер коннектора слева.
Следующие три колонки выбираются из строки, в которой FromPart = 9 (начало коннектора).
Следующие три колонки выбираются из строки, в которой FromPart = 12 (конец коннектора).
Исходные файлики для тестирования сейчас подберу.


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 15 мар 2019, 14:07 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Задача ясна, попробую на вашем примере сверху


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 15 мар 2019, 14:09 
Не в сети
Administrator

Зарегистрирован: 30 авг 2009, 11:02
Сообщений: 2253
Очков репутации: 100626

Добавить очки репутацииУменьшить очки репутации
Файлы
В запросе SourceTable нужно поправить пути по месту разархивирования.
Запрос ConnectsFromAllPages дает таблицу, которую нужно преобразовать.


Вложения:
test1.zip [47.04 Кб]
Скачиваний: 102
Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
Показать сообщения за:  Поле сортировки  
Начать новую тему Ответить на тему  [ Сообщений: 56 ]  На страницу Пред.  1, 2, 3  След.

Часовой пояс: UTC + 3 часа [ Летнее время ]



Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 8


Вы можете начинать темы
Вы можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете добавлять вложения

Найти:
Перейти:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
Вы можете создать форум бесплатно PHPBB3 на Getbb.Ru, Также возможно сделать готовый форум PHPBB2 на Mybb2.ru
Русская поддержка phpBB