avatar

ShinChven's Blog

Create a Spreadsheet with JSON Data Using ExcelJS

Tue Nov 15 2022

ExcelJS is a great JavaScript library to read, manipulate and write spreadsheet data and styles to XLSX and JSON. I've used it multiple times in my work and projects.

Here's a digest of how I use it to create a spreadsheet with JSON Data.

import {Workbook} from 'exceljs';

/**
 * Define a data model to be used in the workbook
 */
type UserModel = {
  id: number;
  name: string;
  phone: string;
}

/**
 * Output JSON/Object data to an Excel workbook.
 * @param outputFile
 * @param data
 */
const outputExcel = async (data: UserModel[], outputFile: string) => {
  // 1. Create a workbook, which is the xlsx file that may contain multiple worksheets.
  const workbook = new Workbook()
  // 2. Create a worksheet with a given name, you may create multiple worksheets with different names in a workbook.
  const worksheet = workbook.addWorksheet('Users');
  // 3. Define columns and bind data fields to columns.
  worksheet.columns = [
    {
      header: 'ID', // Label of column's header.
      key: 'id',    // The key to bind data field to column, see it in the {UserModel} above.
      width: 10,    // Optional, column width
    },
    {header: 'Name', key: 'name', width: 32},
    {header: 'Phone', key: 'phone', width: 32},
  ];
  // 4. Add the data array to the worksheet
  worksheet.addRows(data);
  // you may also add an array of data as a row to while not using the header binding.
  // worksheet.addRow([1, 'John Doe', '0123456789']);
  await workbook.xlsx.writeFile(outputFile);
}

(async () => {
  // Populate data
  const users: UserModel[] = [
    {id: 1, name: 'John', phone: '123456789'},
    {id: 2, name: 'Anne', phone: '987654321'},
    {id: 3, name: 'Zack', phone: '123987456'},
    {id: 4, name: 'Jill', phone: '456123789'},
    {id: 5, name: 'Judy', phone: '789456123'},
    {id: 6, name: 'Jenny', phone: '123789456'},
    {id: 7, name: 'David', phone: '456789123'},
  ]
  // Output data to an Excel file
  await outputExcel(users, 'users.xlsx');
})();