How to read and write Excel file in Node.js ?

Node.js is a versatile and open-source JavaScript runtime environment that operates seamlessly on various platforms. One of its capabilities includes handling file operations, such as reading from and writing to files in formats like txt, ods, xlsx, docx, and more.

To illustrate, let's explore how to read data from an Excel file in the .xlsx format, convert it into JSON, and subsequently write back to the same file. This task can be accomplished efficiently by leveraging the 'xlsx' package.

Module Installation: You can install xlsx module using the following command:

npm install xlsx

Note: For the following example, text.xlsx is a dummy data file that has been used.

Filename: test.xlsx 

Sheet 1:

Sheet 2:

So the excel file test.xlsx has 2 sheets, one having Student details and another having lecturer details.

Read Operation Filename: read.js 

    // Requiring the module
    const reader = require('xlsx')
      
    // Reading our test file
    const file = reader.readFile('./test.xlsx')
      
    let data = []
      
    const sheets = file.SheetNames
      
    for(let i = 0; i < sheets.length; i++)
    {
       const temp = reader.utils.sheet_to_json(
            file.Sheets[file.SheetNames[i]])
       temp.forEach((res) => {
          data.push(res)
       })
    }
      
    // Printing data
    console.log(data)

    Explanation: First, the npm module is included in the read.js file and then the excel file is read into a workbook i.e constant file in the above program.

    The number of files in that particular excel file is available in the SheetNames property of the workbook. It can be accessed as follows:

    const sheets = file.SheetNames  // Here the value of the sheets will be 2

    A for loop is run until the end of the excel file starting from the first page. One of the most important functions used in the code above is the sheet_to_json() function present in the utils module of the xlsx package. It accepts a worksheet object as a parameter and returns an array of JSON objects.

    There is a forEach loop which iterates through every JSON object present in the array temp and pushes it into a variable data which would contain all the data in JSON format.

    Finally, the data is printed or any other modification can be performed on the array of JSON objects.

    Step to run the application:

    Run the read.js file using the following command:

    node read.js

    Output:

    Write Operation In the following example, we will convert an array of JSON objects into an excel sheet and append it to the file.

    Filename: write.js

      // Requiring module
      const reader = require('xlsx')
        
      // Reading our test file
      const file = reader.readFile('./test.xlsx')
        
      // Sample data set
      let student_data = [{
          Student:'Nikhil',
          Age:22,
          Branch:'ISE',
          Marks: 70
      },
      {
          Student:'Amitha',
          Age:21,
          Branch:'EC',
          Marks:80
      }]
        
      const ws = reader.utils.json_to_sheet(student_data)
        
      reader.utils.book_append_sheet(file,ws,"Sheet3")
        
      // Writing to our file
      reader.writeFile(file,'./test.xlsx')

      Explanation: Here we have an array of JSON objects called student_data. We use two main functions in this program i.e json_to_sheet() which accepts an array of objects and converts them into a worksheet and another function is the book_append_sheet() to append the worksheet into the workbook.

      Finally, all the changes are written to the test.xlsx file using writeFile() function which takes a workbook and a excel file as input parameter.

      Step to run the application:

      Run the read.js file using the following command:

      node write.js

      Output: The final test.xlsx file would look something like this: 

      Sheet 1:

      Sheet 2:

      Sheet 3: We can see sheet 3 is appended into the test.xlsx as shown below:

      By at

      No comments:

      Post a Comment