scatter chart problems

Topics: Developer Forum
Nov 22, 2013 at 7:56 PM
I generate the array for phpexcel store it into a $_session varialbe and then have a link to the php page to create a excel data and graph to the screen. It works on a small sampling of data but when I run it with "true" data it will not work, it just displays the error:

Excel cannot open the file 'xxxxx.xlsx' because the file format or file extension is not valid. Verify that the file has not been corupted and that the file extension matches the format of the file.

I can comment this statement and it will put the data into the file and display the spreadsheet without the chart:
$objWorksheet->addChart($chart);

What am I doing wrong??

full php page code below:

<?php
// $valary=array(
    // array('tagname',  'Value'),
    // array('11/19/13 14:42',   89300.0475),
    // array('11/19/13 15:12',  89444.13835),
    // array('11/19/13 15:42',   89332.66993),
    // array('11/19/13 16:12',   89308.91376),
    // array('11/19/13 16:42',  89810.509871),
    // array('11/19/13 17:12',   90237.95934),
    // array('11/19/13 17:42',   90235.1259),
    // array('11/19/13 18:12',   90712.74051),
// );
// $ret=xlscatchart($tagnam,$valary);
// if(isset($_POST['tagnam']) ){
// $tagnam = $_POST['tagnam'];
// } else {
// $tagnam = "nothing entered";
// }
// if(isset($_POST['valary']) ){
// $valary = $_POST['valary'];
// } else {
// $valary = "nothing entered";
// }
// $str_var = $_POST["str_var"];
// $valary = unserialize(base64_decode($str_var));
// $valary=$_SESSION['valary'];
// $tagnam=$_SESSION['tagnam'];
session_start();
$valpass=($_SESSION['valpass']);
$tagpass=($_SESSION['tagpass']);
$numvals=($_SESSION['numvals']);
$valpass[0][0]=$tagpass;
// echo $valpass[0][0];
// echo "<br>tagname=".$tagpass;
// echo "<br>num vals=".$numvals;
$ret=xlscatchart($tagpass,$valpass);
function xlscatchart($tagpass,$valpass){
/* Error reporting /
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
    *
  • Copyright (C) 2006 - 2013 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 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
  • @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  • @version 1.7.9, 2013-06-02
    //* PHPExcel */
    require_once '../Dashboard/Classes/Classes/PHPExcel.php';
    $objPHPExcel = new PHPExcel();
    $objWorksheet = $objPHPExcel->getActiveSheet();
    $objWorksheet->fromArray($valpass
    // array(
    // array('',   2010,   2011,   2012),
    // array(50,   12,   15,       21),
    // array(100,   56,   73,      86),
    // array(150,   52,   61,      69),
    // array(200,   30,   32,      50),
    // array(250,   12,   15,      21),
    // array(300,   56,   73,      86),
    // array(350,   52,   61,      69),
    // array(400,   30,   32,      50),
    // )
    // );
    // array(
    // array('tagname',  'value'),
    // array('11/19/13 14:42',   89300.0475),
    // array('11/19/13 15:12',  89444.13835),
    // array('11/19/13 15:42',   89332.66993),
    // array('11/19/13 16:12',   89308.91376),
    // array('11/19/13 16:42',  89810.509871),
    // array('11/19/13 17:12',   90237.95934),
    // array('11/19/13 17:42',   90235.1259),
    // array('11/19/13 18:12',   90712.74051),
    // )
    );
    // 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!$A$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
// $wksarea="Worksheet!$A$2:$A$".$numvals;
// echo "worksheetarea=".$wksarea;
$xAxisTickValues = array(
new PHPExcel_Chart_DataSeriesValues('String', '$Worksheet!$A$2:$A$98', NULL, 8),  //  Q1 to Q4
// new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$9', NULL, 8),  //  Q1 to Q4
// new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$9', NULL, 8),  //  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
// echo "<br>";
// print_r($xAxisTickValues );
// $wksarea="Worksheet!$B$2:$B$".$numvals;
$dataSeriesValues = array(
new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$98', NULL, 8),
// new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$9', NULL, 8),
// new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$9', NULL, 8),
);
// Build the dataseries
$series = new PHPExcel_Chart_DataSeries(
PHPExcel_Chart_DataSeries::TYPE_SCATTERCHART,   // plotType
NULL,                                           // plotGrouping (Scatter charts don't have any grouping)
range(0, count($dataSeriesValues)-1),           // plotOrder
$dataseriesLabels,                              // plotLabel
$xAxisTickValues,                               // plotCategory
$dataSeriesValues,                              // plotValues
NULL,                                           // smooth line
PHPExcel_Chart_DataSeries::STYLE_LINEMARKER     // plotStyle
);
// 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_TOPRIGHT, NULL, false);
$title = new PHPExcel_Chart_Title('Chart');
$yAxisLabel = new PHPExcel_Chart_Title('Value');
// 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('F7');
$chart->setBottomRightPosition('ZZ98');
// Add the chart to the worksheet
$objWorksheet->addChart($chart); // take this out and you will get a spreadsheet with data but no chart
$filename = "tagvalues.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setIncludeCharts(TRUE);
$objWriter->save('php://output');
}
exit;
Nov 25, 2013 at 7:47 AM
Excel cannot open the file 'xxxxx.xlsx' because the file format or file extension is not valid[...]
When you see this type of message, open the file with Notepad: it is likely that an error message appears... Message that will allow you to pinpoint the problem.
Nov 25, 2013 at 2:41 PM
output is going to the screen not to a file (php://output), rights to save things to the server is restricted here, so I cannot save the file to the server then pull up with notepad.
Nov 25, 2013 at 3:50 PM
Output is going to the browser and your browser offers you save it or open it, no? Choose to save it on your machine and then open it with the Notepad.
Nov 26, 2013 at 1:42 PM
no it trys to open excel and I get the message, nothing comes back to the browser. I know if I comment this statement:
$objWorksheet->addChart($chart);
I will get the data in excel in column a and b. If I put the statement in I get the error message:
Excel cannot open the file 'xxxxx.xlsx' because the file format or file extension is not valid. Verify that the file has not been corupted and that the file extension matches the format of the file.

It seems to be something with the charting parms. I can put in :
// array(

// array('tagname', 'value'),
// array('11/19/13 14:42', 89300.0475),
// array('11/19/13 15:12', 89444.13835),
// array('11/19/13 15:42', 89332.66993),
// array('11/19/13 16:12', 89308.91376),
// array('11/19/13 16:42', 89810.509871),
// array('11/19/13 17:12', 90237.95934),
// array('11/19/13 17:42', 90235.1259),
// array('11/19/13 18:12', 90712.74051),
// )

as values and it gives the data in column a and b and does a scatter chart with no problem with the: $objWorksheet->addChart($chart);
included. This was just the first few values of the real data it had 98 lines and when I use it I get the message:
Excel cannot open the file 'xxxxx.xlsx' because the file format or file extension is not valid. Verify that the file has not been corupted and that the file extension matches the format of the file.
Nov 26, 2013 at 4:45 PM
I got it not sure exactly what I did but it is working now. Thank you!
Nov 26, 2013 at 4:58 PM
Remove the header lines in script. The browser display the file (you get some "garbage" of course).

It's not easy with all comment lines, but playing with your script, i got a valid xlsx with a graph... I think i have change somewhere "$objWorksheet->fromArray($valpass" in "$objWorksheet->fromArray(", a few 98 to 9 and ZZ98 to T30 (i don't have a 100" monitor).