7-2 /excel.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();

$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;