Excel & Neo4j? Let’s code that! (VSTO edition)

So you have a new Graph Database, it’s looking snazzy and graphy and all that, but, well – you really want to see it in a tabular format, ‘cos you’ve got this Excel program hanging about, and well – who doesn’t love a bit of tabular data?

Obviously there are loads of reasons why you might want that data – maybe to do a swanky graph, perhaps to pass over to the boss. You can also get that data into Excel in a few ways –

  • Open up from the Web – you can try opening up the REST endpoint in Excel directly – (I say try because quite frankly – it’s not looking like a good option)
  • Create an application to export to CSV – this is easy – writing a CSV/TSV/#SV is a doddle (in any language) but does mean you have to give it to people to run, and that might give more headaches – however it’s an option!
  • Create an Excel Addin that runs within Excel – slightly more complicated as you need to interact with Excel directly – but does have the benefit that maybe you can use it to send data back to the db as well..

As you can imagine, this is about doing the third option – to be honest, I would only ever pick options 2 or 3, and if I’m super honest – I would normally go for option 2 – as it’s the simplest. Option 3 however has some benefits I’d like to explore.

If you want to look at the project – you can find it at: https://github.com/DotNet4Neo4j/Neo4jDriverExcelAddin

I’ll be using the official driver (Neo4j.Driver) and VSTO addins, with VS 2017.

Onwards!

Sidenote

As I was writing this, I was going to do my usual – step-by-step approach, so went to take a screenshot and noticed this:

image

So we’re going to do a quick overview of the VSTO version, then the next post will tuck into the Excel Web version which looks snazzier – but I don’t have an example as of yet…

Onwards again!

Sidenote 2: Sidenote Harder

As the code is on github I’m not going to show everything, merely the important stuff, as you can get all the code and check it out for yourself!

So – pick the new VSTO addin option:

image

And create your project. You’ll end up with something like this:

image

OK, so an addin needs a few things –

  1. A button on the ribbon
  2. A form (yes, WinForm) to get our input (cypher)
  3. The code that executes stuff

The Form

That’s right. Form. Actually – UserControl, but still WinForms (Hello 2000), let’s add our interface to the project, right click and ‘Add New Item’:

image

For those who’ve not had the pleasure before, the key thing to learn is how the Anchors work to prevent your form doing weird stuff when it’s resized.

Add a textbox to the control:

image

Single line eh? That’s not very useful – let’s MULTI-LINE!

Right–click on the box and select properties and that properties window you never use pops up, ready to be used! Change the name to something useful – or leave it  – it’s up to you – the key settings are Anchor and Multiline. Multline should be true, Anchor should then be all the anchors:

image

If you resize your whole control now, you should see that your textbox will expand and contract with it – good times!

Drag a button onto that form and place it to the bottom right of your textbox, and now we need to set the anchors again, but this time to Bottom, Right so it will move with resizing correctly – also we should probably change the Text to something more meaningful than button1 – again – don’t let me preach UX to you! Play around, make it bigger, change the colour, go WILD.

Once your button dreams have been realised – double click on the button to be taken to the code behind.First we’ll add some custom EventArgs:

internal class ExecuteCypherQueryArgs : EventArgs
{
     public string Cypher { get; set; }
}

and then a custom EventHandler:

internal EventHandler<ExecuteCypherQueryArgs> ExecuteCypher;

Then we call that event when the button is pressed, so the UserControl code looks like:

public partial class ExecuteQuery : UserControl
{
    internal EventHandler<ExecuteCypherQueryArgs> ExecuteCypher;

    public ExecuteQuery()
    {
        InitializeComponent();
    }

    private void _btnExecute_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrWhiteSpace(_txtCypher.Text))
            return;

        ExecuteCypher?.Invoke(this, new ExecuteCypherQueryArgs { Cypher = _txtCypher.Text });
    }
}

The Ribbon

OK, we now have a form, but no way to see said form, so we need a Ribbon. Let’s add a new  Ribbon (XML) to our project

image

Open up the new .xml file and add the following to the &lt;group&gt; elements:

<button id="btnShowHide" label="Show/Hide" onAction="OnShowHideButton"/>

Now open the .cs file that has the same name as your .xml and add the following:

internal event EventHandler ShowHide;

public void OnShowHideButton(Office.IRibbonControl control)
{
    ShowHide?.Invoke(this, null);
}

Basically, we raise an event when the button is pressed. But what is listening for this most epic of notifications??? That’s right.. it’s:

ThisAddin.cs

The unfortunate part about going from here on in is that this is largely plumbing… ugh! The code around how to show/hide a form I’ll skip over – it’s all in the GitHub repo and you can read it easily enough.

There are a couple of bits of interest – one is the ThisAddin_Startup method, in which we create our Driver instance:

private void ThisAddIn_Startup(object sender, EventArgs e)
{
     _driver = GraphDatabase.Driver(new Uri("bolt://localhost"), AuthTokens.Basic("neo4j", "neo"));
}

To improve this, you’d want to get the URL and login details from the user somehow, perhaps a settings form – but I’ll leave that to you! – The important bit is that we store the IDriver instance in the addin. We only want one instance of a Driver per Excel, so this is fine.

The other interesting method is the ExecuteCypher method – (which is hooked up to in the InitializePane method) – This takes the results of our query and puts it into Excel:

private void ExecuteCypher(object sender, ExecuteCypherQueryArgs e)
{
    var worksheet = ((Worksheet) Application.ActiveSheet);

    using (var session = _driver.Session())
    {
        var result = session.Run(e.Cypher);
        int row = 1;
        
        foreach (var record in result)
        {
            var range = worksheet.Range[$"A{row++}"]; //TODO: Hard coded range
            range.Value2 = record["UserId"].As<string>(); //TODO: Hard coded 'UserId' here.
        }
     }
}

Again – HardCoded ranges and ‘Columns’ (UserId) – you’ll want to change these to make sense for your queries, or even better, just make them super generic.

Summing Up

So now we’re at this stage, we have an Excel addin using VSTO that can call Cypher and display the results, there are things we probably want to add – firstly – remove all the hard coded stuff. But what about being able to ‘update’ results based on your query?? That’d be cool – and maybe something we’ll look at in the next addin based post (on Web addins).

Using Neo4j on Windows

In the old days, Neo4j used to come with an .exe to install/run it, personally I’ve never used it, as it was (in my view) awkward to find all the file locations, so I’ve always used the Zip download.

For a while now, you’ve only had the option of the Zip version of Neo4j, so looks like everyone is in my box now! This is good news, the zip version provides a lot more options and control for the user. But, there’s not a whole lot of documentation about how to use it, aside from ‘run neo4j.bat’.

I actually have a collection of shortcuts I use for my Neo4j instances:

image

It makes it super easy for me to start up an instance on demand, but also have multiple instances – also I get groovy icons – which is better than the bog standard ‘console’ icon.

How do you get this?

Of course you want the icons, the ease of use of Neo4j is simply a bonus. First off – I don’t install Neo4j as a service – when I’m developing against Neo, I don’t really want to have to navigate the ‘Services’ dialog everytime I want to start / stop the DB – nor indeed have to open up a shell to do it.

Script for the win

I have a Powershell script – usually titled something like:

Community 3.3.0 – 7474.ps1

So I can know what edition I’m using, what version and finally what port I have Neo4j setup to run on. But what’s inside that file?

#Set the execution policy so we can import the Neo4j Management Module
Set-ExecutionPolicy ByPass

#Import the module
Import-Module D:\Databases\Neo4j\Community\neo4j-community-3.3.0\bin\Neo4j-Management.psd1

#Start the database
Invoke-Neo4j Console

Only 3 lines, and the comments make it pretty obvious – in the last line I invoke Neo4j in it’s Console mode – this means you’ll be able to CTRL+C the window to shut down the DB – and you can also read the messages from the DB as a bonus.

By changing the port numbers in the Neo4j config files, I can run as many instances of Neo as I have ports at the same time. I usually find this handy if I’m doing a comparison between versions – or someone has asked me a question and I want to test out my answer in a sandbox environment.

So far, so good – at the moment we can open up PowerShell and run the script – which will start the instance, but ideally we just want to be able to double click on a shortcut to run the script file.

So, let’s right click and create a new Shortcut:

image

For the location – we want:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -File "D:\Databases\Neo4J\Community 3.3.0 - 7474.ps1"

image

This tells PowerShell to execute the script file in a separate window. Press ‘Next’ and give it a name that makes sense – I stick with the ‘Edition’, ‘Version’ and ‘Port’

image

Press Finish, and now you can double click it and run the DB

image

You need to run it as an administrator – as we load in the powershell files, so right click on the shortcut and select properties:

image

Click on ‘advanced’:

image

Then select the ‘Run as administrator’ checkbox:

image

Press OK, then ‘Apply’.

You’re all set now – unless you want to change the icon, in which case, find an icon you like (either built in Windows ones, or google for it) and press the ‘Change Icon’ button, and select it. Easy.

If you want to add that icon to your start menu (windows 10) I find it easiest to right click on an existing application icon in your start menu, select ‘More’ and then ‘Open File Location’:

image

Then copy/paste your new short cut in there, (I put it in a ‘Neo4j’ folder), and finally press ‘Start’ and search for your new icon:

image

Right click on it and pin to the the start menu as you would normally.(No picture as my short cut for that is WIN+S and that keeps on closing the start menu – and quite frankly I’m not changing it for this limited situation).

image