Tad 教材網 - 線上書籍

:::

\[982\]PHP網站開發 進階應用技巧
=====================

<?php  
 //關閉PHP錯誤報告  
 error\_reporting(0);   
   
 //引入 PHPExcel 物件庫  
 require\_once 'excel/PHPExcel.php';   
   
 //引入 PHPExcel\_IOFactory 物件庫  
 require\_once 'excel/PHPExcel/IOFactory.php';   
   
 //實體化Excel  
 $objPHPExcel = new PHPExcel();   
   
 $objPHPExcel->setActiveSheetIndex(0); //設定預設顯示的工作表  
 $objActSheet = $objPHPExcel->getActiveSheet(); //指定預設工作表為 $objActSheet  
 $objActSheet->setTitle("個人通訊錄"); //設定標題  
   
 $objPHPExcel->createSheet();  
 $objPHPExcel->setActiveSheetIndex(1);  
 $objActSheet1 = $objPHPExcel->getActiveSheet();  
 $objActSheet1->setTitle("收支簿");  
   
 $objPHPExcel->getDefaultStyle()->getFont()->setSize(9);  
   
   
 //-------------------------外觀設定----------------------//  
   
 //設定預設工作表中一個儲存格的外觀  
 $objStyleA1 = $objActSheet->getStyle('A1');  
   
 //設定字型  
 $objFontA1 = $objStyleA1->getFont();  
 $objFontA1->setSize(11);  
 $objFontA1->setBold(true);  
 $objFontA1->getColor()->setRGB('FFFFFF');  
   
 //設定對齊  
 $objAlignA1 = $objStyleA1->getAlignment();  
 $objAlignA1->setHorizontal(PHPExcel\_Style\_Alignment::HORIZONTAL\_CENTER);  
 $objAlignA1->setVertical(PHPExcel\_Style\_Alignment::VERTICAL\_CENTER);  
 $objAlignA1->setWrapText(true);  
   
 //設定邊框  
 $objBorderA1 = $objStyleA1->getBorders();  
 $objBorderA1->getTop()->setBorderStyle(PHPExcel\_Style\_Border::BORDER\_THIN);  
 $objBorderA1->getTop()->getColor()->setRGB('FF0000');  
 $objBorderA1->getBottom()->setBorderStyle(PHPExcel\_Style\_Border::BORDER\_THIN);  
 $objBorderA1->getLeft()->setBorderStyle(PHPExcel\_Style\_Border::BORDER\_THIN);  
 $objBorderA1->getRight()->setBorderStyle(PHPExcel\_Style\_Border::BORDER\_THIN);  
   
 //設定儲存格顏色  
 $objFillA1 = $objStyleA1->getFill();  
 $objFillA1->setFillType(PHPExcel\_Style\_Fill::FILL\_SOLID);  
 $objFillA1->getStartColor()->setRGB('99CC00');  
   
   
 //-------------------------工作表 1----------------------//  
 $objActSheet->getColumnDimension('A')->setWidth(8);  
 $objActSheet->getColumnDimension('B')->setAutoSize(true);  
 $objActSheet->getColumnDimension('C')->setWidth(40);  
   
 //複製格式到其他欄位  
 $objActSheet->duplicateStyle($objStyleA1, 'A1:C1');  
   
   
 //$objActSheet->getStyle('A1:D1')->getFill()->setFillType(PHPExcel\_Style\_Fill:: FILL\_SOLID)->getStartColor()->setRGB('FF99CC');  
   
 $objActSheet->setCellValue('A1', '姓名')  
 ->setCellValue('B1', '電話')  
 ->setCellValue('C1', '地址');  
   
 $objActSheet->setCellValue('A2', '吳弘凱')  
 ->setCellValueExplicit('B2', '0987654321',PHPExcel\_Cell\_DataType:: TYPE\_STRING)  
 ->setCellValue('C2', '台南縣永康市中華路619巷88弄65號4F-2');  
   
   
 //-------------------------工作表 2----------------------//  
 $objActSheet1->getColumnDimension('A')->setAutoSize(true);  
 $objActSheet1->getColumnDimension('B')->setAutoSize(true);  
 $objActSheet1->getColumnDimension('C')->setAutoSize(true);  
 $objActSheet1->getColumnDimension('D')->setWidth(20);  
   
 //複製格式到其他欄位  
 $objActSheet1->duplicateStyle($objStyleA1, 'A1:D2');  
 $objActSheet1->getStyle('A1')->getFont()->setSize(16);  
   
 $objActSheet1->mergeCells('A1:D1')->setCellValue('A1', '我的收支簿');  
   
 $objActSheet1->setCellValue('A2', '日期')  
 ->setCellValue('B2', '收入')  
 ->setCellValue('C2', '支出')  
 ->setCellValue('D2', '事由');  
   
 for($i=3;$i<=15;$i++){  
   
 $income=rand(5000,20000); //隨機產生收入  
 $pay=rand(10,10000); //隨機產生支出  
   
 $objActSheet1->setCellValue("A{$i}", '2010-01-'.$i)  
 ->setCellValue("B{$i}", $income)  
 ->setCellValue("C{$i}", $pay)  
 ->setCellValue("D{$i}", '事由'.$i);  
 }  
 $n=$i-1;  
 $objActSheet1->setCellValue("A{$i}", '合計：')  
 ->setCellValue("B{$i}", "=SUM(B3:B{$n})")  
 ->setCellValue("C{$i}", "=SUM(C3:C{$n})")  
 ->setCellValue("D{$i}", "=B{$i}-C{$i}");  
   
 $objPHPExcel->setActiveSheetIndex(1);  
   
 //輸出  
 header('Content-Type: application/vnd.ms-excel');  
 header('Content-Disposition: attachment;filename=檔名'.date("YmdHis"));  
 header('Cache-Control: max-age=0');  
   
 $objWriter = PHPExcel\_IOFactory::createWriter($objPHPExcel, 'Excel5');  
 $objWriter->save('php://output');  
 exit;  
   
 ?>

:::