PHPExcel : graph working on LibreOffice but not on Excel

Question

I'm currently using PHPExcel (v1.8.0) to generate an xlxs file with datas and graph.

It works well when I open the file on LibreOffice, but in Excel it throws and error : "Excel found unreadable content in .."

When I "repair" the file in Excel, I have my datas but not the chart, and this message : "Removed Part: /xl/drawings/drawing1.xml part. (Drawing shape)"

Strange fact : when I Save As .xls from LibreOffice, I can read the file with the chart in Excel.

You can download a sample file on my DropBox : download here

Here is the whole code to generate my *.xlsx file :

<?php

require_once('PHPExcel.php');


/* increments $nb times a column letter */
function getCol($col, $nb) {
    for ($i = 0; $i < $nb; $i++) {
        $col++;
    }
    return $col;
}

$objPHPExcel = new PHPExcel();
$objWorksheet = $objPHPExcel->getActiveSheet();

$sheetlabels = array("Groupe d'activité", "Données réelles", "Données simulées");
$sheetdatas = array();
$sheetdatas[] = array_map("utf8_encode", $sheetlabels);
foreach ($groups as $k => $group) {
    $sheetdatas[] = array($group, $data_real[$k]*1, $data_simulated[$k]*1);
}
$sheetdatas[] = array("Total", $total_real*1, $total_simulated*1);


$nbdatas = count($groups);
$startCol = 'B';
$startLine = 25;

$objWorksheet->fromArray($sheetdatas, null, ($startCol . $startLine));

$endCol = getCol($startCol, count($sheetlabels)-1);
foreach(range($startCol, $endCol) as $columnID) {
    $objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
}

$currentLine = $startLine + count($groups) +1;
$objWorksheet->getStyle($startCol . $currentLine . ':' . $endCol . $currentLine)->getFont()->setBold(true);

$styleArray = array(
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN
        ),
    ),
);
$objWorksheet->getStyle($startCol . $startLine . ':' . $endCol . $currentLine)->applyFromArray($styleArray);


$currentCol = getCol($startCol, 1);
$currentLine = $startLine + 1;
$endLine = $startLine + count($groups) + 1;
$objWorksheet->getStyle($currentCol . $currentLine . ':' . $endCol . $endLine)->getNumberFormat()->setFormatCode('# ##0.00');

$currentCol = $startCol;
$dataSeriesLabels = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$' . (++$currentCol) . '$' . $startLine, NULL, 1), //  Données réelles
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$' . (++$currentCol) . '$' . $startLine, NULL, 1)  //  Données simulées
);

$currentLine = $startLine + 1;
$xAxisTickValues = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$' . $startCol . '$' . $currentLine . ':$' . $startCol . '$' . ($currentLine + $nbdatas - 1), NULL, $nbdatas)  //  Tous les groups
);

$dataSeriesValues = array();
$currentCol = $startCol;
$currentLine = $startLine + 1;
foreach ($sheetlabels as $sheetlabel) {
    if ($sheetlabel) {
        $currentCol++;
        $dataSeriesValues[] = new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$' . $currentCol . '$' . $currentLine . ':$' . $currentCol . '$' . ($currentLine + $nbdatas - 1), NULL, $nbdatas);
    }
}

//  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
);

$plotArea = new PHPExcel_Chart_PlotArea(NULL, array($series));

$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
$title = new PHPExcel_Chart_Title('Consolidation ' . utf8_encode($label));

$chart = new PHPExcel_Chart(
    'chart1',       // name
    $title,         // title
    $legend,        // legend
    $plotArea,      // plotArea
    true,           // plotVisibleOnly
    0,              // displayBlanksAs
    NULL,           // xAxisLabel
    $yAxisLabel     // yAxisLabel
);

$chart->setTopLeftPosition('B2');
$chart->setBottomRightPosition('L22');

$objWorksheet->addChart($chart);


$filename = "export_" . $indicator_id . "_" . $date . ".xlsx";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setIncludeCharts(true);
$objWriter->save($dir . '/' . $filename);

?>

I have no clue where I made a mistake at this point.

---

Resolution

Alright, I figured out what the problem is.

Seems like you have to specify the plotDirection in order to not have an issue in Excel

$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
        );
        $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_VERTICAL);

I made a pull request to the GithHub project to set a plotDirection default value.

Answer 1

Resolution

Alright, I figured out what the problem is.

Seems like you have to specify the plotDirection in order to not have an issue in Excel

$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
        );
        $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_VERTICAL);

I made a pull request to the GithHub project to set a plotDirection default value.