[1042] XOOPS模組開發進階
<?php
/**
* Phone Book module
*
* You may not change or alter any portion of this comment or credits
* of supporting developers from this source code or any supporting source code
* which is considered copyrighted (c) material of the original comment or credit authors.
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
*
* @copyright The XOOPS Project http://sourceforge.net/projects/xoops/
* @license http://www.fsf.org/copyleft/gpl.html GNU public license
* @package Phone Book
* @since 2.5
* @author tad
* @version $Id $
**/
/*-----------引入檔案區--------------*/
include "header.php";
/*-----------功能函數區--------------*/
require_once TADTOOLS_PATH . '/PHPExcel.php'; //引入 PHPExcel 物件庫
require_once TADTOOLS_PATH . '/PHPExcel/IOFactory.php'; //引入 PHPExcel_IOFactory 物件庫
$objPHPExcel = new PHPExcel(); //實體化Excel
//----------內容-----------//
$myts = MyTextSanitizer::getInstance();
$sql = "select cate_sn, cate_title from `" . $xoopsDB->prefix("phone_book_cate") . "` order by `cate_sort`";
$result = $xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'], 3, mysql_error());
$objPHPExcel->getDefaultStyle()->getFont()->setName('微軟正黑體')->setSize(14);
$i=0;
while (list($cate_sn, $cate_title) = $xoopsDB->fetchRow($result)) {
$objPHPExcel->setActiveSheetIndex($i); //設定預設顯示的工作表
$objActSheet = $objPHPExcel->getActiveSheet(); //指定預設工作表為 $objActSheet
$objActSheet->setTitle($cate_title); //設定標題
$objPHPExcel->createSheet(); //建立新的工作表，上面那三行再來一次，編號要改
$i++;
$objActSheet->getStyle('A1:K1')->getFont()->getColor()->setARGB('00000000');
$objActSheet->getStyle('A1:K1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('00D7E2F2');
$objActSheet->setCellValue("A1", '編號')
->setCellValue("B1", '分類編號')
->setCellValue("C1", '姓名')
->setCellValue("D1", '生日')
->setCellValue("E1", '電話')
->setCellValue("F1", '郵遞區號')
->setCellValue("G1", '縣市')
->setCellValue("H1", '鄉鎮市區')
->setCellValue("I1", '地址')
->setCellValue("J1", '電子信箱')
->setCellValue("K1", '備註');
$objActSheet->getColumnDimension('A')->setWidth(5);
$objActSheet->getColumnDimension('B')->setWidth(5);
$objActSheet->getColumnDimension('C')->setWidth(10);
$objActSheet->getColumnDimension('D')->setWidth(12);
$objActSheet->getColumnDimension('E')->setWidth(14);
$objActSheet->getColumnDimension('F')->setWidth(10);
$objActSheet->getColumnDimension('G')->setWidth(10);
$objActSheet->getColumnDimension('H')->setWidth(10);
$objActSheet->getColumnDimension('I')->setWidth(30);
$objActSheet->getColumnDimension('J')->setWidth(30);
$objActSheet->getColumnDimension('K')->setWidth(20);
$objActSheet->getStyle('I')->getAlignment()->setWrapText(true);
$objActSheet->getStyle('K')->getAlignment()->setWrapText(true);
$sql = "select * from `" . $xoopsDB->prefix("phone_book") . "` where cate_sn='{$cate_sn}'";
$result2 = $xoopsDB->query($sql) or web_error($sql);
$j = 2;
while ($all = $xoopsDB->fetchArray($result2)) {
//以下會產生這些變數： $sn, $cate_sn, $name, $birthday, $phone, $email, $zip, $county, $city, $addr, $note
foreach ($all as $k => $v) {
$$k = $v;
}
//過濾讀出的變數值
$name = $myts->htmlSpecialChars($name);
$birthday = $myts->htmlSpecialChars($birthday);
$phone = $myts->htmlSpecialChars($phone);
$email = $myts->htmlSpecialChars($email);
$zip = $myts->htmlSpecialChars($zip);
$county = $myts->htmlSpecialChars($county);
$city = $myts->htmlSpecialChars($city);
$addr = $myts->htmlSpecialChars($addr);
$note = $myts->displayTarea($note, 0, 1, 0, 1, 1);
$objActSheet->setCellValue("A{$j}", $sn)
->setCellValue("B{$j}", $cate_sn)
->setCellValue("C{$j}", $name)
->setCellValue("D{$j}", $birthday)
->setCellValueExplicit("E{$j}", $phone, PHPExcel_Cell_DataType:: TYPE_STRING)
->setCellValueExplicit("F{$j}", $zip, PHPExcel_Cell_DataType:: TYPE_STRING)
->setCellValue("G{$j}", $county)
->setCellValue("H{$j}", $city)
->setCellValue("I{$j}", $addr)
->setCellValue("J{$j}", $email)
->setCellValue("K{$j}", $note);
$j++;
}
//框線
$objActSheet->getStyle("A1:K{$j}")->getBorders()->getAllborders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setRGB('000000');
$objActSheet->mergeCells("A{$j}:B{$j}")->setCellValue("A{$j}", '資料數共計');
$n=$j-1;
$objActSheet->mergeCells("C{$j}:K{$j}")->setCellValue("C{$j}", "=COUNTA(A2:A{$n})");
$objActSheet->getStyle("C{$j}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objActSheet->getProtection()->setSheet(true);
$objActSheet->protectCells("C{$j}", '12345');
}
$objPHPExcel->setActiveSheetIndex(0); //設定預設顯示的工作表
header('Content-Type: application/vnd.ms-excel');
$filename=iconv('UTF-8', 'Big5', '通訊錄'); //相容IE
header("Content-Disposition: attachment;filename={$filename}.xls");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->setPreCalculateFormulas(true);
$objWriter->save('php://output');
exit;