HTML table to Excel export using/adding regex (?) in function?

Question

I found this function for exporting an HTML table for excel download from this article. I'm using cheerio.js to scrape web data and put it into a MongoDB collection, then displaying that in a table. It seems like the function works all the way up until it reaches a "#" in one of the cells and then stops the export.

Since I didn't write the function myself, I'm not sure what could be causing this but I have a feeling if I add or change some regex it could help? I've never used regex myself so I'm really not sure.

I can post my code for web scraping/the html table if anyone thinks that will be helpful.

HTML to table export:

    function exportTableToExcel(tableID, filename = ''){
    var downloadLink;
    var dataType = 'application/vnd.ms-excel';
    var tableSelect = document.getElementById(tableID);
    var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');

    // Specify file name
    filename = filename?filename+'.xls':'excel_data.xls';

    // Create download link element
    downloadLink = document.createElement("a");

    document.body.appendChild(downloadLink);

    if(navigator.msSaveOrOpenBlob){
        var blob = new Blob(['\ufeff', tableHTML], {
            type: dataType
        });
        navigator.msSaveOrOpenBlob( blob, filename);
    }else{
        // Create a link to the file
        downloadLink.href = 'data:' + dataType + ', ' + tableHTML;

        // Setting the file name
        downloadLink.download = filename;

        //triggering the function
        downloadLink.click();
    }
}

This is how my output appears in the html table (shows up fine):

But most of it gets cut off when exported to excel:

Giovanni Zezza · Accepted Answer · 2020-06-13 10:36:34Z

1

It doesn't seem that the function does anything, other than encapsulate the table with a (fake) declaration of content type 'application/vnd.ms-excel'. The rest is up to Excel.

Blindly replacing a "#" character in the html code of the table (if there actually is a literal "#") is a very simple, easily unuseful and possibly dangerous thing, as to add a line:

tableHTML = tableHTML.replace(/#/g, '%23');

It's unlikely that this may solve the problem. Very likely, it may make it worse.

It may perhaps be better to act on the server side, generating a more Excel friendly html.

You may even try to encode the html, as other answers seem to suggest: Javascript to export html table to Excel

edited Jun 13, 2020 at 10:36

answered Jun 13, 2020 at 9:01

Giovanni Zezza

1691 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Emily Over a year ago

hmm okay that's interesting and good to know, i will look into it a bit more and see what i can find. thanks for the explanation!

Collectives™ on Stack Overflow

HTML table to Excel export using/adding regex (?) in function?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related