json-excel-style
ℹ️ About
This project was inspired by json-as-xlsx, and aims to make the export of json data to Excel easier, with more style provided.
The main dependencies is sheetjs-style and underlying dependencies for this project is sheetjs.
All projects are under the Apache 2.0 License.
🔌 Installation
npm:
npm install json-excel-style --save
yarn:
yarn add json-excel-style
🪛 Main function
-
jsonExcel(data: IJsonSheet[], settings?: ISettings)
export excel file with multiple sheets-
Example (click to show)
const baseUrl = 'http://www.baseUrl.com' const data = [ { sheetName: 'Adults', header: [ { label: 'ID', value: 'id', option: { type: 'hyperlink', render: row => ({ Target: `${baseUrl}/${row?.group}/${row?.id}`, Tooltip: `click to visit ${baseUrl}/${row?.group}/${row?.id}` }), style: { fill: { fgColor: { rgb: 'FFFFAA' } }, font: { bold: true, italic: true } } } }, { label: 'User', value: 'user' }, // Top level data { label: 'Age', value: row => row.age + ' years' }, // Run functions { label: 'Phone', value: row => (row.more ? row.more.phone || '' : '') } // Deep props ], data: [ { id: 1, group: 1, user: 'Andrea', age: 20, more: { phone: '11111111' } }, { id: 2, group: 1, user: 'Luis', age: 21, more: { phone: '22222222' } }, { id: 3, group: 2, user: 'Tom', age: 18, more: { phone: '33333333' } }, { id: 4, group: 2, user: 'Jack', age: 24, more: { phone: '444444444' } } ] }, { sheetName: 'Children', header: false, data: [ { id: 11, group: 3, user: 'Manuel', age: 16, more: { phone: '55555555' } }, { id: 12, group: 4, user: 'Ana', age: 17, more: { phone: '66666666' } } ] } ] const settings = { fileName: 'PersonalInformation', extraLength: 3, } jsonExcel(data, settings)
-
-
jsonSheet(data: IData[], header?: IHeader[] | boolean, fileName?: string, settings?: ISettings)
fast export single sheet excel file-
Example (click to show)
const baseUrl = 'http://www.baseUrl.com' const data = [ { id: 1, group: 1, user: 'Andrea', age: 20, more: { phone: '11111111' } }, { id: 2, group: 1, user: 'Luis', age: 21, more: { phone: '22222222' } }, { id: 3, group: 2, user: 'Tom', age: 18, more: { phone: '33333333' } }, { id: 4, group: 2, user: 'Jack', age: 24, more: { phone: '444444444' } } ] const header = [ { label: 'ID', value: 'id', option: { type: 'hyperlink', render: row => ({ Target: `${baseUrl}/${row?.group}/${row?.id}`, Tooltip: `click to visit ${baseUrl}/${row?.group}/${row?.id}` }), style: { fill: { fgColor: { rgb: 'FFFFAA' } }, font: { bold: true, italic: true } } } }, { label: 'User', value: 'user' }, // Top level data { label: 'Age', value: row => row.age + ' years' }, // Run functions { label: 'Phone', value: row => (row.more ? row.more.phone || '' : '') } // Deep props ] jsonSheet(data, header, "AdultsInformation")
-
🗒 API
- IJsonSheet
Attributes | Type | Required |
---|---|---|
sheetName | string |
false |
header |
IHeader[] | boolean
|
false |
data | IData[] |
true |
- ISettings
Attributes | Describe | Type | Required |
---|---|---|---|
extraLength | A bigger number means that columns will be wider | number |
false |
fileName | The name of the exported file | string |
false |
writeOptions | Check detail | WritingOptions |
false |
- IData
[key: string]
: string | number | boolean | Date | IData | any
- IHeader
Attributes | Sub Attributes | Describe | Type | Required |
---|---|---|---|---|
label | - | Text displayed in the table header | string |
true |
value | - | The key of IData |
string | ((value: IData) => string
|
true |
option | type | Cell type, default "text"
|
"text" | "hyperlink"
|
false |
render | Hyperlink configuration, effective when type is "hyperlink"
|
Hyperlink | ((value: IData) => Hyperlink)
|
false |
|
style | The style of column | CellStyle |
false |
- Hyperlink
Attributes | Describe | Type | Required |
---|---|---|---|
Target | Hyperlink address | string |
true |
Tooltip | Tooltip | string |
false |
- CellStyle
Cell styles are specified by a style object that roughly parallels the OpenXML structure. The style object has five
top-level attributes: fill
, font
, numFmt
, alignment
, and border
.
Style Attribute | Sub Attributes | Values |
---|---|---|
fill | patternType |
"solid" or "none"
|
fgColor | COLOR_SPEC |
|
bgColor | COLOR_SPEC |
|
font | name |
"Calibri" // default |
sz |
"11" // font size in points |
|
color | COLOR_SPEC |
|
bold |
true or false
|
|
underline |
true or false
|
|
italic |
true or false
|
|
strike |
true or false
|
|
outline |
true or false
|
|
shadow |
true or false
|
|
vertAlign |
true or false
|
|
numFmt |
"0" // integer index to built in formats, see StyleBuilder.SSF property |
|
"0.00%" // string matching a built-in format, see StyleBuilder.SSF |
||
"0.0%" // string specifying a custom format |
||
"0.00%;\\(0.00%\\);\\-;@" // string specifying a custom format, escaping special characters |
||
"m/dd/yy" // string a date format using Excel's format notation |
||
alignment | vertical |
"bottom" or "center" or "top"
|
horizontal |
"left" or "center" or "right"
|
|
wrapText |
true or false
|
|
readingOrder |
2 // for right-to-left |
|
textRotation | Number from 0 to 180 or 255 (default is 0 ) |
|
90 is rotated up 90 degrees |
||
45 is rotated up 45 degrees |
||
135 is rotated down 45 degrees |
||
180 is rotated down 180 degrees |
||
255 is special, aligned vertically |
||
border | top | { style: BORDER_STYLE, color: COLOR_SPEC } |
bottom | { style: BORDER_STYLE, color: COLOR_SPEC } |
|
left | { style: BORDER_STYLE, color: COLOR_SPEC } |
|
right | { style: BORDER_STYLE, color: COLOR_SPEC } |
|
diagonal | { style: BORDER_STYLE, color: COLOR_SPEC } |
|
diagonalUp |
true or false
|
|
diagonalDown |
true or false
|
COLOR_SPEC: Colors for fill
, font
, and border
are specified as objects, either:
-
{ auto: 1}
specifying automatic values -
{ rgb: "FFFFAA00" }
specifying a hex ARGB value -
{ theme: "1", tint: "-0.25"}
specifying an integer index to a theme color and a tint value (default 0) -
{ indexed: 64}
default value forfill.bgColor
BORDER_STYLE: Border style is a string value which may take on one of the following values:
thin
medium
thick
dotted
hair
dashed
mediumDashed
dashDot
mediumDashDot
dashDotDot
mediumDashDotDot
slantDashDot
Borders for merged areas are specified for each cell within the merged area. So to apply a box border to a merged area of 3x3 cells, border styles would need to be specified for eight different cells:
- left borders for the three cells on the left,
- right borders for the cells on the right
- top borders for the cells on the top
- bottom borders for the cells on the left
🙏 Thanks
🔖 License
Please consult the attached LICENSE file for details. All rights not explicitly granted by the Apache 2.0 License are reserved by the Original Author.