PHP export large data from mysql database to Excel file that generated on client side download progress

Question

I want to export large data from mysql database to Excel file in PHP that directly can download and generated on client side download progress. So the http response don't need to wait about completely query progress and generated excel on the server, because if it's take too long it will be timed out.

I want that all progress generated on the fly download progress client side until download process 100% completed. It's possible or not? How can i do this concept?

Answer 1

You could call the data into the browser then use a little JavaScript to put it into a CSV file dynamically .

 const data = [
["rahul", "delhi", "accounts dept"],
["rajeev", "UP", "sales dept"]
];

 let csvContent = "data:text/csv;charset=utf-8,";


data.forEach(function(rowArray) {
  let row = rowArray.join(",");
  csvContent += row + "\r\n";
});

var encodedUri = encodeURI(csvContent);
window.open(encodedUri);

So you would take your PHP Mysql result Array and

json_encode($result);

Then once you have posted it to Javascript using XHR - fetch AJAX

json.parse(result)

Then use the code above ...

Answer 2

You can use tableToExcel.js to export table in excel file. This works in a following way :

1). Include this CDN in your project/file

<script src="https://cdn.jsdelivr.net/gh/linways/table-to-excel@v1.0.4/dist/tableToExcel.js"></script>

2). Either Using JavaScript:

<button id="btnExport" onclick="exportReportToExcel(this)">EXPORT REPORT</button>

function exportReportToExcel() {
  let table = document.getElementsByTagName("table"); // you can use document.getElementById('tableId') as well by providing id to the table tag
  TableToExcel.convert(table[0], { // html code may contain multiple tables so here we are refering to 1st table tag
    name: `export.xlsx`, // fileName you could use any name
    sheet: {
      name: 'Sheet 1' // sheetName
    }
  });
}

In this way, you can directly download in excel format, which is a fast process

Answer 3

//replace with your own function ...
var i = 0;
function move() {
  if (i == 0) {
    i = 1;
    var elem = document.getElementById("myBar");
    var width = 1;
    var id = setInterval(frame, 10);
    function frame() {
      if (width >= 100) {
        clearInterval(id);
        i = 0;
      } else {
        width++;
        elem.style.width = width + "%";
      }
    }
  }
}

#myProgress {
  width: 100%;
  background-color: #ddd;
}

#myBar {
  width: 1%;
  height: 30px;
  background-color: #04AA6D;
}

<h1>JavaScript Progress Bar</h1>

<div id="myProgress">
  <div id="myBar"></div>
</div>

<br>
<button onclick="move()">Click Me</button> 

here is how you do an a sync request

 function produceCSV(data)
 {
   let csvContent = "data:text/csv;charset=utf-8,";

   //unstring the data 
   data = JSON.parse(data);
 
   data.forEach(function(rowArray) {
      let row = rowArray.join(",");
      csvContent += row + "\r\n";
    });

    var encodedUri = encodeURI(csvContent);
    window.open(encodedUri);
 }



//simple Async fetch request 
fetch('http://time.jsontest.com/mydatafile.php')
    .then(res => res.json())
    .then((data) => {

        console.log(data);

        produceCSV(data);
 
    }).catch(err => console.error(err));

Just make sure your php file returns json_encode($data);

... And you might want to place some headers in that php file ... like

<?php
// Headers
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json');
header('Access-Control-Allow-Methods: FETCH');
header('Access-Control-Allow-Headers: Access-Control-Allow-Headers,Content-Type,Access-Control-Allow-Methods, Authorization, X-Requested-With');
header('X-Requested-With: XMLHttpRequest');
header('Accept: application/json');



include_once 'connection.php';
include_once 'models.php';

//MYSQL CALL 

$result = mysqlQuery ....
// convert the array to json string  
$result = json_encode($result);

//Echoing data to the screen sends it to the clients browser in a post 
  request ...
echo $result;

If you ever need to find anything out about a JavaScript variable

console.log(yourJSvariable)

is your friend