❯ xls2json --help
Open an xls(x|m) file and transform to json.

Usage: xls2json [-hlmsvV] [-D=<dtfmt>] [-T=<tfmt>] [-t=<tables>]... [<files>...]

  If no `--table`s are provided, then all
  tables will be extracted.

  All files will be processed one by one,
  each outputting on line of json.

      [<files>...]           xls(x|m)-file(s) to transform
  -h, --help                 Show this help message and exit.
  -V, --version              Print version information and exit.
  -m, --memory               Show memory usage information.
  -v, --verbose              Show more information.
      --pretty               Pretty print the JSON.
  -l, --list-tables          List all tables.
  -t, --table=<tables>       Specify the tables to transform
  -p, --password=<password>  Password for opening the input file(s).
  -s, --strip                Strip empty columns and empty rows.
  -D, --datetime-format=<dtfmt>
                             The datetime format.
                             [default: 'yyyy-MM-dd'T'HH:mm:ss.SSS']
  -T, --time-format=<tfmt>   The time format.
                             [default: 'HH:mm:ss.SSS']

By Tammo Ippen <[email protected]>
Issues: https://github.com/tammoippen/xls2json/issues

list of rows "Sheet1": [ # each row is a list of cell-values ["empty"], ["String", "hello"], ["StringNumber", "14.8"], ["Int", 1234], ["bool", true], ["bool", false], ["float", 23.12345], # format for datetime and time can be specified using # the -D and -T options. The format-string documentation: # https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/time/format/DateTimeFormatter.html # Please be aware, that Excel dates have no timezone attached, # so if you provide a format with timezone, your locally configured # timezone will be used. ["datetime", "2021-05-18T21:19:53.040"], ["time", "21:19:32.000"], # formulars are evaluated ["formulars"], ["string", "hello"], ["float", -0.34678748622465627], ["int", 5], ["datetime", "2021-06-03T16:48:26.710"], ["time", "13:37:00.000"] ] } # (format is not true to the jq-output for brevity) # XLSX works the same ❯ xls2json -s src/test/resources/sample.xlsx {"Sheet1":[["empty"],["String","hello"],["StringNumber","14.8"],["Int",1234],["bool",true],["bool",false],["float",23.12345],["datetime","2021-05-18T21:19:53.040"],["time","21:19:32.000"],["formulars"],["string","hello"],["float",-0.34678748622465627],["int",5],["datetime","2021-06-03T16:59:06.466"],["time","13:37:00.000"]]} # works on multiple files as well ❯ xls2json -s src/test/resources/sample.xls* {"Sheet1":[["empty"],["String","hello"],["StringNumber","14.8"],["Int",1234],["bool",true],["bool",false],["float",23.12345],["datetime","2021-05-18T21:19:53.040"],["time","21:19:32.000"],["formulars"],["string","hello"],["float",-0.34678748622465627],["int",5],["datetime","2021-06-03T16:59:06.466"],["time","13:37:00.000"]]} {"Sheet1":[["empty"],["String","hello"],["StringNumber","14.8"],["Int",1234],["bool",true],["bool",false],["float",23.12345],["datetime","2021-05-18T21:19:53.040"],["time","21:19:32.000"],["formulars"],["string","hello"],["float",-0.34678748622465627],["int",5],["datetime","2021-06-03T16:47:22.789"],["time","13:37:00.000"]]} # list the available tables ❯ xls2json -l src/test/resources/sampleTwoSheets.xls ["Sheet1","Sheet2"] # only output some table(s) ❯ xls2json -t Sheet2 -s src/test/resources/sampleTwoSheets.xls | jq { "Sheet2": [ ["empty"], ["String", "hello"], ["StringNumber", "14.8"], ["Int", 1234], ["bool", true], ["bool", false], ["float", 23.12345], ["datetime", "2021-05-18T21:19:53.040"], ["time", "21:19:32.000"], ["formulars"], ["string", "hello"], ["float", -0.34678748622465627], ["int", 5], ["datetime", "2021-06-03T16:48:26.710"], ["time", "13:37:00.000"] ] } # if you are running the native-image executable, you can configure the # garbage collector with the usual options ❯ xls2json -Xmx1k --help Exception in thread "main" java.lang.OutOfMemoryError: Garbage-collected heap size exceeded. # 1kb is a bit low # if you are running with the installable distribution, you can set # the environment variable XLS2JSON_OPTS ❯ XLS2JSON_OPTS="-Xmx1k" xls2json --help Error occurred during initialization of VM Too small maximum heap # still 1kb is too small :D ">

# read a XLS file
❯ ./dist/xls2json-amd64 src/test/resources/empty.xls
{"Sheet1":[]}

# read a XLS file with content
❯ xls2json src/test/resources/sample.xls
{"Sheet1":[["empty",null],["String","hello",null],["StringNumber","14.8",null],["Int",1234,null,null,null,null],["bool",true,null,null,null,null],["bool",false,null,null,null,null],["float",23.12345,null,null,null,null],["datetime","2021-05-18T21:19:53.040",null,null,null,null],["time","21:19:32.000",null],["formulars",null],["string","hello",null],["float",-0.34678748622465627,null],["int",5,null],["datetime","2021-06-03T16:45:56.709",null],["time","13:37:00.000",null],[],[],[],[null,null,null],[null,null,null],[null,null,null]]}

# strip empty cells from from the end of columns and empty rows from the bottom
❯ xls2json -s src/test/resources/sample.xls
{"Sheet1":[["empty"],["String","hello"],["StringNumber","14.8"],["Int",1234],["bool",true],["bool",false],["float",23.12345],["datetime","2021-05-18T21:19:53.040"],["time","21:19:32.000"],["formulars"],["string","hello"],["float",-0.34678748622465627],["int",5],["datetime","2021-06-03T16:47:22.789"],["time","13:37:00.000"]]}

# pretty print the output
❯ xls2json -s --pretty src/test/resources/empty.xls
{
  "Sheet1": []
}

# use jq (https://stedolan.github.io/jq/)
# or gojq (https://github.com/itchyny/gojq)
# to get some nice output and / or to process the json
❯ xls2json -s src/test/resources/sample.xls | jq
{
  # we have a dict sheetname -> list of rows
  "Sheet1": [
    # each row is a list of cell-values
    ["empty"],
    ["String", "hello"],
    ["StringNumber", "14.8"],
    ["Int", 1234],
    ["bool", true],
    ["bool", false],
    ["float", 23.12345],
    # format for datetime and time can be specified using
    # the -D and -T options. The format-string documentation:
    # https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/time/format/DateTimeFormatter.html
    # Please be aware, that Excel dates have no timezone attached,
    # so if you provide a format with timezone, your locally configured
    # timezone will be used.
    ["datetime", "2021-05-18T21:19:53.040"],
    ["time", "21:19:32.000"],
    # formulars are evaluated
    ["formulars"],
    ["string", "hello"],
    ["float", -0.34678748622465627],
    ["int", 5],
    ["datetime", "2021-06-03T16:48:26.710"],
    ["time", "13:37:00.000"]
  ]
}
# (format is not true to the jq-output for brevity)

# XLSX works the same
❯ xls2json -s src/test/resources/sample.xlsx
{"Sheet1":[["empty"],["String","hello"],["StringNumber","14.8"],["Int",1234],["bool",true],["bool",false],["float",23.12345],["datetime","2021-05-18T21:19:53.040"],["time","21:19:32.000"],["formulars"],["string","hello"],["float",-0.34678748622465627],["int",5],["datetime","2021-06-03T16:59:06.466"],["time","13:37:00.000"]]}

# works on multiple files as well
❯ xls2json -s src/test/resources/sample.xls*
{"Sheet1":[["empty"],["String","hello"],["StringNumber","14.8"],["Int",1234],["bool",true],["bool",false],["float",23.12345],["datetime","2021-05-18T21:19:53.040"],["time","21:19:32.000"],["formulars"],["string","hello"],["float",-0.34678748622465627],["int",5],["datetime","2021-06-03T16:59:06.466"],["time","13:37:00.000"]]}
{"Sheet1":[["empty"],["String","hello"],["StringNumber","14.8"],["Int",1234],["bool",true],["bool",false],["float",23.12345],["datetime","2021-05-18T21:19:53.040"],["time","21:19:32.000"],["formulars"],["string","hello"],["float",-0.34678748622465627],["int",5],["datetime","2021-06-03T16:47:22.789"],["time","13:37:00.000"]]}

# list the available tables
❯ xls2json -l src/test/resources/sampleTwoSheets.xls
["Sheet1","Sheet2"]

# only output some table(s)
❯ xls2json -t Sheet2 -s src/test/resources/sampleTwoSheets.xls | jq
{
  "Sheet2": [
    ["empty"],
    ["String", "hello"],
    ["StringNumber", "14.8"],
    ["Int", 1234],
    ["bool", true],
    ["bool", false],
    ["float", 23.12345],
    ["datetime", "2021-05-18T21:19:53.040"],
    ["time", "21:19:32.000"],
    ["formulars"],
    ["string", "hello"],
    ["float", -0.34678748622465627],
    ["int", 5],
    ["datetime", "2021-06-03T16:48:26.710"],
    ["time", "13:37:00.000"]
  ]
}

# if you are running the native-image executable, you can configure the
# garbage collector with the usual options
❯ xls2json -Xmx1k --help
Exception in thread "main" java.lang.OutOfMemoryError: Garbage-collected heap size exceeded.
# 1kb is a bit low

# if you are running with the installable distribution, you can set
# the environment variable XLS2JSON_OPTS
❯ XLS2JSON_OPTS="-Xmx1k" xls2json --help
Error occurred during initialization of VM
Too small maximum heap
# still 1kb is too small :D

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "minProperties": 1,
  "patternProperties": {
    ".*": {
      "type": "array",
      "items": {
        "type": "array",
        "items": {
          "type": ["number", "string", "integer", "boolean", "null"]
        }
      }
    }
  }
}

❯ git clone https://github.com/tammoippen/xls2json.git
❯ cd xls2json
# build all java related targets
# => build/distributions/xls2json-{version}.[tar|zip]
❯ ./gradlew build
# build the native executable for your system
# => build/executable/xls2json
❯ ./gradlew nativeImage
# build the fat jar only (already in build)
# => build/libs/xls2json-{version}-all.jar
❯ ./gradlew shadowJar
# tests and run
❯ ./gradlew test
❯ ./gradlew run --args="--help"

❯ ./gradlew shadowJar
❯ $(JAVA_HOME)/bin/java -agentlib:native-image-agent=config-merge-dir=native-image-config \
    -jar build/libs/xls2json-1.0.0-all.jar \
    the-problematic-excel-file.xls(x)