Export html table to excel using javascript by removing a specific column

Question

Export html table to excel using javascript by removing a specific column

Navigation

#1 by (0 votes)

0

I have the following script to perform an export of a table to excel:

<script>
            function fnExcelReport(){
                var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
                var textRange; var j=0;
                tab = document.getElementById('table'); // id of table

                for(j = 0 ; j < tab.rows.length ; j++) 
                {     
                    tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
                }

                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,"Say Thanks to Sumit.xls");
                }  
                else                 //other browser not tested on IE 11
                    sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  

                return (sa);
            }
        </script>

And my result is shown in the image below:

IwouldliketoknowhowIcanremovethelastcolumn("Actions")?

Thank you

javascript html excel

asked by anonymous 15.12.2017 / 14:24

1 answer

Read data from a SQLite 3 database in Python Time calculation for PHP + MySQL point

score 0 · Answer 1

This response is an adaptation of this response here in SOpt with that other <

Create a button:

<button onclick="tableToExcel('Arquivo.xlsx', '#tblExport')">Exportar Excel</button>

See that the onclick event is added, which will be called the tableToExcel method that receives two parameters, the first is the name how the file will be saved followed by the .xlsx extension, the second parameter is the table a be exported.

To remove the column you want, simply add the CSS class with% example: remover

Example in ES5:

const tableToExcel = (nome, table) => {
    let mimetype = 'application/vnd.ms-excel';
    let link = document.querySelector('#link-to-download');
    let tabela = document.querySelector(table);
    let clone = tabela.cloneNode(true);
    let remover = clone.querySelectorAll('.remover');

    remover.forEach((td) => {
        if (td.parentElement) {
            td.parentElement.removeChild(td);
        }
    });

    
    link.href = window.URL.createObjectURL(new Blob([clone.outerHTML], {
        type: mimetype
    }));
    link.download = nome;
    link.click();
};

table {
    border: 1px solid #ccc;
}
table thead {
  background: #069;
  color: #FFF;
}
table thead tr th {
  width: 140px;
}
table td {
    padding: 4px;
    text-transform: uppercase;
}
table tr:nth-child(2n+0) td {
    background: #e7e7e7;
}

button {
    background: #069;
    border: 0;
    color: #fff;
    cursor: pointer;
    padding: 8px 30px;
    text-transform: uppercase;
}

<table id="tblExport">
    <thead>
        <tr>
            <th>Coluna 1</th>
            <th>Coluna 2</th>
            <th colspan="2" class="remover">Coluna 3</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>teste</td>
            <td>teste</td>
            <td class="remover">teste</td>
            <td class="remover">teste</td>
        </tr>
        <tr>
            <td>teste</td>
            <td>teste</td>
            <td class="remover">teste</td>
            <td class="remover">teste</td>
        </tr>
        <tr>
            <td>teste</td>
            <td>teste</td>
            <td class="remover">teste</td>
            <td class="remover">teste</td>
        </tr>
    </tbody>
</table>
<p><button onclick="tableToExcel('Arquivo.xlsx', '#tblExport')">Exportar Excel</button></p>
<a id="link-to-download" style="display: none;"></a>

To use as answer from question quoted by AP by comment, just change <td class="remover">conteúdo</td> loop to:

for(j = 0 ; j < tab.rows.length ; j++) 
{
    var row = tab.rows[j];
    var numberOfCells = row.cells.length;
    // Altere o número 4 pelo número de colunas no TBODY
    // A cada linha de registro na tabela, sera excluído as duas ultimas colunas,
    // pois a coluna ACAO no THEAD ta setado o atributo 'colspan=2'
    if (numberOfCells === 4) {
       row.deleteCell(-1);
       row.deleteCell(-1);
    } else {
       // Remove a coluna ACAO
       row.deleteCell(-1);
    }
    tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
}

Notice that I made a small adaptation:

var tab = document.getElementById('table');

To:

var tab = document.getElementById('table').cloneNode(true);

By doing so, the element is cloned and avoids changing content already on the page.

The snippet below does not work here in SO , you can see it working in jsbin
Blocked opening 'data: application / vnd.ms-excel,% 3Ctable% 20border% 3D'2px'% 3E% 0A% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 3Cth% 3Column% 201% 3C% 2Fth% 3E% 0A% 20% 20% 20% 20% 20% 20% 20% 20% 20% 3Cth% 3EColuna% 202% 2Fth% 3E% 0A% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 0A% 20% 20% 20% 20% 20% 20% 20% 20% 3C% 2Ftr% 3E% 0A% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 3Ctd% 3Fit% 20% 20% 20% 20% 20% 20% 20% 20% 3% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 3E% 0A% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 3% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 0% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 3C% 2Ftr% 3E% 0% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 3% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20% 20

Export html table to excel using javascript by removing a specific column

1 answer

Example in ES5:

Example running