Loading Excel (XLS)
Library Requirements
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.
Please download these jars and put them into your plugins
directory:
Usage
The usage of apoc.load.xls
is similar to apoc.load.csv
with the main difference the ability to select a worksheet or a range from a sheet to load.
You can either select the sheet by name like 'Kids'
, or offset like 'Results!B2:F3'
CALL apoc.load.xls({url}, {Name of sheet}, {config})
The {config}
parameter is a map
name | description |
---|---|
|
|
|
|
|
|
|
|
|
|
In dateParse the first format matched return the date formatted, otherwise it will return an error In |
Examples for apoc.load.xls
CALL apoc.load.xls('file:///path/to/file.xls','Full',{mapping:{Integer:{type:'int'}, Array:{type:'int',array:true,arraySep:';'}}})
CALL apoc.load.xls('http://bit.ly/2nXgHA2','Kids')
Some examples with type/dateFormat and dateParse:
CALL apoc.load.xls('test_date.xlsx','sheet',{mapping:{Date:{type:'String'}}})
CALL apoc.load.xls('test_date.xlsx','sheet',{mapping:{Date:{type:'String',dateFormat:'iso_date'}}})
CALL apoc.load.xls('test_date.xlsx','sheet',{mapping:{Date:{type:'String',dateParse:["wrongPath", "dd-MM-yyyy", "dd/MM/yyyy", "yyyy/MM/dd", "yyyy/dd/MM", "yyyy-dd-MM'T'hh:mm:ss"]}}})