npm install json2excelformatter// 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);
});
- 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
- config
- data
json2excelformat.initProcess(config, data)- config param contains the basic structure of input data
-
config.dataFormat must be either
default|custom|tree -
defaultdataFormat is used to generate the plain excel format without any styles -
customdataFormat used to generate excel with styles -
treedataFormat 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
}
}
},- dataFormat:
default|custom|tree - For custom and tree dataFormat every value of cell object should contains type:
text|link -
texttype:text mentioned for the value of text is in string format
name: {
type: 'text',
value: {
text: 'Kumar'
},
}-
linktype: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
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: []
}
]
}- 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
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 |
true, false |
| outline | Font outline | true, false |
- 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. |
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
- The original author of json2excelformatter is Sathish Nagarajan