apoc.export.xls.query

Procedure APOC Full

apoc.export.xls.query(query,file,{config,…,params:{params}}) - exports results from the cypher statement as xls to the provided file

Signature

apoc.export.xls.query(query :: STRING?, file :: STRING?, config :: MAP?) :: (file :: STRING?, source :: STRING?, format :: STRING?, nodes :: INTEGER?, relationships :: INTEGER?, properties :: INTEGER?, time :: INTEGER?, rows :: INTEGER?, batchSize :: INTEGER?, batches :: INTEGER?, done :: BOOLEAN?, data :: STRING?)

Input parameters

Name	Type	Default
query	STRING?	null
file	STRING?	null
config	MAP?	null

Name

Type

Default

query

STRING?

null

file

STRING?

null

config

MAP?

null

Output parameters

Name	Type
file	STRING?
source	STRING?
format	STRING?
nodes	INTEGER?
relationships	INTEGER?
properties	INTEGER?
time	INTEGER?
rows	INTEGER?
batchSize	INTEGER?
batches	INTEGER?
done	BOOLEAN?
data	STRING?

Name

Type

file

STRING?

source

STRING?

format

STRING?

nodes

INTEGER?

relationships

INTEGER?

properties

INTEGER?

time

INTEGER?

rows

INTEGER?

batchSize

INTEGER?

batches

INTEGER?

done

BOOLEAN?

data

STRING?

Install Dependencies

For loading XLS we’re using the Apache POI library, which works well with old and new Excel formats, but is quite large. That’s why we decided not to include it into the apoc jar, but make it an optional dependency.

These dependencies are included in apoc-xls-dependencies-4.3.0.12.jar, which can be downloaded from the releases page. Once that file is downloaded, it should be placed in the plugins directory and the Neo4j Server restarted.

Alternatively, you can download these jars from Maven Repository (putting them into plugins directory as well):

For XLS files:

poi-5.1.0.jar

Additional for XLSX files:

Usage Examples

The examples in this section are based on the following sample graph:

CREATE (TheMatrix:Movie {title:'The Matrix', released:1999, tagline:'Welcome to the Real World'})
CREATE (Keanu:Person {name:'Keanu Reeves', born:1964})
CREATE (Carrie:Person {name:'Carrie-Anne Moss', born:1967})
CREATE (Laurence:Person {name:'Laurence Fishburne', born:1961})
CREATE (Hugo:Person {name:'Hugo Weaving', born:1960})
CREATE (LillyW:Person {name:'Lilly Wachowski', born:1967})
CREATE (LanaW:Person {name:'Lana Wachowski', born:1965})
CREATE (JoelS:Person {name:'Joel Silver', born:1952})
CREATE
(Keanu)-[:ACTED_IN {roles:['Neo']}]->(TheMatrix),
(Carrie)-[:ACTED_IN {roles:['Trinity']}]->(TheMatrix),
(Laurence)-[:ACTED_IN {roles:['Morpheus']}]->(TheMatrix),
(Hugo)-[:ACTED_IN {roles:['Agent Smith']}]->(TheMatrix),
(LillyW)-[:DIRECTED]->(TheMatrix),
(LanaW)-[:DIRECTED]->(TheMatrix),
(JoelS)-[:PRODUCED]->(TheMatrix);

The Neo4j Browser visualization below shows the imported graph:

The apoc.export.xls.query procedure exports the results of a Cypher query to a XLS file.

The following query exports all DIRECTED relationships and the nodes with Person and Movie labels on either side of that relationship to the file movies-directed.csv:

WITH "MATCH path = (person:Person)-[:DIRECTED]->(movie)
      RETURN person.name AS name, person.born AS born,
             movie.title AS title, movie.tagline AS tagline, movie.released AS released" AS query
CALL apoc.export.xls.query(query, "movies-directed.xls", {})
YIELD file, source, format, nodes, relationships, properties, time, rows, batchSize, batches, done, data
RETURN file, source, format, nodes, relationships, properties, time, rows, batchSize, batches, done, data;

Table 1. Results
file	source	format	nodes	relationships	properties	time	rows	batchSize	batches	done	data
"movies-directed.xls"	"statement: cols(5)"	"xls"	0	0	0	12	0	20000	1	TRUE	NULL

movies-directed.xls contains one sheet with the name Sheet0.

We can query the contents of this sheet using apoc.load.xls. Let’s have a look at a couple of the sheets:

CALL apoc.load.xls("file://movies-directed.xls", "Sheet0");

Table 2. Results
lineNo	list	map
0	["Lilly Wachowski", 1967, "The Matrix", "Welcome to the Real World", 1999]	{name: "Lilly Wachowski", tagline: "Welcome to the Real World", title: "The Matrix", released: 1999, born: 1967}
1	["Lana Wachowski", 1965, "The Matrix", "Welcome to the Real World", 1999]	{name: "Lana Wachowski", tagline: "Welcome to the Real World", title: "The Matrix", released: 1999, born: 1965}

You can also compress the files to export. See here for more information