Generate .xls file with column omission

Question

Generate .xls file with column omission

Navigation

#1 by (1 votes)

2

I have a table with 5 columns, considering that the first one has a selection box and the last one has a button to delete that particular line.

The question is that I want to export to Excel, in a file .xsl , but without the first and last column, consisting only: ID , name and house . See the export code below:

$("#export").click(function(e) {
    var type = 'data:application/vnd.ms-excel';
    var _div = document.getElementById('table_wrapper');
    var _html = _div.outerHTML.replace(/ /g, '%20');

    var a = document.createElement('a');
    a.href = type + ', ' + _html;
    a.download = 'export_got_' + Math.floor((Math.random() * 9999999) + 1000000) + '.xls';
    a.click();
  });

table, tbody{
    border-style: solid;
    border-width: 1px;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script><divid="table_wrapper">
    <table  cellspacing="5" cellpadding="0" bordercolor="#ccc" id="list">
      <tbody>
        <tr class="header">
          <th><input id="checkBox" type="checkbox"></th>
          <th> ID</th>
          <th> name</th>
          <th> house</th>
          <th> action</th>
        </tr>
        <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>1</td>
          <td>Jon Snow</td>
          <td>Stark</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>2</td>
          <td>James</td>
          <td>Lennister</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        <tr>
  <td><input id="checkBox" type="checkbox"></td>          
          <td>3</td>
          <td>Arya</td>
          <td>Stark</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        
        <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>4</td>
          <td>Cercei </td>
          <td>Lennister</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        
         <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>5</td>
          <td>Daenerys  </td>
          <td>Targaryen</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
      </tbody>
    </table>
  </div> 
  
  <br />

<button id="export">XABLAU</button>

How can I generate a file .xls omitting some columns?

javascript jquery xls

asked by anonymous 21.09.2017 / 21:37

1 answer

Manage video with PHP Insert name instead of value option id

score 1 · Accepted Answer

You have, just clone the node with .cloneNode(true) and make a recursion to remove the elements, as it is using jQuery and does not need recursion, you can delete the first and last column of each line used the :first-child and :last-child , like this:

$("td:first-child, th:first-child, td:last-child, th:last-child", [ELEMENTO CLONADO]).remove();

Note: for simplicity you can do this:
tr > :first-child, tr > :last-child 

It should look like this:

var type = 'data:application/vnd.ms-excel';
var _div = document.getElementById('table_wrapper').cloneNode(true);

$("tr > :first-child, tr > :last-child", _div).remove();

var _html = _div.outerHTML.replace(/ /g, '%20');

_div = null; //"Remove" o clone

Example:

$("#export").click(function(e) {
    var type = 'data:application/vnd.ms-excel';
    var _div = document.getElementById('table_wrapper').cloneNode(true);

    $("tr > :first-child, tr > :last-child", _div).remove();

    var _html = _div.outerHTML.replace(/ /g, '%20');

    _div = null; //"Remove" o clone

    var a = document.createElement('a');
    a.href = type + ', ' + _html;
    a.download = 'export_got_' + Math.floor((Math.random() * 9999999) + 1000000) + '.xls';
    a.click();
 });

table, tbody{
    border-style: solid;
    border-width: 1px;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script><divid="table_wrapper">
    <table  cellspacing="5" cellpadding="0" bordercolor="#ccc" id="list">
      <tbody>
        <tr class="header">
          <th><input id="checkBox" type="checkbox"></th>
          <th> ID</th>
          <th> name</th>
          <th> house</th>
          <th> action</th>
        </tr>
        <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>1</td>
          <td>Jon Snow</td>
          <td>Stark</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>2</td>
          <td>James</td>
          <td>Lennister</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        <tr>
  <td><input id="checkBox" type="checkbox"></td>          
          <td>3</td>
          <td>Arya</td>
          <td>Stark</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        
        <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>4</td>
          <td>Cercei </td>
          <td>Lennister</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        
         <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>5</td>
          <td>Daenerys  </td>
          <td>Targaryen</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
      </tbody>
    </table>
  </div> 
  
  <br />

<button id="export">XABLAU</button>

No jQuery:

If one day you reimplement or rewrite in "Vanilla" you can adapt to:

var type = 'data:application/vnd.ms-excel';
var _div = document.getElementById('table_wrapper').cloneNode(true);

_div.querySelectorAll("tr > :first-child, tr > :last-child").forEach(function (el) {
    el.parentElement.removeChild(el);
});

var _html = _div.outerHTML.replace(/ /g, '%20');

If you want "backward compatibility" (somewhat older browsers):

[].forEach.call(_div.querySelectorAll("tr > :first-child, tr > :last-child"), function (el) {
    el.parentElement.removeChild(el);
});

Alternative with jquery.table2excel

Just to note, this is not a real xls, it's just an alternative with some compatibility, however there is a jquery plugin that can make it easy to convert to xls table2excel , example:

$("#export").click(function(e) {
    var type = 'data:application/vnd.ms-excel';
    var _div = document.getElementById('table_wrapper').cloneNode(true);

    $("tr > :first-child, tr > :last-child", _div).remove();

    $(_div).table2excel({
        name: "Nome do Worksheet",
        filename: 'export_got_' + Math.floor((Math.random() * 9999999) + 1000000) //não precisa de extensão
    });

 });

table, tbody{
    border-style: solid;
    border-width: 1px;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script><scriptsrc="//cdn.rawgit.com/rainabba/jquery-table2excel/1.1.0/dist/jquery.table2excel.min.js"></script>


  <div id="table_wrapper">
    <table  cellspacing="5" cellpadding="0" bordercolor="#ccc" id="list">
      <tbody>
        <tr class="header">
          <th><input id="checkBox" type="checkbox"></th>
          <th> ID</th>
          <th> name</th>
          <th> house</th>
          <th> action</th>
        </tr>
        <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>1</td>
          <td>Jon Snow</td>
          <td>Stark</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>2</td>
          <td>James</td>
          <td>Lennister</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        <tr>
  <td><input id="checkBox" type="checkbox"></td>          
          <td>3</td>
          <td>Arya</td>
          <td>Stark</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        
        <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>4</td>
          <td>Cercei </td>
          <td>Lennister</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
        
         <tr>
          <td><input id="checkBox" type="checkbox"></td>          
          <td>5</td>
          <td>Daenerys  </td>
          <td>Targaryen</td>
          <td><a href='#'>Eliminar</a></td>
        </tr>
      </tbody>
    </table>
  </div> 
  
  <br />

<button id="export">XABLAU</button>