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)