javascript 如何导出我的html表格到excel忽略隐藏行？

30byixjq 于 2023-01-29 发布在 Java

关注(0)|答案(1)|浏览(220)

我想导出一个html表格到excel，如果你正在测试我的代码，现在的出口工程完全罚款，除了一个小细节，你可以看到我在我的表中做了多个搜索框，如果你不搜索任何按钮将导出一切，这很好，如果你搜索的东西，然后点击导入，在excel文件中，您将看到搜索隐藏的行也将导出，但它们也将隐藏在文件中，我想知道是否可以不导出隐藏的行，而仅导出显示的行。

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/xlsx@0.15.1/dist/xlsx.full.min.js"></script>
<button onclick="htmlTableToExcel('xlsx', 'MyTable')">Excel</button>
<table id="MyTable">
  <thead>
    <tr class="header">
      <th style="">Name </th>
      <th style="">Country </th>
      <th style="">Num1 </th>
      <th style="">Num2 </th>
    </tr>
  </thead>

  <tbody>
    <tr class="header">
      <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>

JavaScript

来源：https://stackoverflow.com/questions/75259206/how-to-export-my-html-table-to-excel-ignoring-hidden-rows

1条答案

按热度按时间

6kkfgxo01#

我更改了显示：隐藏到隐藏

rows[i].hidden = hide;

并在保存之前删除行-我假设您也不需要输入行

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() 
});

一个一个二个一个一个一个三个一个一个一个一个一个四个一个
如果您愿意，这里有一个更精简的版本

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/xlsx@0.15.1/dist/xlsx.full.min.js"></script>
<button type="button" id="toExcel">Excel</button>
<table>
  <thead>
    <tr class="header">
      <th style="">Name </th>
      <th style="">Country </th>
      <th style="">Num1 </th>
      <th style="">Num2 </th>
    </tr>
  </thead>
  <tbody id="MyTable">
    <tr class="header">
      <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>

赞(0）回复(0）举报 2023-01-29

我来回答

javascript 如何导出我的html表格到excel忽略隐藏行？

1条答案

相关问题

热门标签

最新问答