Help page

This wiki page is a copy of the central help HTML, which contains links.

Summary

This tool lays out the dependencies between Excel files in a visual graph. Dependencies are based on Excel external references.

• Other functions include:
• Detect possibly outdated links
• Detect circular links
• Quickly open Excel files
• Highlight cells of a single file which contain external references
• Dump external references of a single file
• Dump the graph of dependencies between files

Usage notes

Detection of outdated links

Prerequisites

The colors used in the normal view depend on following conditions:
All Excel files with external links update those external links on startup (an Excel option)
If PivotTables are present, automatic update should also be enabled
No change in the linked file occurs while the dependent file is open, unless the user does a manual update before saving.
Calculations are performed automatically or before saving

Colors

Green color: this file seems up to date with regards to the files it depends on. If preconditions are met, it will be up to date
Orange color: this file has risk of not being up to date as it depends from a file which was modified after this file was opened.
Red color: this file could not be found, timestamp gets default of 1/1/1970

Links detected, but not found in Excel?

External links can be stored inside spreadsheet cells, via "=[]" references, but sometimes inside namded ranges.
Sometimes you just need to open and save a file when "cached and removed" references are detected. If you still cannot find a link, see this guide

Graph or tree?

A tree is a graph where every vertex has only one parent and there are no circular refernces. In order to guarantee up-to-date files, no circular links should exist ( A <-> B). When the tool opens in graph mode, this means no real tree could be constructed. For example, if A -> B ; and C -> B, this is not a real tree. But a tree is constructed anyway, where B will be defined twice, one original and one copie. When clicking on the tree selector, a "generated" tree will be shown, where copies will be shown if a node already was attached to a parent.

Short user manual

Launching the program

You need to have a recent (1.7) version of Java installed.

• Windows: double click on the executable Showlinks.exe or start it from a cmd line:
  Showlinks.exe [fileName to start.xls]
  If you read in large .xlsx files, make sure your computer has enough memory (close other programs) so that the space reserved for the heap space is bigger then 1000MB. You can see the used value of this parameter after startup in the logging screen, eg: VMarguments:[-Xmx1024m]
  The actual memory size can be found after the Max mem size.

• System independent: Run the jar, specifying a max. heap size. For very large files, it may be necessary to use larger heap sizes (try the maximum which gives no errors on your system, eg. 1240) java -Xmx1024m -jar ShowLinks.jar [fileName to start.xls]

Running the program

Select one of two modes in the upper left corner:

• Show dependencies
  Select a file and click START (or paste a file location and press enter)

The selected file will be read, and the Excel external references (links) of this file will on their turn be read, repeating the process for its links, ... until all linked files are constructed in a graph.
When a file A contains a link to a file B, a directed arrow is drawn from A to B. If the modification date (as stored inside the file) of B is newer then the modifcation date of A:
A will get an orange color
The arrow (Edge) will be dotted
If you have a dependency A -> B -> C, and the modification date of B is older than C, then A will also be painted orange regardless of its own modification date.

• Show where used
  Select one or more folders (using ctrl), optionally a start file and filter and click START (or paste folders seperated with ; and press enter)

This mode finds usages of Excel files in other Excel files in one or more folders. Compared to the previous mode, the arrows are reverted and links are not followed (since links are stored the other way around in Excel, as dependency).
The tool will look into the given folders (including subfolders) for files with the following extension:
xls, xlt, xlsx, xlsm, xlsb, xltx, xltm.
If this file contains a link, the target will also be drawn as a Vertex, but will not be parsed. If the checkbox "Stay in folders" is selected, the target will only be drawn if it is also in one of the selected folders.

Filters
To limit the number of files that will be parsed, one can filter subfolders or files, with include or exclude patterns.
You need to pass in regular expressions (Java version), where case insensitive substring matching is used.
If you pas in a starting file, only the files which depend (directly or indirectly) on this file will be shown.

Working on the graph

Mouse functionality

• Tooltip
  When you hold the mousepointer still above a vertex, it displays it's last modification time as stored by Excel as well as the full path
• Left mouse button
  When in Picking mode, you can select one or more vertices, and perform an action or move by dragging the selection.
• Right mouse button
  When above a vertex, allows you to copy paste the current label or to delete the vertex in the graph (not the Excel file)
• Scrollwheel
  Use the scroll wheel to zoom in/out.
• Ctrl + left mouse
  Ctrl + click on a node will center the node in the screen.

Using the control buttons

• Highlight node labels
  Lets you highlight vertices that match a certain pattern.
  You can pass in regular expressions (Java version), where case insensitive substring matching is used.
• Layout options
  You can switch between the graph view and the tree view.
  Layout combo: when in graph mode, you can choose a layout algorithm and freeze it when it keeps optimising its view
  Radial button: when in tree mode, allows to switch from radial (default) layout to a normal tree. A "radial tree" puts the nodes on equal distant rings.
• Display control
  Use the "+/-" or scroll mouse to zoom in/out.
  Use the "Picking/Transforming" combo-box to switch between picking and transforming mode.
  Use the "Show edge labels" checkbox to enable/disable edge labels
• Label display
  Here you can define what text you want to see for each node (file).
• Label Position
  Here you can set the position of the vertex label.
• Collapse/Expanding
  In Graph mode, use the mouse to select multiple vertices either by dragging a region, or by shift-clicking on multiple vertices.
  In Tree mode, you can just select one Vertex
  After you select vertices, use the Collapse button to combine them into a single vertex.
  Select a 'collapsed' vertex and use the Expand button to restore the collapsed vertices.
  The Reset button will restore the original graph.
• View
  Detect cycles: the tool will search for cycles in the directed graph, eg. A depends on B, B depends on C, but C depends on A.
  Vertices will be shown in red color when they are part of a dependency cycle.
  Normal view: show the colors based on the file timestamps.
• Open selected
  Select one or more nodes. Then:
  • Open file:
    Opens the selected file(s) in Excel. If Excel was already open, you may need to open the Excel window manually.
  • Open new window:
    Start a new instance of the tool, with the selected file(s) as starting file
  • Mark links
    Make a copy of the selected file(s) in a temporary directory, mark all cells with external references in a orange color and open the file in Excel.
  • Dump links
    Make a new Excel file in a temporary directory with all the external references listed and open it.
• Save options
  • Dump to .xlsx
    Write the graph out. Ask the user where the file needs to be saved, make a new file with all the vertices and their dependencies listed and open it.
  • Save/Restore layout
    It is possible to save the layout in a configuration file. This configuration file can only be used to restore a layout when the nodes present are identical to when the layout file was stored. This means that start file and possibly input folders and filters are the same.

Technical note

The tool is written in Java and uses the Apache POI framework, the Jung and JGrapht libraries (handy PDF for background on those libraries.
The .exe file was generated with Launch4J.

Logging:

If you want more verbose output, you need to change the log4j.properties file inside the jar (which is zip-compressed) and change the main log level from INFO to DEBUG.
See this Log4j tutorial for other log configuration options.