13. Excel Utils with ExcelJS

Installing ExcelJS

npm install exceljs

Reading Excel Files

import ExcelJS from 'exceljs';

async function readExcel(filePath: string) {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile(filePath);
  const worksheet = workbook.worksheets[0];

  worksheet.eachRow((row, rowNumber) => {
    console.log(`Row ${rowNumber}:`, row.values);
  });
}

// Use in test
test('read test data from Excel', async () => {
  const data = await readExcel('test-data/users.xlsx');
  // Use data for parameterized tests
});

Writing Excel Files

async function writeExcel(data: any[], filePath: string) {
  const workbook = new ExcelJS.Workbook();
  const sheet = workbook.addWorksheet('Sheet1');

  // Headers
  sheet.columns = [
    { header: 'ID', key: 'id', width: 10 },
    { header: 'Name', key: 'name', width: 30 },
    { header: 'Email', key: 'email', width: 40 },
  ];

  // Data
  data.forEach(item => sheet.addRow(item));

  await workbook.xlsx.writeFile(filePath);
}

test('export test results to Excel', async () => {
  await writeExcel([
    { id: 1, name: 'John', email: 'john@test.com' }
  ], 'output/results.xlsx');
});

Uploading Excel Files in Tests

test('upload Excel via UI', async ({ page }) => {
  // Create Excel file
  const workbook = new ExcelJS.Workbook();
  // ... build workbook ...
  await workbook.xlsx.writeFile('temp-upload.xlsx');

  // Upload
  await page.getByLabel('Upload File').setInputFiles('temp-upload.xlsx');

  // Assert success
  await expect(page.getByText('File uploaded')).toBeVisible();
});