Export MySQL Data To Excel in PHP

Sometimes, you may need to export MySQL Data to Excel formats. Export MySQL Data To Excel in PHP

The database or tables of the MySQL database can be exported into various file formats, such as CSV, XML, SQL, Excel, etc., by using the PHP client, phpMyAdmin.

It is also possible to export MySQL data using PHP script instead of exporting the data manually. When a web application requires data to be stored in Excel format from the MySQL database, then it is best to use PHP script to perform this task. This tutorial shows you how to export MySQL data using PHP script.

PHP SPREADSHEET

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.

Use composer to install PhpSpreadsheet into your project:

composer require phpoffice/phpspreadsheet

The above line of command will generate a folder named spreadsheet and this folder would have one folder and two files. vendor, composer.json & composer.lock

Now Create Table Create a table ( Database name is test )

--
-- Table structure for table `student`
--

CREATE TABLE `student` (
  `id` int(6) NOT NULL,
  `name` varchar(55) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `age` int(10) NOT NULL,
  `phone` int(20) NOT NULL,
  `city` varchar(55) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student`
--
INSERT INTO `student` (`id`, `name`, `gender`, `age`, `phone`, `city`) VALUES
(2, 'Laund', 'Male', 21, 43535343, 'Mumbai'),
(3, 'Virendera', 'Male', 33, 43535343, 'Delhi'),
(4, 'dheer', 'Male', 22, 23113113, 'Agra'),
(5, 'Ramehs', 'Male', 21, 52325343, 'Luknow'),
(11, 'Ashifa', 'Female', 42, 13535343, 'Chennai'),
(12, 'Aditi', 'Female', 33, 43535343, 'Banglore'),
(13, 'Narendra Shar', 'Male', 32, 33332333, 'Kolkata');

Now create a index.php file for your landing page or home page

index.php

<?php
$connect = mysqli_connect("localhost","root","","test");
$query = "SELECT * FROM student";
$statement = mysqli_query($connect,$query);
?>

<!DOCTYPE html>
<html>
   <head>
     <title>Convert Database Data to Excel using PHPSpreadsheet</title>
     <meta name="viewport" content="width=device-width, initial-scale=1.0">
     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
     <script src="js/jquery-1.10.2.min.js"></script>
	  	<script src="js/bootstrap.min.js"></script>
	  	<script src="js/jquery.form.js"></script>
     <style>
      .progress-bar { 
      height:20px;
      background:red; 
      width:0px;
      text-align:center;

      border: 2px solid gray;
      }

      .pp{
        width : 600px;
          background-color: #e0e0e0;
      }
</style>   
   </head>
   <body>
     <div class="container">
     <div class="pp"> 
        <div class="progress-bar"></div>
      </div>
     <h3 class="mb-5" align="center">Convert Database Data to Excel using PHPSpreadsheet</h3>
      <div class="row">
      
       <div class="col-sm-4">
         <?php include('insert_record.php') ?>
       </div>

       <div class="col-sm-1">
          
       </div>

       <div class="col-sm-7">
       <h4 class="text-center  ml-4 mb-5">View Records</h4>
        <table class="table mb-5">
        <form method="POST" id="" action="export.php">
            <table class="table table-striped table-bordered">
              <tr>
                <th>Name</th>
                <th>Age</th>
                <th>Gender</th>
                <th>Phone</th>
                <th>City</th>
              </tr>
              <tbody id="table_content">
              <?php
              foreach($statement as $row)
              {
                echo '
                <tr>
                  <td>'.$row["name"].'</td>
                  <td>'.$row["age"].'</td>
                  <td>'.$row["gender"].'</td>
                  <td>'.$row["phone"].'</td>
                  <td>'.$row["city"].'</td>
                </tr>
                ';
              }
              ?>
             </tbody> 
            </table>
            <input type="hidden" name="file_content" id="file_content" />
            <input type="submit" name="convert"  id="convert" class="btn btn-success" value="Convert To Excel">
              <!--<div class="form-group">
							<label>File Upload</label>
							<input type="file" name="uploadFile" id="uploadFile" accept=".jpg, .png" />
						</div>
						<div class="form-group">
							<input type="submit" id="uploadSubmit" value="Upload" class="btn btn-info" />
						</div>
						<div class="progress">
							<div class="progress-bar" role="progressbar" aria-valuenow="0" aria-valuemin="0" aria-valuemax="100"></div>
						</div>
						<div id="targetLayer" style="display:none;"></div>
          </form>
          <br />
          <br />
        </div>  
        <div class="panel panel-default">
				<div class="panel-heading"><b>Ajax File Upload Progress Bar using PHP JQuery</b></div>
			  	<div class="panel-body">
		
					<div id="loader-icon" style="display:none;"><img src="loader.gif" /></div>
				</div>
			</div>
       </div>-->
      </div>
     </div>
     <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
 
  </body>
</html>

<script>
$(document).ready(function(){
	$('#uploadImage').submit(function(event){
		if($('#uploadFile').val())
		{
			event.preventDefault();
			$('#loader-icon').show();
			$('#targetLayer').hide();
			$(this).ajaxSubmit({
				target: '#targetLayer',
				beforeSubmit:function(){
					$('.progress-bar').width('50%');
				},
				uploadProgress: function(event, position, total, percentageComplete)
				{
					$('.progress-bar').animate({
						width: percentageComplete + '%'
					}, {
						duration: 1000
					});
				},
				success:function(){
					$('#loader-icon').hide();
					$('#targetLayer').show();
				},
				resetForm: true
			});
		}
		return false;
	});
});
</script>

Now I will create a file insert file for data insert into the database then we will fetch records and show in excel using a PHP spreadsheet.

insert_record.php

   <?php $connect = mysqli_connect("localhost","root","","test");
   
       if(isset($_POST['submit']))
       {
           $name = $_POST['name'];
           $age = $_POST['age'];
           $gender = $_POST['gender'];
           $phone = $_POST['phone'];
           $city = $_POST['city'];

           $sql = "INSERT into student(name,age,gender,phone,city)VALUES('$name','$age','$gender','$phone','$city')";
           $query = mysqli_query($connect,$sql);

           header('Location:index.php');
        }
    
    ?>
     <h4 class="text-center  ml-4 mb-5">Insert Records</h4>
         <form  action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="POST">
            
            <div class="form-group">
             <input type="text" class="form-control  mb-4" name="name" placeholder="Enter name" required="">
            </div>

            <div class="form-group">
             <input type="text" class="form-control  mb-4" name="age" placeholder="Enter age" required="">
            </div>

            <span><strong>Select Gender : </strong></span>
              Male <input type="radio" class=" mb-4" name="gender" value="Male" style="pointer:cursor">
              Female <input type="radio" class=" mb-4" name="gender" value="Female" style="pointer:cursor">
            <br> <br>

            <div class="form-group mt-3">
             <input type="text" class="form-control mb-4" name="phone" placeholder="Enter phone">
            </div>

            <div class="form-group">
             <input type="text" class="form-control mb-4" name="city" placeholder="Enter city" required="">
            </div>

            <input type="submit" name="submit" class="btn btn-primary btn-block" style="float:right;" value="Submit">
         </form>

Now we will create phpspreadsheet code for converting MySQL Data to Excel sheet .

export.php

<?php


// $connect = mysqli_connect("localhost","root","","test");


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', 'Name');
$activeSheet->setCellValue('B1', 'Age');
$activeSheet->setCellValue('C1', 'Gender');
$activeSheet->setCellValue('D1', 'Phone');
$activeSheet->setCellValue('E1', 'My City');

// Code for displying data of Database

$connect = mysqli_connect("localhost","root","","test");

$query = "SELECT * FROM student";

$statement = mysqli_query($connect,$query);

if(mysqli_num_rows($statement) > 0) {
    $i = 2;
    while($row = mysqli_fetch_assoc($statement)) {
        $activeSheet->setCellValue('A'.$i , $row['name']);
        $activeSheet->setCellValue('B'.$i , $row['age']);
        $activeSheet->setCellValue('C'.$i , $row['gender']);
        $activeSheet->setCellValue('D'.$i , $row['phone']);
        $activeSheet->setCellValue('E'.$i , $row['city']);
        $i++;
    }
}

// $filename = 'products.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';

// echo json_encode($filename);

// 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');

Now create one more file named ajax.php . This file will not refresh the page while downloading the excel sheet

ajax.php

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
	<div class="form-group">
	    <input type="submit" id="uploadSubmit" value="Upload" class="btn btn-info" />
        <p id="#data"></p>
	</div>
<script type="text/javascript">
   $(document).ready(function (){

    $(window).on("load", function () {
         $.ajax({
            url : "export.php",
            type : "POST",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success : function(data){
               $.each(data.result, function(i,post){
					    //  $("#product_name").append(post.product_name); 
              //  $("#product_id").append(post.product_price); 

              var newRow =  "<div class='row update_price'>"
                    +"<div class='col-12 col-sm-12 col-md-2 text-center'>"
                            +"<img class='img-responsive' src='"+post.product_img+"' alt='prewiew' width='50' height='auto'>"
                            +"</div>"
                            +"<div class='col-12 text-sm-center col-sm-12 text-md-left col-md-6'>"
                            +"<h5 class='product-name text-secondary'><strong>"+post.product_name+"</strong></h4>"
                            + "<h5>"
                            +"<small>"+post.product_desc+"</small>"
                            +"</h4>"
                            +"</div>"
                  
                            +"<div class='col-12 col-sm-12 text-sm-center col-md-4 text-md-right  row'>"
                            +"<div class='col-3 col-sm-3 col-md-6 text-md-right' style='padding-top: 5px'>"
                            +"<h5 id='product_price' class='font-weight-bold'><span>"+post.product_price+"</span></h5>"
                            +"</div>"
                            +"<div class='col-4 col-sm-4 col-md-4'>"
                            +"<div class='quantity'>"
                            +"<input type='number'  step='1' max='99' min='1' id='"+post.product_id+"' value='"+post.product_qty+"' title='Qty' class='qty' size='4'>"    
                            +"</div>"
                            +"</div>"
                            +"<div class='col-2 col-sm-2 col-md-2 text-right'>"
                            +"<button type='button' id='.$row['product_id'].' class='btn btn-outline-danger btn-xs delete_cart'>"
                            +"<i class='fa fa-trash' aria-hidden='true'></i>"
                            +"</button>"
                            +"</div>"
                        +"</div>"
                        +"</div>"
                        +"<hr>";
                 
                        $('#data').append(newRow);
                
				});
            } 
          });
     });
    });    
</script>     

Now run the code on Browser .

Subscribe to My Programming YouTube Channel Before Downloading the code :

Download The Code: Export MySQL Data to Excel

Please Read our more articles on php and reactjs

Send Forgot password by mail or message in PHP

Php CRUD Operations Tutorial Using MySQLi in Hindi

Building an Image Gallery with Laravel and React

How To Create Charts In React With Database

Leave a Reply

Your email address will not be published. Required fields are marked *