1. 准备工作
下载PHPExcel:http://phpexcel.codeplex.com
这是个强大的Excel库,这里只演示导出Excel文件的功能,其中的大部分功能可能都用不着。
2. 安装PHPExcel到Codeigniter
1) 解压压缩包里的Classes文件夹中的内容到application\libraries\目录下,目录结构如下:
-- application\libraries\PHPExcel.php
-- application\libraries\PHPExcel (文件夹)
2)修改application\libraries\PHPExcel\IOFactory.php 文件
-- 将其类名从PHPExcel_IOFactory改为IOFactory,遵从CI类命名规则。
-- 将其构造函数改为public
3. 安装完毕,写一个导出excel的控制器(Controller)
代码如下:
1 <?php 2 3 class Table_export extends CI_Controller { 4 5 function __construct() 6 { 7 parent::__construct(); 8 9 // Here you should add some sort of user validation 10 // to prevent strangers from pulling your table data 11 } 12 13 function index($table_name) 14 { 15 $query = $this->db->get($table_name); 16 17 if(!$query) 18 return false; 19 20 // Starting the PHPExcel library 21 $this->load->library('PHPExcel'); 22 $this->load->library('PHPExcel/IOFactory'); 23 24 $objPHPExcel = new PHPExcel(); 25 $objPHPExcel->getProperties()->setTitle("export")->setDescription("none"); 26 27 $objPHPExcel->setActiveSheetIndex(0); 28 29 // Field names in the first row 30 $fields = $query->list_fields(); 31 $col = 0; 32 foreach ($fields as $field) 33 { 34 $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field); 35 $col++; 36 } 37 38 // Fetching the table data 39 $row = 2; 40 foreach($query->result() as $data) 41 { 42 $col = 0; 43 foreach ($fields as $field) 44 { 45 $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field); 46 $col++; 47 } 48 49 $row++; 50 } 51 52 $objPHPExcel->setActiveSheetIndex(0); 53 54 $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5'); 55 56 // Sending headers to force the user to download the file 57 header('Content-Type: application/vnd.ms-excel'); 58 header('Content-Disposition: attachment;filename="Products_'.date('dMy').'.xls"'); 59 header('Cache-Control: max-age=0'); 60 61 $objWriter->save('php://output'); 62 } 63 64 }
4. 测试
加入数据库有表名为products,此时可以访问http://www.yoursite.com/table_export/index/products 导出Excel文件了。
参考:http://www.dannyherran.com/2011/03/exporting-your-mysql-table-data-with-phpexcel-codeigniter/