How to Read and Write Excel file in Node.js and playwright ?

In this example we will explore how to read and write data in excel sheet in JavaScript using node NPM module. To read and write Excel file in Node js we will use the xlsx package from NPM. We can read the excel file using readFile method and update the content using writeFile method provided by xlsx library.

Install the xlsx package

npm install xlsx

Reading an Excel File

This example to Read data from an Excel file

const XLSX = require('xlsx');

// Load the Excel file
const workbook = XLSX.readFile('example.xlsx');

// Select a worksheet
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

// Convert the sheet to JSON
const data = XLSX.utils.sheet_to_json(worksheet);

console.log(data);

Output:

[
  { Name: "Alice", Age: 25 },
  { Name: "Bob", Age: 30 }
]

Writing to an Excel File

This example to Writing to an Excel File.

const XLSX = require('xlsx');

// Sample data
const data = [
  { Name: "Alice", Age: 25 },
  { Name: "Bob", Age: 30 }
];

// Convert JSON to worksheet
const worksheet = XLSX.utils.json_to_sheet(data);

// Create a new workbook and append the worksheet
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");

// Write to file
XLSX.writeFile(workbook, 'output.xlsx');

NOTES : 

1. .readFile() and .writeFile() work with .xlsx, .xls, and .csv.
2. You can also use this with buffers and streams if working with uploads or cloud storage.

Updating an Existing Excel File

This example is to Update an Existing Excel File.

const XLSX = require('xlsx');

// Step 1: Read the existing workbook
const workbook = XLSX.readFile('data.xlsx');

// Step 2: Get the first worksheet
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

// Step 3: Convert worksheet to JSON
const data = XLSX.utils.sheet_to_json(worksheet);

// Step 4: Modify the data (e.g., add a new row)
data.push({ Name: 'Charlie', Age: 28 });

// Step 5: Convert JSON back to worksheet
const updatedWorksheet = XLSX.utils.json_to_sheet(data);

// Step 6: Replace the worksheet in the workbook
workbook.Sheets[sheetName] = updatedWorksheet;

// Step 7: Write the updated workbook back to file
XLSX.writeFile(workbook, 'data.xlsx');

Delete Specific Rows from an Excel Sheet

This example is to Delete Specific Rows from an Excel Sheet.

if you want to delete the row where Name is "Bob", the use the following code.

const XLSX = require('xlsx');

// Read the existing workbook
const workbook = XLSX.readFile('data.xlsx');

// Get the worksheet
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

// Convert to JSON
let data = XLSX.utils.sheet_to_json(worksheet);

// Filter out the row to delete
data = data.filter(row => row.Name !== 'Bob');

// Convert back to worksheet
const newWorksheet = XLSX.utils.json_to_sheet(data);

// Replace the sheet with updated data
workbook.Sheets[sheetName] = newWorksheet;

// Write the workbook back to file
XLSX.writeFile(workbook, 'data.xlsx');

Clear All Data from a Sheet

To clear a worksheet (remove all rows while keeping the sheet structure):

const XLSX = require('xlsx');

// Load workbook
const workbook = XLSX.readFile('data.xlsx');

// Get the sheet name
const sheetName = workbook.SheetNames[0];

// Create an empty sheet (just headers if needed)
const emptyData = []; // or e.g., [{ Name: '', Age: '' }] to keep headers
const emptySheet = XLSX.utils.json_to_sheet(emptyData);

// Replace old sheet
workbook.Sheets[sheetName] = emptySheet;

// Write back to file
XLSX.writeFile(workbook, 'data.xlsx');

Clear a Specific Cell (e.g., B2)

if you want to Clear a Specific Cell (e.g., B2).

const XLSX = require('xlsx');

// Step 1: Read the Excel file
const workbook = XLSX.readFile('data.xlsx');

// Step 2: Get the desired worksheet
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

// Step 3: Delete the value in cell B2
delete worksheet['B2'];

// (Optional) Update the sheet range if needed
worksheet['!ref'] = XLSX.utils.encode_range(XLSX.utils.decode_range(worksheet['!ref']));

// Step 4: Write the workbook back to file
XLSX.writeFile(workbook, 'data.xlsx');

If you want to clear multiple specific cells (e.g., B2, C4), just delete each one the same way.

delete worksheet['B2'];
delete worksheet['C4'];

This is all about working with excel sheet in playwright using JavaScript binding.