Generate default . xls column file

Asked

Viewed 167 times

2

I have a table with 5 columns, whereas the first one has a check box and the last one has a button to delete that particular row.

The issue 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 below the export code:

$("#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>

  <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>

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

javascript jquery xls

1 answer

1


Yes, just clone the node with .cloneNode(true) and make a recursion to remove the elements, like this one using jQuery or need the recursion, can elimitar the first and last column of each row used the selectors :first-child and :last-child, thus:

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

Note: to simplify can do like this:

tr > :first-child, tr > :last-child

Should stay 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>

  <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>

Without jQuery:

If one day comes to reimplementate or rewrite in "Vanilla" 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" (slightly older browsers):

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

Alternative with jquery.table2excel

Just for the record, 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 the 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>

<script src="//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>

  • I think it malfunctioned with the first column. xD

  • @acklay I read the wrong question, corrected.

  • @acklay simplify the selectors

  • That’s right Guilherme! Vlw. Just one more question. When the xls file is generated, when I open it in Excel it goes with the white background. You could export with standard Excel background?

  • @acklay actually this file that you are doing is not Excel for real, but has how to convert relatively easy, so I have a spare time I edit the answer.

  • Blz bro! Abs. Vlw.

  • It is very difficult to export only the selected ones. I think I will open another question later. xD

  • @acklay remembered that I had a jquery plugin for this, I edited the answer.

  • 1

    Now I get it! that way I was just creating a file that would be readable using Excel, just by using table and also by extension .xls. Thanks again bro. Abs;

Show 4 more comments

Browser other questions tagged javascript jquery xls

You are not signed in. Login or sign up in order to post.