使用开源 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");
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //这里改为Excel2007就保存为2007了,保存为5是为了能在client双击就能打开(假设客户端安装了office或openOffice)
-
&Google Document(Spread Sheet)
- 将开发出来的Excel上传并保存到Google Document,发现Goole的单元格合并没有做好。
- 将开发出来的Excel上传并保存到Google Document,发现Goole的单元格合并没有做好。