Fetching Data from Excel in Protractor: A Quick Guide

Automating interactions with applications through testing frameworks like Protractor is not just a trend but has become a necessity in modern web development. One common challenge testers face is the need to validate data-driven test cases where the data source is often an Excel file. Integrating Excel data into your Protractor tests seamlessly can significantly streamline your testing process, ensuring your automation scripts are both dynamic and robust. This guide provides an in-depth look at how to fetch and utilize data from Excel within Protractor tests.

Why Use Excel with Protractor?

Using Excel for test data has several advantages:

• Non-Technical Data Management: Business analysts or project managers who are not versed in programming can manage test data without altering code.
• Flexibility: Excel's familiar interface allows for easy data manipulation, which can be advantageous when test scenarios frequently change.
• Reusability: A single data source can serve multiple scripts, reducing redundancy and simplifying maintenance.

Tools and Libraries You'll Need

To fetch data from Excel in Protractor, you'll need:

• NodeJS: Protractor is built on top of Node.js, so ensure it's installed.
• Protractor: The testing framework itself.
• xlsx: A library for reading Excel files in Node.js.

Setup Process

1. Install Required Packages

Start by installing necessary npm packages:

npm install protractor xlsx

2. Excel File Preparation

Create an Excel file with your test data. For this example, let's name it 'test_data.xlsx' with a sheet named 'Sheet1'. Here's what the structure might look like:

3. Configure Protractor

Ensure your Protractor configuration file includes the necessary settings:

{
    "framework": "jasmine",
    "specs": ["spec/**/*.js"],
    "seleniumAddress": "http://localhost:4444/wd/hub",
    "baseUrl": "http://your.test.app.com"
}

4. Write the Test Script

Now, let's write a test script where we'll use the data from Excel:

var Excel = require('exceljs');
var workbook = new Excel.Workbook();
var assert = require('assert');

describe('Login functionality', function() {
    var data = [];

    beforeAll(function(done) {
        workbook.xlsx.readFile('./test_data.xlsx')
            .then(function() {
                var worksheet = workbook.getWorksheet('Sheet1');
                worksheet.eachRow({includeEmpty: false}, function(row, rowNumber) {
                    if (rowNumber > 1) {  // Skip header row
                        data.push({
                            username: row.getCell(1).value,
                            password: row.getCell(2).value
                        });
                    }
                });
                done();
            });
    });

    data.forEach(function(testData, index) {
        it('should log in with user ' + (index + 1), function() {
            browser.get('/login');

            element(by.id('username')).sendKeys(testData.username);
            element(by.id('password')).sendKeys(testData.password);
            element(by.id('submit')).click();

            // Assertions based on the action you expect after login
            expect(browser.getTitle()).toEqual('Dashboard');
        });
    });
});

Here, we:

• Import and initialize the Excel library.
• Read the Excel file before all tests in beforeAll.
• Extract data into an array for easier manipulation in tests.
• Iterate over each dataset to run tests with different credentials.

💡 Note: Ensure your test data file path is correct relative to where your script is executed.

Handling Larger Datasets

When dealing with larger datasets, consider:

• Streaming Data: Load only a portion of the data at a time to avoid memory issues.
• Parameterization: Use command-line arguments to specify the rows or the sheet you want to run, reducing test times.

Stream Data Example

If you're dealing with a very large Excel file:

var Excel = require('exceljs');
var workbook = new Excel.Workbook();

it('should handle large datasets', function(done) {
    var stream = workbook.xlsx.createInputStream();
    var counter = 0;
    
    stream.on('data', function(row) {
        if (counter++ > 1) {  // Skip header
            // Process data here, similar to the earlier example
        }
    });

    stream.on('end', function() {
        done();
    });

    stream.on('error', function(err) {
        done.fail(err);
    });
});

By summarizing the insights into fetching data from Excel for Protractor tests, we've covered:

• Why integrating Excel data is beneficial.
• The setup process, including necessary tools and libraries.
• How to prepare and configure Excel files for testing.
• Writing the test script to fetch and use Excel data dynamically.
• Optimizing tests for larger datasets.

Can I use different sheets within the same Excel file?

+

Yes, you can load data from different sheets by accessing them through the workbook using their names, e.g., workbook.getWorksheet(‘Sheet2’);

How do I handle data types like dates or times in Excel?

+

The Excel library converts dates and times to JavaScript Date objects. Ensure your script correctly interprets these data types for assertions or inputs.

What if my Excel file changes frequently?

+

Store the Excel file in a dynamic location or pass it as a command-line argument to your test script, allowing for easy updates without altering your test script.