PHPExcel - Excel的PHP处理引擎

PHPExcel 提供了一系列的 PHP语言 类，让你可以轻松地读写操作以下格式的文件：.xls/.xlsx/.csv/.ods/Gnumeric/PDF/HTML...主要功能包括：设置文件的meta信息，多工作表，定制字体和样式，边框和填充，图片，计算公式，转换文件类型等等

之前我只是用到了导出数据到列表格式的Excel文件，这次需要实现的目标是：JSON->phpexcel->excel+chart(line/pie/etc...)

本系列《phpexcel图形图表》文章梗概如下：

phpexcel图形图表(一)-入门----------------介绍PHPExcel类库和画图的基本步骤phpexcel图形图表(二)-图形----------------以最常用的line/pie/bar/radar图形为例作图，并对比和分析4种APiphpexcel图形图表(三)-进阶----------------让图形美化，完成一些高级的效果phpexcel图形图表(三)-高级----------------PHPExcel能完成的图表相关的高级功能

原创文章，转载请注明出处：http://www.cnblogs.com/phpgcs

本篇文章梗概：1. 下载并研究PHPExcel仓库2. PHPExcel画图和普通数据处理的区别3. 注意事项总结

首先最好是把PHPExcel的项目下载到本地来研究

git clone [email protected]:PHPOffice/PHPExcel.git

请先仔细阅读README.md说明文档，因为里面包含了很重要的版本信息，尤其是读写Excel文档的版本范围信息

### Reading * BIFF 5-8 (.xls) Excel 95 and above * Office Open xml (.xlsx) Excel 2007 and above * SPReadsheetML (.xml) Excel 2003 * Open Document Format/OASIS (.ods) * Gnumeric * HTML * SYLK * CSV### Writing * BIFF 8 (.xls) Excel 95 and above * Office Open XML (.xlsx) Excel 2007 and above * HTML * CSV * PDF (using either the tcPDF, DomPDF or mPDF libraries, which need to be installed separately)## Requirements * PHP version 5.2.0 or higher * PHP extension php_zip enabled (required if you need PHPExcel to handle .xlsx .ods or .gnumeric files) * PHP extension php_xml enabled * PHP extension php_gd2 enabled (optional, but required for exact column width autocalculation)

进入Examples文件夹，OhMygod,可以完成这么多的功能呀

01pharSimple.php01simple-download-pdf.php01simple-download-xls.php01simple-download-xlsx.php01simplePCLZip.php01simple.php02types.php02types-xls.php03formulas.php04printing.php05featuredemo.inc.php05featuredemo.php06largescale.php06largescale-with-cellcaching.php06largescale-with-cellcaching-sqlite3.php06largescale-with-cellcaching-sqlite.php06largescale-xls.php07readerPCLZip.php07reader.php08conditionalformatting2.php08conditionalformatting.php09pagebreaks.php10autofilter.php10autofilter-selection-1.php10autofilter-selection-2.php10autofilter-selection-display.php11documentsecurity.php11documentsecurity-xls.php12cellProtection.php13calculation.php14excel5.php15datavalidation.php15datavalidation-xls.php16csv.php17html.php18extendedcalculation.php19namedrange.php20readexcel5.php21pdf.php22heavilyformatted.php23sharedstyles.php24readfilter.php25inmemoryimage.php26utf8.php27imagesexcel5.php28iterator.php29advancedvaluebinder.php30template.php31docproperties_write.php31docproperties_write-xls.php32chartreadwrite.php33chartcreate-area.php33chartcreate-bar.php33chartcreate-bar-stacked.php33chartcreate-column-2.php33chartcreate-column.php33chartcreate-composite.php33chartcreate-line.php33chartcreate-multiple-charts.php33chartcreate-pie.php33chartcreate-radar.php33chartcreate-scatter.php33chartcreate-stock.php34chartupdate.php35chartrender.php36chartreadwriteHTML.php36chartreadwritePDF.php37page_layout_view.php38cloneWorksheet.php40duplicateStyle.phpExcel2003XMLReader.phpExcel2003XMLTest.xml.gitignoreGnumericReader.phpGnumericTest.gnumericimages/list.~lock.33chartcreate-line.xlsx#OOCalcReaderPCLZip.phpOOCalcReader.phpOOCalcTest.odsQuadratic2.phpQuadratic.phpQuadratic.xlsxrunall.phpSylkReader.phpSylkTest.slktemplates/XMLReader.phpXMLTest.xml

好吧，这次的任务是chart，就先从 line chart 入手，相关的文件只有一个 ***line.php

我们先直接运行一把：

liuyuan@ebuinfo:/var/www/projects/PHPExcel/Examples$ php 33chartcreate-line.php 07:13:22 Write to Excel2007 format07:13:23 File written to 33chartcreate-line.xlsx07:13:23 Peak memory usage: 8.75 MB07:13:23 Done writing fileFile has been created in /var/www/projects/PHPExcel/Examples

Wow，生成了一个Excel文件哦，赶紧打开

这也太简单了吧，赶紧看看源码（附带说明）

<?php/** 开启各种PHP Error Report机制 */error_reporting(E_ALL);ini_set('display_errors', TRUE);ini_set('display_startup_errors', TRUE);date_default_timezone_set('Europe/London');define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');date_default_timezone_set('Europe/London');/** 引入最重要的PHPExcel类库的入口文件 */require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';/* 创建PHPExcel对象 */$objPHPExcel = new PHPExcel();/* 其实最常用的操作是Excel的工作表sheet，因此我们取活动sheet对象 */$objWorksheet = $objPHPExcel->getActiveSheet();/* 画一个line图的源数据是一个2维数组，这样可以画多条line，即多个series */$objWorksheet->fromArray(array(array('',2010,2011,2012),array('Q1',   12,   15,21),array('Q2',   56,   73,86),array('Q3',   52,   61,69),array('Q4',   30,   32,0),));/* 这里有一个重要的类PHPExcel_Chart_DataSeriesValues,后面多次用到，它有几个参数 * * 数据类型  Datatype * 指定单元格  Cell reference for data * 格式代码 Format Code * 本系列数据中元素个数  Number of datapoints in series * Data values * Data*/// 设置每一个data series 数据系列的名称$dataseriesLabels = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', NULL, 1),//2010new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', NULL, 1),//2011new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$1', NULL, 1),//2012);//设置X轴Tick数据(X轴每一个刻度值)$xAxisTickValues = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$5', NULL, 4),//Q1 to Q4);//设置作图区域数据$dataSeriesValues = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$5', NULL, 4),new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$5', NULL, 4),new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$5', NULL, 4),);//构建数据系列 dataseries$series = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_LINECHART,// plotTypePHPExcel_Chart_DataSeries::GROUPING_STACKED,// plotGroupingrange(0, count($dataSeriesValues)-1),// plotOrder$dataseriesLabels,// plotLabel$xAxisTickValues,// plotCategory$dataSeriesValues// plotValues);// 给数据系列分配一个做图区域$plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series));// Set the chart legend$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_TOPRIGHT, NULL, false);// 设置图形标题$title = new PHPExcel_Chart_Title('Test Stacked Line Chart');// 设置Y轴标签$yAxisLabel = new PHPExcel_Chart_Title('Value ($k)');// 创建图形$chart = new PHPExcel_Chart('chart1',// name$title,// title$legend,// legend$plotarea,// plotAreatrue,// plotVisibleOnly0,// displayBlanksAsNULL,// xAxisLabel$yAxisLabel// yAxisLabel);// 设置图形绘制区域$chart->setTopLeftPosition('A7');$chart->setBottomRightPosition('H20');// 将图形添加到当前工作表$objWorksheet->addChart($chart);// Save Excel 2007 fileecho date('H:i:s') , " Write to Excel2007 format" , EOL;$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');// 打开做图开关$objWriter->setIncludeCharts(TRUE);$objWriter->save(str_replace('.php', '.xlsx', __FILE__));echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;// Echo memory peak usageecho date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;// Echo doneecho date('H:i:s') , " Done writing file" , EOL;echo 'File has been created in ' , getcwd() , EOL;

总结一下：

1，引入PHPExcel2，填充源数据到工作表上$objWorkSheet->fromArray($array);or$objWorkSheet->setCellValues('A1', 1);3，设置dataseriesPHPExcel_Chart_DataSeries::TYPE_LINECHART,PHPExcel_Chart_DataSeries::GROUPING_STACKED,range(0, count($dataSeriesValues）-1），$dataseriesLabels=>array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$AB$1', NULL, 1)),$xAxisTickValues=>array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$AA$2:$AA$21', NULL, 20),),$dataSeriesValues=>array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$AB$2:$AB$21', NULL, 20))4，设置chart'chartTit