Using Javascript to read excel file

Updated on

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 to read excel file. 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. 


Return data in 2d array- Example Javascript

  /**
   * 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;
  }

Get row count of Excel – Example Javascript

  /**
   * 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;
  }

Get Excel header data – Example Javascript

  /**
   * 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;
}

Get Excel Column data – Example Javascript

 /**
   * 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;
  }

Related Posts