Excel to Graphviz Files

Castelli's Excel To Graphviz Tool ReadMe
Version 5.1.2 2017-04-29
(c)2017 by mcastelli
Comments and feature requests: piqault@gmail.com

***** new with this update
	- automatic update of node identifier and edges after node name change
	- option to unclude node type and cluster name in unique node identifier (data sheet settings: nodeTypeAsIdentifier, clusterAsIdentifier)
	- automatic update of node identifier and edges after unique node identifier option change

***** Whatis
This tool generates "flow" images based on Grapviz from excel. See www.graphviz.org.

Using excel tables you define nodes, edges and clusters.
Using the excel vba macro you create the graphviz description file.
The same excel vba macro generates the graphviz image using the graphviz description file and library.
Image formats are: PNG, SVG and PDF

Knowledge of graphviz and it's possiblities is not required but for more in-depth knowledge of what you are doing you could read the dot guide which comes with the graphviz installation.

***** How does it work
The tool consists of a macro enabled excel workbook (xslm) with 
	- sheets that have to be filled out
	- vba macros that automatically generate dropdown boxes
	- A vba macro that generates the output:
		- a grapviz file: *.gv that can be edited with a graphviz editor (or something like notepad++) 
		- a pdf with the generated image
		- a png image
		- a sgv image 
		
***** prerequisites
	- Macro enabled Excel 2007+
	- Graphviz installed on your system. It's free. http://www.graphviz.org/
	- Graphviz bin on your path (windows) (recommended)

***** the sheets:
	- first sheet: 		Nodes 				- to define nodes
	- second sheet: 	Edges 				- to define edges
	- third sheet: 		Cluster				- to define clusters
	- fourth sheet:		Cluster_Structure	- to define hierarchy of clusters
	- fifth sheet: 		Enum_Node_type		- to define node types (used in nodes sheet)
	- sixth sheet:  	Enum_Edge_type		- to define edge types (used in edges sheet)
	- seventh sheet:	Enum_Cluster_type 	- to define cluster types (used in clusters sheet)
	- eight sheet: 		GVVariables			- Graphviz node, edge and cluster type variables
	- ninth sheet:		GVGraphAttributes	- Grpahviz graph attributes
	- tenth sheet: 		Settings			- Settings for the excel-to-graphviz tool itself

	# first Sheet: Nodes (graphviz nodes)
		Defines the nodes 
		Fill out this sheet with all concerned nodes the following way:
		col A:	the name of the node.
		col B:	(optional) the node version (not the technical version). whole numbers
		col C:	hidden concatenation of name and version. just forget about this but don't overwrite this.
		col D:	(dropdown) the node type (defined in fifth sheet Enum_Edge_type)
		col E: 	(optional) the cluster in which the node resides.
		col F: 	concatenation of name, version, type and cluster to assure uniqueness.
		col H: 	rankID. nodes with the same RankID which are in the same cluster or placed adjacent to another
		col x:	you can add any column you want after col H for your own purpose.
		
	# second Sheet: Edges (graphviz edges)
		contains all relations that exist between listed nodes in a consumer<->consumed (or vice versa if you like) releationship
		Fill out this sheet with all listed nodes the following way:
		col A:	(dropdown) the name(.version) of the consuming(right or top) node
		col B:	(dropdown) the name(.version) of the providing(left or bottom) node
		col C:	(dropdown) The edge type (defined in sheet six: Enum_Edge_type)

	# third Sheet: Clusters (graphviz clusters)
		contains all clusters
		Fill out this sheet with all listed clusters the following way:
		col A:	The name of the cluster
		col B:	(dropdown) the type of the cluster (defined in sheet seven: Enum_Cluster_type)
		
	# fourth Sheet: Cluster_Structure
		Defines the hierarchy of clusters
		Fill out this sheet with listed clusters the following way:
		col A:	(dropdown) The name of the parent(contaiing) cluster
		col B:	(dropdown) The name of the child(contaiined) cluster)
		
	# fifth Sheet: enum_node_type
		Contains all required node types and their graphviz characteristics
		NodeType		the name of the node type
		fontname		of the text 
		fontsize		of the text
		fontcolor		of the text. use html rgb color wiht preceding #
		fixedsize       boolean. make the shape fixed in size
		width			in "
		height			in "
		shape			(dropdown) select one of the available graphviz shape types
		style			(dropdown) select one of the combination of available graphviz shape styles	
		fillcolor		when the style contains the word "filled" then this is the fillcolor. use html rgb colors with preceding #
		gradientangle	For when the fillcolor contains two colors (like #555555:#FFFFFF)
		color			line color. use html rgb colors with preceding #
		image			use absolute paht
		
	# sixth Sheet: enum_edge_type
		Contains all required relation types and their graphviz characteristics (arrows etc.) required for Integration and Business services and processes high level design for use with QuickScan and ImpactAnalysis.
		col A:	relationType	the name of the relation type
		col B:	dir				Direction of the relation type (usually an arrow)
		col C:	color			Color of the releation type (a line if visible)
		col D:	arrowhead		the shape of the relation type's arrowhead (dropdown)
		col E:	arrowtail		the shape of the relation type's arrowtail (dropdown)
		col	F:	arrowsize		the relation type's size of both arrow ends
		col G:	label			the general text that resides with this relation type's line
		col H:	style			(dropdown) select one of the combination of available graphviz shape styles	
		col I:	fontname		of the text 
		col J:	fontsize		of the text
		col K:	fontcolor		of the text. use html rgb color wiht preceding #
		
	# Seventh Sheet: enum_cluster_type
		Contains all required cluster types and their graphviz characteristics
		col A:	ClusterType		the name of the node type
		col B:	fontname		of the text 
		col C:	fontsize		of the text
		col D:	fontcolor		of the text. use html rgb color wiht preceding #
		col E:	style			(dropdown) select one of the combination of available graphviz shape styles	
		col F:	fillcolor		when the style contains the word "filled" then this is the fillcolor. use html rgb colors with preceding #
		col G:	color			line color. use html rgb colors with preceding #
		col H:  gradientangle   For when the fillcolor contains two colors (like #555555:#FFFFFF)

				
	# fourth Sheet: GraphVizVariables
		contains selectable graphviz variables for nodes and edges (artifacts and releations) 
		do not touch unless you know what you're doing.

	# fifth Sheet: GraphVizGraphAttributes
		contains changeable attributes that have an effect on how the graph is rendered and displayed (for dot and neato graphs).
		please refer to the graphviz dotguide.pdf for more information.
		
	# fifth Sheet: Properties
		contains changeable graph specific properties
		
***** the macros:
	There is one macro (Ctrl-M)
	# CreateGraphvizOverviewFromExcel

	note: It is possible that you only want to have a graphviz image from a selection of listed artifacts; By hiding (select row; right click; select hide) the unwanted artifacts you can produce this.	
		
***** your first graph step by step
	- Put the exel file in a, preferrable empty, directory
	- rename file if required
	- mind the prerequisites (check by typing gvedit.exe on cmd line)
	- open excel file and fill out some data in sheet "artifacts" and "relations" or retain current example data
	- make sure excel is macro enabled: Excel home > options > truscenter >trust center settings > macro settings > macro settings > enable all ....
	- run one of the macros by pressing control-m or control-n
	- check your directory
										
***** WARNING
	- do NOT cut and paste rows or cells; this will ruin the macro that produces dropdown boxes.
		use copy, paste instead and then select the unwanted row and press the delete key or overwrite
	- the macros will stop when they encounter an empty row; make sure all rows are contiguous and do NOT cut a row or cells
	- the artifact sheet will highlight double artifacts.
	
***** New/Changed in this version
	- Improved Legenda redering		
	
***** ROADMAP
	- Addition of Integration framework (as option)
	- Optional overriding of attributes per artifact or relations
	- Incorporation layering
	- BPMN example
	- Graphviz examples
	- show image after generation