PHPExcel使用总结

 

使用开源 PHPExcel 有些时间了。 下面是一些简单的开发总结。 开发环境: ubuntu7.10 + apache + php (5.2.3) 。

 

• 开源项目及简单介绍

  Reference URL: http://www.codeplex.com/PHPExcel

  虽然它算不上OLAP Report, 但是也比较实用。( 引用：爱情永远比婚姻圣洁，婚姻永远比爱情实惠)

• 开始引入到项目

  • 下载解压后把他保存在PHPExcel 目录。 下面是具体的目录结构，其中的

  download ，是我们要在下面开发 Excel report 的 目录。

/var/www/report$ find . -type d | grep 'PHPExcel\|download';
./PHPExcel
./PHPExcel/Classes
./PHPExcel/Classes/PHPExcel
./PHPExcel/Classes/PHPExcel/Writer
./PHPExcel/Classes/PHPExcel/Writer/Excel5
./PHPExcel/Classes/PHPExcel/Writer/Excel2007
./PHPExcel/Classes/PHPExcel/Calculation
./PHPExcel/Classes/PHPExcel/Worksheet
./PHPExcel/Classes/PHPExcel/Worksheet/Drawing
./PHPExcel/Classes/PHPExcel/Reader
./PHPExcel/Classes/PHPExcel/Reader/Excel5
./PHPExcel/Classes/PHPExcel/Style
./PHPExcel/Classes/PHPExcel/RichText
./PHPExcel/Classes/PHPExcel/Shared
./PHPExcel/Classes/PHPExcel/Shared/JAMA
./PHPExcel/Classes/PHPExcel/Shared/JAMA/utils
./PHPExcel/Classes/PHPExcel/Shared/JAMA/docs
./PHPExcel/Classes/PHPExcel/Shared/JAMA/docs/includes
./PHPExcel/Classes/PHPExcel/Shared/JAMA/examples
./PHPExcel/Classes/PHPExcel/Shared/JAMA/tests
./PHPExcel/Classes/PHPExcel/Shared/OLE
./PHPExcel/Classes/PHPExcel/Shared/PDF
./PHPExcel/Classes/PHPExcel/Shared/PDF/fonts
./PHPExcel/Classes/PHPExcel/Shared/PDF/config
./PHPExcel/Classes/PHPExcel/Shared/PDF/config/lang
./PHPExcel/Classes/PHPExcel/Shared/PDF/images
./PHPExcel/Classes/PHPExcel/Shared/Escher
./PHPExcel/Classes/PHPExcel/Shared/Escher/DgContainer
./PHPExcel/Classes/PHPExcel/Shared/Escher/DgContainer/SpgrContainer
./PHPExcel/Classes/PHPExcel/Shared/Escher/DggContainer
./PHPExcel/Classes/PHPExcel/Shared/Escher/DggContainer/BstoreContainer
./PHPExcel/Classes/PHPExcel/Shared/Escher/DggContainer/BstoreContainer/BSE
./PHPExcel/Classes/PHPExcel/Shared/trend
./PHPExcel/Classes/PHPExcel/Cell
./PHPExcel/Tests
./PHPExcel/Tests/templates
./PHPExcel/Tests/images
./PHPExcel/Documentation
./PHPExcel/Documentation/API
./PHPExcel/Documentation/API/PHPExcel_Style
./PHPExcel/Documentation/API/JAMA
./PHPExcel/Documentation/API/default
./PHPExcel/Documentation/API/PHPExcel_Shared_Best_Fit
./PHPExcel/Documentation/API/PHPExcel_Writer
./PHPExcel/Documentation/API/PHPExcel_Shared_Escher
./PHPExcel/Documentation/API/PHPExcel_Writer_Excel5
./PHPExcel/Documentation/API/PHPExcel_Worksheet_Drawing
./PHPExcel/Documentation/API/PHPExcel_Cell
./PHPExcel/Documentation/API/PHPExcel_Reader
./PHPExcel/Documentation/API/PHPExcel_Reader_Excel5
./PHPExcel/Documentation/API/__filesource
./PHPExcel/Documentation/API/PHPExcel_Writer_Excel2007
./PHPExcel/Documentation/API/com-tecnick-tcpdf
./PHPExcel/Documentation/API/PHPExcel
./PHPExcel/Documentation/API/media
./PHPExcel/Documentation/API/PHPExcel_RichText
./PHPExcel/Documentation/API/PHPExcel_Shared_OLE
./PHPExcel/Documentation/API/PHPExcel_Calculation
./PHPExcel/Documentation/API/PHPExcel_Worksheet
./PHPExcel/Documentation/API/PHPExcel_Shared
./PHPExcel/Documentation/API/Math_Stats
./download

 

• • 引入到项目

/var/www/report/download$ head -100 businessperformance.php | grep -i 'include\|require';

/** Include path **/

set_include_path(get_include_path() . PATH_SEPARATOR . '../PHPExcel/Classes/');

set_include_path(get_include_path() . PATH_SEPARATOR . '../');

include 'PHPExcel.php';

include 'PHPExcel/IOFactory.php';

require_once 'PHPExcel/RichText.php';

require_once 'ReportConfig.php';

include("AppUtil.php");

include("CommonDefn.php");

include("DataService.php");

include("Logger.php");

 

• 单元格操作及异常

  • 常见的单元格操作(见官方Demo)
  • 单元格操作顺序(格式化/格式刷: 包括前景，背景,字体设置，边框设置,对齐设置,数字格式设置等等。 赋值或设置公式,合并，设置列的宽度等)
  • 如果你收到“Column string index can not be empty”这样的异常，你一定要明白的是你操作的这个单于格的编号一定不存在（要么缺少列号，要么缺少行号）
  • 在不同的操作系统平台，有不同的列数/行数的限制

• Sheet 操作

  •   $objPHPExcel->setActiveSheetIndex(0);  //设置用户打开Excel(SpreadSheet)时看到的首张Sheet.如果没有设置，默认为最后一次操作的Sheet
  • 新建  Sheet:  $objPHPExcel->createSheet();  // Create a new worksheet, after the default sheet
  • $objPHPExcel->setActiveSheetIndex($index);  //[新建之后]选择该单元格作为编辑项
  • $objPHPExcel->getActiveSheet()->setTitle('Rental' ); //保存Sheet

• Excel 版本与客户端下载

  • $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //这里改为Excel2007就保存为2007了,保存为5是为了能在client双击就能打开(假设客户端安装了office或openOffice)
                $filename = str_replace('.php', '.xls', basename(__FILE__));
                header("Pragma: public");
                header("Expires: 0");
                header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
                header("Content-Type: application/force-download");
                header("Content-Type: application/octet-stream");
                header("Content-Type: application/download");;
                header("Content-Disposition: attachment;filename=$filename ");
                header("Content-Transfer-Encoding: binary ");
               
                $objWriter->save("php://output");

• &Google Document(Spread Sheet)
  

  • 将开发出来的Excel上传并保存到Google Document,发现Goole的单元格合并没有做好。