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
Top comments (0)