Generate Excel with Node.js
2 min readSep 18, 2021
Basic usage of sheetjs (xlsx package for Node.js)
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
- Install sheetjs
npm install xlsx2. 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 🎉
