james james - 1 year ago 66
HTML Question

Exclude Specific Columns when exporting data from HTML table to excel

I am using this script for exporting data from HTML table to Excel.

var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,'
, template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
, base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
, format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
return function(table, name) {
if (!table.nodeType) table = document.getElementById(table)
var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
window.location.href = uri + base64(format(template, ctx))

I found this here
but when i export this data it includes all columns in HTML table as expected to do. but my last row contains some icons that i don't want to export to excel.

<div class="row" style="margin-left:20px;">
<div class="grid_4">
<div class="da-panel collapsible">
<input type="button" class="btn btn-success" onclick="tableToExcel('testTable', 'W3C Example Table')" value="Export to Excel" style="float:right">
<div class="da-panel-content">

<div class="da-panel-title" style="border-top:1px solid #ccc;border-bottom:1px solid #ccc">
<h3 style="padding-left:10px;font-weight:bold;">Staff Training Information</h3></div>

<table class="da-table da-ex-datatable-numberpaging" id="testTable" width="100%">
<thead width="100%">
<th width="10%">Staff ID</th>
<th width="10%">Name</th>
<th width="10%">Location</th>
<th width="10%">POCT Test</th>
<th width="10%">Initial Training Date</th>
<th width="10%">Annual Competency Date</th>
<th width="10%">Competency Type</th>
<th width="1%">Next Competency Date</th>
<th width="39%">Action</th>
<tbody width="100%">
$pdo = Database::connect();
$sql = 'SELECT * FROM competency';

foreach ($pdo->query($sql) as $row) {
$id = $row['staff_id'];
echo '<tr>';
echo '<td width="10%">'. $row['staff_id'] . '</td>';
$sql1 = "SELECT *FROM staff WHERE StaffID='$id'";
foreach($pdo->query($sql1) as $res)
echo '<td width="10%">'. $res['StaffName'] . '</td>';
echo '<td width="10%">'. $row['location'] . '</td>';
<td width="10%">
$s = $row['poct_test'];
$val = explode(" ",$s);
for ($i=0; $i<sizeof($val); $i++)
$v = $val[$i];
echo $v."<br/>";
echo '<td width="10%">'. $row['date_of_initial_training'] . '</td>';

echo '<td width="10%">'. $row['annual_competency'] . '</td>';
echo '<td width="10%">'. $row['type_of_competency'] . '</td>';
echo '<td width="1%">'. $row['next_competency'] . '</td>';

echo '<td width="39%">';
echo '<a href="viewtrainingdetails.php?id='.$row['id'].'"><img src="images/ic_zoom.png" height="16" width="16" /></a>';
echo ' ';
echo '<a href="updatetraining.php?id='.$row['id'].'"><img src="images/icn_edit.png"/></a>';

echo ' ';
<a href="javascript:DeleteRecord('<?php echo $row['id'];?>')"><img src="images/icn_logout.png"/></a>
echo '</td>';
echo '</tr>';

As shown in code that last 3 echo contains update/delete icons. I just want to exclude Action column when exporting the table content in excel.
Any help would be highly appreciated.

Answer Source

make a hidden div under the table

<div class="exportData"> </div>

Then on click of the export button call export.php through ajax and put the result into exportData div. Then you can call your print script on the new data brought.

$.post( "export.php", function( data ) {
  $( ".result" ).html( data );

Copy past your for loop on export.php and delete the two cols.