export generated data to excel with php/js

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>";
        }}

JavaScript
​x
 
<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>";
        }}
​

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

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

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

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>

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

Advertisement

Answer