Sling Academy
Home/Node.js/How to generate Excel (xlsx) files using Node.js

How to generate Excel (xlsx) files using Node.js

Last updated: December 30, 2023

Node.js provides a powerful platform for creating various types of files, including Excel (xlsx) files, which are commonly used for reporting and data analysis. By leveraging certain libraries, we can automate the process of generating well-structured Excel documents. This tutorial will guide you through the process step by step.

Getting Started

Before diving into code examples, let’s set up our Node.js environment and install the necessary library for handling Excel files.

const { writeFile } = require('fs').promises;
const ExcelJS = require('exceljs');

async function generateBasicExcel(){
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('My Sheet');

  worksheet.columns = [
    { header: 'Id', key: 'id', width: 10 },
    { header: 'Name', key: 'name', width: 30 },
    { header: 'Email', key: 'email', width: 30 }
  ];

  worksheet.addRow({id: 1, name: 'John Doe', email: '[email protected]'});
  await workbook.xlsx.writeFile('basic.xlsx');

  console.log(`basic.xlsx file generated successfully!`);
}

generateBasicExcel();

Adding Styles and Formats

Customizing the workbook with styles and formats enhances the readability and appearance of the data within the Excel file.

// ... [Code shortened for brevity]

const headerFont = { name: 'Calibri', family: 4, size: 12, bold: true };
worksheet.getRow(1).font = headerFont;

// Apply styles to columns
worksheet.getColumn(2).numFmt = '@'; // Text format
worksheet.getColumn(3).numFmt = '0.00'; // Two decimal places

// ...

Working with Multiple Sheets and Complex Data

Dealing with datasets often requires handling multiple sheets and complex data arrangements.

// ... [Code shortened for brevity]

function addNewSheet(workbook, sheetName){
  const newSheet = workbook.addWorksheet(sheetName);

  // Configure the sheet as needed
  // ...
}

// Use the function as follows
addNewSheet(workbook, 'Second Sheet');

// ...

Data Visualization with Charts

To give a visual touch to the data, Excel charts can be programmatically added to the sheets.

// ... [Code shortened for brevity]

const chart = worksheet.addChart('pie', {
  title: {
    text: 'Sample Pie Chart'
  },
  series: [
    {
      name: 'Pie Series 1',
      labels: ['Category 1', 'Category 2', 'Category 3'],
      dataFormula: 'Sheet1!$B$1:$B$3'
    }
  ],
  legend: {
    position: 'right'
  }
});

// Set the chart position
worksheet.addDrawing(chart);

// ...

Conclusion

By following the steps above and experimenting with different features offered by Node.js libraries, you can generate sophisticated Excel files tailored to your specific needs. The possibilities for automation, formatting, and data presentation are extensive with Node.js and Excel files.

Next Article: ExpressJS: Using MORGAN to Log HTTP Requests

Previous Article: Node.js + Express: How to Return PDF Files

Series: Node.js & Express Tutorials

Node.js

You May Also Like

  • NestJS: How to create cursor-based pagination (2 examples)
  • Cursor-Based Pagination in SequelizeJS: Practical Examples
  • MongooseJS: Cursor-Based Pagination Examples
  • Node.js: How to get location from IP address (3 approaches)
  • SequelizeJS: How to reset auto-increment ID after deleting records
  • SequelizeJS: Grouping Results by Multiple Columns
  • NestJS: Using Faker.js to populate database (for testing)
  • NodeJS: Search and download images by keyword from Unsplash API
  • NestJS: Generate N random users using Faker.js
  • Sequelize Upsert: How to insert or update a record in one query
  • NodeJS: Declaring types when using dotenv with TypeScript
  • Using ExpressJS and Multer with TypeScript
  • NodeJS: Link to static assets (JS, CSS) in Pug templates
  • NodeJS: How to use mixins in Pug templates
  • NodeJS: Displaying images and links in Pug templates
  • ExpressJS + Pug: How to use loops to render array data
  • ExpressJS: Using MORGAN to Log HTTP Requests
  • NodeJS: Using express-fileupload to simply upload files
  • ExpressJS: How to render JSON in Pug templates