XLSX2JSON

Cover XLSX to JSON

Table of Contents

• Installation
• Usage
• CLI Arguments
• Config file
• Example
  • How auto parent lookup works
  • How fallback value works
  • How overriding duplicate works

Installation

Install the package as a dev dependency:

NPM

$ npm install -D @gtomato-web/xlsx2json

Yarn

$ yarn add -D @gtomato-web/xlsx2json

Usage

See CLI Arguments for more information.

NPM

Add script to package.json

{
  ...,
  "scripts": {
    ...,
    "xlsx2json": "xlsx2json -c <path-to-config-file>"
  }
  ...
}

$ npm run xlsx2json -c <path-to-config-file>

Yarn

$ yarn xlsx2json -c <path-to-config-file> 

CLI Arguments

| Argument | Alias | Usage | Optional | Description | Default | |-----------|:-----:|-----------------------------------|:--------:|----------------------------------|:-------:| | --config | -c | --config path/to/config/file.json | N | Path to config file | | | --help | -h | | Y | Show help | false | | --dry-run | -d | | Y | Dry run, no file will be written | false | | --quiet | -q | | Y | Quiet mode, no console output | false |

Config file

Create a config file in JSON format(suggest: xlsx2json.config.json) with the following structure:

type Dir = string;
type SheetName = string;
type ColName = string;
type RowNumber = number;

interface Config {
  src: Dir;
  dist: Dir;
  targets: Array<ColName>;
  keys: Array<ColName>;
  ignore?: {
    rows?: Array<RowNumber> | Record<SheetName, Array<RowNumber>>;
    sheets?: Array<SheetName>;
  },
  options?: {
    autoLookupParent?: boolean,
    fallbackValue?: string | false,
    stringTransformation?: 'camelCase' | 'constantCase' | 'headerCase' | 'paramCase' | 'pascalCase' | 'snakeCase' | false
    overridingDuplicate?: boolean
  }
}

| Options(LV1) | Options(LV2) | Optional | Description | Default | |--------------|----------------------|:--------:|-------------------------------------------------------------------------------------------------------------------------|:------------------------------:| | dist | | N | Path to the destination directory | | | ignore | | Y | | | | | rows | Y | - If rows in array, will ignore listed rows in all sheets | [] | | | | | - If rows in object, will ignore listed rows in corresponding sheets | | | | sheets | Y | Sheets to ignore by their sheet name | [] | | keys | | N | List of column names of fields to create the path to the value in dot notation | | | options | | Y | | | | | autoLookupParent | Y | Set to true to enable parent lookup, read How auto parent lookup works to learn more | true | | | fallbackValue | Y | - If fallbackValue is a string, row(s) with no value will fall back to the provided value | "Missing value in source file" | | | | | - If fallbackValue is false, row(s) with no value will be skipped | | | | stringTransformation | Y | Set to "camelCase" to have camel case object key(s)e.g. exampleOfCamelCase | "camelCase" | | | | | Set to "constantCase" to have constant case object key(s)e.g. EXAMPLE_OF_CONSTANT_CASE | | | | | | Set to "headerCase" to have header case object key(s)e.g. Example-Of-Header-Case | | | | | | Set to "paramCase" to have param case object key(s)e.g. example-of-param-case | | | | | | Set to "pascalCase" to have pascal case object key(s)e.g. ExampleOfPascalCase | | | | | | Set to "snakeCase" to have snake case object key(s)e.g. example_of_snake_case | | | | | | Set to false to disable string transformation | | | | overridingDuplicate | Y | Set to true to replace the value of a duplicate key with the value that follows it | false | | src | | N | Path to the source .xlsx file | | | targets | | N | List of column names of target value field, each item will output a file | |

Example

How auto parent lookup works

Say this is the source file:

| # | A | B | C | D | E | |---|---------|-------------|-------------------|-------------------|-------------------| | 1 | section | key | en_us | zh_cn | zh_hk | | 2 | home | title | title in en | title in cn | title in hk | | 3 | | description | description in en | description in cn | description in hk | | 4 | contact | | contact in en | contact in cn | contact in hk | | 5 | setting | title | title in en | title in cn | title in hk | | 6 | | description | description in en | description in cn | description in hk |

Config will be like...

// xlsx2json.config.json
{
  "src": "path/to/source/file.xlsx",
  "dist": "path/to/destination/directory",
  "targets": [
    "en_us",
    "zh_cn",
    "zh_hk"
  ],
  "keys": [
    "section",
    "key"
  ],
  "options": {
    "autoLookupParent": true,
    "fallbackValue": "",
    "overridingDuplicate": false
  }
}

And output will be like... (we only show en_us output in example, actual output will be 3 files)

// en_us.json
{
  "home": {
    "title": "title in en",
    "description": "description in en"
  },
  "contact": "contact in en",
  "setting": {
    "title": "title in en",
    "description": "description in en"
  }
}

How fallback value works

Say this is the source file:

| # | A | B | C | D | E | |---|---------|-------------|-------------------|-------------------|-------------------| | 1 | section | key | en_us | zh_cn | zh_hk | | 2 | home | title | title in en | title in cn | title in hk | | 3 | | description | | | | | 4 | contact | | contact in en | contact in cn | contact in hk | | 5 | setting | title | | | | | 6 | | description | description in en | description in cn | description in hk |

Config will be like...

// xlsx2json.config.json
{
  "src": "path/to/source/file.xlsx",
  "dist": "path/to/destination/directory",
  "targets": [
    "en_us",
    "zh_cn",
    "zh_hk"
  ],
  "keys": [
    "section",
    "key"
  ],
  "options": {
    "autoLookupParent": true,
    "fallbackValue": "Let's fallback",
    "overridingDuplicate": false
  }
}

And output will be like... (we only show en_us output in example, actual output will be 3 files)

// en_us.json
{
  "home": {
    "title": "title in en",
    "description": "Let's fallback"
  },
  "contact": "contact in en",
  "setting": {
    "title": "Let's fallback",
    "description": "description in en"
  }
}

How overriding duplicate works

Say this is the source file:

| # | A | B | C | D | E | |---|---------|-------------|----------------------------|----------------------------|----------------------------| | 1 | section | key | en_us | zh_cn | zh_hk | | 2 | home | title | original title in en | original title in cn | original title in hk | | 3 | | title | let's crash in en | let's crash in cn | let's crash in hk | | 4 | | description | description in en | description in cn | description in hk | | 5 | contact | | contact in en | contact in cn | contact in hk | | 6 | setting | title | title in en | title in cn | title in hk | | 7 | | description | original description in en | original description in cn | original description in hk | | 8 | | description | let's crash in en | let's crash in cn | let's crash in hk |

Config will be like...

// xlsx2json.config.json
{
  "src": "path/to/source/file.xlsx",
  "dist": "path/to/destination/directory",
  "targets": [
    "en_us",
    "zh_cn",
    "zh_hk"
  ],
  "keys": [
    "section",
    "key"
  ],
  "options": {
    "autoLookupParent": true,
    "fallbackValue": "",
    "overridingDuplicate": true
  }
}

And output will be like... (we only show en_us output in example, actual output will be 3 files)

// en_us.json
{
  "home": {
    "title": "let's crash in en",
    "description": "description in en"
  },
  "contact": "contact in en",
  "setting": {
    "title": "title in en",
    "description": "let's crash in en"
  }
}