[1012]PHP進階開發及TadTools工具應用
<?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;
?>