3

I am migrating the existing/working app from PHPExcel (phpoffice/phpexcel: 1.8.0) to PHPSpreadsheet and I have hit an issue when generating Excel file containing one or more charts.

Without any addChart calls, the generated file is valid, but as soon as I add a chart, the resulting file becomes invalid. Excel does attempt to recover the data, which succeeds but without a chart.

Excel recovery tool shows:

Excel completed file level validation and repair. 
Some parts of this workbook may have been repaired or discarded.
Removed Part: /xl/drawings/drawing1.xml part.  (Drawing shape)

Current using: PHP 7.1 and phpoffice/phpspreadsheet: "1.10.1"

My code is very similar to Example for PieChart, which I am attempting to add.

Part of the code which generates PieChart:

  • $ageCounterRowIndex has value 6
  • $expectedPointCount has value 4
  • My data tick values are located in D3:D6
  • My values are located in E3:E6
$dataSeriesLabels = [];

$xAxisTickValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, "$quotedSheetTitle\$D\$3:\$D\$$ageCounterRowIndex", $expectedPointCount)
];

$dataSeriesValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, "$quotedSheetTitle\$E\$3:\$E\$$ageCounterRowIndex", $expectedPointCount),
];

$dataSeries = new DataSeries(
    DataSeries::TYPE_PIECHART,
    NULL, 
    range(0, count($dataSeriesValues) - 1), 
    $dataSeriesLabels, 
    $xAxisTickValues, 
    $dataSeriesValues 
);

$pieLayout = new Layout();
$pieLayout->setShowVal(TRUE)->setShowPercent(TRUE);

$plotArea = new PlotArea($pieLayout, [ $dataSeries ]);
$legend = new Legend(Legend::POSITION_RIGHT, NULL, FALSE);

$chart = new Chart(
    'piechart_' . ($sheetIndex - 1), 
    null,
    $legend, 
    $plotArea, 
    true, 
    0, 
    NULL, 
    NULL 
);

$chart->setTopLeftPosition('G2');
$chart->setBottomRightPosition('L15');
$sheet->addChart($chart);
Muhammad Furqan
  • 33
  • 1
  • 6
Jovan Perovic
  • 19,846
  • 5
  • 44
  • 85

1 Answers1

7

Changing the displayBlanksAs parameter in the New Chart call from 0 to 'gap' solved this issue for me.

I found this bug report about it on the PhpSpreadsheet repository

$chart = new Chart(
    'piechart_' . ($sheetIndex - 1), 
    null,
    $legend, 
    $plotArea, 
    true, 
    0, 
    NULL, 
    NULL 
);

becomes 

$chart = new Chart(
    'piechart_' . ($sheetIndex - 1), 
    null,
    $legend, 
    $plotArea, 
    true, 
    'gap', 
    NULL, 
    NULL 
);
kramar
  • 131
  • 2
  • 3