xlsx-json

xlsx-json

Installation

$ npm install xlsx-json

Usage

var xlsx2json = require('xlsx-json');

var config = require('./task.json');
xlsx2json(config, function(err) {
    if (err) {
        console.log(err);
    }
});

Config

config work in task.json. task.json is an array of work object, each work object contains input , sheet , range, output the four properties. It's like

[
    {
        "input": "xxx.xlsx",
        "sheet": "sheetName1",
        "range": "A1:H52",
        "output": "xxxx.json"
    }
]

Rules

• Root is an object, not an array.

• Parse assign range of file. Read file from top to bottom, from left to right.

• Keys will be read like the properties in JSON. For example, test.a[1][2].b represent what it is in JSON. If the properties don't exist, they will be generated. So if the root doesn't have the property test before, it will be

  {
      "test": {
          "a": [
              null,
              [
                  null,
                  null,
                  {
                      "b": 120
                  }
              ]
          ]
      }
  }
  

• In normal cases, keys and values come in pairs. A non-empty value represent a key, and the element in next column is the value. You can write only one key-value pair in a row, you can also write several relative key-value pairs in a row if you like.

• If the first element of a row is empty and the second is not empty, it represent the beginning of a table. The elements in this row after the empty cell are the table head. From the next row, the concat of first element and the table head represent the key, the element in corresponding cell is the value.

  |            | enabel | actions |
  | battle.pve | true   |       1 |
  | battle.pvp | false  |       2 |
  

  will be parsed to

  {
      "battle": {
          "pve": {
              "enable": true,
              "actions": 1
          },
          "pvp": {
              "enable": false,
              "actions": 2
          }
      }
  }
  

• If first two elements of a row are both empty, they will end a table. So you can use an empty row to end a table.

• If a value if [] , the next several consecutive elements will be pushed to this empty array.

Use Cases Is King

| atk   | 100 | def   |   200 |     |     |
| speed | 300 |       | power | 400 |     |
| hp    | 500 | magic |       | 600 | 700 |

to

{
    "600": 700,
    "atk": 100,
    "def": 200,
    "speed": 300,
    "power": 400,
    "hp": 500
}

When test is undefined

| test.a[1][2].b | 120 |

to

{
    "test": {
        "a": [
            null,
            [
                null,
                null,
                {
                    "b": 120
                }
            ]
        ]
    }
}

Array:

| testArr | [] | 1 | 2 | 3 | 4 |

to

{
    "testArr": [
        1,
        2,
        3,
        4
    ]
}

Key concat:

|                 | enabel | actions | vipRequired |
| battle.pve      | true   |       1 | false       |
| battle.pvp      | false  |       2 | false       |
| battle.boss     | false  |       3 | true        |
| battle.team     | true   |       4 | true        |

to

{
    "battle": {
        "pve": {
            "enable": true,
            "actions": 1,
            "vipRequired" false
        },
        "pvp": {
            "enable": false,
            "actions": 2,
            "vipRequired" false
        },
        "boss": {
            "enable": false,
            "actions": 3,
            "vipRequired" true
        },
        "team": {
            "enable": true,
            "actions": 4,
            "vipRequired" true
        }
    }
}

More complex:

|           | card.S | card.A | card.B | vip | bonus |
| rewards.1 |    900 |    600 |    450 |   3 |     8 |
| rewards.2 |   1200 |    800 |    600 |   5 |    16 |
| rewards.3 |   1800 |   1200 |    900 |   7 |    24 |

to

{
    "rewards": {
        "1": {
            "card": {
                "S": 900,
                "A": 600,
                "B": 450
            },
            "vip": 3,
            "bonus": 8
        },
        "2": {
            "card": {
                "S": 1200,
                "A": 800,
                "B": 600
            },
            "vip": 5,
            "bonus": 16
        },
        "3": {
            "card": {
                "S": 1800,
                "A": 1200,
                "B": 900
            },
            "vip": 7,
            "bonus": 24
        }
    }
}

Key contains array:

|            | [0].id | [0].type | [0].amount | [1].id | [1].type | [1].amount |
| rewards[0] |   1001 | item     |         50 |   2001 | equip    |          5 |
| rewards[1] |   1002 | item     |        100 |   2002 | equip    |         10 |

to

{
    "rewards": [
        [
            {
                "id": 1001,
                "type": "item",
                "amount": 50
            },
            {
                "id": 2001,
                "type": "equip",
                "amount": 5
            }
        ],
        [
            {
                "id": 1002,
                "type": "item",
                "amount": 100
            },
            {
                "id": 2002,
                "type": "equip",
                "amount": 10
            }
        ]
    ]
}

It's convenient and flexible, isn't it ?