Json2excelformatter NPM

Installation

npm install json2excelformatter

Example

// for typescript
import { json2excelformat } from 'json2excelformatter';
// for javascript
const { json2excelformat } = require('json2excelformatter');

import { sampleJson } from './sampleJson';

/**
 * @param config
 * @param data
 * @returns {*} Promise<workbook>
*/
json2excelformat.initProcess(sampleJson.config, sampleJson.data)
.then((workbook) => {
    workbook.xlsx.writeFile('test.xlsx');
})
.catch((err) => {
    console.log(err);
});

Output

excel-outputTree

Features

Cutomizable Excel Generation
Adding stylesheet for each cell
Creating XLSX file in own path using workbook promise object
Adding hyperlink and Tooltip
Generate excel in Parent Child Tree format

Input Params

config
data

    json2excelformat.initProcess(config, data)

config

config param contains the basic structure of input data
config.dataFormat must be either default | custom | tree
default dataFormat is used to generate the plain excel format without any styles
custom dataFormat used to generate excel with styles
tree dataFormat mainly used to create the Parent Child format include styles

    config: {
        dataFormat: 'default', // tree -> if parent child means // default -> for normal array of object
        sheetName: 'sheet1',
        creator: 'example@gmail.com',
        lastModifiedBy: 'example@gmail.com',
        headerStyle: {
            fill: {
                type: 'pattern',
                pattern: 'solid',
                fgColor: { argb: 'F7F30C'}
            },
            font: {
                color: { argb: '00000' },
                size: 14,
                bold: true
            }
        }
    },

data

dataFormat: default | custom | tree
For custom and tree dataFormat every value of cell object should contains type: text | link

text type:text mentioned for the value of text is in string format

   name: {
       type: 'text',
       value: {
           text: 'Kumar'
       },
   }

link type:link mentioned for the value of text as string, this type expects hyperLink and toolTip

   address: {
       type: 'link',
       value: {
           text: '123street',
           hyperLink: 'https://google.com',
           toolTip: 'https://google.com',
       }
   }

`default` data example
```ts
    data: {
        header: [
            { header: 'ID', key: 'id', width: 30 },
            { header: 'Name', key: 'name', width: 30 },
            { header: 'Mobile Number', key: 'mob', width: 30 },
            { header: 'Address', key: 'addr', width: 30 },
            { header: 'Pincode', key: 'zip', width: 30 }
        ],
        rowSets: [
            { id: 1, name: 'Sathish', mob: '123', addr: '123street', zip: '99889'},
            { id: 1, name: 'Kumar', mob: '123', addr: '123street', zip: '99889'},
            { id: 1, name: 'Nagarajan', mob: '123', addr: '123street', zip: '99889'},
            { id: 1, name: 'Bhoosan', mob: '123', addr: '123street', zip: '99889'}
        ]
    }

custom data example

    data: [
        {
            id: {
                type: 'text',
                value: {
                    text: '1',
                    style: {},
                },
            },
            name: {
                type: 'text',
                value: {
                    text: 'Sathish',
                    style: {},
                },
            },
            address: {
                type: 'link',
                value: {
                    text: '123street',
                    hyperLink: 'https://google.com',
                    toolTip: 'https://google.com',
                    style: {
                        font: {
                            color: { argb: 'EB0D2F' },
                            size: 11,
                            italic: true,
                            bold: true
                        },
                        fill: {
                            type: 'pattern',
                            pattern: 'solid',
                            fgColor: { argb: 'F7F30C'}
                        }
                    },
                },
            }
        },
        {
            id: {
                type: 'text',
                value: {
                    text: '2',
                    style: {},
                },
            },
            name: {
                type: 'text',
                value: {
                    text: 'Kumar',
                    style: {},
                },
            },
            address: {
                type: 'link',
                value: {
                    text: '123street',
                    hyperLink: 'https://google.com',
                    toolTip: 'https://google.com',
                    style: {
                        font: {
                            color: { argb: 'EB0D2F' },
                            size: 11,
                            italic: true,
                            bold: true
                        },
                        fill: {
                            type: 'pattern',
                            pattern: 'solid',
                            fgColor: { argb: 'F7F30C'}
                        }
                    },
                },
            }
        }
    ]

tree data example

Tree must have root node and also need children atleast as empty array.

    data: {
        type: 'text',
        value: {
            text: 'Root',
            style: {},
        },
        children: [
            {
                type: 'text',
                value: {
                    text: 'Parent 1',
                    style: {},
                },
                children: [
                    {
                        type: 'link',
                        value: {
                            text: 'child 1 of parent 1',
                            hyperLink: 'https://google.com',
                            toolTip: 'https://google.com',
                            style: {
                                font: {
                                    color: { argb: 'EB0D2F' },
                                    size: 11,
                                    italic: true,
                                    bold: true
                                },
                                fill: {
                                    type: 'pattern',
                                    pattern: 'solid',
                                    fgColor: { argb: 'F7F30C'}
                                }
                            }
                        },
                        children: [
                            {
                                type: 'text',
                                value: {
                                    text: 'child 1.1 of parent 1',
                                    style: {
                                        font: {
                                            color: { argb: 'EB0D2F' },
                                            size: 11,
                                            italic: true,
                                            bold: true
                                        },
                                        fill: {
                                            type: 'pattern',
                                            pattern: 'solid',
                                            fgColor: { argb: 'F7F30C'}
                                        }
                                    }
                                },
                                children: [
                                    {
                                        type: 'link',
                                        value: {
                                            text: 'child 1.1.1 of parent 1',
                                            hyperLink: 'https://google.com',
                                            toolTip: 'https://google.com',
                                            style: {},
                                        },
                                        children: []
                                    }
                                ]
                            }
                        ]
                    }
                ]
            },
            {
                type: 'text',
                value: {
                    text: 'Parent 2',
                    style: {},
                },
                children: [
                    {
                        type: 'link',
                        value: {
                            text: 'child 1 of parent 2',
                            hyperLink: 'https://google.com',
                            toolTip: 'https://google.com',
                            style: {},
                        },
                        children: []
                    }
                ]
            },
            {
                type: 'text',
                value: {
                    text: 'Parent 3',
                    style: {},
                },
                children: []
            }
        ]
    }

styles

Cells, Rows and Columns each support a rich set of styles and formats that affect how the cells are displayed.
By the help of exceljs, json2excelformatter generates the excel and styles.
Styles are set by assigning the following properties
- font
- fill

Fonts

    style: {
        font: {
            color: { argb: 'EB0D2F' },
            size: 11,
            italic: true,
            bold: true,
            underline: true,
            strike: true,
            outline: true
        }
    }

Font Property	Description	Example Value(s)
size	Font size. An integer value.	9, 10, 12, 16, etc.
color	Colour description, an object containing an ARGB value.	{ argb: 'FFFF0000'}
bold	Font weight	true, false
italic	Font slope	true, false
underline	Font underline style	true, false, 'none', 'single', 'double', 'singleAccounting', 'doubleAccounting'
strike	Font strikethrough	true, false
outline	Font outline	true, false

Fills

In this library supports only type: pattern

    style: {
        fill: {
            type: 'pattern',
            pattern: 'darkTrellis',
            fgColor: { argb: 'F7F30C' },
            bgColor: { argb: 'FF0000FF' }
        }
    }

Property	Required	Description
type	Y	Value: 'pattern'Specifies this fill uses patterns
pattern	Y	Specifies type of pattern (see `valid-pattern-types` below)
fgColor	N	Specifies the pattern foreground color. Default is black.
bgColor	N	Specifies the pattern background color. Default is white.