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

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

This application will display a list of products by selecting the data from the products table. Users can see the product details, there will be two download buttons, one for Excel and the other for CSV.

create and download Excel and CSV in PHP

php export to excelFolders and Files

generate excel file in php mysql

We have a folder named 'excel' under 'xampp/htdocs' as we are using XAMPP.

  1. Folder 'cfg' - This folder is for dbconnect.php that is used to connect to the MySQL database.
  2. Folder 'css' - Custom stylesheet is in this folder
  3. Folder 'output' - The 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

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 the composer first. If you do not have Composer installed, you can download and install it from https://getcomposer.org/download/.

How to verify if composer is installed

To check if the composer is installed, go to the command prompt in Windows and enter composer. If it is already installed, it will show the composer version and usage etc. This means the composer is installed already. If you see something like 'Command is not recognizable', the composer is not installed.

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

Download PHPSpreadsheet

Once Composer is installed successfully, go to the command line by typing 'cmd' in search box of your Windows. 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 Excel library. I go to D:\xampp\htdocs\excel\PHPspreadsheet. Once you are in your required folder, type the below command and press Enter:

composer require phpoffice/phpspreadsheet

For my example, see the below screenshot:

fetch data in excel or generate excel file in PHP

This will download the PHPSpreadsheet library into the folder you selected. Usually, it installs very fast without any errors. Still, check if there is any error. Once it is installed successfully, verify that there is a folder called vendor within it. The vendor folder will contain the required files and folder.

Create product table in MySQL database

We will create a table named 'product' in MySQL database. This table will store product details, The 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 product, 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

We will create some dummy data in this table.

product.sql


CREATE TABLE `product` (
  `product_id` int(11) NOT NULL,
  `product_name` varchar(200) NOT NULL,
  `price` decimal(12,2) NOT NULL,
  `stock` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `product` (`product_id`, `product_name`, `price`, `stock`) VALUES
(1, 'Sofa', '10000.00', 34),
(2, 'Dining Table', '6000.00', 100),
(3, 'Dining Chair(set of 4)', '5000.00', 30),
(4, 'Mattress', '8000.00', 111);

ALTER TABLE `product`
  ADD PRIMARY KEY (`product_id`);

ALTER TABLE `product`
  MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

Connect to MySQL database (dbconnect.php)

We will use a separate PHP program to connect to MySQL database. We will create it in 'cfg' folder.

dbconnect.php


<?php
  $server="localhost";
  $userid="root";
  $pwd="";
  $dbname="demo";
  $conn = mysqli_connect($server, $userid, $pwd, $dbname);
//Check connection
if (!$conn) 
  	die("Connection Error: " . mysqli_connect_error());

We are using mysqli_connect() method with four parameters.

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

Include dbconnect.php in other php programs where database connection is needed. You can also read the topic How to connect to MySQL database in PHP using MySQLi and PDO.

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 the database table in an HTML table. There will be two buttons to download the files, one for xlsx and the other for csv.

Include Spreadhseet Library

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


<?php 
require 'PhpSpreadsheet/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

include "cfg/dbconnect.php";

Make sure you give the 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, mention the proper path for autoload.php. This is very important to set it correctly to avoid errors. In line 3 and 4, we are importing Spreadsheet and Xlsx packages. Then include dbconnect.php to establish the connection to MySQL database.

Display Products in an HTML table

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


<h2>List of Products</h2>
<?php
$sql = "SELECT *  FROM product order by product_id";
$result = mysqli_query($conn,$sql);
?>
<div class="table-responsive">
	<table class="table table-bordered table-hover">
		<thead>
			<tr><th>Product Id</th><th>Product Name</th><th>Price (INR)</th><th>Stock</th>
            </tr>
        </thead>
        <?php if (mysqli_num_rows($result) > 0) { 
        	foreach ($result as $value){  ?>                  
        		<tr>
        			<td><?php echo $value['product_id']; ?></td>
        			<td><?php echo $value['product_name']; ?></td>
        			<td><?php echo $value['price']; ?></td>
        			<td><?php echo $value['stock'];?></td>
        		</tr>
        	<?php }
        }
        else
        	echo '<td colspan = "5"> No Products Available</td><tr>'; ?>
    </table>
</div>

Generate Excel and CSV files

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 the below code:

<?php
if (mysqli_num_rows($result) > 0) {  
	// create an xlsx file for the products
	$spreadsheet = new Spreadsheet(); // create a spreadsheet object
	$sheet = $spreadsheet->getActiveSheet();   // get the sheet to write
	//column headers in first line
	$sheet->setCellValue("A1","product_id");
	$sheet->setCellValue("B1","product_name");
	$sheet->setCellValue("C1","price");
	$sheet->setCellValue("D1","stock");
	$row = 1; $col = 0;
	foreach ($result as $value){
		$col = 65; // start with A column, increment to get B, C, D etc
		$row++;
		$sheet->setCellValue(chr($col).$row, $value['product_id']);
		$col++;
		$sheet->setCellValue(chr($col).$row, $value['product_name']);
		$col++;
		$sheet->setCellValue(chr($col).$row, $value['price']);
		$col++;
		$sheet->setCellValue(chr($col).$row, $value['stock']);
	}
	$writer = new Xlsx($spreadsheet);   // excel spreadsheet 
	$writer->save('output/product.xlsx');
	// create a csv file also
	$file = fopen("output/product.csv","w");
	$line = array("Product ID","Product Name","Price","Stock");
	fputcsv($file, $line); 
	foreach ($result as $row){
		$line = array($row['product_id'],$row['product_name'],$row['price'],$row['stock']);
		fputcsv($file, $line); 
	}
	fclose($file);
	?>
	<div class="download-btn">
		<a href="output/product.xlsx" class="btn btn-success" download>Download Excel</a>  <a href="output/product.csv" class="btn btn-primary" download>Download CSV</a>
	</div>
<?php } ?>

In lines 4 and 5 we create a Spreadsheet object and point to the current active sheet. Then we define the column headers which will be the first line of the Excel file. Method setCellValue() needs two parameters, cell position and the value. You can easily see A1 is column A in the first row, B1 is column B of the first row and so on. It is the 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 columns A, B, C and D. Next row 3. This way we will write all the rows returned by the SQL statement in a loop.

First, column A is 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 write using the method setCellValue() for each database column and increment $col after each call to setCellValue().

Once the loop ends, we need to create an 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 we mentioned output/product.xlsx in the save() method.

For the CSV file you need to open a file in PHP using fopen() function. Then write the header column in the 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 the other for CSV download. In Line 36, we defined these two buttons. Note that for the download option we are using download attribute in the anchor tag that works for HTML5.

Add CSS (style.css)

We will use the below styles. We have already added style.css in index.php. See below:

style.css


h2{
	text-align:center;
	margin-top: 20px;
}
.download-btn{
text-align: right;
}
table>thead{
	font-size:13px;
	background-color:#154360;
	color:#fff;
}

create dynamic excel file in PHPTest the Application

Run localhost/excel in the browser. Make sure service are running in XAMPP control panel. You will see below screen which displays the products.

Database Data Export to Excel File using PHP

If you see the output folder, you will see two files are generated, one for Excel and the other for CSV.

Download Excel and CSV files and check if everything is working correctly.

How to Download a File in PHPNote

  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.

use download button on a php pageDownload Source Code

You can download the source code by clicking on the Download button below.

php export to excel on button clickConclusion

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

I hope this topic was useful to you.