Excel Files

Sometimes we may need to read and write the data from the excel sheet, some people may use csv files with protractor alternatively but there are situation where we can use only excel/spread sheets.

We can read the excels sheets using excelJS or xlsx packages, and these packages donot come along with protractor so we have to install it separately.

ExcelJS package with protractor

xlsx package support more file types than ExcelJS, and also better than ExcelJS, then Why did I choose ExcelJS over xlsx package.

When you have an issue with a module in a package you should get easy help to solve it, over the internet i have searched for almost 5 hours to get some examples for xlsx package but no luck. xlsx package reads the data in awkward way, if you are looking only for xlsx package to read excel then you can skip this few blocks.

ExcelJS :

ExcelJS doesnot come along with protractor, so use below npm command to download ExcelJS for protractor to read excel files.


									npm install exceljs			
									


exceljs-typescript-protractor

Read Excel file with ExcelJS along with Protractor

We can read the excel sheet using ExcelJS modules in protractor with Javascript/Typescript, i will be explaining this tutorial with typescript. sample-excelsheet-for-protractor

Even if you keep your excel sheet in open condition, it doesnot affect the program

steps to read a value from Excel :

  • Import the exceljs module with few class like WorkBook, WorkSheet, Row, Cell
  • 
    import {Workbook, Row, Cell} from 'exceljs';			
    


  • Create a object to Workbook class
  • 
    // create object for workbook
    let wb:Workbook = new Workbook();			
    


  • From Workbook object we have use readFile along with file type, here xlsx is file type.
  • Create then block to resolve the promise to get values of workbook
  • 
    // read xlsx file type
    wb.xlsx.readFile("./test.xlsx").then(function(){			
    


  • Create WorkSheet object using getWorksheet method from Workbook object (wb), here worksheet is nothing but the sheet present in the excel file
  • 
    // sheet object
    let sheet:Worksheet = wb.getWorksheet("Sheet1");			
    


  • Create a Row from that worksheet object, we have to provide the row number we intended to read
  • 
    //row objct
    let rowObject:Row = sheet.getRow(3);			
    


  • Create cell object from which cell we want to read from and call toString() method from that cell object to make that cell as string valued
  • 
    // cell object
    let cellObject:Cell = rowObject.getCell(2);	
    //print
    console.log(cellObject.value);		
    


  • Use the retrieved value as url in protractor for navigation
Complete program for reading a value from column 2 and row 3 , which is B3

import { browser, element, by, ExpectedConditions, protractor} from 'protractor'
import { Alert, WebElement } from 'selenium-webdriver';
import {Workbook, Row, Cell} from 'exceljs';

describe('Protractor Typescript Demo', function() {
	browser.ignoreSynchronization = true; // for non-angular websites
	it('Excel File Operations', function() {
		// set implicit time to 30 seconds
		browser.manage().timeouts().implicitlyWait(30000);
		// create object for workbook
		var wb:Workbook = new Workbook();
		// read xlsx file type
		wb.xlsx.readFile("./test.xlsx").then(function(){
			//sheet object
			let sheet:Worksheet = wb.getWorksheet("Sheet1");
			//row objct
			let rowObject:Row = sheet.getRow(3);
			// cell object
			let cellObject:Cell = rowObject.getCell(2);
			//print
			console.log(cellObject.value);
			//use the cell value as url for navigation
			browser.get(cellObject.toString())
		});
	});
});	


exceljs-typescript-javascript-protractor

Read a specific column in Excel file

We can read a complete column using ExcelJS with protractor, to get complete colunm we need to know which row we want to retrieve as well as how many rows are present in the excel sheet. read-column-typescript-protractor By using rowCount variable from the worksheet object we will be able to get the number of rows, this includes empty rows till the final value, if there are empty rows after last value then exceljs will not consider them.

In below example we are trying to get the values from column B which is at index 2 and the row value changes as we will be iterating till last row. because of this reason i have assigned for loop variable to row value.

Complete program for reading values from a specific row.


import { browser, element, by, ExpectedConditions, protractor} from 'protractor'
import { Alert, WebElement } from 'selenium-webdriver';
import {Workbook, Row, Cell, Worksheet} from 'exceljs';

describe('Protractor Typescript Demo', function() {
	browser.ignoreSynchronization = true; // for non-angular websites
	it('Excel File Operations', function() {
		// set implicit time to 30 seconds
		browser.manage().timeouts().implicitlyWait(30000);
		// create object for workbook
		var wb = new Workbook();
		// read xlsx file type
		wb.xlsx.readFile("./w.xlsx").then(function(){
			//sheet object
			let sheet:Worksheet = wb.getWorksheet("Sheet1");
			
			let totalRowsIncludingEmptyRows:number = sheet.rowCount
			console.log("total nuumber of rows : "+totalRowsIncludingEmptyRows)

			// loop till end of row
			for(let i=1; i < = totalRowsIncludingEmptyRows; i++){
				let cellValue = sheet.getRow(i).getCell(2).toString();
				console.log("Column B value from the row '"+i+"' : "+ cellValue)
			}
		});
	});
});			


specific-column-read-protractor

Empty value Cell vs null value cell in protractor

As I was writing the above tutorial, I was trying to fetch values from a specific column, in excel sheet I was able to see 5 rows has value with 1 cell with no value in between them.

So I was expecting that the rowCount() method should return number of rows as 6 but it returned as 10.

I was wondering that protractor has gone mad, then i thought like it will not go mad just because of excel sheet, so I thought like lets put some time on it.

After few minutes of mind processing i came to the conclusion that, there are three different cell types in excel:

  • Cell with Values : these cell will have some kind of value like string, number, image, formula
  • null cells : these cell are not created inside the excel sheet yet, but user will able to see that boxes in excel file. These are will not be part when we try to get number of rows using rowCount
  • Cell with no values or empty cells : these are the cells which had values in them but later user erased them, so currently it has no value but these are present in excel sheet.

    Sometimes these cell also got created when user puts a value at some cell but he donot made put any value in cell before that.

    These cell value is also null but these cells are included when we get number of rows using rowCount

    In layman terms: You cannot build the 11th floor without building 10 floors, similarly if you put value in 12 row, system will create 11 rows automatically .
In below example we will see how it works different-types-of-cells Program show cases different cells

									import { browser, element, by, ExpectedConditions, protractor} from 'protractor'
									import { Alert, WebElement } from 'selenium-webdriver';
									import {Workbook, Row, Cell, Worksheet} from 'exceljs';

									describe('Protractor Typescript Demo', function() {
										browser.ignoreSynchronization = true; // for non-angular websites
										it('Excel File Operations', function() {
											// set implicit time to 30 seconds
											browser.manage().timeouts().implicitlyWait(30000);
											// create object for workbook
											var wb = new Workbook();
											// read xlsx file type
											wb.xlsx.readFile("./e.xlsx").then(function(){
												//sheet object
												let sheet:Worksheet = wb.getWorksheet("Sheet1");
												
												let totalRowsIncludingEmptyRows:number = sheet.rowCount
												console.log("total number of rows : "+totalRowsIncludingEmptyRows)

												let emptyCell = sheet.getRow(1).getCell(2).value
												console.log("Empty cell's value : "+emptyCell)

												let valueCell = sheet.getRow(2).getCell(2).value
												console.log("Value cell's value : "+valueCell)

												let nullCell = sheet.getRow(3).getCell(2).value
												console.log("null cell's value : "+nullCell)
											});
										});
									});			
									


counting-rows-exceljs-protractor If you are interested in getting number of rows which has values then use actualRowCount


									sheet.actualRowCount			
									


Methods present in JavaScript Executor

Writing excel sheet in protractor

Writing a excel sheet is almost similar to the reading a value from the excel sheet only the difference is we will assign a value to the cell.

Make sure you donot have the excel file in open while performing the writing operation

This below code assumes that there is excel file existing in local system already.


import { browser, element, by, ExpectedConditions, protractor} from 'protractor'
import { Alert, WebElement } from 'selenium-webdriver';
import {Workbook, Row, Cell, Worksheet} from 'exceljs';

describe('Protractor Typescript Demo', function() {
	browser.ignoreSynchronization = true; // for non-angular websites
	it('Excel File Operations', function() {
		// set implicit time to 30 seconds
		browser.manage().timeouts().implicitlyWait(30000);
		// create object for workbook
		var wb = new Workbook();
		// read xlsx file type
		wb.xlsx.readFile("./write.xlsx").then(function(){
			//sheet object
			let sheet:Worksheet = wb.getWorksheet("Sheet1");

			// write to excel sheet
			sheet.getRow(1).getCell(2).value = "first time wring to excel sheet"
		});
	});
});			


writing-excel-protractor-javascript-typescript

Create excel file & write in protractor with ExcelJS

In above example we have seen how to write a value in existing file, but sometimes we will be in situation where we must create the excel file and write it.

Steps to Create and write Excel file in typescript protractor :

  • Import the excelJS module and required classes
  • Create object for workbook
  • Add a new excel sheet to the Workbook object using addWorksheet method, it actually creates a worksheet
  • Navigate to google.com using protractor
  • Get the row and cell from the sheet and set value as title of the google, you can get the title using getTitle() method
  • Write the workbook object into local system as excel file using writeFile method with xlsx format

									import { browser, element, by} from 'protractor'
									import {Workbook, Row, Cell, Worksheet} from 'exceljs';

									describe('Protractor Typescript Demo', function() {
										browser.ignoreSynchronization = true; // for non-angular websites
										it('Excel File Operations', function() {
											// set implicit time to 30 seconds
											browser.manage().timeouts().implicitlyWait(30000);
											var wb = new Workbook()
											// create a worksheet
											var worksheet = wb.addWorksheet('My Sheet');

											// navigate to google
											browser.get("http://google.com")
											browser.getTitle().then(function(title){
												worksheet.getRow(1).getCell(1).value = title;
												wb.xlsx.writeFile('./createExample.xlsx');
											})
										});
									});			
									


create-excel-protractor-typescript-javascript-exceljs

Create Excels based Headers in Protractor

In above example, we have created excel file with some value using the row and column number but sometimes we may have a situation where we have to write the excel file using some headers.

Basically I am talking about writing excel file without using row and column number.

We have to set the headers first then we have to place the values for those headers.


import { browser, element, by} from 'protractor'
import { Alert, WebElement } from 'selenium-webdriver';
import {Workbook, Row, Cell, Worksheet} from 'exceljs';

describe('Protractor Typescript Demo', function() {
	browser.ignoreSynchronization = true; // for non-angular websites
	it('Excel File Operations', function() {
		// set implicit time to 30 seconds
		browser.manage().timeouts().implicitlyWait(30000);

		var wb = new Workbook()
		var worksheet = wb.addWorksheet('My Sheet');
		
		// set the column headers
        worksheet.columns = [
            { header: 'Id', key: 'id' },
            { header: 'Name', key: 'name'},
            { header: 'Designation', key: 'desig'}
		];
		
		// add rows
        worksheet.addRow({id: 1, name: 'karthiq', desig: "Senior test engg"});
		worksheet.addRow({id: 2, name: 'Joe', desig: "programmer"});

		//write the file to local system
        wb.xlsx.writeFile('./headerExcel.xlsx')
	});
});	


excel-header-protractor-typescript-javascript

Read a Value in Excel Sheets in protractor

Use below npm command to install xlsx package and install it in your local folder similar way with your protractor.


									npm install xlsx			
									


install-xlsx-protractor-read-excel-files We can read the excel sheets cell by cell, unlike other language package xlsx package works like human.

As a person if you are able to see a value in a cell then xlsx with protractor also sees that values, so user donot have to worry like from what index it starts from for rows and columns.

reading-xlsx-excel-protractor As showed in above image, we re going to read the value present in the B3 cell. Here B is the column name and 3 is the row number.

Steps to read value from excel in protractor :

  • Create a describe and it blocks using jasmine unit testing framework
  • Import the xlsx module, and store it in a variable
  • 
    // import xlsx module
    var XLSX = require('xlsx');			
    


  • Call the readFile method from the xlsx module to read the excel file and we have to pass the excel file path to it.
  • 
    // read the excel file
    var workbook = XLSX.readFile('test.xlsx');			
    


  • For reading a cell from the specific sheet, we have to provide a excel sheet name, or we can use the index of excel sheet to get the default assigned name for that excel sheet using workbook package
  • 
    //get the sheet default name
    var sheet_name = workbook.SheetNames[0];			
    


  • Use workbook.Sheet() method to read and store the sheet object in a variable
  • 
    var worksheet = workbook.Sheets[sheet_name];			
    


  • Now we have to provide the details of the cell from which we want to retrieve the data to the worksheet, these details are nothing but the column name and row number
  • 
    var address_of_cell = 'B3';
    /* Find desired cell */
    var cell_object = worksheet[address_of_cell];			
    


  • Once we receive the cell object then we can retrieve the value using a variable call vpresent in the cell object
  • 
    /* Get the value */
    var cell_value = cell_object.v;
    console.log("***** B3 Cell value *****:"+cell_value)			
    


  • Print the value to console
  • Now pass the retrieved cell value to get method of protractor to open an url
Complete program for reading excel sheet in protractor

import { browser, element, by, ExpectedConditions, protractor} from 'protractor'

describe('Protractor Typescript Demo', function() {
	browser.ignoreSynchronization = true; // for non-angular websites
	it('CSV File Operations', ***function() {
		// set implicit time to 30 seconds
		browser.manage().timeouts().implicitlyWait(30000);
		
		// import xlsx module
		var XLSX = require('xlsx');

		// read the excel file
		var workbook = XLSX.readFile('test.xlsx');

		//get the sheet default name
		var sheet_name = workbook.SheetNames[0];

		var worksheet = workbook.Sheets[sheet_name];

		var address_of_cell = 'B3';
		/* Find desired cell */
		var cell_object = worksheet[address_of_cell];

		/* Get the value */
		var cell_value = cell_object.v;
		console.log("***** B3 Cell value *****:"+cell_value)

		// open an url using the cell value retrieved
		browser.get(cell_value)
	});
});			


http protocol will not be visible in browser even it is present. read-excel-protractor-xlsx

Read All the data present in a Sheet

In above example we have seen how to read data from single cell, in this example we are going to read total excel sheet in protractor.

  • We have to get all the sheets in the excel file first
  • Iterate all the sheets
  • Iterate the cells in all the sheets

									import { browser, element, by, ExpectedConditions, protractor} from 'protractor'
									import { Alert, WebElement } from 'selenium-webdriver';
									import * as XLSX from 'xlsx';
									import { WSAEFAULT } from 'constants';

									describe('Protractor Typescript Demo', function() {
										browser.ignoreSynchronization = true; // for non-angular websites
										it('CSV File Operations', function() {
											// set implicit time to 30 seconds
											browser.manage().timeouts().implicitlyWait(30000);
											var XLSX = require('xlsx');
											var workbook = XLSX.readFile('test.xlsx');
											var sheet_name_list = workbook.SheetNames
											sheet_name_list.forEach(function(y) { /* iterate through sheets */
												var worksheet = workbook.Sheets[y];
												for (let z in worksheet) {
													/* all keys that do not begin with "!" correspond to cell addresses */
													if(z[0] === '!') continue;
													console.log(y + "!" + z + "=" + JSON.stringify(worksheet[z].v));
												}
											});
										});
									});			
									


Protractor Interview Questions

 
Join My Facebook Group
Join Group
 

About Author

Myself KarthiQ, I am the author of this blog, I know ways to write a good article but some how I donot have the skills to make it to reach people, would you like help me to reach more people By sharing this Article in the social media.

Share this Article Facebook
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions
Copyright © CherCher Tech