json-excel-style v1.0.0

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

  • 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

• 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

• ISettings

• IData

[key: string] : string | number | boolean | Date | IData | any

• IHeader

• Hyperlink

• 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.

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 for fill.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

• sheetjs
• json-as-xlsx
• sheetjs-style

🔖 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.