How to export data from database into Excel using AJAX call & JavaScript?

Question

I have

listbox

containing some Farmers Ids,i want to to get the details (phone number, address, etc) from the database and export it to Excel file, I thought of using AJAX call to get the data from the database, but how can I export these data to Excel file?

JavaScript code:

function ExportData()

{

    var ul = document.getElementById("FarmersID");

    var items = ul.getElementsByTagName("option");

    var farmers=[];



    if (items.length == 0)

    {

        alert ("No Farmers Found!");

        return false;

    }

    else

    {

        for (var i = 0; i < items.length; ++i) 

        {

            farmers.push(items[i].text);

        }

        var jsonString = JSON.stringify(farmers);



        $.ajax({

            type:"POST",

            url:"get_farmerdata.php",

            data:

                { 

                   'FarmerData':jsonString

                },

            success: function (response) 

                {

                    alert(response);

                },

            error: function(jqXHR, textStatus, errorThrown) {

            console.log('ERROR', textStatus, errorThrown);

            }

       })

   }

}

PHP code:

<html>

<?php

    include_once "connect.php";



    $FarmerData=json_decode($_POST['FarmerData'],true);

    $ids = join("','",$FarmerData);   



    $stmt ="SELECT * FROM Farmers where Farm_id IN ('$ids')";



    foreach ($conn->query($stmt) as $row) 

    {

        echo $row['nick_name_']."<br>";

    }   

?>

</html>

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

You could use table2excel jquery plugin.

In your PHP code, just echo the database data that you execute in a simple HTML table.

After that in your jquery, append the response table from the php to your page, and use the table2excel plugin to export it:

success: function (response) 
{
   $('#YourTableDiv').append(response);

   $('#YourTableDiv').table2excel({
      exclude: ".excludeThisClass",
      name: "Worksheet Name",
      filename: "SomeFile" //do not include extension
      fileext: ".xls", // MUST use .xls extension, only this will work
   }); 
},

This is my example code in jsfiddle that I create: https://jsfiddle.net/bj3djhbr/

Note, in this fiddle I'm using old version of table2excel, so it will download the file in .xlxs, which is unfortunately cannot be opened (known issue), so just rename it to .xls. But if you use the latest version, on the github link that I give you, it should work fine.

How to export data from database into Excel using AJAX call & JavaScript?

Sign up