# Exchanging Data
There are several drawbacks to using a macro‑enabled Excel workbook as your Graphviz IDE:
- Data and code live in the same file. As new versions of the workbook are released with additional features, it becomes tedious to copy existing data across multiple worksheets—and to reapply all ribbon settings—from the old version to the new one.
- Excel workbooks are binary files. Internally, an Excel file is a ZIP archive. Because it is not a plain text format, it does not work well with version control systems such as Git, nor does it lend itself to meaningful diffs between versions.
- Text‑based sharing services are incompatible. Platforms such as Pastebin (opens new window) make it easy to share examples and snippets, but they require text files rather than binary Excel files.
- Macro‑enabled workbooks are often distrusted. Many users—and many email systems—treat VBA‑enabled files as unsafe. It is common for email systems to strip the attachment entirely due to the presence of macros.
These limitations made it clear that a text‑based representation of the workbook’s data, styles, and settings was needed. The features that support exporting and importing this information are provided on the Exchange ribbon tab. There is no associated worksheet, as data exchange operates directly on the internal contents of the workbook.
Let’s look at an existing spreadsheet and walk through exporting it from one workbook and importing it into another using the Exchange logic. The Exchange tab is the final tab in the workbook and appears as follows:
 |
|---|
# The Exchange Ribbon Tab
The Exchange ribbon tab appears as follows, and is organized as illustrated
Windows
 |
|---|
macOS
 |
|---|
Export JSON- Writes contents out to JSON fileImport JSON- Reads JSON file, and restores data to workbookGraphing Options- Include options chosen in the ribbons andsettingsworksheetWorkbook Metadata- Include information such as user, Excel version, etc.Worksheet Layouts- Include information on how the workbook is organized'data' Worksheet- Include the contents of thedataworksheet- Export
Include row number- Include the row number of where the data was locatedInclude row height- Include the height of the rowInclude row visibility- Include information which tells if the row was visible or hidden
- Import
Append- When importing, append the data if existing data existsReplace- When importing, ignore any data and replace the contents
- Export
'styles' Worksheet- Include the contents of thestylesworksheet- Export
Include row number- Include the row number of where the data was locatedInclude row height- Include the height of the rowInclude row visibility- Include information which tells if the row was visible or hidden
- Import
Append- When importing, append the data if existing data existsReplace- When importing, ignore any data and replace the contents
- Export
'sql' - WorksheetInclude the contents of thesqlworksheet- Export
Include row number- Include the row number of where the data was locatedInclude row height- Include the height of the rowInclude row visibility-Include information which tells if the row was visible or hidden
- Import
Append- When importing, append the data if existing data existsReplace- When importing, ignore any data and replace the contents
- Export
'svg' - WorksheetInclude the contents of thesvgworksheet- Export
Include row number- Include the row number of where the data was locatedInclude row height- Include the height of the rowInclude row visibility-Include information which tells if the row was visible or hidden
- Import
Append- When importing, append the data if existing data existsReplace- When importing, ignore any data and replace the contents
- Export
# Exporting Relationship Visualizer Data to JSON format
You can export all of the data, or only selected portions, depending on how you intend to use it. Exporting subsets is especially useful when working in teams—for example, you can export just the style definitions to share with others, or export individual team members’ data and combine it into a larger workbook using the Append option during import.
You may export the entire workbook to a JSON file and later import only the sections you need into a new workbook.
Here are example snippets of exported workbook contents:
# Graphing Options
 |
|---|
{
"settings": {
"data": {
"options": {
"graph": {
"center": false,
"clusterRank": "",
"compound": false,
"dim": "",
"dimen": "",
"forceLabels": false,
"graphType": "directed",
"mode": "",
"model": "",
"newrank": false,
"ordering": "",
"orientation": false,
"outputOrder": "",
"overlap": "",
"smoothing": "",
"transparentBackground": false
},
--- SNIP ---
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# Workbook Metadata
 |
|---|
{
"metadata": {
"name": "E2GXF",
"type": "Excel to Graphviz Exchange Format",
"version": "1.0",
"user": "Jeffrey Long",
"date": "2023-04-09",
"time": "12:17:35",
"os": "Windows (64-bit) NT 10.00",
"excel": "16.0",
"filename": "Relationship Visualizer.xlsm"
}
}
2
3
4
5
6
7
8
9
10
11
12
13
# Worksheet Layouts
 |
|---|
{
"layouts": {
"data": {
"rows": [
{
"id": "heading",
"row": 1,
"height": 15,
"hidden": false
},
{
"id": "first",
"row": 2,
"height": 15,
"hidden": false
}
],
"columns": [
{
"id": "flag",
"column": 1,
"heading": "",
"width": 1.71,
"hidden": false,
"wrapText": false
},
{
"id": `Item`,
"column": 2,
"heading": `Item`,
"width": 20.71,
"hidden": false,
"wrapText": false
},
{
"id": "tailLabel",
"column": 3,
"heading": `Tail Label`,
"width": 0,
"hidden": true,
"wrapText": true
},
{
"id": `Label`,
"column": 4,
"heading": `Label`,
"width": 0,
"hidden": true,
"wrapText": true
}
]
}
}
--- SNIP ---
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
# Worksheet Contents
# 'data' Worksheet
 |
|---|
"content": {
"data": [
{
"row": 2,
"hidden": false,
"height": 15
},
{
"row": 3,
"hidden": false,
"height": 15,
"item": "Parts",
"relatedItem": "Assembly"
},
{
"row": 4,
"hidden": false,
"height": 15,
"item": "Assembly",
"relatedItem": "Paint",
"extraAttributes": {
"weight": "100"
}
},
{
"row": 5,
"hidden": false,
"height": 15,
"item": "Paint",
"relatedItem": "Quality Control",
"extraAttributes": {
"weight": "100"
}
},
{
"row": 6,
"hidden": false,
"height": 15,
"item": "Quality Control",
"label": "Mechanical Flaws",
"relatedItem": "Assembly"
},
{
"row": 7,
"hidden": false,
"height": 15,
"item": "Quality Control",
"label": "Paint Flaws",
"relatedItem": "Paint"
},
{
"row": 8,
"hidden": false,
"height": 15,
"item": "Quality Control",
"label": "No Defects",
"relatedItem": "Shipping"
}
],
--- SNIP ---
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# 'styles' Worksheet
 |
|---|
{
"content": {
"styles": [
{
"row": 20,
"hidden": false,
"height": 45,
"name": "Border 6 Begin",
"type": "subgraph-open",
"format": {
"penwidth": "1",
"colorscheme": "reds9",
"fillcolor": "2",
"fontname": "Arial Bold",
"fontsize": "12",
"style": "filled",
"margin": "18"
},
"viewSwitches": [
"yes",
"no",
"yes"
]
},
{
"row": 21,
"hidden": false,
"height": 15,
"name": "Border 6 End",
"type": "subgraph-close",
"viewSwitches": [
"yes",
"no",
"yes"
]
},
--- SNIP ---
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# 'sql' Worksheet
 |
|---|
"content": {
"sql": [
{
"row": 7,
"hidden": false,
"height": 310.5,
"sqlStatement": "SELECT \u000A [State Code] as [item], \u000A 'Medium Square' as [style name],\u000A 'sortv=%rsc%' as [attributes],\u000A [State] as [label],\u000A 5 as [split length],\u000A '\\l' as [line ending], \u000A [State Code] as [external label],\u000A [State] as [tooltip],\u000A [Region] as [cluster],\u000A 'Border 6 ' as [cluster style name],\u000A [Region] as [cluster tooltip],\u000A 'sortv=%clc% packmode=array_utr3' as [cluster attributes],\u000A [Division] as [subcluster],\u000A 'Border %scc% ' as [subcluster style name],\u000A [Division] as [subcluster tooltip],\u000A 'sortv=%scc% packmode=array_utr3' as [subcluster attributes]\u000AFROM \u000A [census regions$] \u000AORDER BY \u000A [Region] ASC, \u000A [Division] ASC, \u000A [State Code] ASC",
"excelFile": "usa states.xlsx",
"status": "SUCCESS",
"filters": [
"EXAMPLE 01",
"EXAMPLE 01",
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
]
},
{
"row": 8,
"hidden": false,
"height": 15,
"filters": [
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
]
},
--- SNIP ---
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
# 'svg' Worksheet
 |
|---|
{
"content": {
"svg": [
{
"row": 2,
"hidden": false,
"height": 12.75,
"enabled": false
},
{
"row": 3,
"hidden": false,
"height": 15,
"enabled": false,
"find": "Modify the <svg> element to add an onload() function"
},
{
"row": 4,
"hidden": false,
"height": 15,
"enabled": false
},
{
"row": 5,
"hidden": false,
"height": 45,
"find": "xmlns:xlink=\"http://www.w3.org/1999/xlink\">",
"replace": "xmlns:xlink=\"http://www.w3.org/1999/xlink\" onload=\"makeDraggable(evt)\">\u000A <!-- NOTE: The graphviz-generated content in this file has been modified by Excel to Graphviz %%[Version]%% -->\u000A"
},
--- SNIP ---
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Make the selections of the data you wish to export, and press the Export JSON button
|
|---|
You will be prompted to specify the name of a JSON file that the data should be written to. Enter a file name and press the Save button.
Windows
 |
|---|
macOS
 |
|---|
Once the data is written to the file you will receive a pop-up message such as:
 |
|---|
Press the OK button, and you are done.
# Importing JSON Data into the Relationship Visualizer
The Import function works the opposite of the Export function. It reads a JSON file which has been exported by the Relationship Visualizer to reconstitute the JSON data into a Relationship Visualizer spreadsheet.
To import a JSON file, start by choosing the sections which you want included. Just as you can export an entire workbook, or sections of the workbook, you may also import an entire workbook or just sections of a workbook.
A key difference for importing worksheets comes via the import option dropdown lists.
 |
|---|
data, styles, sql, and svg worksheets choices have Import of Append and Replace.
 |
|---|
These are mutually exclusive choices that allow you to specify whether to replace the contents in the worksheet, or append the data in the worksheet. Replace is the default, and is intended for loading the data into an empty worksheet. Append is useful for consolidating data when multiple people are preparing the data.
For example, assume a husband and wife each prepare a family tree of their ancestors. The husband can export his ancestor's data, and the wife can import the husband's data with Append checked. The import will place the data in the data worksheet after the wife's data and the family tree will become complete for this family unit.
Once you have selected your Import, press the Import JSON button.
 |
|---|
You will be prompted to Choose an Excel to Graphviz data exchange file
 |
|---|
Select the file and press the OK button. The data will be imported (which may take several seconds). If the Automatic Refresh checkbox on the Graphviz tab is checked, the Relationship Visualizer will graph the data to the worksheet, otherwise press the Refresh Graph button to see the graph.
 |
|---|
Like this tool? Buy me a coffee! ☕ (opens new window)