How to Load Millions of records in EXCEL Sheet using PHP Spreadsheet. We can use PHP or any other language.
Learn how to import and analyze millions of records in Excel using PHP. We can load thousands and millions of data on the browser using PHP.
We can use PHP Spreadsheet for loading data on EXCEL Sheet. How to Load Millions of records in EXCEL Sheet using PHP Spreadsheet PHP is a very powerful language. PhpSpreadsheet is a library written in pure PHP and offers a set of classes that allow you to read and write various spreadsheet file formats such as Excel and LibreOffice Calc.
Install PHP Spreadsheet in your system using composer. If you don’t have a composer then first install the composer. Install composer from here https://getcomposer.org/download/
The composer is a packager manager ( Dependency Manager ) using the composer we install packages for our project. Composer is like NPM or YARN
Installation
Use composer to install PhpSpreadsheet into your project:
composer require phpoffice/phpspreadsheet
This is another image of the project . Now we are loading 1 million records on browser then We can save these records in EXCE SHEET using PHP Spreadsheet.
If you click on Download as excel then your all records will be stored into EXCEL Sheet . Now we will index.php page for landing page .
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta http-equiv="X-UA-Compatible" content="ie=edge" />
<title>Load Mysql TO Excel</title>
<script src="https://code.jquery.com/jquery-3.4.1.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo="crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" type="text/css"/>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.button {
background-color: #008cba; /* Green */
border: none;
color: white;
padding: 15px 32px;
text-align: center;
text-decoration: none;
display: inline-block;
font-size: 16px;
margin: 4px 2px;
cursor: pointer;
}
.button:disabled {
opacity: 0.5;
}
.hide {
display: none;
}
</style>
</head>
<body>
<div class="container">
<h4 id="excelsheet" class="text-center mt-5">
<p>Load Mysql data to EXCEL Sheet</p>
<span class="text-primary mt-4">100,000 Records</span>
</h4>
<p id="loadExcel"> </p>
<h4 class="text-center mt-5" id="downloaded"></h4>
<button class="button" id="showData">
<!-- <i class="loading-icon fa fa-spinner fa-spin hide"></i> -->
<span class="btn-txt">Load Data from Mysql</span>
</button>
<div class="alert alert-success mt-5" id="alertMsg" role="alert">
<h5 class="text-center mt-2"><i class="loading-icon fa fa-spinner fa-spin hide"></i> <span class="loading-icon text-center hide">100,000 Records are Loading.... </br>(Wait for few Seocnds)</span></h5>
<p class="text-center data-load font-weight-bold"> All 100,000 Records will load here...</p>
</div>
<div class="result"></div>
</div>
<script>
$(document).ready(function() {
$("#showData").on("click", function() {
$(".result").text("");
$(".loading-icon").removeClass("hide");
$(".data-load").hide();
$(".button").attr("disabled", true);
$(".btn-txt").text("Fetching Data from Mysql Database...");
$.get("show_excel.php", function(data) {
$(".result").html(data);
$("#showData").hide();
$("#alertMsg").hide();
$(".loading-icon").addClass("hide");
$(".button").attr("disabled", false);
$(".btn-txt").text("Fetch Data From Server");
});
});
});
</script>
</body>
</html>Now we will create the page for RECORDS fetch from the database ( 1 million records fetch ) . So create show_excel.php .
<?php
$connect = mysqli_connect("localhost","root","","php_excel");
$query = "SELECT * FROM excel_data";
$statement = mysqli_query($connect,$query);
?>
<!DOCTYPE html>
<html>
<head>
<title>Mysql Data to Excel</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<div class="row">
<div class="col-sm-12">
<h6 class="text-center ml-4 mb-1">View Records (Scroll Down to see all Records)</h6>
<table class="table mb-5">
<!-- <button class="btn btn-success convert" id="btnHide">Convert</button> -->
<input type="submit" name="convert" id="convert" class="btn btn-success" value="Download as excel sheet">
<form method="POST" id="convert_form" action="export.php">
<table class="table table-striped table-bordered">
<tr>
<th>No</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Birthdate</th>
<th>Record Added</th>
<div class="pp">
<div class="progress-bar"></div>
</div>
</tr>
<tbody id="table_content">
<?php
foreach($statement as $row)
{
echo '
<tr>
<td>'.$row["id"].'</td>
<td>'.$row["first_name"].'</td>
<td>'.$row["last_name"].'</td>
<td>'.$row["email"].'</td>
<td>'.$row["birthdate"].'</td>
<td>'.$row["added"].'</td>
</tr>
';
}
?>
</tbody>
</table>
<input type="hidden" name="file_content" id="file_content" />
</form>
<br />
<br />
</div>
</div>
</div>
</body>
</html>
<script>
$(document).ready(function() {
$("#convert").on("click", function() {
$(".result").text("");
$(".loading-icon").removeClass("hide");
$(".data-load").hide();
$(".button").attr("disabled", true);
$(".btn-txt").text("Fetching Data from Mysql Database...");
window.location.href = 'export.php';
$('#excelsheet').hide();
$('#loadExcel').html('<link href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" type="text/css"/> <div class="alert alert-success mt-5" id="alertMsg1" role="alert"> <h6 class="text-center mt-2"><i class="loading-icon fa fa-spinner fa-spin hide"></i> <span class="loading-icon text-center hide">100,000 Records are Loading.... </br>(Wait for few Seocnds)</span></h6> <p class="text-center font-weight-bold " id="finish"> Excel Sheet is Laoding ...</br> (Wait for few Seconds)</p> </div>')
.delay( 18800 ).fadeOut('#finish');
$('#downloaded').html('<h4 class="text-center">Thanks For Downloading</h4>');
});
});
</script>The above code will load data on the browser . Now we have to transfer all records into EXCEL SHEET So create page export.php page . This page will have code for data transfer from database to excel sheet .
<?php
include 'spreadsheet/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$Excel_writer = new Xlsx($spreadsheet);
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();
// Code for describing Cell in EXCEL
$activeSheet->setCellValue('A1', 'First Name');
$activeSheet->setCellValue('B1', 'Last Name');
$activeSheet->setCellValue('C1', 'Email');
$activeSheet->setCellValue('D1', 'Birth Date');
$activeSheet->setCellValue('E1', 'Record Date');
// Code for displying data of Database
$connect = mysqli_connect("localhost","root","","php_excel");
$query = "SELECT * FROM excel_data";
$statement = mysqli_query($connect,$query);
if(mysqli_num_rows($statement) > 0) {
$i = 2;
while($row = mysqli_fetch_assoc($statement)) {
$activeSheet->setCellValue('A'.$i , $row['first_name']);
$activeSheet->setCellValue('B'.$i , $row['last_name']);
$activeSheet->setCellValue('C'.$i , $row['email']);
$activeSheet->setCellValue('D'.$i , $row['birthdate']);
$activeSheet->setCellValue('E'.$i , $row['added']);
$i++;
}
}
// $filename = 'ExcelData.xlsx';
// Code for generating random name for file
$n=5;
function getName($n) {
$characters = 'abcef';
$randomString = '';
for ($i = 0; $i < $n; $i++) {
$index = rand(0, strlen($characters) - 1);
$randomString .= $characters[$index];
}
return $randomString;
}
$data = getName($n);
$filename = $data.'.xls';
// Header code of PHP
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='. $filename);
header('Cache-Control: max-age=0');
$Excel_writer->save('php://output');
Only three files are for this project and one folder will be for PHP SPREADSHEET Library .
Download the CODE : Source Code Download