search

catamphetamine / write-excel-file

Write simple *.xlsx files in a browser or Node.js
https://catamphetamine.gitlab.io/write-excel-file/
MIT License
25 stars 3 forks source link

readme

write-excel-file

Write simple *.xlsx files in a browser or Node.js

Demo

Also check out read-excel-file for reading small to medium *.xlsx files.

Install

npm install write-excel-file --save

If you're not using a bundler then use a standalone version from a CDN.

Data

To write an *.xlsx file, one should provide the data — an array of rows. Each row must be an array of cells.

Each cell should have a value, a type, and, optionally, other cell parameters.

If a cell doesn't have a type, then it is automatically detected from the value, or defaults to a String. Possible types are:

An empty cell could be represented by null or undefined.

const HEADER_ROW = [
  {
    value: 'Name',
    fontWeight: 'bold'
  },
  {
    value: 'Date of Birth',
    fontWeight: 'bold'
  },
  {
    value: 'Cost',
    fontWeight: 'bold'
  },
  {
    value: 'Paid',
    fontWeight: 'bold'
  }
]

const DATA_ROW_1 = [
  // "Name"
  {
    type: String,
    value: 'John Smith'
  },

  // "Date of Birth"
  {
    type: Date,
    value: new Date(),
    format: 'mm/dd/yyyy'
  },

  // "Cost"
  {
    type: Number,
    value: 1800
  },

  // "Paid"
  {
    type: Boolean,
    value: true
  }
]

const data = [
  HEADER_ROW,
  DATA_ROW_1,
  ...
]

API

Browser

import writeXlsxFile from 'write-excel-file'

await writeXlsxFile(data, {
  columns, // (optional) column widths, etc.
  fileName: 'file.xlsx'
})

Uses file-saver to save an *.xlsx file from a web browser.

If fileName parameter is not passed then the returned Promise resolves to a "blob" with the contents of the *.xlsx file.

Node.js

const writeXlsxFile = require('write-excel-file/node')

await writeXlsxFile(data, {
  columns, // (optional) column widths, etc.
  filePath: '/path/to/file.xlsx'
})

If filePath parameter is not passed, but buffer: true parameter is passed, then it returns a Buffer:

const buffer = await writeXlsxFile(data, { buffer: true })

If neither filePath parameter nor buffer: true parameter are passed, then it returns a readable Stream:

const output = fs.createWriteStream(...)
const stream = await writeXlsxFile(data)
stream.pipe(output)
AWS S3 might refuse to accept the stream ##### AWS S3 might throw `Cannot determine length of [object Object]`: ```js await new AWS.S3().putObject({ Bucket: ..., Key: ..., Body: stream, ContentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }).promise() ``` The reason is that AWS S3 [only accepts streams of known length](https://github.com/aws/aws-sdk-js/issues/2961), and the length of a zip file can't be known in advance. Workaround for AWS SDK v2: write to `Buffer` instead of a stream. Workaround for AWS SDK [v3](https://aws.amazon.com/blogs/developer/modular-packages-in-aws-sdk-for-javascript/): use [`Upload`](https://github.com/aws/aws-sdk-js/issues/2961#issuecomment-868352176) operation.

Schema

Alternatively, instead of providing data, one could provide a list of objects and a schema describing each column:

const objects = [
  {
    name: 'John Smith',
    dateOfBirth: new Date(),
    cost: 1800,
    paid: true
  },
  {
    name: 'Alice Brown',
    dateOfBirth: new Date(),
    cost: 2600,
    paid: false
  }
]
const schema = [
  {
    column: 'Name',
    type: String,
    value: student => student.name
  },
  {
    column: 'Date of Birth',
    type: Date,
    format: 'mm/dd/yyyy',
    value: student => student.dateOfBirth
  },
  {
    column: 'Cost',
    type: Number,
    format: '#,##0.00',
    value: student => student.cost
  },
  {
    column: 'Paid',
    type: Boolean,
    value: student => student.paid
  }
]

When using a schema, column types are required (not autodetected).

Schema API

Browser

import writeXlsxFile from 'write-excel-file'

await writeXlsxFile(objects, {
  schema,
  fileName: 'file.xlsx'
})

Node.js

const writeXlsxFile = require('write-excel-file/node')

await writeXlsxFile(objects, {
  schema,
  filePath: '/path/to/file.xlsx'
})

Cell Parameters

Aside from having a type and a value, each cell (or schema column) can also have:

Table Header

Schema

When using a schema, column titles can be set via a column property on each column. It will be printed at the top of the table.

const schema = [
  // Column #1
  {
    column: 'Name', // Column title
    value: student => student.name
  },
  ...
]

If column property is missing then column title won't be printed.

The default table header style is fontWeight: "bold" and align being same as the schema column's align. One can provide a custom table header style by supplying a getHeaderStyle parameter:

await writeXlsxFile(objects, {
  schema,
  getHeaderStyle: (column) => ({
    backgroundColor: '#eeeeee',
    fontWeight: 'bold',
    align: column.align,
    indent: column.indent
  }),
  filePath: '/path/to/file.xlsx'
})

Cell Data

When not using a schema, one can print column titles by supplying them as the first row of the data:

const data = [
  [
    { value: 'Name', fontWeight: 'bold' },
    { value: 'Age', fontWeight: 'bold'},
    ...
  ],
  ...
]

Column Width

Column width can also be specified (in "characters").

Schema

To specify column width when using a schema, set a width on a schema column:

const schema = [
  // Column #1
  {
    column: 'Name',
    value: student => student.name,
    width: 20 // Column width (in characters).
  },
  ...
]

Cell Data

When not using a schema, one can provide a separate columns parameter to specify column widths:

// Set Column #3 width to "20 characters".
const columns = [
  {},
  {},
  { width: 20 }, // in characters
  {}
]

Font

The default font is Calibri at 12px. To change the default font, pass fontFamily and fontSize parameters when calling writeXlsxFile():

await writeXlsxFile(data, {
  filePath: '/path/to/file.xlsx',
  fontFamily: 'Arial',
  fontSize: 16
})

Orientation

To specify custom orientation (for all sheets), pass orientation parameter when calling writeXlsxFile():

await writeXlsxFile(data, {
  filePath: '/path/to/file.xlsx',
  orientation: 'landscape'
})

Date Format

To set the default date format, pass dateFormat parameter when calling writeXlsxFile():

await writeXlsxFile(data, {
  filePath: '/path/to/file.xlsx',
  dateFormat: 'mm/dd/yyyy'
})

Sticky Rows

To make some of the top rows "sticky" (Excel calls them "frozen"), pass stickyRowsCount parameter when calling writeXlsxFile():

await writeXlsxFile(data, {
  filePath: '/path/to/file.xlsx',
  stickyRowsCount: 1
})

Sticky Columns

To make some of the columns at the start "sticky" (Excel calls them "frozen"), pass stickyColumnsCount parameter when calling writeXlsxFile():

await writeXlsxFile(data, {
  filePath: '/path/to/file.xlsx',
  stickyColumnsCount: 1
})

Hide Grid Lines

To hide grid lines, pass showGridLines: false parameter when calling writeXlsxFile():

await writeXlsxFile(data, {
  filePath: '/path/to/file.xlsx',
  showGridLines: false
})

Right-to-Left

To use right-to-left layout on all sheets, pass rightToLeft: true parameter when calling writeXlsxFile():

await writeXlsxFile(data, {
  filePath: '/path/to/file.xlsx',
  rightToLeft: true
})

Sheet Name

To set the default sheet name, pass a sheet parameter when calling writeXlsxFile():

await writeXlsxFile(data, {
  filePath: '/path/to/file.xlsx',
  sheet: 'Data'
})

Multiple Sheets

Schema

To generate an *.xlsx file with multiple sheets:

await writeXlsxFile([objects1, objects2], {
  schema: [schema1, schema2],
  sheets: ['Sheet 1', 'Sheet 2'],
  filePath: '/path/to/file.xlsx'
})

Cell Data

To generate an *.xlsx file with multiple sheets:

await writeXlsxFile([data1, data2], {
  columns: [columns1, columns2], // (optional)
  sheets: ['Sheet 1', 'Sheet 2'],
  filePath: '/path/to/file.xlsx'
})

Images

This library currently doesn't support inserting images in a spreadsheet, although implementing such feature would be kinda trivial because I've already researched it a bit and described the implementation instructions in a document.

TypeScript

This library comes with TypeScript "typings". If you happen to find any bugs in those, create an issue.

CDN

One can use any npm CDN service, e.g. unpkg.com or jsdelivr.net

<script src="https://unpkg.com/write-excel-file@1.x/bundle/write-excel-file.min.js"></script>

<script>
  writeXlsxFile(objects, schema, {
    fileName: 'file.xlsx'
  })
</script>

References

This project was inspired by zipcelx package.

GitHub

On March 9th, 2020, GitHub, Inc. silently banned my account (erasing all my repos, issues and comments, even in my employer's private repos) without any notice or explanation. Because of that, all source codes had to be promptly moved to GitLab. The GitHub repo is now only used as a backup (you can star the repo there too), and the primary repo is now the GitLab one. Issues can be reported in any repo.

License

MIT