News:

BB code in posts seems to be working again!
I haven't turned on every single tag, so please let me know if there are any that are used/needed but not activated.

Main Menu

Import Excel data into Visio and generate related diagram

Started by Pitou40, November 09, 2022, 05:49:17 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Pitou40

Hello everyone,

I'm using Visio 2016 pro.
I have some data in format .xlsx that I would like to use to generate a diagram in visio.
My data are in a simple matrix with 3 columns: predecessors are in column 1, text for labels is in column 2 and successors are in column 3.
The tricky part is that the successors for a given predecessor are all stored in the same excel cell separated by ;

predec.   text              succes.
1           activity #1      2;3;4
2           activity #2      3;4
3           activity #3      4
4           activity #4   
5           activity #5   

Is there a way using a macro to generate the corresponding diagram (as shown enclosed)? I tried to modify the code found in this link http://visguy.com/vgforum/index.php?topic=5922.0 (which seems close to my needs) but with no success  :(
Many thanks for your help !
Patrick.

ovi

I would proceed by steps:

1. Understand how data is read data from excel.
For example there in the code I pubblished on this post there is a part responsble for opening a file and reading the data. But probably you can find many other posts where it is show how to do it.

2. Programatically generate the shapes. Here you have a simple example from the microft docs. Obviously you have to use the data read in step 1.
Probably here the most difficult part will be to decide where to place the shapes exactly; but if you have in mind a standard layout and each shape has a constant size it should be duable.

3. Draw connection beetween shapes using data from successor column.
The fact that successors are stored in the same cell is not a big problem. You can use the split function to convert the strings to arrays.
My guess is that here the chanenges will be:
  - how to make sure the lines don't overlap if this is not intended (what happens if activity #2 is also connected to activity #3, 4, 5...n ?)
  - how to determine were the lines begin (in the screenshot attached the line connecting activity #1 with #3 starts from another line, what happens in the case of more lines starting from there?)

My suggestion is to think from the beging how the diagram should look with different data. Try to draw differend diagrams without forgetting the edge cases (a lot of activities, an activity connected to every other activity and so on... only if those scenarios are relevant for your domain of course). Next, try to simplify the layout as much as possibile and "standardize" it: if the way you draw it from the first activity to the last one follows a pattern with a limited amount of steps you will probably be able to implement something that works.

If you decide to go for it and you have specific questions post them below or in a new post and for sure me ore someone else will try to help you  :)

Nikolay

I could explain how I went about it for this minecraft diagram:
https://nbelyh.github.io/minecraft_recipes_svgpublish/dist/minecraft_recipes_svgpublish1.html

First, I got the data in a form of a table (that is effectively like your Excel file, just a list with connections)
https://github.com/nbelyh/minecraft_recipes_svgpublish/tree/main/data

Second, I run some code to put these on Visio diagram (without any logic, my code just drops the shapes one-by-one in a single line).
Then the code connects them to each other according to the connections described in the file.
https://github.com/nbelyh/minecraft_recipes_svgpublish/blob/main/extract/Program.cs

Reading Excel files, dropping shapes, making connections is easy and is well documented in many places.
This can be done using any major programming language, basically. In my case, it was C#

Last, I used Visio "Page Layout" function to make it look more or less acceptable.

Keep in mind, now there is Visio Data Visualizer.

Unfortunately, I had little success with it. In my case, for some reason, it just kept crashing/hanging for this dataset after making just a few dozens of lines/connections.
The drawing contains ~150 elements and ~300 connections. But for normal diagrams like Org Chart, it may work perfectly.

wapperdude

QuoteKeep in mind, now there is Visio Data Visualizer.

This is available for Visio Plan 2 only.
Visio 2019 Pro

Pitou40

Many thanks to all of you for your replies, and sorry for my late one.

I will have a look at all the different links you shared. (I am also thinking to modify the excel file to match with the solution proposed in the link of my first post).

Regarding Visio Data Visualizer, I already tried to use it since it would be perfect for this exercise, but I had also some issues if the number of elements is "too high".
BR.

wapperdude

Not having any experience with the Data Visualizer, I don't suppose it's possible to fracture the large amount of data into more digestible portions?

Just a thought.
Visio 2019 Pro

Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 269 (show)
Files included: 34 - 1306KB. (show)
Memory used: 1125KB.
Tokens: post-login.
Cache hits: 13: 0.00107s for 26,766 bytes (show)
Cache misses: 2: (show)
Queries used: 16.

[Show Queries]