How to dynamically take table rows and create an Excel sheet

Question

I have the following script which takes the first row (TH) and adds it to a table dynamically which will then be exported to an Excel sheet. For some reason, it keeps adding and stacking on top of the next table row.

Here is the script:

var decode_entities = (function () {
    // Remove HTML Entities
    var element = document.createElement('div');

    function decode_HTML_entities(str) {

        if (str && typeof str === 'string') {

            // Escape HTML before decoding for HTML Entities
            str = escape(str).replace(/%26/g, '&').replace(/%23/g, '#').replace(/%3B/g, ';');

            element.innerHTML = str;
            if (element.innerText) {
                str = element.innerText;
                element.innerText = '';
            } else {
                // Firefox support
                str = element.textContent;
                element.textContent = '';
            }
        }
        return unescape(str);
    }
    return decode_HTML_entities;
})();

function fnExcelReport() {
    var tab_text = "<table border='0'><tr bgcolor='#FFFFFF' class='mainTR'>";
    var textRange;
    var j, p = "", q = "";
    var tab = document.getElementById('BookingResults'); // id of table


    for (j = 0 ; j < tab.rows.length ; j++) {
        for (var y = 0; y < tab.rows[j].cells.length; y++) {
            if (y == tab.rows[j].cells.length - 1) {
                if (j == 0) {
                    q = q + "<TH scope=col class='headerLast vTop'>";
                }
                else {
                    q = q + "<TD class='bodyLast vTop'>";
                }
                q = q + decode_entities(tab.rows[j].cells[y].innerHTML);
                //alert("LAST COL INNER HTML: " + decode_entities(tab.rows[j].cells[y].innerHTML));
                if (j == 0) {
                    q = q + "</TH>";
                }
                else {
                    q = q + "</TD>";
                }
            }
            else {
                if (j == 0) {
                    q = q + "<TH scope=col class='headerNotLast vTop'>";
                }
                else {
                    q = q + "<TD class='bodyNotLast vTop'>";
                }
                q = q + tab.rows[j].cells[y].innerHTML;
                if (j == 0) {
                    q = q + "</TH>";
                }
                else {
                    q = q + "</TD>";
                }
                p = p + tab.rows[j].cells[y].innerHTML;
                //alert("NOT LAST COL INNERHTML: " + p);
            }
        }
        //alert(q);
        //q = "";
        //alert(tab.rows[j].innerHTML);
        //tab_text = tab_text + tab.rows[j].innerHTML + "</tr><tr bgcolor='#FFFFFF' class='mainTR'>";
        tab_text = tab_text + q + "</tr><tr bgcolor='#FFFFFF' class='mainTR'>";
        alert(tab_text);
    }

    tab_text = tab_text + "</table>";
    tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, "");//remove if u want links in your table
    tab_text = tab_text.replace(/<img[^>]*>/gi, ""); // remove if u want images in your table
    tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE ");

    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
    {
        txtArea1.document.open("txt/html", "replace");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus();
        sa = txtArea1.document.execCommand("SaveAs", true, "SpecificGuidelines.xls");
    }
    else {                //other browser not tested on IE 11
        sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));
    }

    return (sa);
}

Here is the first alert (shows the first row (TH)):

enter image description here

Here is the second alert (The red highlight is repeating, and I am sure on the third run, the first two will repeat and so forth...):

enter image description here

How do I make sure the TH/TD doesn't keep adding up and instead it goes to the next row and append.

So I am looking to modify the script so it is:

<table>
    <tr>
        <th></th> //TH is the header so it appears only once in the table.
    </tr>
    <tr>
        <td></td>
    </tr>
    <tr>
        <td></td>
    </tr>
    ...
</table>

What is happening right now:

<table>
    <tr>
        <th></th>
    </tr>
    <tr>
        <th></th> //brought from the first entry
        <td></td>
    </tr>
    <tr>
        <th></th> //brought from the first entry
        <td></td> //brought from the second entry
        <td></td>
    </tr>
    ...
</table>

http://jsfiddle.net/brk7vhff/6/ – SearchForKnowledge Jan 08 '15 at 19:09 — SearchForKnowledge, Jan 08 '15 at 19:09

score 0 · Answer 1 · answered Jan 08 '15 at 17:12

0

You need to move <tr bgcolor='#FFFFFF' class='mainTR'> into your loop to open a row for each iteration of the loop and then at end of loop (but inside it) close the <tr>.

Your open tag is outside of the rows loop and the close is after the loop, thus you only create one row.

var tab_text = "<table border='0'>";
for (j = 0 ; j < tab.rows.length ; j++) {
    tab_text+='<tr>'
        /* loop over cells here */

    tab_text+='</tr>';

}

answered Jan 08 '15 at 17:12

charlietfl

170,828
13
121
150

Can you please show me... I just did a test and it looks like it keeps adding column to the right. However, I think you maybe right. – SearchForKnowledge Jan 08 '15 at 17:13
But I open it at the top: `var tab_text = "";` and then in the for look I Iterate through the TH/TD and then have this line in the outer for loop: `tab_text = tab_text + q + "";` So I am closing after we iterate through the entire row and then start a new row.
– SearchForKnowledge Jan 08 '15 at 17:15
see simplified example – charlietfl Jan 08 '15 at 17:16
Where would the variable `q` go then? – SearchForKnowledge Jan 08 '15 at 17:17
`q` should start as new `var` in each loop, but simpler to just keep doing `tabtext +=` – charlietfl Jan 08 '15 at 17:18
Here is the JSFiddle: http://jsfiddle.net/brk7vhff/2/ Can you please help me edit it. Thanks. You are right, however, it is adding as a column rather than the next row. – SearchForKnowledge Jan 08 '15 at 17:19
you haven't tried what I showed you. I'm not going to rewrite it if you aren't willing to make initial adjustments – charlietfl Jan 08 '15 at 17:20
I think my entire logic might be off as well. I have only 5 rows of table data but this `k = k + tab_text + "

"; document.getElementById('result').innerHTML = k;` displays almost 10 pages of data... :/ repeating at over and over again many times. – SearchForKnowledge Jan 08 '15 at 17:21
table can't have `
` as child. Suggest you get this working as html first, then port it to excel. – charlietfl Jan 08 '15 at 17:24
I was adding `
` to put space between the text. – SearchForKnowledge Jan 08 '15 at 17:28
But good point in your response. Other than the `
` did I get what you are trying to explain? – SearchForKnowledge Jan 08 '15 at 17:30
either way would be easier to be able to see what's happening by having this render into the fiddle – charlietfl Jan 08 '15 at 17:30
So I have 7 rows including the header in my table. The HTML generated by the Javascript is doing what I am having an issue with (there are almost 45 rows, repeating)... Would you like to see? – SearchForKnowledge Jan 08 '15 at 17:33
create a demo that I can see output, not just the code in function – charlietfl Jan 08 '15 at 17:35
I know what is happening... I have to reset q after each iteration and remove the end tag outside the outer for loop. I am testing it out now. JSFiddle throws an error because I have HTML tag inside the table cell. – SearchForKnowledge Jan 08 '15 at 17:37
Ok got it work in HTML but it is not saving to Excel correctly anymore :/ – SearchForKnowledge Jan 08 '15 at 17:40
I think I have to put the entire table inside one ROW and export it to Excel. Now that I have the HTML correctly it only export one column inside one row and that is all. – SearchForKnowledge Jan 08 '15 at 17:48
I decided to to convert everything to a html pop up and give the ability to print. and it works. – SearchForKnowledge Jan 12 '15 at 13:57

How to dynamically take table rows and create an Excel sheet

1 Answers1