How to create and download Excel and CSV files in PHP and MySQL

You can genearte Excel and CSV files in PHP by selecting data from MySQL database with the help of Spreadsheet library. In this topic, I will develop an application to create and download Excel and CSV file.

This application will display a list of products by selecting all products from products table. User can see the product details, there will be two download buttons, one for excel and other for csv.

create and download Excel and CSV in PHP

php export to excelFolders and Files

Below is a screenshot of the folder structure and files I am using:

generate excel file in php mysql

I have created a folder named 'excel' under 'xampp/htdocs' as I am using XAMPP. If you are using WAMP, create 'excel' folder under 'www'.

  1. Folder 'cfg' - In this folder I have kept dbconnect.php which is used to connect to the MySQL database.
  2. Folder 'css' - My custom stylesheet is in this folder
  3. Folder 'output' - Output files, excel and csv, are generated in this folder
  4. Folder 'PHPSpreadsheet' - In this folder PHPSpreadsheet library files are installed
  5. index.php is our main program that displays the product details and generates the files

Below is the screenshot of the page generated by index.php

fputcsv xlsx

Step 1 - Download Composer if not installed already

You need to install PHPSpreadsheet in your computer, using composer you can easily install PHPSpreadsheet. So you need to get composer first. If you do not have composer installed you can download and install composer from https://getcomposer.org/download/.

How to verify if composer is installed

To make sure composer is installed just go to command prompt in windows and enter composer. If it is already installed, it will show composer version and usage etc. This means composer is installed already. If you see something like 'Command is not recognizable', then composer is not installed.

Create Excel File From MySQLi Table in PHP

If it is not installed, download it in your local hard drive. Then run it and get it installed. This is a very useful package to install many other packages easily. Try and make sure it is installed correctly.

Step 2 - Download PHPSpreadsheet

Once Composer is installed successfully, go to command line by typing 'cmd' in search box of your windows. Once you are in command line, you need to go to your PHP project folder where you want to install PHPSpreadsheet. My php project folder is "D:\xampp\htdocs\excel". I created a folder named "PHPspreadsheet" to install the excel library. I go to D:\xampp\htdocs\excel\PHPspreadsheet folder in command line. Once you are in your required folder, type below command:

composer require phpoffice/phpspreadsheet

For my example, please see below screenshot:

fetch data in excel or generate excel file in PHP

Then press Enter, this will download the PHPSpreadsheet library into the folder you selected. Usually, it installs very fast without any error. Still check if there is any error. Once it is installed successfully, check there will be a folder called vendor within the folder you installed it. Vendor folder will contain required files and folder.

Step 3 - Create a product table in MySQL database

Let us create a table named 'product' in MySQL database. This table will have product details, like product_id, product_name, price, stock. I have a database called 'demo'. If you have an existing database other than demo you can also use it. Just make sure same table does not exist already. Table structure is given below:

Generate Excel and CSV in PHP

Table has 4 columns.

  1. product_id - It is the unique id of a user, primary key and auto incremented
  2. product_name - Name of the product
  3. price - Price of the product
  4. stock - Stock available of the product

Data in the product table are as below:

Generate Excel and CSV in PHP and mysql

Create table script with data for this table is given below, you can use this code to create the table. You can also download it from download section later in this topic.

product.sql

After you create the table, please verify if table structure is same as above and rows are inserted in the table as given above.

Step 4 - Connect to MySQL database (dbconnect.php)

Use below script to connect to MySQL database. Note that we have this database connection php program in 'cfg' folder. This is written once and used in every program where database connection is needed. This will be easy for maintenance and also will enable reusability of the code.

dbconnect.php

I am using mysqli_connect() function which needs 4 parameters.

  1. server - in our case it is localhost
  2. userid - we are using root user
  3. password - no password for user root
  4. database name - demo in our case.

If connection is successful then it will return true and false otherwise. This Boolean value is returned and stored in the $conn variable. We will include this dbconnect.php in other php programs so that we do not need to write it again in the program. For detail database connection understanding please read topic How to Connect to MySQL database in PHP using mysqli_connect.

So now we are ready to write PHP code to create excel and csv files.

Step 5 - Write PHP code to create Excel and CSV files with option to download (index.php)

We will write a PHP program to display all products from database table in a HTML table.

There will also be two buttons to download files, one for xlsx and other for csv file download. Next, we will write code for creating xlsx and csv files. These files will contain products details which are displayed on the screen. Let us see the code for index.php.

Include Spreadhseet Library

We need to include the PHPSpreadsheet libraries in our index.php to use the classes.

Make sure you give correct path of autoload.php. In my case, PHPSpreadsheet folder contains the vendor folder and index.php is in the same folder where PHPSpreadsheet folder exists. Depending on your folder structure, make sure you mention proper path for autoload.php. This is vey important to set it correctly to avoid error. In line 3 and 4, we are importing Spreadsheet and Xlsx packages. Then include dbconnect.php to establish connection to MySQL database.

Display Products in a HTML table

Next, we need to write code to select data from database table and display all products in a HTML table. See below code:

Generate Excel and CSV files

Now, we will write code to display product details in an xlsx file and a csv file. Since we already got the product details from database, all we will be doing is just fetch each row of data and write them in xlsx and csv files. Let's see below code:

In line 4 and 5 we are creating a Spreadsheet object and pointing to the current active sheet. Then we are defining the column headers which will be the first line of the excel file. Method setCellValue() needs two parameter, cell position and the value. You can easily see A1 is the column A in the first row, B1 is the column B of first row and so on. It is exactly same as in an excel cell position.

Once column headers are defined, we will write product details in successive rows. So we must start writing from row 2 with column A, B, C and D. Next row 3 with column A, B, C and D. This way we will write all rows returned by the SQL statement in a loop.

First, for column A id set using value 65 in Ascii. So that we can increment it to get B, C, D etc. Also increment $row for each iteration and reset $col to 65 at the beginning of each iteration. Next we just write using method setCellValue() for each database column and increment $col after each call to setCellValue().

Once the loop ends, we need to create Xlsx spreadsheet instance to write into an xlsx file. Note that in our case we have an output folder to keep all our output files, so I mentioned output/product.xlsx in the save() method.

For CSV file you need to open a file in PHP using fopen() function. Then write the header column in first line. Then in a loop write all product rows in the file. fputcsv() function is used to write into the file.

Now, we need to define two buttons to download these two files. One for xlsx download and other for CSV download. In Line 36, we defined these two buttons. Note that for download option we are using download attribute in the anchor tag which works for HTML5.

Here is the complete code for index.php

index.php

Step 6 - Add CSS (style.css)

We need to add some styles. Very simple and basic styles are used here. You can always add better styles. I have already added style.css in index.php. See below:

style.css

create dynamic excel file in PHPTest the Application

Make sure in your XAMPP control panel Apache and MySQL services are running. Open the browser and run localhost/excel. You will see below screen which displays the products.

Database Data Export to Excel File using PHP

Now if you see in output folder, you will see two files are generated, one for excel and other for CSV. Open and verify if all products are written in the files. Just see below contents of output folder:

onclick download excel file in php

Now click on xlsx download, file product.xlsx will be downloaded in your download folder or it will ask to select folder to save the file depending on your download setting in your browser. Same way, it will work for csv file. After downloading, open the files and see if all products are appearing in the xlsx as well as in csv file.

Hope you could successfully implement it and test it.

How to Download a File in PHPImportant Note

  1. You can refer to
    PHPSpreadsheet documentation for more documentation on PHPSpreadsheet. You can use many other methods for cell formatting.
  2. If you want to get data from multiple tables, you just need to change the select statement to join multiple tables and get data in a spreadsheet.
  3. Even though I have used 'xlsx' file extension, you can use 'xls' extension also for the Excel file. You may get a warning while opening the xls file in latest version of MS office, but you may ignore and open the file.

use download button on a php pageDownload Source Code

All program including the spreadhsheet library are in the zip file. You can download it by clicking on the Download button below.

php export to excel on button clickConclusion

I have used a simple example, using all rows from one table. In actual project, there could be requirement to select data from multiple tables. So, your SQL statement will not be so simple. Anyway, that is more about selecting data. But, for excel file, you might have to use different format or use formulas etc. So, read PHPSpreadsheet documentation to know more about using PHPSpreadsheet and use them as per your requirement.

Hope this topic was useful to you.