Tad 教材網 - 線上書籍

:::

\[1012\]PHP進階開發及TadTools工具應用
============================

```
<pre class="brush: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);

//設定字型
$objActSheet->getStyle('A1:I1')->getFont()
            ->setName('SimHei')
            ->setSize('14');

//設定底色
$objActSheet->getStyle('A1:I1')->getFill()
            ->setFillType(PHPExcel_Style_Fill:: FILL_SOLID)
            ->getStartColor()
            ->setARGB('FFC9E3F3');

//設定對齊
$objActSheet->getStyle('A1:I1')->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setWrapText(true);
            
//設定框線
$objBorder=$objActSheet->getDefaultStyle()->getBorders();
$objBorder->getBottom()
          ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)
          ->getColor()->setRGB('000000');
$objBorder->getRight()
          ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)
          ->getColor()->setRGB('000000');

$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}", $gsn)
              ->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;
?>
```

:::