Export Data to Excel in PHP MySQL

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 inu 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

Related Keywords:

export to excel in php,export data to excel in php example,export data to excel in php example,export html table to excel in php,export data to excel in php demo,php mysql export to excel with formatting,php export to excel with formatting,export html table to excel using php,php export to excel xlsx,php excel export example

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.

  • fvandal

    Have you even tried the same with CodeIgniter and Ajax?

    • EhteshamMehmood

      Dear @fvandal:disqus Not yet tried. But soon will try export data to excel in codeigniter.

  • S.A. Khalili

    Easy and without using any library but I get error in UTF-8 letters. Though I added charset=uft-8 in content-type but still get error.

  • Guillermo Vargas

    Why when the open the file say this: the file format and extension of don’t match. the file could be corrupted or unsafe. Unless you trust its source, don´t open it. Do you want to open it anyway?

    If make clic in yes I get the information good

  • NRich

    Thank you Ehtesham Mehmood, this information is well explained and structured. It has really helped me. Be blessed

    • EhteshamMehmood

      Thanks for appreciation….. Keep Coding 🙂

  • Markiverse Media

    iam getting all the data in same column could you help on this

    • EhteshamMehmood

      @markiversemedia:disqus Yes sure please share your code which you want to export from mysql and show in excel format. So then I can have an idea where is the fault.

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