Create and Download Excel and CSV in PHP using PhpSpreadsheet

Download Composer if not installed already

You need to install PHPSpreadsheet in your computer, using composer you can easily install PHPSpreadsheet. So, you have 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 the 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, the 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.

Download PHPSpreadsheet

Once Composer is installed successfully, go to the command line by typing 'cmd' in the Windows search box. 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 the below command and press Enter:

composer require phpoffice/phpspreadsheet

For my example, see the below screenshot:

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 the MySQL database. This table will store product details, The table structure is given below:

The table has four 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="test";
  $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 - "test" in our case.

Include dbconnect.php in other PHP programs where a 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 and download Excel and CSV files (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 Excel and the other for CSV.

We will 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 mention 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 lines 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 and a CSV file. Since we got the product details from the database, all we will be doing is fetching each row and writing 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 in 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 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 have used the download attribute in the anchor tag; this works for HTML 5.

Add CSS (style.css)

We will use the 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;
}