excel#

Write Excel workbooks from JSON data.

Install#

npm i @nodedk/excel

Usage#

var excel = require('@nodedk/excel')

var data = [
  {
    sheet: 'Firmaliste',
    columns: [
      { label: 'Name', value: 'name' },
      { label: 'Organization number', value: 'organizationNumber' }
    ],
    content: [
      {
        name: 'NodeDK AS',
        organizationNumber: '123456789'
      }
    ]
  }
]

excel(data, { fileName: '/tmp/companies' })

The output file is /tmp/companies.xlsx.

API#

excel(data, settings = {}, workbookCallback)#

Returns the writer’s result synchronously. An empty data array returns undefined. By default, writes an .xlsx file and returns undefined.

Each worksheet in data accepts:

• sheet: name, default 'Sheet 1', 'Sheet 2', and so on.
• columns: array of column definitions, default [].
• content: row objects, default [].
• tables: optional array of { columns, content } blocks, used instead of the worksheet’s top-level columns/content when nonempty.
• tablesLayout: 'vertical' (default) or 'horizontal'.
• tablesGap: blank rows or columns between blocks, default 1.

Each column accepts:

• label: column heading.
• value: row property path, including dots, or function (row) returning the cell value.
• format: Excel number format string, or 'hyperlink' for link values.
• headerStyle, cellStyle: style objects, applied when styles are enabled.

Cell values can be strings, numbers, booleans, Dates, null, or styled cell objects. A styled cell uses v for its value, with optional t (cell type), s (style), z (number format), and l: { Target } (hyperlink). Style objects support font, fill, border, alignment, and numFmt.

Settings are forwarded to json-as-xlsx:

• fileName: path without the extension; default 'Spreadsheet'.
• extraLength: additional computed column width, default 1.
• writeMode: 'write' returns serialized data; 'writeFile' writes a file. When omitted, writeOptions.type: 'buffer' returns a Buffer; otherwise the writer saves a file.
• writeOptions: underlying workbook serialization options, including type and bookType. With writeMode: 'write', type defaults to 'buffer'; other output types include 'array', 'base64', 'binary', and 'string'.
• RTL: right-to-left workbook view, default false.
• enableStyles: apply cell styles, default false; requires the xlsx book type.
• writeEmptyValuesAsBlankCells: keep nullish and empty values as blank cells, default false.

The optional workbookCallback(workbook) runs before serialization. It may modify the workbook synchronously; its return value is ignored.

Return a Buffer#

var excel = require('@nodedk/excel')

var buffer = excel(
  [
    {
      sheet: 'People',
      columns: [{ label: 'Name', value: 'name' }],
      content: [{ name: 'Ada' }]
    }
  ],
  { writeMode: 'write', writeOptions: { type: 'buffer' } },
  function (workbook) {
    workbook.Props = { Title: 'People' }
  }
)

console.log(buffer.length)

Created by Vidar Eldøy