Exporting Data to Excel with PHP and MySQL

I’ll show you how to export grid data to an excel file in this post. Export/Import is a relatively popular functionality for web development; nevertheless, there are times when we need to export entire grid data into an excel file.

in which case we should use the approach described below. In PHP, we simply need to set header information to force the browser to launch the download window.

Video Tutorial

If you are more comfortable in watching a video that explains about Exporting Data to Excel with PHP and MySQL, then you should watch this video tutorial.

You can also check other tutorial of Export Data with PHP,

Export MySQL Data To Excel in PHP

Because Excel is the finest format for storing data in a file, exporting data in Excel format is a very important tool that allows users to save data for offline use. You’ll learn how to use PHP and MySQL to export data to Excel.

So the file structure for this example is the following:

  • index.php : This is the entry file.
  • connection.php: This file is use to connect MySQL with PHP
  • generate_excel.php : This is main PHP file that ll have export method to export data into the excel.

Create MySQL Database Table

Let’s create a tasks table that ll all tasks records which will export later on in excel format.

Now, I’ll insert some sample data into the tasks table.

Create MySQL Connection With PHP

We’ll create a connection.php file and add the below code. in this file, We’ll pass database hostname, database username, database password, and database name.

Get the Tasks Data from MySQL Database Table

We’ll receive entries from a MySQL database table tasks and put them in an array so we can show them and export them to an excel file. Added below code into the top of the generate_excel.php file.

Export Data to Excel

Let’s create export features using PHP and export data into excel. We’ll also force the to browser download the file instead of display it. We’ll add the below code into the generate_excel.php file.

the code shown above, The switch case block will execute based on the parameter value and the method invoked.
Browsers are being forced to download an excel file.

Create HTML and Display Records with Export Button

Define html layout for display data in table and button to fire export-to-csv action. Added below code into the index.php file.

We’ve imported the ‘generate_excel.php’ file at the top of the code, which will yield task data for display in the HTML table. We’ve established a dropwodn with a ‘export to excel’ option. All task data will be displayed in an HTML table.

Submit Form Using jQuery

We’ve included a dropdown and need to catch and fire events so that when a user selects an option, the form is submitted. We’ll paste the code below at the bottom of the index.php file.

Result:

export-to-excel with php and mysql
I hope its help you!.

Demo and Download source Code From Below Link

21 thoughts on “Exporting Data to Excel with PHP and MySQL

  1. Dear friend this is more like csv format, excel format contains many features like formatting ,auto calculations ..etc.
    You should use open source excel libraries for that.

    Regards,
    Kaustubh

  2. this code downloading full page
    i want to download particular array in excel. what can i do for that..kindluy help me

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.