I want to export my html table to an xlsx file but the formatting is screwed

Question

import React, { useEffect, useState } from 'react';
import * as XLSX from 'xlsx';

export default function AnnualReporTest() {

    const thStyles = {
        border: '1px solid black',
        padding: '10px',
        textAlign: 'center',
        backgroundColor: 'lightgray',
    };

    const exportToExcel = () => {
        const ws = XLSX.utils.table_to_sheet(document.getElementById('report-table'));

        ws['!cols'] = [{ wch: 20 }, { wch: 20 }, { wch: 20 },{ wch: 20 }, { wch: 20 }, { wch: 20 },{ wch: 10 }, { wch: 10 }, { wch: 15 },{ wch: 15 }, { wch: 15 }]

        const wb = XLSX.utils.book_new();
        XLSX.utils.book_append_sheet(wb, ws, 'Report');
        XLSX.writeFile(wb, 'report.xlsx');
    };

    return (
        <div>
            <button onClick={exportToExcel}>Export to Excel</button>
            <div>
                <table id="report-table">
                <tr>
                    <th style={thStyles}>Firstname</th>
                    <th style={thStyles}>Lastname</th>
                    <th style={thStyles}>Age</th>
                </tr>
                <tr>
                    <td style={thStyles}>Jill</td>
                    <td style={thStyles}>Smith</td>
                    <td style={thStyles}>50</td>
                    <td style={thStyles}>
                                <table>
                                    <tr>
                                        <td style={thStyles}>First Budgetary Requirement</td>
                                    </tr>
                                    <tr>
                                        <td style={thStyles}>Second Budgetary Requirement</td>
                                    </tr>
                                </table>
                            </td>
                </tr>
                <tr>
                    <td style={thStyles}>Eve</td>
                    <td style={thStyles}>Jackson</td>
                    <td style={thStyles}>94</td>
                    <td style={thStyles}>
                                <table>
                                    <tr>
                                        <td style={thStyles}>First Budgetary Requirement</td>
                                    </tr>
                                    <tr>
                                        <td style={thStyles}>Second Budgetary Requirement</td>
                                    </tr>
                                </table>
                            </td>
                </tr>
                </table>
            </div>
            <br/>
            Hello World!
        </div>
    );
}

this is how it looks like in my sitethis is the exported xlsx file

I tried to use nested tables so that certain cells are split into different rows. All is well and good in the frontend but converting the html table into xlsx seemed to ruin the format of the tables. I used XLSX library.

Answer 1

It is totally normal that it is looking so strange. You have defined 3 headers ( your <th> tags) and have defined 4 cells ( <td> ) . Excel doesn´t know where to add the data and just adds it in the first cell. You should add another header and the problem should be fixed :

 <tr>
                <th style={thStyles}>Firstname</th>
                <th style={thStyles}>Lastname</th>
                <th style={thStyles}>Age</th>
                <th style={thStyles}>Requirement</th>
 </tr>