7-4 /excel_one.php

<?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();

$objPHPExcel->getDefaultStyle()->getFont()->setName('微軟正黑體')->setSize(14);


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

$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") . "` ";
$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;