Export Data to Excel in PHP MySQL

Export data functionality is very useful where someone has to save data from the web application to his personal computer for future use.So today in this tutorial we will learn how we can Export Data to Excel using PHP and MySQL.

Working:

In this script, we will make an HTML form in which we use a table for viewing the book records.These book records will be read from the database, and then by using our Export Data to Excel script, we will export these records into excel sheet.

 

1-Create Database

First of all, we will create the database for our PHP export data to excel script.So copy this below SQL query and paste it into your SQL editor.

2-Create Table

Now we will create one table into the database named books.

3-Database Connection

We are using PDO PHP Data Object for making a connection with our Database. If you want to explore more PDO see this guide PHP PDO CRUD.Create a file name db_con.php and paste below code and save it.

 

4-Create HTML Table for View

Here we will create a simple HTML table using Bootstrap for viewing the book records with the export data to Excel button.This Table will contain four columns Sr.No, Book Name, Book Author, Book ISBN.

 

5-SQL Query Fetching Data

Now we will write a SQL query from it we will fetch data from the MySQL database.

 

6-Read Data through Loop

Now we will read the data using while loop.

 

7-Complete index.php Code for Export Data Script

This is the complete code script in which we made an HTML form and fetch the data from the MySQL database.

 

This is the output for the above export data to excel script.

export-data-to-excel-in-php-mysql

Now at this stage, we displayed our records into the table now the next step is to export data to excel for this we will make a new file export-book.php.

Export-PHP File

8-Read record from the Database

 

9-Set Column Header for Excel Sheet

We will make a format for the excel sheet header.See below code.

10-Read data from loop

Here in this code, we used two loop one is while loop and other is foreach loop. Here, in the loop data fetched and stored into the variable $rec. So, we loop will run and separate the each row of record.

Now, we have to get the column-wise data from the row. So, we will use “foreach” loop inside the while loop.we have to add one tab (“\t”) value between each and every column values and store this full string to any new variable.After this Now, our columns are ready. We have to separate the rows now. So, we have to add a newline character (“\n”) between each new row:

 

 

11-Direct Download File

We have to download directly when the user clicks or press the export data to excel button.So for this add the code below in your export-book.php file.

 

12-Set File Name

To set the file name add the code below.

 

13-Final Print(echo)

This is the end of the script we will print the table row data and header data to the excel file.

 

14-Complete Code for Export Data to Excel using PHP

 

 

When you click on the export to excel link your file will be downloaded into the browser like in the below screen shot.

exporting-data-into-excel-using-php

Now open this file into excel and you will see this output.

export data to excel php mysql

 

Conclusion:

So in this tutorial, we have shown you how to export data to Excel in PHP. Our simple PHP script lets you implement export data to excel functionality very smoothly.The user can export data to Excel and download it in a .xls file by just one click.If you find this information useful, remember to share it on your social network. If you have any specific concerns/doubt about this article you can comment below and let us know.




download-code excel export data




  • ProgrammingNewbie .

    Amazing…..sir you are a saint, thanks for the share!

    • EhteshamMehmood

      Your welcome, Keep Learning

      • ProgrammingNewbie .

        Yes I will, seems that you are a true guru, is there a donation jar set, for real tho! Kudos

        • EhteshamMehmood

          Thanks for your appreciation. Your appreciation itself is a donation. Just need your prayers.

Read previous post:
nodejs basics phpcodify
Basics of Node.js and How it has Revolutionized Web Development

Node js is a packaged compilation of three components, namely - Google’s V8 Java Script engine, the libuv platform abstraction...

Close