Excel file converted to html table then set as editable, but changes aren't reflected when converted back as excel file

Question

I am working with react and using useState to handle variables. I also have xlsx (SheetJS) as a dependency.

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

export default function ExcelImport() {

    const tnStyles = {
        border: '1px solid black',
        padding: '5px',
        backgroundColor: 'white',
        textAlign: 'center',
        verticalAlign: 'top',
        whiteSpace: 'pre-wrap'
    };

    const [htmlData, setHtmlData] = useState('');

    const convertExcelToHTML = (file) => {
        const reader = new FileReader();
        reader.onload = (e) => {
            const data = new Uint8Array(e.target.result);
            const workbook = XLSX.read(data, { type: 'array' });
            const firstSheetName = workbook.SheetNames[0];
            const worksheet = workbook.Sheets[firstSheetName];
            const html = XLSX.utils.sheet_to_html(worksheet);
            setHtmlData(html);
        };
        reader.readAsArrayBuffer(file);
    };

    const handleFileChange = (e) => {
        const file = e.target.files[0];
        if (file) {
            convertExcelToHTML(file);
        }
    };

    const exportToFile = (htmlData) => {
        //export
        const ws = XLSX.utils.table_to_sheet(document.getElementById('base-table'));
        const wb = XLSX.utils.book_new();
        XLSX.utils.book_append_sheet(wb, ws, 'Report');
        XLSX.writeFile(wb, 'report.xlsx');
    };

    const handleHtmlChange = (e) => {
        const editedHtml = e.target.innerHTML;
        setHtmlData(editedHtml);
    };

    return (
        <div>
            <div>
                <input type="file" accept=".xls, .xlsx" onChange={handleFileChange} />
                <div
                    id="base-table"
                    style={tnStyles}
                    contentEditable={true}
                    dangerouslySetInnerHTML={{ __html: htmlData }}
                    onInput={handleHtmlChange}
                />
            </div>
            <div>
                <button style={tnStyles} onClick={() => exportToFile(htmlData)}>Export Edited HTML Content to Excel</button>
            </div>
        </div>
    );
}

Desired Behavior: User uploads excel file ---> file is converted to html table ---> table is displayed with contentEditable={true} ---> user edits table ---> changes are saved ---> user exports updated html table as excel

Current Behavior: User uploads excel file ---> file is converted to html table ---> table is displayed with contentEditable={true} ---> user edits table ---> changes are saved ---> user exports the original untouched version of the html table

Plan was to create a site that receives excel files as input then convert said excel files as html table then display them in the website while being editable (text only). The expected result was to have a table with editable contents that could then be converted back as excel file then exported.

I am using the free version of SheetJS.

I've tried storing the changes in a different variable (editedHtml) with it's own useState then leaving the original as is while only the changed html table will be exported.

I've tried using console logs to look at the contents of both htmlData and editedHtml. Both reflected the changes made but when exporting either of them, the changes are never applied and I am left with the same excel table that I uploaded to the site.

Answer 1

The generated html table stores the actual value of each cell in a property called 'data-v'. In this code the text within the td element can be changed and updated and saved to "htmlData", the reason why it is not reflected in the exported excel is because xlsx uses the value of 'data-v' as the value of the cell and not the content of its td element.

What I did to fix the issue:

1. Parse the values inside the td elements and store them in an array as key-value pairs [id: text, id text, id: text...].
2. Replace the values of 'data-v' with the parsed values. I used the specific td id to find which value goes where. Save in a variable 'editedData'.
3. Set 'editedData' as the value of 'htmlData' then export as xlsx file.