Generate Excel with Node.js

Basic usage of sheetjs (xlsx package for Node.js)

Image by F1 Digitals from Pixabay

TL;DR

const XLSX = require('xlsx');const data = [
{ name: 'Diary', code: 'diary_code', author: 'Pagorn' },
{ name: 'Note', code: 'note_code', author: 'Pagorn' },
{ name: 'Medium', code: 'medium_code', author: 'Pagorn' },
]
const workSheet = XLSX.utils.json_to_sheet(data);
const workBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workBook, workSheet, "Sheet 1");
XLSX.writeFile(workBook, "./temp/sample.xlsx");

Step by step

  1. Install sheetjs
npm install xlsx

2. import xlsx

const XLSX = require('xlsx');

3. Initial Work Sheet with data. There are 3 ways. Pick one

3.1 aoa_to_sheet (array of arrays)

const aoaData = [
['name', 'code', 'author'],
['Diary', 'diary_code', 'Pagorn'],
['Note', 'note_code', 'Pagorn'],
['Medium', 'medium_code', 'Pagorn'],
];
const workSheet = XLSX.utils.aoa_to_sheet(aoaData);

3.2 json_to_sheet

const jsonData = [
{ name: 'Diary', code: 'diary_code', author: 'Pagorn' },
{ name: 'Note', code: 'note_code', author: 'Pagorn' },
{ name: 'Medium', code: 'medium_code', author: 'Pagorn' },
]
const workSheet = XLSX.utils.json_to_sheet(jsonData);

3.3 table_to_sheet

const htmlString = `
<table>
<tr><th>name</th><th>code</th><th>author</th></tr>
<tr><td>Diary</td><td>diary_code</td><td>Pagorn</td></tr>
<tr><td>Note</td><td>note_code</td><td>Pagorn</td></tr>
<tr><td>Medium</td><td>medium_code</td><td>Pagorn</td></tr>
</table>
`
// Convert string to HTMLTableElement for Node.js
const jsdom = require('jsdom');
const { JSDOM } = jsdom;
const dom = new JSDOM(htmlString);
const table = dom.window.document.getElementById('table1')
const workSheet = XLSX.utils.table_to_sheet(table);

4. Initial Work Book

const workBook = XLSX.utils.book_new();

5. Append Work Book to Work Sheet

XLSX.utils.book_append_sheet(workBook, workSheet, 'Sheet 1');

6. Write Work Book to a file

XLSX.writeFile(workBook, './temp/sample.xlsx');

7. Run and see the result 🎉

sample.xlsx

Developer