2011
Sep
09




宣告Excel 物件

Example
  1. require_once('../PHPExcel/PHPExcel.php');require_once('../PHPExcel/PHPExcel/IOFactory.php');$PHPExcel=new PHPExcel();
  2.  
  3. //作者
  4. $PHPExcel->getProperties()->setCreator("Jackie");
  5. //標題
  6. $PHPExcel->getProperties()->setTitle("Jackie的Excel試玩");
  7. //Excel 描述
  8. $PHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

簡單的編輯

Example
  1. //指定目前要編輯的工作表 ,預設0是指第一個工作表
  2. $PHPExcel->setActiveSheetIndex(0);
  3. //在欄位A1 寫入文字
  4. $PHPExcel->getActiveSheet()->setCellValue('A1', '測試');
  5. //建立下一個工作表$PHPExcel->createSheet();
  6. //指定開始改第二個工作表
  7. $PHPExcel->setActiveSheetIndex(1);
  8. //在欄位 (1,2) 寫入文字
  9. $PHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,2 '測試');

輸出Excel

Example
  1. $PHPExcelWriter = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');
  2. $PHPExcelWriter->save('filename.xls');
  3. header("Content-type: application/force-download");
  4. header("Content-Disposition: attachment; filename=\"filename.xls\"");
  5. header("Content-Length: ".filesize('filename.xls'));
  6. @readfile('filename.xls');

天數轉日期

Example
  1. //PHPExcel有自動將天數轉成日期的功能
  2. require_once '../PHPExcel/PHPExcel/Cell/AdvancedValueBinder.php';
  3. PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
  4. $PHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
  5. $PHPExcel->getActiveSheet()->setCellValue('D1', 40813);
  6. //1900/1/1 經過40813天後 = 2011/09/27

設定色彩,樣式

Example
  1. //設定 style
  2. //ARGB : alpha透明 +RGB
  3. $PHPExcel->getActiveSheet()->getStyle('B3')->getFont()->getColor()->setARGB('50ff0000');
  4. $PHPExcel->getActiveSheet()->setCellValue('B3', 'red');
  5. $PHPExcel->getActiveSheet()->getStyle('B4')->getFont()->getColor()->setRGB('0000FF');
  6. $PHPExcel->getActiveSheet()->setCellValue('B4', 'blue');
  7. //設定背景色
  8. $PHPExcel->getActiveSheet()->getStyle('B2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  9. $PHPExcel->getActiveSheet()->getStyle('B2')->getFill()->getStartColor()->setARGB('FFFF0000');
  10.  
  11. //用Array設定樣式
  12. /*
  13.  
  14. font 設定字型與顏色
  15. fill 填滿色彩
  16.  
  17. */
  18. $styleArray = array( 'font' => array( 'bold' => true, 'color'=> array( 'argb' => "ff00f0f0" ) ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 90, 'startcolor' => array( 'argb' => 'FFc5c5c5', ) ));
  19. $PHPExcel->getActiveSheet()->getStyle('D')->applyFromArray($styleArray);
  20. $PHPExcel->getActiveSheet()->setCellValue('D3', 'test');
  21.  
  22. //設定預設樣式
  23. $PHPExcel->getDefaultStyle()->getFont()->setName('Arial');
  24. $PHPExcel->getDefaultStyle()->getFont()->setSize(12);
  25. $PHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);//設定欄位寬度
  26. $PHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(130);
  27. //合併儲存格
  28. $PHPExcel->getActiveSheet()->mergeCells('A7:E7');
  29. $PHPExcel->getActiveSheet()->setCellValue('A7','test');
  30.  
  31. //貼上圖片
  32. $objDrawing = new PHPExcel_Worksheet_Drawing();
  33. $objDrawing->setName('Logo');$objDrawing->setDescription('Logo');
  34. $objDrawing->setPath('test.jpg');$objDrawing->setHeight(86);
  35. $objDrawing->setWidth(86);$objDrawing->setCoordinates('D5');
  36. $objDrawing->setWorksheet($PHPExcel->getActiveSheet());
  • 禁止修改Excel: $PHPExcel->getActiveSheet()->getProtection()->setSheet(true);

Excel 轉 Array

Example
  1. require('PHPExcel/PHPExcel.php');
  2. require_once 'PHPExcel/PHPExcel/IOFactory.php';
  3. $excel= new PHPExcel();
  4. $PHPReader = PHPExcel_IOFactory::createReader('Excel5'); // Excel5 = 2003 版本
  5. $exceler = $PHPReader->load($file); //讀取excel檔案
  6. $arr = $exceler->getSheet(0)->toArray();
  7.  
  8. print_r($arr); //輸出來看一看吧

回應 (Leave a comment)