When a table is exported as an excel file I want all the data in the table to be saved as well in all of the cells.

This is my javascript code:

function exportTableToExcel(tableID, filename = ''){
    var downloadLink;
    var dataType = 'application/vnd.ms-excel';
    var tableSelect = document.getElementById(tableID);
    var tableHTML = tableSelect.outerHTML.replace(/ /g, ' ');    
    filename = filename?filename '.xls':'excel_data.xls';    
    downloadLink = document.createElement("a");
    document.body.appendChild(downloadLink);
    if(navigator.msSaveOrOpenBlob){
        var blob = new Blob(['\ufeff', tableHTML], {
            type: dataType
        });
        navigator.msSaveOrOpenBlob( blob, filename);
    }
    else{
        downloadLink.href = 'data:'   dataType   ', '   tableHTML;    
        downloadLink.download = filename;
        downloadLink.click();
    }
}

function addField(table){

  var tableRef = document.getElementById(table);
  var newRow   = tableRef.insertRow(-1);

  var newCell  = newRow.insertCell(0);
  var newElem = document.createElement( 'input' );
  newElem.setAttribute("name", "links");
  newElem.setAttribute("type", "text");
  newCell.appendChild(newElem);

  newCell = newRow.insertCell(1);
  newElem = document.createElement( 'input' );
  newElem.setAttribute("name", "keywords");
  newElem.setAttribute("type", "text");
  newCell.appendChild(newElem);

  newCell = newRow.insertCell(2);
  newElem = document.createElement( 'input' );
  newElem.setAttribute("name", "violationtype");
  newElem.setAttribute("type", "text");
  newCell.appendChild(newElem);

  newCell = newRow.insertCell(3);
  newElem = document.createElement( 'input' );
  newElem.setAttribute("type", "button");
  newElem.setAttribute("value", "Delete Row");
  newElem.setAttribute("onclick", 'SomeDeleteRowFunction(this)')
  newCell.appendChild(newElem);
}
    
window.SomeDeleteRowFunction = function SomeDeleteRowFunction(o) {
     var p=o.parentNode.parentNode;
     p.parentNode.removeChild(p);
}

and here is my html

<table id="tblData" >
    <tr>
        <th>Name</th>
        <th>HW</th>
        <th>HW</th>
    </tr>
    <tr>
      <td><input type="text" name="links" /></td>
      <td><input type="text" name="keywords" /></td> 
      <td><input type="text" name="violationtype" /></td>
      <td><input type="button" value="Delete Row" onclick="SomeDeleteRowFunction(tblData)"/></td>          
    </tr>
</table>
<input type="submit"  value="Add another line" onclick="addField('tblData');" />
<button onclick="exportTableToExcel('tblData', 'class1')">Save data</button>

the code creates a table that is able to create more rows and the cells are editable when you double click them. there is then a button that allows for the table to be exported into an excel file. it all works except when you click the excel file that was just downloaded it doesn't have any data in the cells... it understands how many rows gets added but doesn't take in the data along with it.

CodePudding user response：

Your problem is that excel bases the value of the cell in the content of the element, not in its attributes. Inputs hold their value in the attribute value, thus you wont see it.

You can fix this by replacing the inputs with their values before the export (creating a copy first so you don't break your interface)

This would be your fixed exportTableToExcel function (I commented the changes, the rest of the code is the same)

        function exportTableToExcel(tableID, filename = '') {
            var downloadLink;
            var dataType = 'application/vnd.ms-excel';
            // Note the cloneNode(true) at the end
            // this creates a virtual copy of your table so you can alter it
            // without breaking the interface
            var tableSelect = document.getElementById(tableID).cloneNode(true);
            // replaces each input in the table with its value
            tableSelect.querySelectorAll("input").forEach(function (element) {
                element.closest("td").innerHTML = element.value
            })
            // tge rest is the same as in your code
            var tableHTML = tableSelect.outerHTML.replace(/ /g, ' ');
            filename = filename ? filename   '.xls' : 'excel_data.xls';
            downloadLink = document.createElement("a");
            document.body.appendChild(downloadLink);
            if (navigator.msSaveOrOpenBlob) {
                var blob = new Blob(['\ufeff', tableHTML], {
                    type: dataType
                });
                navigator.msSaveOrOpenBlob(blob, filename);
            }
            else {
                downloadLink.href = 'data:'   dataType   ', '   tableHTML;
                downloadLink.download = filename;
                downloadLink.click();
            }
        }

This is the result

Note that the buttons also show up. You may want to get rid of them by .remove() with the proper selector in the cloned table

Note: The approach you are using does not generate a proper excel file. Although excel will be able to open the file, it will warn you that its not fully compliant with the format because excel uses its own XML structure, that differs from HTML tables. If you want a better result I recommend you check a library dedicated to that, like exceljs or similar