excel.php

8-6 excel.php

<?php
include_once "header.php";
require_once TADTOOLS_PATH . '/PHPExcel.php'; //引入 PHPExcel 物件庫
require_once TADTOOLS_PATH . '/PHPExcel/IOFactory.php'; //引入 PHPExcel_IOFactory 物件庫
$objPHPExcel = new PHPExcel(); //實體化Excel

//----------內容-----------//
$objPHPExcel->setActiveSheetIndex(0);  //設定預設顯示的工作表
$objActSheet = $objPHPExcel->getActiveSheet(); //指定預設工作表為 $objActSheet
$objActSheet->setTitle("文章列表1");  //設定標題
$objPHPExcel->createSheet(); //建立新的工作表，上面那三行再來一次，編號要改
$objPHPExcel->getDefaultStyle()->getFont()->setName('微軟正黑體')->setSize(14);

$i=1;
$objActSheet->mergeCells("A{$i}:J{$i}")->setCellValue("A1", '巷談集所有文章備份');


$objActSheet->getStyle('A:J')->getAlignment()
    ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER) //垂直置中
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //水平置中
$objActSheet->getStyle('E')->getAlignment()
    ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); //水平靠左
$objActSheet->getStyle('F')->getAlignment()
    ->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP) //垂直靠上
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT) //水平靠左
    ->setWrapText(true); //自動換行


$i++;
$objActSheet->setCellValue("A{$i}", '編號');
$objActSheet->setCellValue("B{$i}", '精華');
$objActSheet->setCellValue("C{$i}", '主題編號');
$objActSheet->setCellValue("D{$i}", '排序');
$objActSheet->setCellValue("E{$i}", '標題');
$objActSheet->setCellValue("F{$i}", '內容');
$objActSheet->setCellValue("G{$i}", '作者');
$objActSheet->setCellValue("H{$i}", '建立時間');
$objActSheet->setCellValue("I{$i}", '修改時間');
$objActSheet->setCellValue("J{$i}", 'uid');

$objActSheet->getStyle('A1:J1')->getFont()->setBold(true)->getColor()->setARGB('00FFFFFF');
$objActSheet->getStyle('A1:J1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('00474747');



$objActSheet->getColumnDimension('A')->setWidth(5);
$objActSheet->getColumnDimension('B')->setWidth(5);
$objActSheet->getColumnDimension('C')->setWidth(5);
$objActSheet->getColumnDimension('D')->setWidth(5);
$objActSheet->getColumnDimension('E')->setWidth(40);
$objActSheet->getColumnDimension('F')->setWidth(80);
$objActSheet->getColumnDimension('G')->setWidth(15);
$objActSheet->getColumnDimension('H')->setWidth(20);
$objActSheet->getColumnDimension('I')->setWidth(20);
$objActSheet->getColumnDimension('J')->setWidth(8);

$i++;
$tbl = $xoopsDB->prefix('snews');
$sql = "SELECT * FROM `$tbl` ORDER BY `update_time` DESC";
$result = $xoopsDB->query($sql) or web_error($sql);

while ($snews = $xoopsDB->fetchRow($result)) {  
    foreach($snews as $key=> $val){
        $objActSheet->setCellValueByColumnAndRow($key, $i, $val);
    }  
    
    $objActSheet->getRowDimension($i)->setRowHeight(60);
    $i++;
}
$n=$i-1;
$objActSheet->setCellValue("A{$i}", '共');
$objActSheet->setCellValue("B{$i}", "=count(A3:A{$n})");
$objActSheet->setCellValue("C{$i}", '篇文章');

$objActSheet->getStyle("A1:J{$n}")->getBorders()->getAllborders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setRGB('000000');

$objActSheet->getProtection()->setSheet(true);
$objActSheet->getProtection()->setSort(true);
$objActSheet->getProtection()->setInsertRows(true);
$objActSheet->getProtection()->setFormatCells(true);
$objActSheet->getProtection()->setPassword('1234');


$title=iconv('UTF-8','Big5','文章備份');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='.$title.'.xls');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
exit;
excel
8-5 save_sort.php