Here we will see how to generate excel report from MySQL database using Codeigniter. Codeigniter is one of the popular PHP framework for web applications and it’s easy to work with Codeigniter for building web applications.

Why excel reporting?

Most people in your organization are likely to be familiar with excel, hence it ensures a short (or nonexistent) learning curve during implementation.

It allows you to use templates and formulas to aggregate data.

It has very easy-to-use charting functionality compared to other software, and several helpful built-in features.

It is also great because you have the ability to cut and paste into reports. Most people write reports in Word, so it’s easy to cut and paste data or charts from Excel into Word.

Here we will create a web application to generate excel report from MySQL database using Codeigniter.

We will create a table in MySQL database and retrieve the records from the table and display on a web page in table format. We will provide a hyper link to generate the excel file from the table data. You will be given a popup asking to save the generated excel file. Then you can choose your convenient location for saving the file.

Prerequisites

Knowledge of PHP & Codeigniter 3
Knowledge of MySQL or any other database

Example – Generate excel report from MySQL database using Codeigniter

Now we will implement the example on generate excel report from MySQL database using Codeigniter using the following steps:

We need to create MySQL table and autoload few libraries, helper class etc. Also we need to generate HTML table in view. So to finish all these steps please read the tutorial Generate HTML table from MySQL database using Codeigniter before further proceeding. We will add our additional functionality for generating excel report from MySQL table here.

We will generate excel report from our MySQL database using Codeigniter but Codeigniter does not provide any built-in functionality for generating excel report. So we need to use third party library for excel generation. One such library can be downloaded from the URL xlsx library.

Now extract the downloaded library and put it into application/libraries folder.

Now to get advantage of xlsx library we won’t use xlsx library directly, instead we will create a new library class that will extend the xlsx functionality for using into our own application.

So create a new file inside application/libraries/Excel.php and create class Excel that extends XLSXWriter class.

<?php

if(!defined('BASEPATH')) exit('No direct script access allowed');

require_once dirname(__FILE__) . '/xlsxwriter.class.php';

class Excel extends XLSXWriter { 
	
	function __construct() { 
		parent::__construct(); 
	}
	
}

/* End of file Excel.php */
/* Location: ./application/libraries/Excel.php */

Please make sure you have read Generate HTML table from MySQL database using Codeigniter before reading this tutorial.

You may would also like to read

Generate PDF report from MySQL database using Codeigniter

Now we have the existing controller file application/controllers/product.php. Therefore  we will just add our new functionality to the existing class file to generate excel report from MySQL database using Codeigniter.

Add below function generate_excel_report() to the existing controller. Please do not delete or modify the existing code from the controller.

Here we first load the Excel library to use the functions for generating excel report from MySQL database using Codeigniter.

Then we define column header names with their types. Next we create an object of Excel and put some meta data information such as keywords, title, subject, author etc.

Next we write the header names. Then we fetch the product information from MySQL database and write each row information to the Excel object. Finally we write the data to excel file.

Now as we want to download the file forcefully and give user an option to save the file we set some http header parameters. If you want you may also write as string by uncommeting the line $writer->writeToString().

public function generate_xlsx_report() {
	//load xlsx library
	$this->load->library('Excel');
	
	//define column headers
	$headers = array('Product Id' => 'integer', 'Price' => 'price', 'Sale Price' => 'price', 'Sales Count' => 'integer', 'Sale Date' => 'string');
	
	//fetch data from database
	$salesinfo = $this->product_model->get_salesinfo();
	
	//create writer object
	$writer = new Excel();
	
        //meta data info
	$keywords = array('xlsx','MySQL','Codeigniter');
	$writer->setTitle('Sales Information for Products');
	$writer->setSubject('Report generated using Codeigniter and XLSXWriter');
	$writer->setAuthor('https://roytuts.com');
	$writer->setCompany('https://roytuts.com');
	$writer->setKeywords($keywords);
	$writer->setDescription('Sales information for products');
	$writer->setTempDir(sys_get_temp_dir());
	
	//write headers
	$writer->writeSheetHeader('Sheet1', $headers);
	
	//write rows to sheet1
	foreach ($salesinfo as $sf):
		$writer->writeSheetRow('Sheet1',array($sf->id, $sf->price, $sf->sale_price, $sf->sales_count, $sf->sale_date));
	endforeach;
	
	$fileLocation = 'salesinfo.xlsx';
	
	//write to xlsx file
	$writer->writeToFile($fileLocation);
	//echo $writer->writeToString();
	
	//force download
	header('Content-Description: File Transfer');
	header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
	header("Content-Disposition: attachment; filename=".basename($fileLocation));
	header("Content-Transfer-Encoding: binary");
	header("Expires: 0");
	header("Pragma: public");
	header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
	header('Content-Length: ' . filesize($fileLocation)); //Remove

	ob_clean();
	flush();

	readfile($fileLocation);
	unlink($fileLocation);
	exit(0);
}

We don’t have any change or modification to the model class, so it remains as it is.

Now we will add just one hyperlink on which user will click to generate excel report from MySQL database using Codeigniter. So let’s create hyperlink on the view file.

<?php
	echo anchor('product/generate_pdf'...
        
        echo '&nbsp;&nbsp;';

        echo anchor('product/generate_xlsx_report', 'Generate Excel Report');
	
	echo '<p/>';

	$this->table->set_heading(...

The above hyperlink appears just above the HTML table as shown below in the image.

So we have finished coding part. We will now test our application.

Hit the URL http://localhost/ci3/index.php in the browser and click on the hyperlink Generate Excel Report.

You will get popup with xlsx file asking for saving to a destination directory. The generated xlsx report will look something similar to below image.

Congratulations! You have successfully generated excel report from MySQL database using Codeigniter.

You may would also like to read

Generate PDF report from MySQL database using Codeigniter

Thanks for reading.