Create a Spreadsheet with JSON Data Using ExcelJS
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');
})();