How to export my html table to excel ignoring hidden rows?-CodePudding

I want to export an html table to excel, if you are testing my code right now the export works perfectly fine except for a small detail, as you can see I made multiple search box in my table, if you don't search anything the button will export everything, that's good, if you search something and then click on import, on the excel file you will see that the rows that were hidden by the search will also be export but they will also be hidden in the file, and I wonder if it's possible to not export the row that are hidden but only those that are displayed.

function search(tableId) {

  var table = document.getElementById(tableId);

  var rows = table.getElementsByTagName("tr");

  for (var i = 2; i < rows.length; i  ) {
    var hide = false;
    var cells = rows[i].getElementsByTagName("td");
    for (var j = 0; j < cells.length; j  ) {
      var input = document.getElementById("REC_"   j);
      if (input && cells[j].innerHTML.toLowerCase().indexOf(input.value.toLowerCase()) == -1) {
        hide = true;
        break;
      }
    }
    if (hide) {
      rows[i].style.display = "none";
    } else {
      rows[i].style.display = "";
    }
  }
}

function htmlTableToExcel(type, tableId) {

  var data = document.getElementById(tableId);
  var excelFile = XLSX.utils.table_to_book(data, {
    sheet: "sheet1"
  });
  XLSX.write(excelFile, {
    bookType: type,
    bookSST: true,
    type: 'base64'
  });
  XLSX.writeFile(excelFile, 'MyTable.'   type);

}

#MyTable {
  border-collapse: collapse;
  width: 100%;
  border: 1px solid #ddd;
  font-size: 18px;
}

#MyTable th,
#MyTable td {
  text-align: center;
  padding: 12px;
}

#MyTable tr {
  border-bottom: 1px solid #ddd;
}

#MyTable th,
#MyTable tr:hover {
  background-color: #f1f1f1;
}

.divider {
  border-top: 3px solid #bbb;
}

hr.solid {
  border-top: 3px solid #bbb;
}


/*#myTable thead th {
  position: sticky;
  top: 0; 
  resize: horizontal;
  overflow: auto;
  min-width: 70px;
}*/

#MyTable thead tr {
  position: relative;
}

.resizer {
  position: absolute;
  top: 0;
  right: 0;
  width: 5px;
  cursor: col-resize;
  user-select: none;
  border-right: 2px solid silver;
}

.resizer:hover,
.resizing {}

.resizable {
  border: 1px solid gray;
  height: 100px;
  width: 100px;
  position: relative;
}

<script type="text/javascript" src="https://unpkg.com/[email protected]/dist/xlsx.full.min.js"></script>
<button onclick="htmlTableToExcel('xlsx', 'MyTable')">Excel</button>
<table id="MyTable">
  <thead>
    <tr >
      <th style="">Name </th>
      <th style="">Country </th>
      <th style="">Num1 </th>
      <th style="">Num2 </th>
    </tr>
  </thead>

  <tbody>
    <tr >
      <td> <input type="text" id="REC_0" onkeyup="search('MyTable')"> </td>
      <td> <input type="text" id="REC_1" onkeyup="search('MyTable')"> </td>
      <td> <input type="text" id="REC_2" onkeyup="search('MyTable')"> </td>
      <td> <input type="text" id="REC_3" onkeyup="search('MyTable')"> </td>
    </tr>
    <tr>
      <td style="cursor: pointer">Alfreds Futterkiste</td>
      <td>Germany</td>
      <td>546</td>
      <td>444</td>
    </tr>
    <tr>
      <td>Berglunds snabbkop</td>
      <td>Sweden</td>
      <td>456</td>
      <td>458</td>
    </tr>
    <tr>
      <td>Island Trading</td>
      <td>UK</td>
      <td>564</td>
      <td>258</td>
    </tr>
    <tr>
      <td>Koniglich Essen</td>
      <td>Germany</td>
      <td>648</td>
      <td>879</td>
    </tr>
    <tr>
      <td>Alexis</td>
      <td>Germany</td>
      <td>984</td>
      <td>365</td>
    </tr>
  </tbody>

</table>

CodePudding user response：

I changed the display:hidden to hidden

rows[i].hidden = hide;

and remove the rows before saving - I assume you do not need the input row either

const table = document.getElementById(tableId);
const data = document.createElement('table');
data.innerHTML = table.innerHTML;
data.querySelectorAll('tr').forEach(row => { 
  if (row.hidden || (row.closest('tbody') && row.matches('.header'))) row.remove() 
});

function search(tableId) {
  var table = document.getElementById(tableId);
  var rows = table.getElementsByTagName("tr");
  for (var i = 2; i < rows.length; i  ) {
    var hide = false;
    var cells = rows[i].getElementsByTagName("td");
    for (var j = 0; j < cells.length; j  ) {
      var input = document.getElementById("REC_"   j);
      if (input && cells[j].innerHTML.toLowerCase().indexOf(input.value.toLowerCase()) == -1) {
        hide = true;
        break;
      }
    }
    rows[i].hidden = hide;
  }
}
function htmlTableToExcel(type, tableId) {
  const table = document.getElementById(tableId);
  const data = document.createElement('table');
  data.innerHTML = table.innerHTML;
  data.querySelectorAll('tr').forEach(row => { 
    if (row.hidden || (row.closest('tbody') && row.matches('.header'))) row.remove() 
  })
  console.log(data.outerHTML)
  var excelFile = XLSX.utils.table_to_book(data, {
    sheet: "sheet1"
  });
  XLSX.write(excelFile, {
    bookType: type,
    bookSST: true,
    type: 'base64'
  });
  XLSX.writeFile(excelFile, 'MyTable.'   type);
}

#MyTable {
  border-collapse: collapse;
  width: 100%;
  border: 1px solid #ddd;
  font-size: 18px;
}

#MyTable th,
#MyTable td {
  text-align: center;
  padding: 12px;
}

#MyTable tr {
  border-bottom: 1px solid #ddd;
}

#MyTable th,
#MyTable tr:hover {
  background-color: #f1f1f1;
}

.divider {
  border-top: 3px solid #bbb;
}

hr.solid {
  border-top: 3px solid #bbb;
}


/*#myTable thead th {
  position: sticky;
  top: 0; 
  resize: horizontal;
  overflow: auto;
  min-width: 70px;
}*/

#MyTable thead tr {
  position: relative;
}

.resizer {
  position: absolute;
  top: 0;
  right: 0;
  width: 5px;
  cursor: col-resize;
  user-select: none;
  border-right: 2px solid silver;
}

.resizer:hover,
.resizing {}

.resizable {
  border: 1px solid gray;
  height: 100px;
  width: 100px;
  position: relative;
}

<script type="text/javascript" src="https://unpkg.com/[email protected]/dist/xlsx.full.min.js"></script>
<button onclick="htmlTableToExcel('xlsx', 'MyTable')">Excel</button>
<table id="MyTable">
  <thead>
    <tr >
      <th style="">Name </th>
      <th style="">Country </th>
      <th style="">Num1 </th>
      <th style="">Num2 </th>
    </tr>
  </thead>
  <tbody>
    <tr >
      <td> <input type="text" id="REC_0" onkeyup="search('MyTable')"> </td>
      <td> <input type="text" id="REC_1" onkeyup="search('MyTable')"> </td>
      <td> <input type="text" id="REC_2" onkeyup="search('MyTable')"> </td>
      <td> <input type="text" id="REC_3" onkeyup="search('MyTable')"> </td>
    </tr>
    <tr>
      <td style="cursor: pointer">Alfreds Futterkiste</td>
      <td>Germany</td>
      <td>546</td>
      <td>444</td>
    </tr>
    <tr>
      <td>Berglunds snabbkop</td>
      <td>Sweden</td>
      <td>456</td>
      <td>458</td>
    </tr>
    <tr>
      <td>Island Trading</td>
      <td>UK</td>
      <td>564</td>
      <td>258</td>
    </tr>
    <tr>
      <td>Koniglich Essen</td>
      <td>Germany</td>
      <td>648</td>
      <td>879</td>
    </tr>
    <tr>
      <td>Alexis</td>
      <td>Germany</td>
      <td>984</td>
      <td>365</td>
    </tr>
  </tbody>

</table>

Here is a more streamlined version if you like

const table = document.getElementById("MyTable");

const search = () => {
  const vals = [...table.querySelectorAll("tr.header input")]
    .map(inp => inp.value.trim().toLowerCase());
  const empty = vals.every(val => val ==="");
  [...table.querySelectorAll("tr")]
    .slice(1) // ingore the input field row
    .forEach(row => {
      row.hidden = !empty && ![...row.querySelectorAll("td")]
        .some((cell, i) => {
          const val = vals[i];
          if (val) {
            const cellContent = cell.textContent.toLowerCase();
            return val && cellContent.includes(val);
          }
          return false;
        });

    });
   
};
table.addEventListener("input", search);
document.getElementById("toExcel").addEventListener("click", () => {
  const table = document.getElementById(tableId);
  const data = document.createElement('table');
  data.innerHTML = table.innerHTML;
  data.querySelectorAll('tr').forEach(row => {
    if (row.hidden || (row.closest('tbody') && row.matches('.header'))) row.remove()
  })
  console.log(data.outerHTML)
  var excelFile = XLSX.utils.table_to_book(data, {
    sheet: "sheet1"
  });
  XLSX.write(excelFile, {
    bookType: type,
    bookSST: true,
    type: 'base64'
  });
  XLSX.writeFile(excelFile, 'MyTable.'   type);
})

#MyTable {
  border-collapse: collapse;
  width: 100%;
  border: 1px solid #ddd;
  font-size: 18px;
}

#MyTable th,
#MyTable td {
  text-align: center;
  padding: 12px;
}

#MyTable tr {
  border-bottom: 1px solid #ddd;
}

#MyTable th,
#MyTable tr:hover {
  background-color: #f1f1f1;
}

.divider {
  border-top: 3px solid #bbb;
}

hr.solid {
  border-top: 3px solid #bbb;
}


/*#myTable thead th {
  position: sticky;
  top: 0; 
  resize: horizontal;
  overflow: auto;
  min-width: 70px;
}*/

#MyTable thead tr {
  position: relative;
}

.resizer {
  position: absolute;
  top: 0;
  right: 0;
  width: 5px;
  cursor: col-resize;
  user-select: none;
  border-right: 2px solid silver;
}

.resizer:hover,
.resizing {}

.resizable {
  border: 1px solid gray;
  height: 100px;
  width: 100px;
  position: relative;
}

<script type="text/javascript" src="https://unpkg.com/[email protected]/dist/xlsx.full.min.js"></script>
<button type="button" id="toExcel">Excel</button>
<table>
  <thead>
    <tr >
      <th style="">Name </th>
      <th style="">Country </th>
      <th style="">Num1 </th>
      <th style="">Num2 </th>
    </tr>
  </thead>
  <tbody id="MyTable">
    <tr >
      <td> <input type="text" id="REC_0"> </td>
      <td> <input type="text" id="REC_1"> </td>
      <td> <input type="text" id="REC_2"> </td>
      <td> <input type="text" id="REC_3"> </td>
    </tr>
    <tr>
      <td style="cursor: pointer">Alfreds Futterkiste</td>
      <td>Germany</td>
      <td>546</td>
      <td>444</td>
    </tr>
    <tr>
      <td>Berglunds snabbkop</td>
      <td>Sweden</td>
      <td>456</td>
      <td>458</td>
    </tr>
    <tr>
      <td>Island Trading</td>
      <td>UK</td>
      <td>564</td>
      <td>258</td>
    </tr>
    <tr>
      <td>Koniglich Essen</td>
      <td>Germany</td>
      <td>648</td>
      <td>879</td>
    </tr>
    <tr>
      <td>Alexis</td>
      <td>Germany</td>
      <td>984</td>
      <td>365</td>
    </tr>
  </tbody>

</table>