本文實例講述了thinkPHP+phpexcel實現excel報表輸出功能。分享給大家供大家參考，具體如下：
準備工作：
1.下載phpexcel1.7.6類包；
2.解壓至TP框架的ThinkPHP\Vendor目錄下，改類包文件夾名為PHPExcel176，目錄結構如下圖；
編寫代碼(以一個訂單匯總數據為例)：
1. 創建數據庫及表;
2. 創建tp項目，配置項目的數據庫連接，這些基本的就不說了；
3. 在項目的Lib\Action下創建一個新的類文件ExportStatisticsAction.class.php，然后在 index方法中實現excel導出；
4. 導出方法的步驟：
①查詢數據
②導入phpexcel類庫
③創建excel對象并設置excel對象的屬性
④設置excel的行列樣式(字體、高寬、顏色、邊框、合并等)
⑤繪制報表表頭
⑥將查詢數據寫入excel
⑦設置excel的sheet的名稱
⑧設置excel報表打開后初始的sheet
⑨設置輸出的excel的頭參數及文件名
⑩調用創建excel的方法生成excel文件
代碼如下：
<?php
/**
* Created by lonm.shi.
* Date: 2012-02-09
* Time: 下午4:54
* To change this template use File | Settings | File Templates.
*/
class ExportStatisticsAction extends Action {
public function index(){
$model= D("OrdersView");
$OrdersData= $model->select(); //查詢數據得到$OrdersData二維數組
vendor("PHPExcel176.PHPExcel");
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("ctos")
->setLastModifiedBy("ctos")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
//set width
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);
//設置行高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
//set font size bold
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//設置水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//合并cell
$objPHPExcel->getActiveSheet()->mergeCells('A1:J1');
// set table header content
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '訂單數據匯總 時間:'.date('Y-m-d H:i:s'))
->setCellValue('A2', '訂單ID')
->setCellValue('B2', '下單人')
->setCellValue('C2', '客戶名稱')
->setCellValue('D2', '下單時間')
->setCellValue('E2', '需求機型')
->setCellValue('F2', '需求數量')
->setCellValue('G2', '需求交期')
->setCellValue('H2', '確認BOM料號')
->setCellValue('I2', 'PMC確認交期')
->setCellValue('J2', 'PMC交貨備注');
// Miscellaneous glyphs, UTF-8
for($i=0;$i<count($OrdersData)-1;$i++){
$objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+3), $OrdersData[$i]['id']);
$objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $OrdersData[$i]['realname']);
$objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $OrdersData[$i]['customer_name']);
$objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+3), toDate($OrdersData[$i]['create_time'])); //這里調用了common.php的時間戳轉換函數
$objPHPExcel->getActiveSheet(0)->setCellValue('E'.($i+3), $OrdersData[$i]['require_product']);
$objPHPExcel->getActiveSheet(0)->setCellValue('F'.($i+3), $OrdersData[$i]['require_count']);
$objPHPExcel->getActiveSheet(0)->setCellValue('G'.($i+3), $OrdersData[$i]['require_time']);
$objPHPExcel->getActiveSheet(0)->setCellValue('H'.($i+3), $OrdersData[$i]['product_bom_encoding']);
$objPHPExcel->getActiveSheet(0)->setCellValue('I'.($i+3), $OrdersData[$i]['delivery_time']);
$objPHPExcel->getActiveSheet(0)->setCellValue('J'.($i+3), $OrdersData[$i]['delivery_memo']);
$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);
}
// sheet命名
$objPHPExcel->getActiveSheet()->setTitle('訂單匯總表');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// excel頭參數
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="訂單匯總表('.date('Ymd-His').').xls"'); //日期為文件名后綴
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel5為xls格式，excel2007為xlsx格式
$objWriter->save('php://output');
}
}
【網站聲明】本站除付費源碼經過測試外，其他素材未做測試，不保證完整性，網站上部分源碼僅限學習交流，請勿用于商業用途。如損害你的權益請聯系客服QQ：2655101040 給予處理，謝謝支持。