Export table data to Excel using PHP and MYSQL without PLUGIN

Dec 25, 2018 / PHP / MySQL /

how to export data to the excel sheet without using any plugin in PHP and MySQL. Here in this quick tutorial I will let you know how to export grid data into excel file. Export/Import is very common functionality for web development, some time we need to export all grid data into excel file that time we need below method to export data into excel file. It’s very easy and simple steps in PHP, we need to set header information to force browser to open download window.
We often need to generate reports in excel sheet, but sometime we have our data stored in MySQL database. It is quiet easy to fetch records from MySQL and export the result in .csv file or .xls file and let user to force download it. We will make use of PHP and MySQL to do this functionality. This downloading option is very important in web application. Using this code we can generate a report from our MySQL data.
This tutorial will explain about downloading the MySQL data into excel sheet format using PHP MySQL. Here we will select all the data as per our MySQL query and generate a excel file.

Create a mysql database : test

 


CREATE DATABASE databasename;

 

Create a mysql database table :demo

 


create table demo(
   id INT NOT NULL AUTO_INCREMENT,
   country VARCHAR(100) NOT NULL,
   state VARCHAR(40) NOT NULL,
   city VARCHAR(40) NOT NULL,
   pin VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( id )
);

 

Create button for export data

 



<a href="export.php?export=true">Export</a>


 

Create a php file :export.php

 


// Database Connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";
 
 $conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
 
if(isset($_GET['export'])){
if($_GET['export'] == 'true'){
$query = mysqli_query($conn, 'select * from demo'); // Get data from Database from demo table
 
 
    $delimiter = ",";
    $filename = "significant_" . date('Ymd') . ".csv"; // Create file name
     
    //create a file pointer
    $f = fopen('php://memory', 'w'); 
     
    //set column headers
    $fields = array('ID', 'Country', 'State', 'City', 'Pin');
    fputcsv($f, $fields, $delimiter);
     
    //output each row of the data, format line as csv and write to file pointer
    while($row = $query->fetch_assoc()){
        
        $lineData = array($row['id'], $row['country'], $row['state'], $row['city'], $row['pin']);
        fputcsv($f, $lineData, $delimiter);
    }
     
    //move back to beginning of file
    fseek($f, 0);
     
    //set headers to download file rather than displayed
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '";');
     
    //output all remaining data on a file pointer
    fpassthru($f);
 
 }
}

Comments

aditya ( Mar 30, 2019 11:57:20 )

is it support doc?

Would you like to share your thoughts?

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

Copyright 2018. All rights are reserved Significant Techno Web Design & Web Development By Significant Techno