Does your PHPExcel generated excel file doesn't work when opening with Microsoft Excel (File format or extension is invalid ) ? If you're a victim of this horrible problem, there are a lot of factors that can cause this message.
If you're using plain php and you use the following code :
$excelWriter->save("php://output");
Mark Baker (the PHPOffice developer) said :
check your script to see where this might be sent to the
php://outputstream. Check that there's no space before your initial<?phpopening tag; watch out in particular for?> <?phpor similar closing/opening tags. And also check any files that might be included by your script.
And that's a difficult point to achieve , because it's difficult to check which files have spaces between the tags (and if you use a framework like symfony.... this will be a difficul task to achieve), however some developers solved this issue using :
ob_end_clean(); // ob_end_clean Limpia el búfer de salida y desactiva el búfer de salida
$excelWriter->save("php://output");
If the previous solution didn't work for your, then you have only 1 option left, but before continue you should know a couple of important notes:
- A streamed response will generate a corrupt file almost always.
- PHPExcel may not be the only guilty of the corrupt file, in most of the cases, your code (the way you draw the data) may be the corrupt.
- Save the file somewhere in your server using the save method, then generate a download of the file (direct or indirect) if you need to.
The following code will create an excel file with a basic chart example that will not get corrupted, test it :
// Remove the \ before every class if you're using plain php ( new \PHP_Something)
public function createexcelfileAction() {
// if you are using plain php use instead,
//$excel = new PHPExcel();
$excel = $this->get('phpexcel')->createPHPExcelObject();
$excel->createSheet();
$excel->setActiveSheetIndex(1);
$excel->getActiveSheet()->setTitle('ChartTest');
$objWorksheet = $excel->getActiveSheet();
$objWorksheet->fromArray(
array(
array('', 'Rainfall (mm)', 'Temperature (°F)', 'Humidity (%)'),
array('Jan', 78, 52, 61),
array('Feb', 64, 54, 62),
array('Mar', 62, 57, 63),
array('Apr', 21, 62, 59),
array('May', 11, 75, 60),
array('Jun', 1, 75, 57),
array('Jul', 1, 79, 56),
array('Aug', 1, 79, 59),
array('Sep', 10, 75, 60),
array('Oct', 40, 68, 63),
array('Nov', 69, 62, 64),
array('Dec', 89, 57, 66),
)
);
// 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
$dataseriesLabels1 = array(
new \PHPExcel_Chart_DataSeriesValues('String', 'Grafico!$B$1', NULL, 1), // Temperature
);
$dataseriesLabels2 = array(
new \PHPExcel_Chart_DataSeriesValues('String', 'Grafico!$C$1', NULL, 1), // Rainfall
);
$dataseriesLabels3 = array(
new \PHPExcel_Chart_DataSeriesValues('String', 'Grafico!$D$1', NULL, 1), // Humidity
);
// 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', 'Grafico!$A$2:$A$13', NULL, 12), // Jan to Dec
);
// 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
$dataSeriesValues1 = array(
new \PHPExcel_Chart_DataSeriesValues('Number', 'Grafico!$B$2:$B$13', NULL, 12),
);
// Build the dataseries
$series1 = new \PHPExcel_Chart_DataSeries(
\PHPExcel_Chart_DataSeries::TYPE_BARCHART, // plotType
\PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED, // plotGrouping
range(0, count($dataSeriesValues1) - 1), // plotOrder
$dataseriesLabels1, // plotLabel
$xAxisTickValues, // plotCategory
$dataSeriesValues1 // plotValues
);
// Set additional dataseries parameters
// Make it a vertical column rather than a horizontal bar graph
$series1->setPlotDirection(\PHPExcel_Chart_DataSeries::DIRECTION_COL);
// 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
$dataSeriesValues2 = array(
new \PHPExcel_Chart_DataSeriesValues('Number', 'Grafico!$C$2:$C$13', NULL, 12),
);
// Build the dataseries
$series2 = new \PHPExcel_Chart_DataSeries(
\PHPExcel_Chart_DataSeries::TYPE_LINECHART, // plotType
\PHPExcel_Chart_DataSeries::GROUPING_STANDARD, // plotGrouping
range(0, count($dataSeriesValues2) - 1), // plotOrder
$dataseriesLabels2, // plotLabel
NULL, // plotCategory
$dataSeriesValues2 // plotValues
);
// 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
$dataSeriesValues3 = array(
new \PHPExcel_Chart_DataSeriesValues('Number', 'Grafico!$D$2:$D$13', NULL, 12),
);
// Build the dataseries
$series3 = new \PHPExcel_Chart_DataSeries(
\PHPExcel_Chart_DataSeries::TYPE_AREACHART, // plotType
\PHPExcel_Chart_DataSeries::GROUPING_STANDARD, // plotGrouping
range(0, count($dataSeriesValues2) - 1), // plotOrder
$dataseriesLabels3, // plotLabel
NULL, // plotCategory
$dataSeriesValues3 // plotValues
);
// Set the series in the plot area
$plotarea = new \PHPExcel_Chart_PlotArea(NULL, array($series1, $series2, $series3));
// Set the chart legend
$legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
$title = new \PHPExcel_Chart_Title('Chart awesome');
// Create the chart
$chart = new \PHPExcel_Chart(
'chart1', // name
$title, // title
$legend, // legend
$plotarea, // plotArea
true, // plotVisibleOnly
0, // displayBlanksAs
NULL, // xAxisLabel
NULL // yAxisLabel
);
// Set the position where the chart should appear in the worksheet
$chart->setTopLeftPosition('F2');
$chart->setBottomRightPosition('O16');
// Add the chart to the worksheet
$objWorksheet->addChart($chart);
// if you're using plain php use instead :
// $writer = new PHPExcel_Writer_Excel2007($excel);
$writer = $this->get('phpexcel')->createWriter($excel, 'Excel2007');
$writer->setIncludeCharts(TRUE);
// Save the file somewhere in your project
$writer->save('file.xlsx');
// then your file will be not corrupted anymore
// Do not use streamed responses with excel files that include charts ! otherwise it will get corrupted
/**
$response = $this->get('phpexcel')->createStreamedResponse($writer);
// adding headers
$dispositionHeader = $response->headers->makeDisposition(
ResponseHeaderBag::DISPOSITION_ATTACHMENT,
'stream-file.xlsx'
);
$response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
$response->headers->set('Pragma', 'public');
$response->headers->set('Cache-Control', 'maxage=1');
$response->headers->set('Content-Disposition', $dispositionHeader);
**/
// YOU NEED TO GIVE YOUR OWN RESPONSE, OTHERWISE THIS WILL THROW ERROR
return 'Create your own response';
}
If everything went right, the generated excel file will be a normal excel file with the following content (and no corrupted file message):
Everything should be in order now, however you need to test your code saving the file with the save method instead of a streamed response.
If your file still corrupted, you should definitely reevaluate your code and check that you're rendering with sense data because that may be the problem.
You can get more examples of charts if you visit the official phpexcel github repository here, there are a lot of examples of how to use different types of charts. May the force be with you !
