How to Center align values in the Downloaded Excel file created using JavaScript?

Use the below library to add styling to the downloaded excel file.

exceljs – npm

function downloadExcelReport() {
    const commissionReport = document.querySelector('.row');
    const tableData = [];
    const rows = commissionReport.querySelectorAll('tr');


    // Get the selected date range from the input fields
    let fromDate = document.getElementById("regional_fromdate").value;
    let toDate = document.getElementById("regional_todate").value;


    // Format dates to MM/DD/YYYY if necessary
    if (fromDate.includes('-')) {
        let [fromYear, fromMonth, fromDay] = fromDate.split('-');
        fromDate = `${fromMonth}/${fromDay}/${fromYear}`;
    }
    if (toDate.includes('-')) {
        let [toYear, toMonth, toDay] = toDate.split('-');
        toDate = `${toMonth}/${toDay}/${toYear}`;
    }


    // Create a time frame string based on the selected dates
    const timeFrameText = `Regional Leaderboard: ${fromDate} to ${toDate}`;


    // Load exceljs library
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Regional Report');


    // Merge cells for the title and apply styles
    worksheet.mergeCells('A1', 'G1'); // Merging from A1 to G1
    const titleCell = worksheet.getCell('A1');
    titleCell.value = timeFrameText;
    titleCell.font = { name: 'Arial', size: 16, bold: true };  // Larger, bold font
    titleCell.alignment = { vertical: 'middle', horizontal: 'center' }; // Center the text


    // Create an empty row for spacing
    worksheet.addRow([]);


    // Get table headers, skipping the "ID" column (last column, index 7 in this case)
    const headers = [];
    rows[0].querySelectorAll('th').forEach((header, index) => {
        if (index !== 7) {  // Skipping the last column (ID)
            headers.push(header.innerText);
        }
    });
    worksheet.addRow(headers);
    // Apply bold style to headers
    const headerRow = worksheet.getRow(3);
    headerRow.eachCell((cell) => {
        cell.font = { bold: true };
        cell.alignment = { vertical: 'middle', horizontal: 'center' };
    });


    rows.forEach((row, rowIndex) => {
        if (rowIndex === 0) return; // Skip the header row
        const rowData = [];
        row.querySelectorAll('td').forEach((cell, cellIndex) => {
            if (cellIndex !== 7) {  // Skipping the last column (ID)
                rowData.push(cell.innerText);
            }
        });
        worksheet.addRow(rowData);
    });


    // Set fixed column widths for a more compact table layout
    worksheet.columns = [
        { width: 8 },  // Rank
        { width: 15 },   // Name
        { width: 20 },  // Region
        { width: 24 },  // Quota Achieved - Trending
        { width: 27 },  // Quota Achieved - Full Period
        { width: 15 },  // New Accounts
        { width: 20 },  // New Account Amount
    ];


    worksheet.eachRow((row, rowIndex) => {
        row.eachCell({ includeEmpty: true }, (cell, colIndex) => {
            if (colIndex !== 2) { // Exclude the 'Name' column from center alignment
                cell.alignment = { horizontal: 'center' };
            }
        });
    });


    // Save the file as Excel
    workbook.xlsx.writeBuffer().then((buffer) => {
        const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
        const link = document.createElement('a');
        link.href = URL.createObjectURL(blob);
        link.download = 'regional_report.xlsx';
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    });
}