How to create an excel file using xlsx library in Nodejs ?

In this blog, you will learn how to create an Excel file and how to add sheets with a specific name and how to define width of each column. Just play with me along the blog and by the end of this blog, you will have a basic understanding about generating an excel file in Nodejs.

Step 1 :

Import the xlsx library as below and create an array using some sample data.

const XLSX = require(“xlsx”); const data = [{ id: 1, name: "Venkata", age: "22", city: "Hyderabad", jobTitle: "Frontend Developer" },{ id: 2, name: "Satya", age: "23", city: "Bengaluru", jobTitle: "CEO" },{ id: 3, name: "Sandeep", age: "24", city: "Chennai", jobTitle: "Android and iOS Developer" },{ id: 4, name: "Kumar", age: "25", city: "Mumbai", jobTitle: "Manager" }]

Step 2 :

  1. Create a new excel work book wb.
  2. Create a new sheet ws with data provided.
  3. Now append the sheet ws to workbook wb with a sheet name.
  4. XLSX.utils.book_append_sheet(wb, ws, “Sheet1”); Here first argument is the file which you want to append sheet and second argument is the sheet which you are created and third one is the sheet name.
  5. Finally you can generate an excel file with a name of your choice.
const wb = XLSX.utils.book_new(); const ws = XLSX.utils.json_to_sheet(data); XLSX.utils.book_append_sheet(wb, ws, "Sheet1");XLSX.writeFile(wb, "sample_file.xlsx");
File with data

If you don't pass data or the data is empty, then the file created will be an empty file.

File without data

Step 3 :

If you want to add more sheets with different data, then you need to create more sheets with a different variable name and just append to the workbook with a specific name.

const bankDetails = [{ no: 1, name: "Venkata", accountNumber: "598562000286", bankName: "SBI", ifsc: "SBIN0005889" },{ no: 2, name: "Satya", accountNumber: "558562000286", bankName: "ICICI", ifsc: "ICIC0005189" },{ no: 3, name: "Sandeep", accountNumber: "1000562000286", bankName: "Indian Bank", ifsc: "IDIB0001234" },{ no: 4, name: "Kumar", accountNumber: "85400548891005", bankName: "HDFC", ifsc: "HDFC0001248" }]const wsTwo = XLSX.utils.json_to_sheet(bankDetails);XLSX.utils.book_append_sheet(wb, wsTwo, "Bank Details");

Then file will be generated with two sheets with bank details in sheet two.

Sheet two

Step 4 :

With above steps, you can generate an excel file on your own with multiple sheets and different use cases. Now one last and important thing i want to discuss is column width. If you have noticed in above examples, the ifsc in Bank Details sheet is overflowing. To overcome this problem, you can predefine the width for each column.

const wscols = [{ wch: 5 },{ wch: 13 },{ wch: 13 },{ wch: 13 },{ wch: 13 },];wsTwo['!cols'] = wscols;

Just before writing file, please add the above line. Here we have defined column widths and assigned to wsTwo sheet so that the changes will reflect in Bank Details sheet.

Column Width Changed

Conclusion :

I tried to explain the most basic things a beginner require to start using xlsx npm library. So if you are not a beginner and want to explore more, you can go to xlsx documentation in npmjs.

Thanks for reading the blog! Please make sure you give this post some claps via the clap button on the left if you found this post useful. Please leave a comment here and follow me here on Medium.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store