How to Export Json Data to Excel File Using JavaScript

JSON to excel conversion

I researched about this a lot and finally I could come up with a solution to this.

public exportAsExcelFile(json: Array<object>, excelFileName: string): void {
var worksheet: XLSX.WorkSheet = XLSX.utils.aoa_to_sheet([
  [`${excelFileName}`]]); // message to display
worksheet['!merges'] = [{ s: { r: 0, c: 0 }, e: { r: 0, c: 3 } }]; //for merging columns. s : start, e: end, c: column, r: row
XLSX.utils.sheet_add_json(worksheet, json, { origin: "A2" }); //origin for json data
const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
var range = XLSX.utils.decode_range(worksheet['!ref']);
for (var C = range.s.r; C <= range.e.r; ++C) {
  var address = XLSX.utils.encode_col(C) + "1"; 
  if (!worksheet[address]) continue;
  worksheet[address].v = worksheet[address].v.charAt(0).toUpperCase() + worksheet[address].v.substr(1).toLowerCase();
}
}

How can I export a JSON object to Excel using Nextjs/React?

Use xlsx third-party library.

npm install xlsx

Using Library:

import XLSX from "xlsx";

Download function trigger at button click(pass data as argument):

downloadExcel = (data) => {
    const worksheet = XLSX.utils.json_to_sheet(data);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
    //let buffer = XLSX.write(workbook, { bookType: "xlsx", type: "buffer" });
    //XLSX.write(workbook, { bookType: "xlsx", type: "binary" });
    XLSX.writeFile(workbook, "DataSheet.xlsx");
  };

Download Button: (function call: you should modify it as per your requirement, below one is React Class Component implementation, that's why I used this )

<button onClick={()=>this.downloadExcel(data)}>
    Download As Excel
</button>

Related Topics