I wonder what it says about RavenDB that we spend time doing excel integration .

At any rate, we have the following documents inside RavenDB:

And we want to get this data into Excel. Not only that, but we want this to be something more than just a flat file. We want something that will auto update itself.

We start by defining the shape of the output, using a transformer.

Then we go an visit the following url:

http://localhost:8080/databases/MusicBox/streams/query/Raven/DocumentsByEntityName?query=Tag:Albums&resultsTransformer=Albums/ShapedForExcel&format=excel

• http://localhost:8080/databases/MusicBox – The server & database that we are querying.
• streams/query/Raven/DocumentsByEntityName?query=Tag:Albums – Stream the results of querying the index Raven/DocumentsByEntityName for all Tag:Albums (effectively, give me all the albums).
• resultsTransformer=Albums/ShapedForExcel – transform the results using the specified transformer.
• format=excel – output this in a format that excel will find easy to understand

The output looks like this:

Now, let us take this baby and push this to Excel. We create a new document, and then go to the Data tab, and then to From Text:

We have a File Open Dialog, and we paste the previous URL as the source, then hit enter.

We have to deal with the import wizard, just hit next on the first page.

We mark the input as comma delimited, and then hit finish.

We now need to select where it would go on the document:

And now we have the data inside Excel:

We aren’t done yet, we have the data in, now we need to tell Excel to refresh it:

Click on the connections button, where you’ll see something like this:

Go to Properties:

• Uncheck Prompt for file name on refresh
• Check Refresh data when opening the file

Close the file, go to your database and change something. Open the file again, and you can see the new values in there.

You have now create an Excel file that can automatically pull data from RavenDB and give your users immediate access to the data in a format that they are very comfortable with.