see on GitHub

How to integrate with Excel?

A very common use case for many application is to expose data to users as an Excel file. RavenDB has a dedicated support that allows to directly consume data stored in a database by Excel application. The integration of Excel with the data store is achieved by a designated query streaming endpoint that outputs a stream in a format acceptable by Excel, Comma Separated Values (CSV).

In order to take advantage of this feature you need to specify a valid query according to RQL syntax.

The generic HTTP request will have the following address:

http://localhost:8080/databases/[db_name]/streams/queries?query=[query]

In order to include only specific properties in the CSV output you can use the field parameter like so:

http://localhost:8080/databases/[db_name]/streams/queries?query=[query]&field=[field-1]&field=[field-2]...&field=[field-N]

Example

Firstly let's create a database, Northwind, and import the sample data into it. Now let's query the product collection include the category document and project some of its properties using the below RQL

from Products as p
load p.Category as c
select 
{
    Name: p.Name,
    Category: c.Name,
}

In order to execute the above query we will need to use the following url:

http://localhost:8080/databases/Northwind/streams/queries?query=from%20Products%20as%20p%0Aload%20p.Category%20as%20c%0Aselect%20%0A%7B%0A%20%20%20%20Name%3A%20p.Name%2C%0A%20%20%20%20Category%3A%20c.Name%2C%0A%7D

Going to the above address in a web browser will download you an export.csv file containing following results:

Name,Category
Chang,Beverages
Aniseed Syrup,Condiments
Chef Anton's Cajun Seasoning,Condiments
Chef Anton's Gumbo Mix,Condiments
Grandma's Boysenberry Spread,Condiments
Uncle Bob's Organic Dried Pears,Produce
Northwoods Cranberry Sauce,Condiments
Mishi Kobe Niku,Meat/Poultry
Ikura,Seafood
Queso Cabrales,Dairy Products
Queso Manchego La Pastora,Dairy Products
Konbu,Seafood
Tofu,Produce
Genen Shouyu,Condiments
Pavlova,Confections
Alice Mutton,Meat/Poultry
Carnarvon Tigers,Seafood

Now to push them to Excel we need to create new spreadsheet and import data From Text:

Importing data from text in Excel

Then in a Open File Dialog we paste our querying url:

Open File Dialog

Next, the Import Wizard will show up where we can adjust our import settings (don't forget to check Comma as a desired delimiter):

Import Wizard Step 1
Import Wizard Step 2
Import Wizard Step 3

Finally we need to select where we would like to place the imported data:

Select where to put the data

As a result of previous actions, the spreadsheet data should look like:

Excel results

Now we must tell Excel to refresh data. Click on Connections in Data panel:

Excel connections

You will see something like that:

Excel connections dialog

Go to Properties and:
1. uncheck Prompt for file name on refresh.
2. check Refresh data when opening the file.

Excel connection properties

Finally you can close the file, change something in the database and reopen it. You will see new values.