1.0.4 • Published 1 year ago

json2excelformatter v1.0.4

Weekly downloads
-
License
MIT
Repository
github
Last release
1 year ago

LinkedIn License Donate Node.Js exceljs

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 PropertyDescriptionExample Value(s)
sizeFont size. An integer value.9, 10, 12, 16, etc.
colorColour description, an object containing an ARGB value.{ argb: 'FFFF0000'}
boldFont weighttrue, false
italicFont slopetrue, false
underlineFont underline styletrue, false, 'none', 'single', 'double', 'singleAccounting', 'doubleAccounting'
strikeFont strikethroughtrue, false
outlineFont outlinetrue, false

Fills

  • In this library supports only type: pattern
    style: {
        fill: {
            type: 'pattern',
            pattern: 'darkTrellis',
            fgColor: { argb: 'F7F30C' },
            bgColor: { argb: 'FF0000FF' }
        }
    }
PropertyRequiredDescription
typeYValue: 'pattern'Specifies this fill uses patterns
patternYSpecifies type of pattern (see valid-pattern-types below)
fgColorNSpecifies the pattern foreground color. Default is black.
bgColorNSpecifies the pattern background color. Default is white.

Note: If you want to fill a cell using the solid pattern, then you don't need to specify bgColor.

Valid Pattern Types

  • none
  • solid
  • darkGray
  • mediumGray
  • lightGray
  • gray125
  • gray0625
  • darkHorizontal
  • darkVertical
  • darkDown
  • darkUp
  • darkGrid
  • darkTrellis
  • lightHorizontal
  • lightVertical
  • lightDown
  • lightUp
  • lightGrid
  • lightTrellis

Note: For further more customization please refer the exceljs documentation

Poople

License

MIT