Skip to content
Advertisement

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

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.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement