NoSQL Zone is brought to you in partnership with:

Ayende Rahien is working for Hibernating Rhinos LTD, a Israeli based company producing developer productivity tools for OLTP applications such as NHibernate Profiler (nhprof.com), Linq to SQL Profiler(l2sprof.com), Entity Framework Profiler (efprof.com) and more. Ayende is a DZone MVB and is not an employee of DZone and has posted 479 posts at DZone. You can read more from them at their website. View Full User Profile

RavenDB 2.5 Features: Import Data to Excel

05.07.2013
| 1023 views |
  • submit to reddit

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

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

image

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.

image

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:

image

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:

image

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

image

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

image

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

image

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

image

And now we have the data inside Excel:

image

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

image

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

image

Go to Properties:

image

  • 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.

Published at DZone with permission of Ayende Rahien, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)