MySQL export Excel file method in PHP

We use the simplest method directly with Php+mysql to achieve, the method is as follows.

The code is as follows	Copy Code
<?php Include (' db/db.php '); Include a library class $db = new db (); $result = mysql_query (' select * market_sig into outfile ' D:product3.xls; '); Var_dump ($result); ?>

Above is our native PHP combined with the MySQL outfile file export method, this method has a problem is not to implement the download function, only in the build on the server.

The following methods are more comprehensive

Download phpexcel:http://phpexcel.codeplex.com

Let's look at the code first.

The code is as follows

Copy Code

<?php Class Table_export extends Ci_controller { function __construct () { Parent::__construct (); Here you should add some sort of user validation To prevent strangers from pulling your table data } Function index ($table _name) { $this->load->database (); $query = $this->db->query ("select * from ' $table _name ' WHERE del= 1"); $query = mb_convert_encoding ("gb2312", "UTF-8", $query); 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) ->setcellvalue (' A1 ', iconv (' GBK ', ' utf-8 ', ' Chinese Hello ')) ->setcellvalue (' B2 ', ' world! ') ->setcellvalue (' C1 ', ' Hello '); Field names in the $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 '); Send banner to force users to download files 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 '); } } ?>

Look at the configuration method.

1 Extract the contents of the Classes folder in the compressed package into the Applicationlibraries directory, the directory structure is as follows:

--applicationlibrariesphpexcel.php

--Applicationlibrariesphpexcel (folder)

2) Modify applicationlibrariesphpexceliofactory.php file

--Change its class name from Phpexcel_iofactory to Iofactory and follow the CI class naming rules.

--change its constructor to public

There are many other ways to use this method like this one, because Phpexcel is a practical plug-in that is easy to operate on Excel tables.