This repository has been archived by the owner on Jan 2, 2019. It is now read-only.
PHPExcel Charts #646
Comments
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
Hi,
I am trying to create for the first time a PHPExcel Chart, a stacked bar, using example\33chartcreate-bar.php and adjusting to my needs. I will admit though, that I am adjusting blindly because I have not found any documentation on charts. So my question is 2-part:
Working Example:
'); date_default_timezone_set('Europe/London'); /** - PHPExcel * - Copyright (C) 2006 - 2014 PHPExcel * - This library is free software; you can redistribute it and/or - modify it under the terms of the GNU Lesser General Public - License as published by the Free Software Foundation; either - version 2.1 of the License, or (at your option) any later version. * - This library is distributed in the hope that it will be useful, - but WITHOUT ANY WARRANTY; without even the implied warranty of - MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU - Lesser General Public License for more details. * - You should have received a copy of the GNU Lesser General Public - License along with this library; if not, write to the Free Software - Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA * - @category PHPExcel - @Package PHPExcel - @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel) - @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL - @Version 1.8.0, 2014-03-02 */ /*\* PHPExcel */ require_once dirname(**FILE**) . '/../Classes/PHPExcel.php'; $objPHPExcel = new PHPExcel(); $objWorksheet = $objPHPExcel->getActiveSheet(); $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), ) ); // Set the Labels for each data series we want to plot // Datatype // Cell reference for data // Format Code // Number of datapoints in series // Data values // Data Marker $dataseriesLabels = array( new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', NULL, 1), // 2010 new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', NULL, 1), // 2011 new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$1', NULL, 1), // 2012 ); // Set the X-Axis Labels // Datatype // Cell reference for data // Format Code // Number of datapoints in series // Data values // Data Marker $xAxisTickValues = array( new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$5', NULL, 4), // Q1 to Q4 ); // Set the Data values for each data series we want to plot // Datatype // Cell reference for data // Format Code // Number of datapoints in series // Data values // Data Marker $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), ); // Build the dataseries $series = new PHPExcel_Chart_DataSeries( PHPExcel_Chart_DataSeries::TYPE_BARCHART, // plotType PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED, // plotGrouping range(0, count($dataSeriesValues)-1), // plotOrder $dataseriesLabels, // plotLabel $xAxisTickValues, // plotCategory $dataSeriesValues // plotValues ); // Set additional dataseries parameters // Make it a horizontal bar rather than a vertical column graph $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_BAR); // Set the series in the plot area $plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series)); // Set the chart legend $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); $title = new PHPExcel_Chart_Title('Test Bar Chart'); $yAxisLabel = new PHPExcel_Chart_Title('Value ($k)'); // Create the chart $chart = new PHPExcel_Chart( 'chart1', // name $title, // title $legend, // legend $plotarea, // plotArea true, // plotVisibleOnly 0, // displayBlanksAs NULL, // xAxisLabel $yAxisLabel // yAxisLabel ); // Set the position where the chart should appear in the worksheet $chart->setTopLeftPosition('A7'); $chart->setBottomRightPosition('H20'); // Add the chart to the worksheet $objWorksheet->addChart($chart); // Save Excel 2007 file echo 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 usage echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL; // Echo done echo date('H:i:s') , " Done writing file" , EOL; echo 'File has been created in ' , getcwd() , EOL; } My code: { //I have already populated my cells and are showing correctly private static function draw_graph($excel_last_row, &$excel_obj){ ``` $excel_last_row -= 2; //Do Trip Labels $dataseriesLabels = array( new PHPExcel_Chart_DataSeriesValues('String', '\'Daily Passenger Totals\'!$C$4', NULL, 1), // Trip A new PHPExcel_Chart_DataSeriesValues('String', '\'Daily Passenger Totals\'!$D$4', NULL, 1), // Trip B new PHPExcel_Chart_DataSeriesValues('String', '\'Daily Passenger Totals\'!$E$4', NULL, 1), // Trip C new PHPExcel_Chart_DataSeriesValues('String', '\'Daily Passenger Totals\'!$F$4', NULL, 1), // Trip D new PHPExcel_Chart_DataSeriesValues('String', '\'Daily Passenger Totals\'!$G$4', NULL, 1), // Trip E new PHPExcel_Chart_DataSeriesValues('String', '\'Daily Passenger Totals\'!$H$4', NULL, 1), // Trip F new PHPExcel_Chart_DataSeriesValues('String', '\'Daily Passenger Totals\'!$I$4', NULL, 1), // Trip OUTSOURCED ); //X-Axis Labels(Dates) $x_labels = self::get_graph_col_range('B', $excel_last_row); $xAxisTickValues = array( new PHPExcel_Chart_DataSeriesValues('String', $x_labels, NULL, 1), // Dates ); //plot actual values $values_a = self::get_graph_col_range('C', $excel_last_row); $values_b = self::get_graph_col_range('D', $excel_last_row); $values_c = self::get_graph_col_range('E', $excel_last_row); $values_d = self::get_graph_col_range('F', $excel_last_row); $values_e = self::get_graph_col_range('G', $excel_last_row); $values_f = self::get_graph_col_range('H', $excel_last_row); $values_outsourced = self::get_graph_col_range('I', $excel_last_row); $dataSeriesValues = array( new PHPExcel_Chart_DataSeriesValues('String', $values_a, NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', $values_b, NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', $values_c, NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', $values_d, NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', $values_e, NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', $values_f, NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', $values_outsourced, NULL, 1), ); //Build data series $series = new PHPExcel_Chart_DataSeries( PHPExcel_Chart_DataSeries::TYPE_STOCKCHART, PHPExcel_Chart_DataSeries::GROUPING_STACKED, range(0, count($dataSeriesValues)-1), $dataseriesLabels, $xAxisTickValues ); //Set the series in the plot area $plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series)); //Set the chart legend $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_BOTTOM, NULL, false); $title = new PHPExcel_Chart_Title(''); $yAxisLabel = new PHPExcel_Chart_Title(''); //Create the chart $chart = new PHPExcel_Chart( 'chart1', // name $title, // title $legend, // legend $plotarea, // plotArea true, // plotVisibleOnly 0, // displayBlanksAs NULL, // xAxisLabel $yAxisLabel // yAxisLabel ); //Set the position where the chart should appear in the worksheet $top_row = $excel_last_row + 4; $bottom_row = $top_row + 24; $chart->setTopLeftPosition('A' . $top_row); $chart->setBottomRightPosition('M' . $bottom_row); echo '{
'; var_dump($chart); //Add the chart to the worksheet $objWorksheet = $excel_obj->getActiveSheet(); $objWorksheet->addChart($chart); } private static function get_graph_col_range($column, $last_row, $first_row = 5){ $range = '\'Daily Passenger Totals\'!$'; $range .= $column . '$' . $first_row . ':$' . $column . '$' . $last_row; return $range; } ``` } var dump { object(PHPExcel_Chart)#671 (15) { ["_name":"PHPExcel_Chart":private]=> string(6) "chart1" ["_worksheet":"PHPExcel_Chart":private]=> NULL ["_title":"PHPExcel_Chart":private]=> object(PHPExcel_Chart_Title)#675 (2) { ["_caption":"PHPExcel_Chart_Title":private]=> string(0) "" ["_layout":"PHPExcel_Chart_Title":private]=> NULL } ["_legend":"PHPExcel_Chart":private]=> object(PHPExcel_Chart_Legend)#678 (3) { ["_position":"PHPExcel_Chart_Legend":private]=> string(1) "b" ["_overlay":"PHPExcel_Chart_Legend":private]=> bool(false) ["_layout":"PHPExcel_Chart_Legend":private]=> NULL } ["_xAxisLabel":"PHPExcel_Chart":private]=> NULL ["_yAxisLabel":"PHPExcel_Chart":private]=> object(PHPExcel_Chart_Title)#676 (2) { ["_caption":"PHPExcel_Chart_Title":private]=> string(0) "" ["_layout":"PHPExcel_Chart_Title":private]=> NULL } ["_plotArea":"PHPExcel_Chart":private]=> object(PHPExcel_Chart_PlotArea)#677 (2) { ["_layout":"PHPExcel_Chart_PlotArea":private]=> NULL ["_plotSeries":"PHPExcel_Chart_PlotArea":private]=> array(1) { [0]=> object(PHPExcel_Chart_DataSeries)#682 (9) { ["_plotType":"PHPExcel_Chart_DataSeries":private]=> string(10) "stockChart" ["_plotGrouping":"PHPExcel_Chart_DataSeries":private]=> string(7) "stacked" ["_plotDirection":"PHPExcel_Chart_DataSeries":private]=> NULL ["_plotStyle":"PHPExcel_Chart_DataSeries":private]=> NULL ["_plotOrder":"PHPExcel_Chart_DataSeries":private]=> array(7) { [0]=> int(0) [1]=> int(1) [2]=> int(2) [3]=> int(3) [4]=> int(4) [5]=> int(5) [6]=> int(6) } ["_plotLabel":"PHPExcel_Chart_DataSeries":private]=> array(8) { [0]=> object(PHPExcel_Chart_DataSeriesValues)#635 (6) { ["_dataType":"PHPExcel_Chart_DataSeriesValues":private]=> string(6) "String" ["_dataSource":"PHPExcel_Chart_DataSeriesValues":private]=> string(29) "'Daily Passenger Totals'!$C$4" ["_formatCode":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_marker":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_pointCount":"PHPExcel_Chart_DataSeriesValues":private]=> int(1) ["_dataValues":"PHPExcel_Chart_DataSeriesValues":private]=> array(0) { } } [1]=> object(PHPExcel_Chart_DataSeriesValues)#627 (6) { ["_dataType":"PHPExcel_Chart_DataSeriesValues":private]=> string(6) "String" ["_dataSource":"PHPExcel_Chart_DataSeriesValues":private]=> string(29) "'Daily Passenger Totals'!$D$4" ["_formatCode":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_marker":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_pointCount":"PHPExcel_Chart_DataSeriesValues":private]=> int(1) ["_dataValues":"PHPExcel_Chart_DataSeriesValues":private]=> array(0) { } } [2]=> object(PHPExcel_Chart_DataSeriesValues)#548 (6) { ["_dataType":"PHPExcel_Chart_DataSeriesValues":private]=> string(6) "String" ["_dataSource":"PHPExcel_Chart_DataSeriesValues":private]=> string(29) "'Daily Passenger Totals'!$E$4" ["_formatCode":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_marker":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_pointCount":"PHPExcel_Chart_DataSeriesValues":private]=> int(1) ["_dataValues":"PHPExcel_Chart_DataSeriesValues":private]=> array(0) { } } [3]=> object(PHPExcel_Chart_DataSeriesValues)#626 (6) { ["_dataType":"PHPExcel_Chart_DataSeriesValues":private]=> string(6) "String" ["_dataSource":"PHPExcel_Chart_DataSeriesValues":private]=> string(29) "'Daily Passenger Totals'!$F$4" ["_formatCode":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_marker":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_pointCount":"PHPExcel_Chart_DataSeriesValues":private]=> int(1) ["_dataValues":"PHPExcel_Chart_DataSeriesValues":private]=> array(0) { } } [4]=> object(PHPExcel_Chart_DataSeriesValues)#629 (6) { ["_dataType":"PHPExcel_Chart_DataSeriesValues":private]=> string(6) "String" ["_dataSource":"PHPExcel_Chart_DataSeriesValues":private]=> string(29) "'Daily Passenger Totals'!$G$4" ["_formatCode":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_marker":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_pointCount":"PHPExcel_Chart_DataSeriesValues":private]=> int(1) ["_dataValues":"PHPExcel_Chart_DataSeriesValues":private]=> array(0) { } } [5]=> object(PHPExcel_Chart_DataSeriesValues)#628 (6) { ["_dataType":"PHPExcel_Chart_DataSeriesValues":private]=> string(6) "String" ["_dataSource":"PHPExcel_Chart_DataSeriesValues":private]=> string(29) "'Daily Passenger Totals'!$H$4" ["_formatCode":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_marker":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_pointCount":"PHPExcel_Chart_DataSeriesValues":private]=> int(1) ["_dataValues":"PHPExcel_Chart_DataSeriesValues":private]=> array(0) { } } [6]=> object(PHPExcel_Chart_DataSeriesValues)#688 (6) { ["_dataType":"PHPExcel_Chart_DataSeriesValues":private]=> string(6) "String" ["_dataSource":"PHPExcel_Chart_DataSeriesValues":private]=> string(29) "'Daily Passenger Totals'!$I$4" ["_formatCode":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_marker":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_pointCount":"PHPExcel_Chart_DataSeriesValues":private]=> int(1) ["_dataValues":"PHPExcel_Chart_DataSeriesValues":private]=> array(0) { } } [""]=> object(PHPExcel_Chart_DataSeriesValues)#679 (6) { ["_dataType":"PHPExcel_Chart_DataSeriesValues":private]=> string(6) "Number" ["_dataSource":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_formatCode":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_marker":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_pointCount":"PHPExcel_Chart_DataSeriesValues":private]=> int(0) ["_dataValues":"PHPExcel_Chart_DataSeriesValues":private]=> array(0) { } } } ["_plotCategory":"PHPExcel_Chart_DataSeries":private]=> array(2) { [0]=> object(PHPExcel_Chart_DataSeriesValues)#630 (6) { ["_dataType":"PHPExcel_Chart_DataSeriesValues":private]=> string(6) "String" ["_dataSource":"PHPExcel_Chart_DataSeriesValues":private]=> string(35) "'Daily Passenger Totals'!$B$5:$B$13" ["_formatCode":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_marker":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_pointCount":"PHPExcel_Chart_DataSeriesValues":private]=> int(1) ["_dataValues":"PHPExcel_Chart_DataSeriesValues":private]=> array(0) { } } [""]=> object(PHPExcel_Chart_DataSeriesValues)#680 (6) { ["_dataType":"PHPExcel_Chart_DataSeriesValues":private]=> string(6) "Number" ["_dataSource":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_formatCode":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_marker":"PHPExcel_Chart_DataSeriesValues":private]=> NULL ["_pointCount":"PHPExcel_Chart_DataSeriesValues":private]=> int(0) ["_dataValues":"PHPExcel_Chart_DataSeriesValues":private]=> array(0) { } } } ["_smoothLine":"PHPExcel_Chart_DataSeries":private]=> NULL ["_plotValues":"PHPExcel_Chart_DataSeries":private]=> array(0) { } } } } ["_plotVisibleOnly":"PHPExcel_Chart":private]=> bool(true) ["_displayBlanksAs":"PHPExcel_Chart":private]=> int(0) ["_topLeftCellRef":"PHPExcel_Chart":private]=> string(3) "A17" ["_topLeftXOffset":"PHPExcel_Chart":private]=> int(0) ["_topLeftYOffset":"PHPExcel_Chart":private]=> int(0) ["_bottomRightCellRef":"PHPExcel_Chart":private]=> string(3) "M41" ["_bottomRightXOffset":"PHPExcel_Chart":private]=> int(10) ["_bottomRightYOffset":"PHPExcel_Chart":private]=> int(10) } } my error { Notice: Undefined offset: 0 in C:\inetpub\wwwroot\CSS_Reservations\excel_lib\PHPExcel\Chart\DataSeries.php on line 147 Notice: Undefined index: in C:\inetpub\wwwroot\CSS_Reservations\excel_lib\PHPExcel\Chart\DataSeries.php on line 147 Notice: Undefined offset: 0 in C:\inetpub\wwwroot\CSS_Reservations\excel_lib\PHPExcel\Chart\DataSeries.php on line 148 Notice: Undefined offset: 0 in C:\inetpub\wwwroot\CSS_Reservations\excel_lib\PHPExcel\Chart\DataSeries.php on line 152 Notice: Undefined index: in C:\inetpub\wwwroot\CSS_Reservations\excel_lib\PHPExcel\Chart\DataSeries.php on line 152 Notice: Undefined offset: 0 in C:\inetpub\wwwroot\CSS_Reservations\excel_lib\PHPExcel\Chart\DataSeries.php on line 153 } Any help will be greatly appreciatedThe text was updated successfully, but these errors were encountered: