14-1 上課範例：admin/excel.php

<?php
include_once "header_admin.php";
require_once '../class/PHPExcel.php';    //引入 PHPExcel 物件庫
require_once '../class/PHPExcel/IOFactory.php';    //引入 PHPExcel_IOFactory 物件庫
$objPHPExcel = new PHPExcel();  //實體化Excel
//----------內容-----------//

$objPHPExcel->setActiveSheetIndex(0);  //設定預設顯示的工作表
$objActSheet = $objPHPExcel->getActiveSheet(); //指定預設工作表為 $objActSheet
$objActSheet->setTitle("通訊錄");  //設定標題
$objPHPExcel->createSheet(); //建立新的工作表，上面那三行再來一次，編號要改

$objActSheet->getColumnDimension('A')->setWidth(8);
$objActSheet->getColumnDimension('B')->setWidth(40);
$objActSheet->getColumnDimension('C')->setWidth(15);
$objActSheet->getColumnDimension('D')->setWidth(25);
$objActSheet->getColumnDimension('E')->setWidth(10);
$objActSheet->getColumnDimension('F')->setWidth(8);
$objActSheet->getColumnDimension('G')->setWidth(8);
$objActSheet->getColumnDimension('H')->setWidth(8);
$objActSheet->getColumnDimension('I')->setWidth(18);
/*
$objPHPExcel->getDefaultStyle()->getFont()->setName('標楷體');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(16);
*/

$objActSheet-> getStyle('A1:I1')-> getFont()-> setName('SimHei')-> setSize('14');
$objActSheet-> getStyle('A1:I1')-> getFill()-> setFillType(PHPExcel_Style_Fill:: FILL_SOLID)-> getStartColor()-> setARGB('FFC9E3F3');


$objActSheet->setCellValue("A1", '群組')
            ->setCellValue("B1", '姓名')
            ->setCellValue("C1", '電話')
            ->setCellValue("D1", '信箱')
            ->setCellValue("E1", '生日')
            ->setCellValue("F1", '郵遞區號')
            ->setCellValue("G1", '縣市')
            ->setCellValue("H1", '鄉鎮市區')
            ->setCellValue("I1", '地址');


$cate=get_contact_cate_all();
	
$sql = "select * from `".$xoopsDB->prefix("contact")."` ";
$result = $xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'],3, mysql_error());

$i=2;
while($all=$xoopsDB->fetchArray($result)){
	//以下會產生這些變數： $tel , $email , $name , $gsn , $sn , $birthday , $zip , $county , $city , $addr
	foreach($all as $k=>$v){
		$$k=$v;
	}

  $objActSheet->setCellValue("A{$i}", $cate[$gsn]['title'])
              ->setCellValue("B{$i}", $name)
              //->setCellValue("C{$i}", $tel)
              ->setCellValueExplicit("C{$i}", $tel , PHPExcel_Cell_DataType:: TYPE_STRING)
              ->setCellValue("D{$i}", $email)
              ->setCellValue("E{$i}", $birthday)
              ->setCellValue("F{$i}", $zip)
              ->setCellValue("G{$i}", $county)
              ->setCellValue("H{$i}", $city)
              ->setCellValue("I{$i}", $addr);
  $i++;
}


$objActSheet->mergeCells("A{$i}:H{$i}")->setCellValue("A{$i}", '通訊錄資料數共計');
$n=$i-1;
$objActSheet->setCellValue("I{$i}", "=COUNTA(B2:B{$n})");

$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
$objPHPExcel->getActiveSheet()->protectCells("I{$i}", '12345');

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='.iconv('UTF-8','Big5','通訊錄').'.xls');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
exit;
?>