write-excel-file

    1.3.3 • Public • Published

    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.

    Use

    To write an *.xlsx file, provide the data — an array of rows, each row being an array of cells, each cell having a type and a value:

    const data = [
      // Row #1
      [
        // Column #1
        {
          value: 'Name',
          fontWeight: 'bold'
        },
        // Column #2
        {
          value: 'Date of Birth',
          fontWeight: 'bold'
        },
        // Column #3
        {
          value: 'Cost',
          fontWeight: 'bold'
        },
        // Column #4
        {
          value: 'Paid',
          fontWeight: 'bold'
        }
      ],
      // Row #2
      [
        // Column #1
        {
          type: String,
          value: 'John Smith'
        },
        // Column #2
        {
          type: Date,
          value: new Date(),
          format: 'mm/dd/yyyy'
        },
        // Column #3
        {
          type: Number,
          value: 1800
        },
        // Column #4
        {
          type: Boolean,
          value: true
        }
      ],
      // Row #3
      [
        // Column #1
        {
          type: String,
          value: 'Alice Brown'
        },
        // Column #2
        {
          type: Date,
          value: new Date(),
          format: 'mm/dd/yyyy'
        },
        // Column #3
        {
          type: Number,
          value: 2600
        },
        // Column #4
        {
          type: Boolean,
          value: false
        }
      ]
    ]

    Or, alternatively, provide a list of objects and a schema to transform those objects into data:

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

    If no type is specified for a cell (or a schema column) then it defaults to a String.

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

    • align: string — Horizontal alignment of cell content. Available values: "left", "center", "right".

    • alignVertical: string — Vertical alignment of cell content. Available values: "top", "center", "bottom".

    • height: number — Row height, in "points".

    • span: number — Column span. Even if a cell spans several columns, you still have to provide a value for every cell in every row and column.

    • rowSpan: number — Row span. Even if a cell spans several rows, you still have to provide a value for every cell in every row and column.

    • wrap: boolean — Set to true to "wrap" text when it overflows the cell.

    • fontWeight: string — Can be used to print text in bold. Available values: "bold".

    • color: string — Cell text color (in hexademical format). Example: "#aabbcc".

    • backgroundColor: string — Cell background color (in hexademical format). Example: "#aabbcc".

    • borderColor: string — Cell border color. Example: "#aabbcc".

    • borderStyle: string — Cell border style. Example: "thick".

      • leftBorderColor
      • leftBorderStyle
      • rightBorderColor
      • rightBorderStyle
      • topBorderColor
      • topBorderStyle
      • bottomBorderColor
      • bottomBorderStyle
    • format: string — Cell data format. Can only be used on Date or Number cells. There're many formats supported in the *.xlsx standard. Some of the common ones:

      • 0.00 — Floating-point number with 2 decimal places. Example: 1234.56.

      • 0.000 — Floating-point number with 3 decimal places. Example: 1234.567.

      • #,##0 — Number with a comma as a thousands separator, as used in most English-speaking countries. Example: 1,234,567.

      • #,##0.00 — Currency, as in most English-speaking countries. Example: 1,234.50.

      • 0% — Percents. Example: 30%.

      • 0.00% — Percents with 2 decimal places. Example: 30.00%.

      • All Date cells (or schema columns) require a format (unless the default dateFormat is set):

        • mm/dd/yy — US date format. Example: 12/31/00 for December 31, 2000.

        • mmm d yyyy — Example: Dec 31 2000.

        • d mmmm yyyy — Example: 31 December 2000.

        • dd/mm/yyyy hh:mm AM/PM — US date-time format. Example: 31/12/2000 12:30 AM.

        • or any other format where:

          • yy — Last two digits of a year number.
          • yyyy — Four digits of a year number.
          • m — Month number without a leading 0.
          • mm — Month number with a leading 0 (when less than 10).
          • mmm — Month name (short).
          • mmmm — Month name (long).
          • d — Day number without a leading 0.
          • dd — Day number with a leading 0 (when less than 10).
          • h — Hours without a leading 0.
          • hh — Hours with a leading 0 (when less than 10).
          • mm — Minutes with a leading 0 (when less than 10).
          • ss — Seconds with a leading 0 (when less than 10).
          • AM/PM — Either AM or PM, depending on the time.

    API

    Browser

    import writeXlsxFile from 'write-excel-file'
    
    // When passing `data` for each cell.
    await writeXlsxFile(data, {
      columns, // optional
      fileName: 'file.xlsx'
    })
    
    // When passing `objects` and `schema`.
    await writeXlsxFile(objects, {
      schema,
      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')
    
    // When passing `data` for each cell.
    await writeXlsxFile(data, {
      columns, // optional
      filePath: '/path/to/file.xlsx'
    })
    
    // When passing `objects` and `schema`.
    await writeXlsxFile(objects, {
      schema,
      filePath: '/path/to/file.xlsx'
    })

    If filePath parameter is not passed then the returned Promise resolves to a Stream-like object having a .pipe() method:

    const output = fs.createWriteStream(...)
    const stream = await writeXlsxFile(objects)
    stream.pipe(output)

    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 headerStyle parameter:

    await writeXlsxFile(objects, {
      schema,
      headerStyle: {
        backgroundColor: '#eeeeee',
        fontWeight: 'bold',
        align: 'center'
      },
      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
    })

    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'
    })

    Multiple Sheets

    Schema

    To generate an *.xlsx file with multiple sheets:

    • Pass a sheets parameter — an array of sheet names.
    • The objects argument should be an array of objects for each sheet.
    • The schema parameter should be an array of schemas for each sheet.
    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:

    • Pass a sheets parameter — an array of sheet names.
    • The data argument should be an array of data for each sheet.
    await writeXlsxFile([data1, data2], {
      sheets: ['Sheet 1', 'Sheet 2'],
      filePath: '/path/to/file.xlsx'
    })

    TypeScript

    Not implemented. I'm not familiar with TypeScript.

    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

    Install

    npm i write-excel-file

    DownloadsWeekly Downloads

    358

    Version

    1.3.3

    License

    MIT

    Unpacked Size

    902 kB

    Total Files

    155

    Last publish

    Collaborators

    • catamphetamine