Exporting your MySQL table data with PHPExcel + CodeIgniter

Posted by on Mar 8, 2011 in Backend | 15 comments

PHPExcel + CodeIgniter Most of the time my clients need to download data from their database tables. Exporting to CSV is a pain in the rear for users and it leads to confusion (you know the colon and semicolon stuff). Today, I decided to make a very small controller that is portable and efficient for exporting full MySQL tables to Excel 2003 using PHPExcel and CodeIgniter.

First of all, you need PHPExcel which should be installed as a CodeIgniter library. In order to do this, you should follow the steps posted here.

Once you have PHPExcel installed and configured, make a controller exactly like this one:

class Table_export extends Controller {

	function __construct()
	{
		parent::Controller();

		// Here you should add some sort of user validation
		// to prevent strangers from pulling your table data
	}

	function index($table_name)
	{
		$query = $this->db->get($table_name);

		if(!$query)
			return false;

		// Starting the PHPExcel library
		$this->load->library('PHPExcel');
		$this->load->library('PHPExcel/IOFactory');

		$objPHPExcel = new PHPExcel();
		$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");

		$objPHPExcel->setActiveSheetIndex(0);

		// Field names in the first row
		$fields = $query->list_fields();
		$col = 0;
		foreach ($fields as $field)
		{
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
			$col++;
		}

		// Fetching the table data
		$row = 2;
		foreach($query->result() as $data)
		{
			$col = 0;
			foreach ($fields as $field)
			{
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
				$col++;
			}

			$row++;
		}

		$objPHPExcel->setActiveSheetIndex(0);

		$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');

		// Sending headers to force the user to download the file
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Products_'.date('dMy').'.xls"');
		header('Cache-Control: max-age=0');

		$objWriter->save('php://output');
	}

}

Whenever you need to export data from a MySQL table, you just need to call this controller and pass the table name as a parameter, sort of like http://www.yoursite.com/table_export/products. Obviously, you should always add some sort of security measure in order to prevent strangers from pulling all your table information. Just add some session protection to your constructor and you’re set.

  • Denis Francis

    Muito bom!

    Só tenho uma dúvida: tentei transformar esse controller em uma lib, passando a query como parâmetro, porém não funcionou.
    Preciso passar a query como parâmetro devido a necessidade de usar vários joins aintes de exportar.

    Att.,

    • http://www.dannyherran.com Danny Herran

      Hi, please post some code and I’d be glad to help. It should totally work with a custom query. In fact, most of the time I use custom queries to export data. You just need to modify the controller.

  • Brad Morse

    This does about the same, but your code is geared more toward to ease of use, this gives you more power, but you need to supply the query: http://codeigniter.com/wiki/Excel_Plugin/

    • http://www.dannyherran.com Danny Herran

      Thanks for sharing the link! Yes, indeed, they both do almost the same, the only difference is the use of PHPExcel in my code.

  • Carlos

    Hi friend how can’t use this? I’ don’t understand? I need export some data from sql table to excel, I’m using PHPExcel (codeplex) I need help. this is my code:

    require_once(“Classes/PHPExcel.php”);
    require_once(“Classes/PHPExcel/Reader/Excel2007.php”);

    $objPHPexcel = PHPExcel_IOFactory::load(‘reporteencts.xlsx’);

    $conexion = mysql_connect(“localhost”, “root”, “”);
    mysql_select_db(“test”,$conec);
    $sql=”SELECT * FROM testtable”;

    $rst=mysql_query($sql,$conec);

    if(!$rst)
    die(” MySQL Error”);

    while($fila = mysql_fetch_array($rst))
    {
    $objPHPExcel->getActiveSheet()->setCellValue(“A”, $fila[saleman]);
    $objPHPExcel->getActiveSheet()->setCellValue(“B”, $fila[name_c]);
    $objPHPExcel->getActiveSheet()->setCellValue(“C”, $fila[company]);
    }

    //$objWriter->save($archivo_loc.$archivo_nom);
    header(‘Content-Type: application/vnd.ms-excel’);
    header(‘Content-Disposition: attachment;filename=”Report.xls”‘);
    header(‘Cache-Control: max-age=0′);

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, ‘Excel5′);
    $objWriter->save(‘php://output’);

  • http://www.spicyweb.com.br Caio Guimarães

    Having trouble with the generated file.

    When I try to open it, Excel says “the file is corrupt and cannot be opened”. If I try to extract the data (as offered by Excel), it extracts the data and shows it, apparently, with no errors.

    Before, I was using ‘$this->dbutil->csv_from_result(…)’ to generate a CSV file, but it also gave me the same error.

    I’m using: Windows 7 Ultimate with MS Office 2007

  • Pingback: Exporting your MySQL table data with PHPExcel + CodeIgniter « Niroze's Weblog

  • http://land-news.ru/ hea8y

    I have obtained:
    Cannot redeclare class PHPExcel_IOFactory
    or
    Cannot redeclare class IOFactory … IOFactory.php on line 52

    please help me?

  • http://land-news.ru/ hea8y

    Thank you.
    how to upload not just one table with data as a single sheet,
    but all the tables from the database as the data sheets?

    help me please? :
    I need all the tables from the database as sheets with data

  • http://land-news.ru/ hea8y

    how to import a file from Excel to Mysql ?

  • http://cpesonline.ej.am jackass

    hi,

    I came up with an error msg trying to work out with your code using PHPExcel

    Fatal error: Cannot redeclare class IOFactory in C:\xampp\htdocs\codeigniter\application\libraries\PHPExcel\IOFactory.php on line bla.. bla.. bla..

    what’s the main reason of this?
    Any answer would be highly appreciated.. Thanks!

  • Firman

    This really helped me.
    Thank you…^_^

  • Razibul Hasan

    Dear Friend. This code is very helpful for me. Thanks You……………………

  • http://stackoverflow Sachin

    ~~~~~~~~~~~~~Controller~~~~~~~~~~~~~
    function get_report()
    {
    $this->load->dbutil();
    // get the object
    $report = $this->usermodel->index();
    //pass it to db utility function
    $new_report = $this->dbutil->csv_from_result($report);
    //Now use it to write file. write_file helper function will do it
    write_file(‘useruploads/break_report.csv’,$new_report);
    //Done
    $data = file_get_contents(“useruploads/break_report.csv”); // Read the file’s contents
    $name = ‘break_report.csv’;
    force_download($name, $data);
    }

    ~~~~~~~~~~~~~~~Model~~~~~~~~~~~~~~~~~~~~
    function index()
    {
    return $query = $this->db->get(‘wg_user_break’);
    }

    ~~~~~~~~~~~~~~~~View~~~~~~~~~~~~~~~~~~~~~
    <a href="”>Excel Report

    That’s it no big deal…. your excel file is ready to download…….

  • Swapnil

    Thanks for the code Danny!!.. I had question if we could save the excel file on sever when this function is called rather than downloading on the user browser?