Simple-export-excel

Parse the json oject to excel

Install

npm install simple-export-excel --save

Usage

    .exportXls(headers, data, sheetsname)

You have to specify headers and data, and pass them to .exortXls

var header = [
    ['item','price']
]

var data = [
    [
        {
            'item':'apple',
            'price':1000
        },
        {
            'item':'banana',
            'price':1000
        }
    ],//sheet 1
]

For example

var xls = require('simple-export-excel');
var headers = 
[
    [
        "item", "price"
    ],
    [
        "name", "id"
    ]
]

var data = [
                [
                    {
                        'item':'apple',
                        'price':1000
                    },
                    {
                        'item':'banana',
                        'price':1000
                    }
                ],//sheet 1
                [
                    {
                        'name':'andy',
                        'id':112
                    },
                    {
                        'name':'bob',
                        'id':90,
                    }
                ]//sheet 2
            ]

var ret =  xls.exportXls( headers, data);
var fs = require('fs');
fs.writeFileSync('./test.xlsx',ret,'binary')

Output:

(sheet_0)

|item|price| | :------------- | :--------- | |apple|1000| |banana|1000

(sheet_1)

|name|id| | :------------- | :--------- | |andy|112| |bob|90|

Note: output will be binary data. You can use fs.writeFile to write these into a file.

var fs = require('fs');
fs.writeFileSync('./test.xlsx',ret,'binary')

Notice

case 1.

In the following case: because we don't specify 'year' in the headers, simple-export-excel will ignore the 'year' during the parsing process.

var xls = require('simple-export-excel');
var headers = 
[
    [
        "name", "id"
    ],
 
]

var data = [

                [
                    {
                        'name':'andy',
                        'id':112
                    },
                    {
                        'name':'bob',
                        'id':90,
                        'year':'2017',
                    }
                ]//sheet 1
            ]

var ret =  xls.exportXls( headers, data);
var fs = require('fs');
fs.writeFileSync('./test.xlsx',ret,'binary')

the Output will be

|name|id| | :------------- | :--------- | |andy|112| |bob|90|

case 2.

As you can see, the name property of first object stores a array ['andy', 'andy lai'].

var data = [

                [
                    {
                        'name':['andy','andy lai'],
                        'id':112
                    },
                    {
                        'name':'bob',
                        'id':90,
                    }
                ]//sheet 1
            ]

In this case, simple-export-excel will combine all element in the array with ';'.

The output will be

|name|id| | :------------- | :--------- | | andy; andy lai| 112| | bob | 90 |

case 3. Nested JSON

var xls = require('simple-export-excel');
const header = 
    [
        [   //header of sheet 1 
            'item',
            'price',
            'dealership.us[0].name',
            'dealership.us[0].address',
            'dealership.us[0].prop',
            'dealership.us[1].name',
            'dealership.us[1].address',
            'dealership.us[1].prop'
        ] 
    ]
var data = 
    {
                
        'item': 'apple',
        'price': 1000,
        'dealership': {
            'us': [
                    {
                        name: 'andy',
                        address: 'street 1, no 38',
                        prop: ['car', 'house']
                    },
                    {
                        name: 'bob',
                        address: 'stree 2, no 113',
                        prop: ['stock', 'car']
                    }
            ]
        }
    }

var ret = xls.exportXls(header, data);
var fs = require('fs');
fs.writeFileSync('./test.xlsx',ret,'binary')

The output will be like:

|item|price|dealership.us[0].name|dealership.us[0].address|dealership.us[0].prop|dealership.us[1].name|dealership.us[1].address|dealership.us[1].prop| |:---|:----|:--------------------|:-----------------------|:--------------------|:--------------------|:-----------------------|:--------------------| |apple|1000|andy|street 1, no 38|car;house|bob|stree 2, no 113|stock;car|

Release Note:

2.0.1

The white spaces in the sheet name will be replaced by '_'