I have 2 date input fields(startdate,endate) that a user can selected. Once selected, data will show that is registered between the 2 dates provided by the user in a table format. how can I export that data that is shown in the table to excel with a button click without third party plugins ?
code that I used :
<body> <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> startdate: <input type="date" name="from_date"> enddate: <input type="date" name="to_date"> <input type="submit" name="date" id="date"> </form> <!--<div> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST"> <button type="submit" name="excel" value="excel" id='excel'> Export to excel</button> </form> </div> ---> <?php require('settings.php'); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); if (isset($_POST["date"])) { $startDate = date("Y-m-d", strtotime($_POST['from_date'])); // Y-m-d $endDate = date("Y-m-d", strtotime($_POST['to_date'])); // something weird is happening with the dates random dates slip between date ranges $sql = "SELECT distinct latestv.* from( select distinct Werkomschrijving_nr from POH_GL4 where versie Between ? and ? ) changedw left join (select distinct Werkomschrijving_nr, max(versie) AS maxdate, omschrijving from POH_GL4 group by Werkomschrijving_nr,omschrijving) latestv on latestv.Werkomschrijving_nr = changedw.Werkomschrijving_nr"; $stmt = $db->prepare($sql); $stmt->execute([$startDate, $endDate]); $result = $stmt->fetchAll(); echo "<table>"; echo "<tr><th>nr werkomschrijving</th><th>Last change date </th><th>Omschrijving</th></tr>"; foreach ($result as $key => $row) { echo "<tr>"; echo "<td>" . $row['Werkomschrijving_nr'] . "</td>"; echo "<td>" . $row['maxdate'] . "</td>"; echo "<td>" . $row['omschrijving'] . "</td>"; echo "</tr>"; }}
Advertisement
Answer
The exportTableToExcel() function convert HTML table data to excel and download as XLS file (.xls).
=> tableID – Required. Specify the HTML table ID to export data from.
=> filename – Optional. Specify the file name to download excel data.
js code:-
function exportTableToExcel(tableID, filename = ''){ var downloadLink; var dataType = 'application/vnd.ms-excel'; var tableSelect = document.getElementById(tableID); var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20'); // Specify file name filename = filename?filename+'.xls':'excel_data.xls'; // Create download link element downloadLink = document.createElement("a"); document.body.appendChild(downloadLink); if(navigator.msSaveOrOpenBlob){ var blob = new Blob(['ufeff', tableHTML], { type: dataType }); navigator.msSaveOrOpenBlob( blob, filename); }else{ // Create a link to the file downloadLink.href = 'data:' + dataType + ', ' + tableHTML; // Setting the file name downloadLink.download = filename; //triggering the function downloadLink.click(); } }
Html Table: The HTML table contains some users data with some basic fields, in Your case you have dates, in below code you see name, email,,etc…
<table id="tblData"> <tr> <th>Name</th> <th>Email</th> <th>Country</th> </tr> <tr> <td>John Doe</td> <td>john@gmail.com</td> <td>USA</td> </tr> <tr> <td>Michael Addison</td> <td>michael@gmail.com</td> <td>UK</td> </tr> <tr> <td>Sam Farmer</td> <td>sam@gmail.com</td> <td>France</td> </tr> </table>
The button triggers exportTableToExcel() function to export HTML table data using JavaScript.
<button onclick="exportTableToExcel('tblData')">Export Table Data To Excel File</button>
If you want to export data with the custom file name, pass your desired file name in the exportTableToExcel() function.
<button onclick="exportTableToExcel('tblData', 'members-data')">Export Table Data To Excel File</button>
This code helps you to add export functionality in the table data without any third-party jQuery plugin or server-side script. You can easily export the table data using minimal JavaScript code. Also, the functionality of the example code can be extended as per your needs.