json-key-string-xlsx

Introduction

Convert between json and xlsx files by key string in a browser or NodeJS.

Quick Preview

Content structure of xlsx file

1. The first column is a string of JSON key names.
2. The other columns are the values of the JSON key string.

lang	cn	en
userInfo[0].name	用户名	username
userInfo[0].nickname	昵称	nickname

Excel file will be converted to

Similarly, the JSON can also be converted to xlsx file.

[
  {
    "lang": "cn",
    "userInfo": [
      {
        "name": "用户名",
        "nickname": "昵称"
      }
    ]
  },
  {
    "lang": "en",
    "userInfo": [
      {
        "name": "username",
        "nickname": "nickname"
      }
    ]
  }
]

Getting Started

Install

In the browser, just add a script tag:

<script src="https://cdn.jsdelivr.net/npm/xlsx@0.15.0/dist/xlsx.full.min.js"></script>
<script src="dist/json-key-string-xlsx.umd.min.js"></script>

CDN Availability (click to show)

unpkg	https://unpkg.com/json-key-string-xlsx/
jsDelivr	https://jsdelivr.com/package/npm/json-key-string-xlsx

With npm:

$ npm i json-key-string-xlsx --save

Usage

1. Convert xlsx file to JSON

Sample files of this api: google docs or tencent docs

Commonjs

const XLSX2JSON = require('json-key-string-xlsx');
const xlsx2json = new XLSX2JSON();
const path = require('path');
const xlsxPath = path.join('./excel.xlsx');

const jsonData = xlsx2json.parse2json(xlsxPath);
// console.log(xlsx2json.parse2jsonCover);

ES Module

import xlsxJsonJs from 'json-key-string-xlsx';
const xlsx2json = new xlsxJsonJs();

UMD

<script src="https://cdn.jsdelivr.net/npm/xlsx@0.15.0/dist/xlsx.full.min.js"></script>
<script src="dist/json-key-string-xlsx.umd.min.js"></script>

<input type="file" name="file" id="file">
<script type="text/javascript">
  const xlsx2json = new jsonKeyStringXlsx();
  function handleFile(e) {
    const files = e.target.files, f = files[0];
    const reader = new FileReader();
    reader.onload = function(e) {
      const data = new Uint8Array(e.target.result);
      const jsonData = xlsx2json.parse2json(data, {type: 'array'});
    };
    reader.readAsArrayBuffer(f);
  }
  document.getElementById('file').addEventListener('change', handleFile, false);
</script>

console.log(jsonData) (click to show)

[
  {
    "filename": "cn",
    "lang": "cn",
    "title": "文章标题",
    "userInfo": [
      {
        "name": "用户名"
      },
      {
        "nickname": "用户昵称"
      }
    ],
    "disclaimer": {
      "title": "免责申明",
      "content": [
        "1，非人工检索方式",
        {
          "c": {
            "a": [
              {
                "b": [
                  "6，网络传播权"
                ]
              }
            ]
          }
        },
        "3，自动搜索获得",
        "4，自行承担风险",
        "5，个人隐私权"
      ]
    },
    "statusCode": {
      "200": "成功",
      "404": "失败"
    },
    "companyInfo": {
      "name": "公司名",
      "address": {
        "city": [
          "广州市",
          "北京市"
        ],
        "address": [
          "番禺万达",
          "某大厦"
        ]
      },
      "industry": "互联网"
    },
    "fortest1": {
      "key": "test1 key值，再次",
      "key2": "test1 key2值"
    },
    "fortest2": [
      {
        "key": "test2 key值，原始"
      },
      {
        "key2": "test2 key值，覆盖"
      },
      {
        "key2": "test2 key2值"
      }
    ],
    "fortest3": [
      {
        "key": "test3 对象改为数组"
      }
    ],
    "fortest4": {
      "key": "test4 数组改为对象"
    }
  },
  {
    "filename": "en",
    ......
  }
]

If some keys are overwritten, you can get details from xlsx2json.parse2jsonCover.

[ 'sheet name "main", row 12, value "disclaimer.content[1].c.a[0].b[0]"',
  'sheet name "main", row 16, value "fortest1.key"',
  'sheet name "main", row 21, value "fortest2[1].key2"',
  'sheet name "main", row 23, value "fortest3[].key"',
  'sheet name "main", row 25, value "fortest4.key"' ]

2. Convert JSON to xlsx file

The objData can also be an array of object.

const XLSX2JSON = require('json-key-string-xlsx');
const xlsx2json = new XLSX2JSON();
const objData = {
  "lang": "en",
  "userInfo": [
    {
      "name": "username",
      "nickname": "nickname"
    }
  ]
}
const aoaData = xlsx2json.json2XlsxByKey(objData);
// const aoaData = xlsx2json.json2XlsxByKey(objData, outputPath);
// const arrayBufferData = xlsx2json.json2XlsxByKey(objData, {type: 'array'});

Output file:

lang	en
userInfo[0].name	username
userInfo[0].nickname	nickname

License

MIT