How to Read Excel Files in Playwright

The problem is simple. You have a login test with a hard-coded username and password. It works, but it only covers one scenario. What if you need to test with 10 different credentials? 50? You are not going to copy-paste the same test over and over. That is a maintenance nightmare.

The solution? Read your test data from an Excel file. Keep the data separate from the test logic, and anyone on the team can update inputs without touching the code. Let's dive in.

Why Read Test Data from Excel Files?

Your Excel spreadsheet can hold as many rows of credentials as you need, and the same test script handles all of them. You change data without changing code. You add new test cases by just adding rows to a spreadsheet. And if someone non-technical on your team needs to update test data, they open the Excel file and edit it. No code changes, no pull requests.

This is data-driven testing in Playwright in action. If you want a deeper look at the concept, check out that article.

Installing the xlsx NPM Package

We are not going to reinvent the wheel. The xlsx package on NPM has almost four million weekly downloads. It handles .xlsx, .xls, and .csv files out of the box.

Install it as a dev dependency:

npm install xlsx --save-dev

After installation, you will see the package added to devDependencies in your package.json. That's it, first step is done.

Reading Excel Data Step by Step

Let's say you have a file called userdata.xlsx under the data folder in your project. The spreadsheet has two columns, email and password, with a row for each test user.

First, we need two imports:

import * as XLSX from 'xlsx';import path from 'path';

The xlsx library does the heavy lifting, and path helps us resolve file paths inside the project.

Next, read the workbook and grab the worksheet:

const userDataFile = path.join(__dirname, 'data', 'userdata.xlsx');const workbook = XLSX.readFile(userDataFile);const worksheet = workbook.Sheets['Sheet1'];

We build the full path to the Excel file using path.join(), read the entire workbook with XLSX.readFile(), and then grab the specific worksheet by name. In this case, Sheet1.

Now convert the worksheet to JSON:

const data = XLSX.utils.sheet_to_json(worksheet);

The sheet_to_json() method converts the worksheet into an array of JavaScript objects. Each row becomes an object, and the column headers become the keys. So if your spreadsheet has email and password columns, you get something like this:

[  { "email": "[email protected]", "password": "pass123" },  { "email": "[email protected]", "password": "pass456" }]

Yes, that simple :)

Using Excel Data in Playwright Tests

Let's put it all together. Full working example:

import { test, expect } from '@playwright/test';import * as XLSX from 'xlsx';import path from 'path';const userDataFile = path.join(__dirname, 'data', 'userdata.xlsx');const workbook = XLSX.readFile(userDataFile);const worksheet = workbook.Sheets['Sheet1'];const data = XLSX.utils.sheet_to_json(worksheet);test('login with Excel data', async ({ page }) => {  await page.goto('https://example.com/login');  await page.getByLabel('Email').fill(`${data[0]['email']}`);  await page.getByLabel('Password').fill(`${data[0]['password']}`);  await page.getByRole('button', { name: 'Login' }).click();});

Instead of hard-coded values, we pull the email and password directly from the first row of the spreadsheet. Want to test with the second user? Change data[0] to data[1]. Want to loop through all users? Wrap it in a for loop or use Playwright's test.describe() to generate a test for each row.

If you are new to Playwright and need help setting up your project first, check out How to Install Playwright in VS Code.

Adding TypeScript Type Safety

To avoid TypeScript complaining that the object sheet_to_json() is of type unknown create an interface that matches your spreadsheet columns:

interface UserData {  email: string;  password: string;}

Then pass it as a generic type parameter:

const data = XLSX.utils.sheet_to_json<UserData>(worksheet);

And that's it. No more red squiggly lines. TypeScript now knows exactly what fields each object has, so you get full IntelliSense support when accessing data[0].email or data[0].password. If someone later adds a new column to the spreadsheet, you update the interface in one place and TypeScript tells you everywhere it is used.

Final Thoughts

That's all there is to it. Install the xlsx package, read the workbook, convert to JSON, use the data in your tests. No need to reinvent the wheel when a proven NPM package with millions of downloads already does the job.

I recommend combining this with data-driven testing patterns to run the same test across dozens of different inputs. Write the test once, let the spreadsheet do the rest.

Microsoft Playwright is growing in popularity on the market very quickly and will soon be a mainstream framework. Get the new skills at Bondar Academy with the Playwright UI Testing Mastery program. Start from scratch and become an expert to increase your value on the market!