[TIL] Data parsing to .xlsx-SheetJs

When you need Excel files from server data, using xlsx library can help code simpler and more easier to develop a function of Exceldownload.

In this article, you will know not only how to parse data(json) to sheet but customized header names on the top of excel name of file that you download.

Usage

1. To use xlsx functions, install xlsm library in your terminal on the same project you work on.

npm install xlsx npm: xlsx

Then, npm package will be injected in package.json where indicates being ready to exposes the module in each file.

2. Import the module parameter in the top of file

import { utils, writeFile } from 'xlsx';

3. Generate a worksheet and create a new workbook

const workSheet = utils.json_to_sheet(data) //data : server data which transfers into excel data
const workbook = utils.book_new();

• json_to_sheet generates a worksheet which and book_new creates a new workbook whice will be attacted in excel file.

4. Convert default headers into customized ones and add an exisiting worksheet to a workbook

utils.sheet_add_aoa(workSheet, [header], { origin: 0 });
utils.book_append_sheet(workbook, workSheet, sheetName);   

• sheet_add_aoa takes 3 parameters. One is worksheet, Second is an array of arrays of JS values for updating a worksheet object and the other is the cell’s location starting point
• book_append_sheet appends a worksheet to the workbook. Set the sheetName you want to use in the third parameter

5. Finally, generate and attempt to save file

 writeFile(workbook, filename, opts);

writeFile creates a spreadsheet file and tries to write it to the system.

While this is happening, In the browser, it will try to prompt the user to download the file. In NodeJS, it will write to the local directory.

---

Codes

import { utils, writeFile } from 'xlsx';

const data = axios.post('api/download/test');
const header = ['이름', '나이', '주소', '번호']

if(data.lenght){
    try {
        const workSheet = utils.json_to_sheet(data)
        const workbook = utils.book_new();
        utils.sheet_add_aoa(workSheet, [header], { origin: 0 });
        utils.book_append_sheet(workbook, workSheet, sheetName[0]);
        writeFile(workbook, `${title}.xlsx`);
    }catch(e) {
        console.error(e)
        alert('다운로드 실패. 다시 시도해주세요.')
    }
}else {
	alert('데이터가 없습니다. 목록에서 확인해주세요.')
}

---

Reference

npmjs_xlsx