Complete tutorial on using Javascript to read excel file – 4 examples

In this page we will see how to use javascript to read excel file.

Javascript based test automation tools are becoming more and more popular. This has created a need of expert automation engineers with exposure towards Javascript. 

Reading and writing excel files is one of the most important part of test automation as we tend to store our input/output data in the excel files.

Read further to know how Javascript read excel file operations can be performed. On this page we have a list of functions for read capability of Excel files using Javascript such as Get Data from Excel, Get Row count from an Excel sheet, Get Header data of an Excel sheet etc. using JavaScript. 

Example Javascript to read excel data – Return data in 2d array

  /**
   * Reads data from an excel file and returns into a 2-d array
   * @param filepath absolute path of the file
   * @param sheetName sheet name to be read from
   */

  async getExcel_data(filepath, sheetName) {
    var workbook = new Excel.Workbook();
    var data = workbook.csv.readFile(filepath).then(function () {
      var rowData = [];
      var worksheet = workbook.getWorksheet(sheetName);
      var rows = worksheet.rowCount;
      for (var i = 1; i <= rows; i++) {
        var row = worksheet.getRow(i);
        rowData[i - 1] = new Array(row.cellCount);
        for (var j = 1; j <= row.cellCount; j++) {
          rowData[i - 1][j - 1] = row.getCell(j).value;
        }
      }
      return rowData;
    });
    //2D array returned
    return data;
  }

Example Javascript to read excel data – Get row count of Excel

  /**
   * returns row count from excel file
   * @param filepath absolute path of the file
   * @param sheetName sheet name to be read from
   */
  async getExcel_rowCount(filepath, sheetName) {
    var deferred = protractor.promise.defer();
    var workbook = new Excel.Workbook();
    var data = workbook.csv.readFile(filepath).then(function () {
      var worksheet = workbook.getWorksheet(sheetName);
      var rowCount = worksheet.rowCount;
      deferred.fulfill(rowCount-1);
      
    });
    return deferred.promise;
  }

Example Javascript to read excel data – Get Excel header data

  /**
   * returns header data in an array
   * @param filepath filepath absolute path of the file
   * @param sheetName sheetName sheet name to be read from
   */
  async getExcel_headerData(filepath, sheetName) {
    var workbook = new Excel.Workbook();
    var data = workbook.csv.readFile(filepath).then(function () {
      var headerData = [];
      var worksheet = workbook.getWorksheet(sheetName);
      var headerRow = worksheet.getRow(1);
      for (var i = 1; i <= headerRow.cellCount; i++) {
        headerData[i - 1] = headerRow.getCell(i).value;
      }
      return headerData;
    });
    //1D array returned
    return data;
}

Example Javascript to read excel data – Get Excel Column data

 /**
   * Reads data from an excel file and returns into a 1-d array
   * @param filepath absolute path of the file
   * @param sheetName sheet name to be read from
   */

  async getExcel_Columndata(filepath, sheetName, colNum) {
    var workbook = new Excel.Workbook();
    var data = workbook.csv.readFile(filepath).then(function () {
      var columnData = [];
      var worksheet = workbook.getWorksheet(sheetName);
      var rows = worksheet.rowCount;
      for (var i = 1; i <= rows; i++) {
        var row = worksheet.getRow(i);
        columnData[i-1] = row.getCell(colNum).value;
      }
      return columnData;
    });
    //1D array returned
    return data;
  }