33chartcreate-stock.php 5.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151
  1. <?php
  2. /** Error reporting */
  3. error_reporting(E_ALL);
  4. ini_set('display_errors', TRUE);
  5. ini_set('display_startup_errors', TRUE);
  6. date_default_timezone_set('Europe/London');
  7. define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
  8. date_default_timezone_set('Europe/London');
  9. /**
  10. * PHPExcel
  11. *
  12. * Copyright (c) 2006 - 2015 PHPExcel
  13. *
  14. * This library is free software; you can redistribute it and/or
  15. * modify it under the terms of the GNU Lesser General Public
  16. * License as published by the Free Software Foundation; either
  17. * version 2.1 of the License, or (at your option) any later version.
  18. *
  19. * This library is distributed in the hope that it will be useful,
  20. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  21. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  22. * Lesser General Public License for more details.
  23. *
  24. * You should have received a copy of the GNU Lesser General Public
  25. * License along with this library; if not, write to the Free Software
  26. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  27. *
  28. * @category PHPExcel
  29. * @package PHPExcel
  30. * @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
  31. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  32. * @version ##VERSION##, ##DATE##
  33. */
  34. /** PHPExcel */
  35. require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
  36. $objPHPExcel = new PHPExcel();
  37. $objWorksheet = $objPHPExcel->getActiveSheet();
  38. $objWorksheet->fromArray(
  39. array(
  40. array('Counts', 'Max', 'Min', 'Min Threshold', 'Max Threshold' ),
  41. array(10, 10, 5, 0, 50 ),
  42. array(30, 20, 10, 0, 50 ),
  43. array(20, 30, 15, 0, 50 ),
  44. array(40, 10, 0, 0, 50 ),
  45. array(100, 40, 5, 0, 50 ),
  46. ), null, 'A1', true
  47. );
  48. $objWorksheet->getStyle('B2:E6')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
  49. // Set the Labels for each data series we want to plot
  50. // Datatype
  51. // Cell reference for data
  52. // Format Code
  53. // Number of datapoints in series
  54. // Data values
  55. // Data Marker
  56. $dataSeriesLabels = array(
  57. new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', NULL, 1), //Max / Open
  58. new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', NULL, 1), //Min / Close
  59. new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$1', NULL, 1), //Min Threshold / Min
  60. new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$E$1', NULL, 1), //Max Threshold / Max
  61. );
  62. // Set the X-Axis Labels
  63. // Datatype
  64. // Cell reference for data
  65. // Format Code
  66. // Number of datapoints in series
  67. // Data values
  68. // Data Marker
  69. $xAxisTickValues = array(
  70. new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$6', NULL, 5), // Counts
  71. );
  72. // Set the Data values for each data series we want to plot
  73. // Datatype
  74. // Cell reference for data
  75. // Format Code
  76. // Number of datapoints in series
  77. // Data values
  78. // Data Marker
  79. $dataSeriesValues = array(
  80. new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$6', NULL, 5),
  81. new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$6', NULL, 5),
  82. new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$6', NULL, 5),
  83. new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$E$2:$E$6', NULL, 5),
  84. );
  85. // Build the dataseries
  86. $series = new PHPExcel_Chart_DataSeries(
  87. PHPExcel_Chart_DataSeries::TYPE_STOCKCHART, // plotType
  88. null, // plotGrouping - if we set this to not null, then xlsx throws error
  89. range(0, count($dataSeriesValues)-1), // plotOrder
  90. $dataSeriesLabels, // plotLabel
  91. $xAxisTickValues, // plotCategory
  92. $dataSeriesValues // plotValues
  93. );
  94. // Set the series in the plot area
  95. $plotArea = new PHPExcel_Chart_PlotArea(NULL, array($series));
  96. // Set the chart legend
  97. $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
  98. $title = new PHPExcel_Chart_Title('Test Stock Chart');
  99. $xAxisLabel = new PHPExcel_Chart_Title('Counts');
  100. $yAxisLabel = new PHPExcel_Chart_Title('Values');
  101. // Create the chart
  102. $chart = new PHPExcel_Chart(
  103. 'stock-chart', // name
  104. $title, // title
  105. $legend, // legend
  106. $plotArea, // plotArea
  107. true, // plotVisibleOnly
  108. 0, // displayBlanksAs
  109. $xAxisLabel, // xAxisLabel
  110. $yAxisLabel // yAxisLabel
  111. );
  112. // Set the position where the chart should appear in the worksheet
  113. $chart->setTopLeftPosition('A7');
  114. $chart->setBottomRightPosition('H20');
  115. // Add the chart to the worksheet
  116. $objWorksheet->addChart($chart);
  117. // Save Excel 2007 file
  118. echo date('H:i:s') , " Write to Excel2007 format" , EOL;
  119. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  120. $objWriter->setIncludeCharts(TRUE);
  121. $filename = str_replace('.php', '.xlsx', __FILE__);
  122. if(file_exists($filename)) {
  123. unlink($filename);
  124. }
  125. $objWriter->save($filename);
  126. echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
  127. // Echo memory peak usage
  128. echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
  129. // Echo done
  130. echo date('H:i:s') , " Done writing file" , EOL;
  131. echo 'File has been created in ' , getcwd() , EOL;