How to Export Formatted Excel Reports Using ExcelJS in JavaScript

This example demonstrates how to generate a styled Excel report using ExcelJS in JavaScript. It includes custom headers, conditional formatting, column alignment, and dynamic column width adjustment.

import ExcelJS from 'exceljs';
import { saveAs } from 'file-saver';

const downloadReport = async (reportData, options = {}) => {
 try {
  const {
   centerAlignedCols = ['Quantity'],
   rightAlignedCols = ['Sales Amount', 'Amount Range', 'Comm %', 'Commission Total', 'Premium Sales', 'Total Premium Delivered'],
   keysToExclude = [],
   reportTitle = 'SALES COMMISSION REPORT',
   salesperson = 'N/A',
   fromDate = 'N/A',
   toDate = 'N/A',
  } = options;

  if (!Array.isArray(reportData) || reportData.length === 0) {
   console.warn('No data available to download.');
   return;
  }

  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Report');

  // === Header Section ===
  worksheet.mergeCells('A1:N6');
  const headerCell = worksheet.getCell('A1');
  headerCell.value = {
   richText: [
    { text: `${reportTitle}n`, font: { bold: true, size: 18, color: { argb: '071D3E' } } },
    { text: `Salesperson: ${salesperson}n`, font: { size: 11 } },
    { text: `From Date: ${fromDate}n`, font: { size: 11 } },
    { text: `To Date: ${toDate}`, font: { size: 11 } },
   ],
  };
  headerCell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
  worksheet.addRow([]); // spacer

  // === Table Headers ===
  const columns = Object.keys(reportData[0]).filter(key => !keysToExclude.includes(key));
  worksheet.getRow(7).values = columns;
  worksheet.getRow(7).font = { bold: true, color: { argb: 'FFFFFF' } };
  worksheet.getRow(7).eachCell(cell => {
   cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: '071D3E' } };
   cell.alignment = { horizontal: 'center', vertical: 'middle' };
  });

  // === Data Rows ===
  let rowIndex = 8;
  reportData.forEach(record => {
   const rowValues = columns.map(col => record[col]);
   const row = worksheet.insertRow(rowIndex++, rowValues);

   columns.forEach((colName, colIndex) => {
    const value = record[colName];
    const cell = row.getCell(colIndex + 1);

    // Alignment
    if (centerAlignedCols.includes(colName)) {
     cell.alignment = { horizontal: 'center', vertical: 'middle' };
    } else if (rightAlignedCols.includes(colName)) {
     cell.alignment = { horizontal: 'right', vertical: 'middle' };
    } else {
     cell.alignment = { horizontal: 'left', vertical: 'middle' };
    }

    // Conditional Formatting
    if ((colName === 'Sales Amount' || colName === 'Commission Total') && Number(value) < 0) {
     cell.font = { color: { argb: 'FF0000' } }; // red
    } else if (colName === 'Tier Type') {
     const tier = value?.trim().toUpperCase();
     if (tier === 'RED') cell.font = { color: { argb: 'FF0000' } };
     else if (tier === 'ORANGE') cell.font = { color: { argb: 'FFA500' } };
     else if (tier) cell.font = { color: { argb: '008000' } };
    }
   });
  });

  // === Auto-fit Columns ===
  worksheet.columns.forEach(column => {
   let maxLength = 0;
   column.eachCell({ includeEmpty: true }, cell => {
    const length = cell.value ? cell.value.toString().length : 0;
    if (length > maxLength) maxLength = length;
   });
   column.width = maxLength;
  });

  // === Export File ===
  const buffer = await workbook.xlsx.writeBuffer();
  const blob = new Blob([buffer], {
   type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  });

  saveAs(blob, `Report_${new Date().toISOString().split('T')[0]}.xlsx`);
  console.log('File downloaded successfully.');
 } catch (error) {
  console.error('Error generating report:', error);
 }
};