How to create xlsx files in NodeJs

Spreadsheets/Excel files are the most common and efficient way to manage our day to day data in row and column formats. The .xlsx is the file extensions which are used to store the data generated by Spreadsheet or Excel software.

When we build web applications which deal with lots of important data which structured in a tabular manner then often our client demands to include a feature to export that data into excel file because Spreadsheet and Excel are the most popular software used by business people to analyze those data. You can download the project files at the end of the tutorial.

In this tutorial, we are going to learn how we can create .xlxs files in NodeJS. We will also see how we can dynamically fetch the data from our MySQL database and create a .xlxs file. For this purpose, I am going to use a third party package called node-xlxs.

To connect to the MySQL database we require another package call mysql which will help us to talk to our MySQL database. Let’s quickly install all the packages using NPM.

> npm install node-xlsx mysql

How to create an xlsx file using node-xslx package.

First, we need to import all the packages which we required for this tutorial.

const mysql = require('mysql')
const xlsx = require('node-xlsx')
const fs = require('fs')

The first package is mysql package which we need to access the MySQL database. Next is the node-xlsx package which will help us to create .xlsx files.

The last package is the fs module which is used to read/write files in NodeJS and it comes with the NodeJS installation so that don’t have to explicitly install it.

Let’s try to create a simple .xlxs file.

const mysql = require("mysql");
const xlsx = require("node-xlsx");
const fs = require("fs");


const data = 
[
  ["col_heading1", "col_heading2", "col_heading3"],
  ["row1_val1", "row1_val2", "row1_val3"],
  ["row2_val1", "row2_val2", "row2_val3"],
  ["row3_val1", "row3_val2", "row3_val3"]
]

const buffer = xlsx.build([{ name: "demo_sheet", data: data }])

fs.writeFile('demo.xlsx', buffer, (err) => {
    if (err) throw err

    console.log('Done...');
    
})

The xlsx object provides a method name as build which will take an array as an argument and JSON object of name/value pair inside that array. Here we now provide name key as a name of the sheet created by the node.xlsx and second key is data which will take the data constant created by us.

It usually takes the array of an array as data. The first array in the data array acts as the column headers for our xlxs file and rest acts as the rows which need to be populated in out xlxs file. The build method will return a buffer object which we have to explicitly write it to a file.

That’s why on the next line we are trying to write the buffer data to the demo.xlsx file. If we run our app using npm then you can check your current directory to see if the demo.xlxs is generated or not. Open the demo.xlxs file and you will see something like this image,

simple-demo-for-node-xlsx

simple demo for node xlsx

How to create the xlxs file using the dynamic data from the database.

Now let’s see how can we create the xlxs file using the data from the database. First, need to create a database. For this tutorial, I am using MySQL as my database. I am naming my database as fake_db, You can choose any name you want.

CREATE DATABASE fake_db;

Let’s create a table ‘users’ to store some data in it.

CREATE TABLE users (
    id INT(10) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    phone VARCHAR(10),
    address TEXT,
    email VARCHAR(255)
)

After creating the table, we need to add some records to it. You can add 4-5 records like this,

INSERT INTO `users` (`id`, `name`, `phone`, `address`, `email`) VALUES (NULL, 'Antonio J. Hardy', '7203042446', '3787 Davis Lane\r\nTable Mesa, CO 80303', 'AntonioJHardy@dayrep.com'), (NULL, 'Salvatore A. Alex', '9414875407', '3155 Medical Center Drive\r\nSarasota, FL 34236', 'SalvatoreAAlex@armyspy.com');

Now we need to connect to this database using our mysql module we have already installed and imported.

// Connect to mysql
const con = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'fake_db',
})

con.connect((err) => {
    if (err) throw err;
    console.log('Connected to database...');
})

The con object we created maintains a connection to our database and allow us to query the database using a method called query().

con.query(query, (err, result, fields) => {})

It takes two arguments, First is the query string which is just regular SQL queries understand by the MySQL database and second is the callback method. This method gives us access to three different variables, First is err which will hold error details if occurs any while executing the SQL query, next is the result, it actually holds all the records which are returned by the database.

Last is fields it provides us metadata of the table on which we are performing the SELECT query. If you log the fields object on the console you can find the following data,

[ FieldPacket {
    catalog: 'def',
    db: 'fake_db',
    table: 'users',
    orgTable: 'users',
    name: 'id',
    orgName: 'id',
    charsetNr: 63,
    length: 10,
    type: 3,
    flags: 16899,
    decimals: 0,
    default: undefined,
    zeroFill: false,
    protocol41: true },
  FieldPacket {
    catalog: 'def',
    db: 'fake_db',
    table: 'users',
    orgTable: 'users',
    name: 'name',
    orgName: 'name',
    charsetNr: 33,
    length: 765,
    type: 253,
    flags: 0,
    decimals: 0,
    default: undefined,
    zeroFill: false,
    protocol41: true },
  FieldPacket {
    catalog: 'def',
    db: 'fake_db',
    table: 'users',
    orgTable: 'users',
    name: 'phone',
    orgName: 'phone',
    charsetNr: 33,
    length: 30,
    type: 253,
    flags: 0,
    decimals: 0,
    default: undefined,
    zeroFill: false,
    protocol41: true },
  FieldPacket {
    catalog: 'def',
    db: 'fake_db',
    table: 'users',
    orgTable: 'users',
    name: 'address',
    orgName: 'address',
    charsetNr: 33,
    length: 196605,
    type: 252,
    flags: 16,
    decimals: 0,
    default: undefined,
    zeroFill: false,
    protocol41: true },
  FieldPacket {
    catalog: 'def',
    db: 'fake_db',
    table: 'users',
    orgTable: 'users',
    name: 'email',
    orgName: 'email',
    charsetNr: 33,
    length: 765,
    type: 253,
    flags: 0,
    decimals: 0,
    default: undefined,
    zeroFill: false,
    protocol41: true } ]

As you can see in the above JSON data,  you can have access to the column name of the table on which you are performing a SELECT query. We will use this information to generate dynamic column names for our xlsx files.

As you know we have to have our data as an array of arrays but our query() method returns data as an array of JSON object like this,

[  {
    id: 1,
    name: 'Dolores A. Gibby',
    phone: '8326978158',
    address: '863 Werninger Street\r\nSugar Land, TX 77478',
    email: 'DoloresAGibby@armyspy.com' },
   {
    id: 2,
    name: 'Frank P. Randolph',
    phone: '6185303302',
    address: '1026 Butternut Lane\r\nMaryland Heights, IL 63043',
    email: 'FrankPRandolph@teleworm.us' },
   {
    id: 3,
    name: 'Antonio J. Hardy',
    phone: '7203042446',
    address: '3787 Davis Lane\r\nTable Mesa, CO 80303',
    email: 'AntonioJHardy@dayrep.com' },
   {
    id: 4,
    name: 'Salvatore A. Alex',
    phone: '9414875407',
    address: '3155 Medical Center Drive\r\nSarasota, FL 34236',
    email: 'SalvatoreAAlex@armyspy.com' 
    } 
]

We need to convert the above JSON data into an array. Let’s make a function to do this.

const json2Array = function (result, fields) {
    let out = [];
    let temp = [];

    // Create headers array
    fields.forEach(item => {
        temp.push(item.name)
    });

    // temp array works as column headers in .xlsx file
    out.push(temp)
    
    result.forEach(item => {
        out.push([item.id, item.name, item.phone, item.address, item.email])
    })

    return out;
}

The above method creates an array using the results and fields values which actually comes from the query method. Our final code will look like this,

const mysql = require('mysql')
const xlsx = require('node-xlsx')
const fs = require('fs')

// Connect to mysql
const con = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'fake_db',
})

con.connect((err) => {
    if (err) throw err;
    console.log('Connected to database...');
})

con.query('SELECT * FROM users', (err, result, fields) => {
    if(err) throw err

    const data = json2Array(result, fields)

    const buffer = xlsx.build([{ name: 'Users', data: data}])

    // Write the buffer to a file

    fs.writeFile('users.xlsx', buffer, (fs_err) => {
        if (fs_err) throw fs_err

        console.log('Excel file created...');        
    })
})


const json2Array = function (result, fields) {
    let out = [];
    let temp = [];

    // Create headers array
    fields.forEach(item => {
        temp.push(item.name)
    });

    // temp array works as column headers in .xlsx file
    out.push(temp)
    
    result.forEach(item => {
        out.push([item.id, item.name, item.phone, item.address, item.email])
    })

    return out;
}

Now run the code and it will generate the expected files. Check your working directory and will find a new file name users.xlsx. Open this file using Spreadsheet/Excel software and you will see the following result.

demo-of-node-xlsx-using-database-data

If you have any suggestions or issue write it in the comment box…

Happy Coding…:)

How to create autocomplete feature in javascript using awesomplete

Ropali Munshi
Ropali Munshi
Ropali Munshi is fullstack PHP Developer. He is passionate developer who loves to learn and expirement with new programming languages , libraries and frameworks. Nowdays he is more into the JavaScript realm.

Leave a Reply

Your email address will not be published. Required fields are marked *