PHPExcel 是用來操作Office Excel 文檔的一個PHP類庫,它基于微軟的OpenXML標準和PHP語言。可以使用它來讀取、寫入不同格式的電子表格。而Codeigniter是一個功能強大的PHP框架。二者結(jié)合就能起到非常棒的效果啦!
1. 準備工作
下載PHPExcel:http://phpexcel.codeplex.com
這是個強大的Excel庫,這里只演示導(dǎo)出Excel文件的功能,其中的大部分功能可能都用不著。
2. 安裝PHPExcel到Codeigniter
1) 解壓壓縮包里的Classes文件夾中的內(nèi)容到application\libraries\目錄下,目錄結(jié)構(gòu)如下:
-- application\libraries\PHPExcel.php
-- application\libraries\PHPExcel (文件夾)
2)修改application\libraries\PHPExcel\IOFactory.php 文件
-- 將其類名從PHPExcel_IOFactory改為IOFactory,遵從CI類命名規(guī)則。
-- 將其構(gòu)造函數(shù)改為public
3. 安裝完畢,寫一個導(dǎo)出excel的控制器(Controller)
代碼如下:
<?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)
{
$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');
}
}
4. 測試
加入數(shù)據(jù)庫有表名為products,此時可以訪問http://www.yoursite.com/table_export/index/products 導(dǎo)出Excel文件了。
參考:http://www.dannyherran.com/2011/03/exporting-your-mysql-table-data-with-phpexcel-codeigniter/