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

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.

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.

Download PHPSpreadsheet

Once Composer is installed successfully, go to 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 the excel library. I go to D:\xampp\htdocs\excel\PHPspreadsheet. Once you are in your required folder, type below command:

composer require phpoffice/phpspreadsheet

For my example, see below screenshot:

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.

Create product table in MySQL database

We will create a table named 'product' in MySQL database. This table will store product details, Table structure is given below:

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.

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.

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

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 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 an HTML table

Next, we need to write code to select data from 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

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 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 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 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 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 we 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.

Add CSS (style.css)

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

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