Over a million developers have joined DZone.

RavenDB 2.5 Features: Import Data to Excel

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:

Published at DZone with permission of Ayende Rahien, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}