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?)
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? |
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.4.0.34.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):
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;
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");
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 |