今天寫(xiě)項(xiàng)目需要將查詢結(jié)果用PHP導(dǎo)出為excel文件,查了網(wǎng)上主流的一些做法,發(fā)現(xiàn)phpexcel功能很強(qiáng)大,試著模仿著examples寫(xiě)了一個(gè)簡(jiǎn)單的導(dǎo)出excel的php代碼
下載最新的phpexcel插件可以上官網(wǎng)查找,這里就不多做說(shuō)明
**
excel寫(xiě)入數(shù)據(jù)及輸出過(guò)程
**
1.引入phpexcel插件 ,引入前進(jìn)行錯(cuò)誤檢查(也可忽略)
error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE);//date_default_timezone_set('Europe/London');define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '
');if (PHP_SAPI == 'cli') die('This excel should only be run from a Web Browser');require_once dirname(__FILE__) . '/PHPExcel.php';1234567812345678
2.創(chuàng)建一個(gè)excel,
$objPHPExcel = new PHPExcel();11
3.因?yàn)轫?xiàng)目的excel有特定的格式,所以需要引入一個(gè)模板,可提前將模板寫(xiě)好放在摸個(gè)目錄下,接著引用該模版,引用的方法如下
$objReader = PHPExcel_IOFactory::createReader('Excel2007');//如果是引入excel2003,將Excel2007改為Excel5即可,如果不改,則輸出的excel會(huì)錯(cuò)誤無(wú)法打開(kāi)$objPHPExcel = $objReader->load("模板的路徑");1212
4.設(shè)置文件屬性(也可忽略采用默認(rèn)的格式,暫時(shí)沒(méi)發(fā)現(xiàn)什么問(wèn)題)
$objPHPExcel->getProperties()->setCreator("zly")->setLastModifiedBy("zly") ->setTitle("zly")->setSubject("PHPExcel zly Document")->setDescription("zlydocument for PHPExcel, generated using PHP classes.")->setKeywords("office PHPExcel php")->setCategory("zlyresult file");1234567812345678
5.寫(xiě)數(shù)據(jù)
//setActiveSheetIndex(num) num為要寫(xiě)入的工作薄的位置,如1表示sheet1(暫時(shí)不確定)//setCellValue(param1,param2) param1為你要寫(xiě)入數(shù)據(jù)的位置,param2為要在該位置寫(xiě)的數(shù)據(jù)
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', "你好")
->setCellValue('D1',"你好")->setCellValue('F1', "你好") ->setCellValue('G1', "你好");12345671234567
6.改寫(xiě)工作薄名稱(如果沒(méi)用可不寫(xiě))
$objPHPExcel->getActiveSheet(0)->setTitle('zly');11
7.設(shè)置打開(kāi)excel時(shí)優(yōu)先打開(kāi)的工作?。J(rèn)1)
$objPHPExcel->getActiveSheet(0)->setTitle('zly');11
8.設(shè)置頁(yè)面頭信息
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // 如果是excel2003,則寫(xiě)header('Content-Type: application/vnd.ms-excel');否則輸出的excel無(wú)法打開(kāi) header('Content-Disposition: attachment;filename="sj.xlsx"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header ('Pragma: public'); // HTTP/1.012345678910111234567891011
9.輸出excel的代碼
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); //如果為excel2003,則改為Excel5$objWriter->save('php://output');1212
**
注意點(diǎn):
**
1.一定要分清引入的模板是2003的還是2007
$objReader = PHPExcel_IOFactory::createReader('param');header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // 如果是excel2003,則寫(xiě)header('Content-Type: application/vnd.ms-excel');否則輸出的excel無(wú)```$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');//2003用Excel5,2007用Excel2007123123
2.如果有從數(shù)據(jù)庫(kù)讀數(shù)據(jù)再寫(xiě)入時(shí),一定不要有錯(cuò)誤信息輸出,否則文件無(wú)法打開(kāi)
3.頁(yè)面也不要有其他任何輸出,否則文件無(wú)法打開(kāi)
OK,到此一個(gè)簡(jiǎn)單的excel導(dǎo)出的代碼就完成啦,如果還要具體的設(shè)置列寬或者顏色背景什么的可以參照api文檔