Export an Array of JSON Objects to Excel with SheetJS

In this article, we will demonstrate how to convert JSON response data to an Excel file with defined headers and columns that can be downloaded directly by the client.

To accomplish this, we will utilize the SheetJS plugin.

Let's begin, add a button that users will click to generate an export

<button type="button" id="exportExcel" > Export Excel </button>

then create array of "employees" objects

const employees = [
  {
    id: '1',
    name: 'Saitama',
    age: '32'
  },
  {
    id: '2',
    name: 'Genos',
    age: "24"
  }
]

Next create a workbook
XLSX.utils.json_to_sheet generates a worksheet:

const worksheet = XLSX.utils.json_to_sheet(employees);

XLSX.utils.book_new creates a new workbook and XLSX.utils.book_append_sheet appends a worksheet to the workbook. The new worksheet will be called "Empoloyess":

const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Employees");

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

compression: true enables ZIP compression for XLSX and other formats.

XLSX.writeFile(workbook, "Employee Lists.xlsx", { compression: true });

That’s it! this is the simplest code to convert JSON data in XLSX EXCEL file which will download on the client side.

Access functioning demo here

PS.

By default, json_to_sheet creates a worksheet with a header row. In this case, the headers come from the JS object keys: "id", "names" and "age".

Let's create array of custom header

const excelHeader = [
  "Employee Id",
  "Full Name",
  "Age"
]

If we want to fix the header and customize the name we can simply use XLSX.utils.sheet_add_aoa that can write text values to the existing worksheet starting at cell A1:

XLSX.utils.sheet_add_aoa(worksheet, [excelHeader], { origin: "A1" });

And since some names may exceed the default column width, we can adjust the column width by defining the "!cols" worksheet property. For example, the following line sets the width of column A to approximately length of the header + 5

// Map the array and get the length and add 5 to add extra spaces.
// Push the value to the defined variable `wscols` 
// Assign the varialbe `wscols` to `worksheet["!cols"]`
let wscols = []
excelHeader.map(arr => {
  wscols.push({ wch: arr.length + 5 })
})
worksheet["!cols"] = wscols;

Access functioning demo here