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:
For XLS files:
Additional for XLSX files:
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 format config you can use the pattern describe as the Temporal functions: temporal funcionts
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"]}}})