npm package: @gtomato-web/xlsx2json
Description: Covert .xlsx to .json
Author: GTomato Web
Latest version: 1.2.2
Deprecated: Unmaintained

# XLSX2JSON

Cover XLSX to JSON

## Table of Contents

- [Installation](#installation)
- [Usage](#usage)
- [CLI Arguments](#cli-arguments)
- [Config file](#config-file)
- [Example](#example)
  - [How auto parent lookup works](#how-auto-parent-lookup-works)
  - [How fallback value works](#how-fallback-value-works)
  - [How overriding duplicate works](#how-overriding-duplicate-works)

## Installation

Install the package as a dev dependency:

### NPM

```shell
$ npm install -D @gtomato-web/xlsx2json
```

### Yarn

```shell
$ yarn add -D @gtomato-web/xlsx2json
```

## Usage

See [CLI Arguments](#cli-arguments) for more information.

### NPM

Add script to package.json

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

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

### Yarn

```shell
$ 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:

```typescript
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](#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)<br/>e.g. exampleOfCamelCase                                       |          "camelCase"           |
|              |                      |          | Set to `"constantCase"` to have constant case object key(s)<br/>e.g. EXAMPLE_OF_CONSTANT_CASE                           |                                |
|              |                      |          | Set to `"headerCase"` to have header case object key(s)<br/>e.g. Example-Of-Header-Case                                 |                                |
|              |                      |          | Set to `"paramCase"` to have param case object key(s)<br/>e.g. example-of-param-case                                    |                                |
|              |                      |          | Set to `"pascalCase"` to have pascal case object key(s)<br/>e.g. ExampleOfPascalCase                                    |                                |
|              |                      |          | Set to `"snakeCase"` to have snake case object key(s)<br/>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...

```json
// 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)

```json
// 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...

```json
// 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)

```json
// 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...

```json
// 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)

```json
// 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"
  }
}
```