Tutorial
Installation
From a Julia session, run:
julia> using Pkg
julia> Pkg.add("XLSXasJSON")
Usage Exmple
If you are familiar with a JSONPointer you can get start right away with example datas in the test.
JSONWorkbook
By default, first rows of each sheets are considered as JSONPointer for data structure. And each sheets are pared to Array{OrderedDict, 1}
using XLSXasJSON
p = joinpath(dirname(pathof(XLSXasJSON)), "../test/data")
xf = joinpath(p, "example.xlsx")
jwb = JSONWorkbook(xf)
You can access worksheet via jwb[1]
or jwb["sheetname"]
JSONWorksheet
using XLSXasJSON
p = joinpath(dirname(pathof(XLSXasJSON)), "../test/data")
xf = joinpath(p, "example.xlsx")
jws = JSONWorksheet(xf, :Sheet1)
You can access rows of data with jws[1, :]
Writing JSON File
using XLSXasJSON
p = joinpath(dirname(pathof(XLSXasJSON)), "../test/data")
xf = joinpath(p, "example.xlsx")
jwb = JSONWorkbook(xf)
# Writing whole sheet
XLSXasJSON.write(pwd(), jwb)
# Writing singsheet
XLSXasJSON.write("Sheet1.json", jwb[1]; indent = 2)
Arguments
row_oriented
: if 'true'(the default) it will look for colum names in '1:1', iffalse
it will look for colum names in 'A:A'start_line
: starting index of position of columnname.squeeze
: squeezes all rows of Worksheet to a singe row.delim
: a String or Regrex that of deliminator for converting single cell to array.
JSONPointer Exmples
Basic
A simple, row oriented key
/color |
---|
red |
produces
[{
"color": "red"
}]
Dict
Nested names looks like:
/color/name | color/value |
---|---|
red | #f00 |
and produces
[{
"color": {
"name": "red",
"value": "#f00"
}
}]
It can has as many nests as you want
/a/b/c/d/e/f |
---|
It can be done |
and produces
[{
"a": {
"b": {
"c": {
"d": {
"e": {
"f": "It can be done"
}
}
}
}
}
}]
Array
Sometimes it's convinient to put array values in seperate column in XLSX
/color/name | color/rgb/1 | color/rgb/2 | color/rgb/3 |
---|---|---|---|
red | 255 | 0 | 0 |
[{
"color": {
"name": "red",
"rgb": [255, 0, 0]
}
}]
Type Declarations
You can declare Type with ::
operator the same way as in Julia.
- The value of
array
will be splitted with deliminator ';'. - Instead Julia type, only JSON types can be used
/array::array | /array_int::array{integer} | /array_float::array{number} |
---|---|---|
100;200;300 | 100;200;300 | 100;200;300 |
and produces
[{
"array": [
"100",
"200",
"300"
],
"array_int": [
100,
200,
300
],
"array_float": [
100.0,
200.0,
300.0
]
}]
All of the above
Now you know all the rules necessary to create any json data structure you want with just a column name. This is a more complete row-oriented example:
/a/b | /a/b2::array{integer} | /a/b3/1,Type | /a/b3/1/Amount | /a/b3/2/Type | /a/b3/2/Amount | /a/b3/3/Type | /a/b3/3/Amount::array |
---|---|---|---|---|---|---|---|
Fooood | 100;200;300 | Cake | 50 | Chocolate | 19 | Ingredient | Salt;100 |
would produce
[
{
"a": {
"b": "Fooood",
"b2": [
100,
200,
300
],
"b3": [
{
"Type": "Cake",
"Amount": 50
},
{
"Type": "Chocolate",
"Amount": 19
},
{
"Type": "Ingredient",
"Amount": [
"Salt",
"100"
]
}
]
}
}
]