PHPExcel chart included imported file's output is corrupt

[ZoliQua]

Asked on Stackowerflow http://stackoverflow.com/questions/36433126/phpexcel-chart-included-imported-files-output-is-corrupt as the same.

I'm not sure if it is the bug of PHPExcel, but please help me if you can

I'm a biologist and try to handle a ton of data from FlowCytometry then handling it with Excel. I have exported my files in csv then I tried to put them in a pre created Excel file with php. I figured out when I read and write exactly the same Excel file with PHPExcel PHP extension it doesn't handles my charts the output file is corrupt, and Excel prompting to fix and fixes it without the charts.

I simplified my files without any new input and produces the same results, can you help me to fix this?

My simplified code:

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
ini_set('max_execution_time', '-1');
ini_set('memory_limit', '512M');
date_default_timezone_set('Europe/London');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** Include PHPExcel */
require_once dirname(__FILE__) . '/../PHPExcel/Classes/PHPExcel.php';

$txtOutputExcelName = "Histogram.xlsx";

// Create new PHPExcel object
echo date('H:i:s') , " Create new PHPExcel object from import" , EOL;

// Sample File Name that is going to be cloned
$inputFileName = './sample/Analysis_sample.xlsx';

$objPHPExcel = new PHPExcel();
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setIncludeCharts(TRUE);
$objPHPExcel = $objReader->load($inputFileName);

// 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($txtOutputExcelName);

My simplified Excel file is here: https://dl.dropboxusercontent.com/u/4860217/Analysis_sample.xlsx

Thank you for your help, Zoltan

• Mac OS X 10.11.4
• PHP version 7.0.0
• PHPExcel 1.8.1

[tangix]

1.7.8 was the last version I had working charts in.

[MarkBaker]

@tangix Please explain that comment; charts work in all tests now as correctly (or better) than in 1.7.8

If you make a sweeping comment like that, please provide specific examples to back up exactly what no longer works

[tangix]

@MarkBaker I have a project where I open a template Excel-file created in Excel. I add some values and setup the graph in it. Save it back to Excel. This works just fine with 1.7.8. In 1.7.9 and 1.8.0 the resulting file is corrupt and the Excel diagnostics points to the chart.

I have sent you some example files.

[MarkBaker]

Perhaps I need a bit more than that, because the 32chartreadwrite.php examples for pie charts all run as expected, and MS Excel makes no complaints when opening the file

[tangix]

This is the code I am running.

$objReader = PHPExcel_IOFactory::createReader("Excel2007");

/** @var PHPExcel $excel */
$excel = $objReader->load('template_swe.xlsx');

$excel->getActiveSheet()->setCellValue('B19', 193000);
$excel->getActiveSheet()->setCellValue('B26', 34, 45);
$excel->getActiveSheet()->setCellValue('B25', 65445);
$excel->getActiveSheet()->setCellValue('B27', 47600);
$excel->getActiveSheet()->getStyle('B26')->getNumberFormat()->setFormatCode('0.00');
$excel->getActiveSheet()->getStyle('B25')->getNumberFormat()->setFormatCode('0');

$excel->getActiveSheet()->setCellValue('F19', 'Beräknat i COPCALC. Ort ' . $stadmodell['namn']);
$excel->getActiveSheet()->setCellValue('F25', 'Beräknat i COPCALC. Värmepump ');
$excel->getActiveSheet()->setCellValue('F26', 'Beräknat i COPCALC');
$excel->getActiveSheet()->setCellValue('F27', 'Uppskattat som 17000 * maximal eleffekt');
$excel->getActiveSheet()->setCellValue('A2', 'COPCALC referens: ');

$excel->getProperties()->setCreator("COPCALC " . "SED_DEV_ID")
    ->setLastModifiedBy("Mattias Sandström")
    ->setTitle("Ekonomisk Beräkning")
    ->setSubject("Ekonomisk Beräkning")
    ->setDescription("Ekonomisk beräkning baserat på resultatet från COPCALC");

$excel->getSecurity()->setLockWindows(TRUE);
$excel->getSecurity()->setLockStructure(TRUE);
$excel->getSecurity()->setWorkbookPassword("PHPExcel");

// Set sheet security

$excel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
$excel->getActiveSheet()->getProtection()->setSheet(TRUE); // This should be enabled in order to enable any of the following!
$excel->getActiveSheet()->getProtection()->setSort(TRUE);
$excel->getActiveSheet()->getProtection()->setInsertRows(TRUE);
$excel->getActiveSheet()->getProtection()->setFormatCells(TRUE);
$excel->getActiveSheet()->getStyle('B10:B15')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
$excel->getActiveSheet()->getStyle('B17:B18')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
$excel->getActiveSheet()->getStyle('B22:B24')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
$excel->getActiveSheet()->getStyle('B27')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
$excel->getActiveSheet()->getStyle('B29')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

$excel->setActiveSheetIndex(1);
$excel->getActiveSheet()->getProtection()->setSheet(TRUE); // This should be enabled in order to enable any of the following!
$excel->getActiveSheet()->getProtection()->setSort(TRUE);
$excel->getActiveSheet()->getProtection()->setInsertRows(TRUE);
$excel->getActiveSheet()->getProtection()->setFormatCells(TRUE);

$excel->setActiveSheetIndex(0);

// Rita upp diagrammet
$objWorksheet = $excel->getActiveSheet();
$dataseriesLabels = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Kalkyl!$P$3', NULL, 1),
    new PHPExcel_Chart_DataSeriesValues('String', 'Kalkyl!$Q$3', NULL, 1)
);

$xAxisTickValues = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Kalkyl!$A$5:$A$54', NULL, 50),);
$dataSeriesValues = array(
    new PHPExcel_Chart_DataSeriesValues('Number', 'Kalkyl!$P$5:$P$54', NULL, 50),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Kalkyl!$Q$5:$Q$54', NULL, 50)
);
$dataSeriesValues[0]->setPointMarker('none');
$dataSeriesValues[1]->setPointMarker('none');

$series1 = new PHPExcel_Chart_DataSeries(
    PHPExcel_Chart_DataSeries::TYPE_SCATTERCHART,       // plotType
    NULL,  // plotGrouping
    range(0, count($dataSeriesValues) - 1),          // plotOrder
    $dataseriesLabels,                             // plotLabel
    $xAxisTickValues,                               // plotCategory
    $dataSeriesValues,                              // plotValues
    TRUE,
    PHPExcel_Chart_DataSeries::STYLE_SMOOTHMARKER
);

$plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series1));
$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, FALSE);
$title = new PHPExcel_Chart_Title('Real vinst per år');

$chart = new PHPExcel_Chart(
    'chart1',       // name
    $title,         // title
    $legend,        // legend
    $plotarea,      // plotArea
    TRUE,           // plotVisibleOnly
    0,              // displayBlanksAs
    new PHPExcel_Chart_Title('År'),           // xAxisLabel
    new PHPExcel_Chart_Title('Kronor')            // yAxisLabel
);

$chart->setTopLeftPosition('A32');
$chart->setBottomRightPosition('M50');
$objWorksheet->addChart($chart);

$excel->setActiveSheetIndex(0);

$objWriter = PHPExcel_IOFactory::createWriter($excel, "Excel2007");
$objWriter->setIncludeCharts(TRUE);
$ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
$filename = "output_$v.xlsx";

$objWriter->save($filename);

[ZoliQua]

Hi,

Can I try & test the version 1.7.8, whether it works?

Thank you, Zoltan

[tangix]

It's available at the old codeplex site: https://phpexcel.codeplex.com/releases/view/96183