php - We found a problem with some content in 'Spreadsheet.xlsx'. Do you want us to try to recover as much as we can?

Question

I have the following basic PHP project (just one file plus composer config):

composer.json

{
    "config": {
        "optimize-autoloader": true,
        "platform": {
            "php": "7.4.9"
        }
    },
    "require": {
        "phpoffice/phpspreadsheet": "1.10.0"
    }
}

index.php

<?php

require_once __DIR__ . '/vendor/autoload.php';
function errorHandler() {
    return true;
}
set_error_handler('errorHandler');

$sheets = array(
    array('index' => 0, 'title' => 'Graph'),
    array('index' => 1, 'title' => 'Data'),
);

$phpSpreadsheetObject = new PhpOfficePhpSpreadsheetSpreadsheet();

foreach ($sheets as $sheet) {
    $name = $sheet['title'];
    if ($sheet['index']) {
        $worksheet[$name] = $phpSpreadsheetObject->createSheet($sheet['index']);
    } else {
        $worksheet[$name] = $phpSpreadsheetObject->getActiveSheet();
    }
    $phpSpreadsheetObject->setActiveSheetIndex($sheet['index']);
    $worksheet[$name]->setTitle($sheet['title']);
}

$sheet = 'Graph'; // !!! SHEET CHANGE

$phpSpreadsheetObject->setActiveSheetIndex(1);
$worksheet[$sheet]->getColumnDimension('A')->setWidth("50");

// Charts

// Clients Chart
$xAxisTickValues = array(new PhpOfficePhpSpreadsheetChartDataSeriesValues('String', "'Data'!A2:A4", null, 3));
$dataSeriesValues = array(new PhpOfficePhpSpreadsheetChartDataSeriesValues('Number', "'Data'!B2:B4", null, 3));
$chartSeries = new PhpOfficePhpSpreadsheetChartDataSeries(
    PhpOfficePhpSpreadsheetChartDataSeries::TYPE_BARCHART, // plotType
    PhpOfficePhpSpreadsheetChartDataSeries::GROUPING_CLUSTERED, // plotGrouping
    range(0, count($dataSeriesValues) - 1), // plotOrder
    [], // plotLabel
    $xAxisTickValues, // plotCategory
    $dataSeriesValues // plotValues
);
$chartSeries->setPlotDirection(PhpOfficePhpSpreadsheetChartDataSeries::DIRECTION_COLUMN);
$plotArea = new PhpOfficePhpSpreadsheetChartPlotArea(null, array($chartSeries));
$title = new PhpOfficePhpSpreadsheetChartTitle('Clients');
$yAxisLabel = new PhpOfficePhpSpreadsheetChartTitle('');
$charts = new PhpOfficePhpSpreadsheetChartChart(
    'clients', // name
    $title, // title
    null, // legend
    $plotArea, // plotArea
    true, // plotVisibleOnly
    0, // displayBlanksAs
    null, // xAxisLabel
    $yAxisLabel // yAxisLabel
);
$charts->setTopLeftPosition('A1');
$charts->setBottomRightPosition('B19');
$worksheet[$sheet]->addChart($charts);

$sheet = 'Data'; // !!! SHEET CHANGE

$phpSpreadsheetObject->setActiveSheetIndex(1);
$dataArray = array(
    1 => array('Date', 'Clients'),
    2 => array(date('m/d/y', strtotime('01/01/2021')), '500'),
    3 => array(date('m/d/y', strtotime('01/02/2021')), '725'),
    4 => array(date('m/d/y', strtotime('01/03/2021')), '930'),
);
foreach (range('A', 'B') as $columnID) {
    $worksheet[$sheet]->getColumnDimension($columnID)->setAutoSize(true);
}
$worksheet[$sheet]->fromArray($dataArray, ' ', 'A1');

// set the first tab as active
$phpSpreadsheetObject->setActiveSheetIndex(0);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename=Spreadsheet.xlsx");
header('Cache-Control: max-age=0');
$objWriter = new PhpOfficePhpSpreadsheetWriterXlsx($phpSpreadsheetObject);
$objWriter->setIncludeCharts(true);
$objWriter->save('php://output');

?>

Setup:

$ composer i

When I visit the url:

http://localhost/index.php

the following Excel file is downloaded:

where you have 2 sheets: { Graph, Data }. The Graph is genreated based on the data on sheet: Data.

So far so good.

My Problem is: When I upgrade:

"phpoffice/phpspreadsheet": "1.10.0" -> "phpoffice/phpspreadsheet": "1.10.1"

(just patch update)

and hit the same url again, I get the following errors when trying to open the generated Excel file:

We found a problem with some content in 'Spreadsheet (1).xlsx'. Do you want us to try to recover as much as we can? if you trust the source of this workbook, click Yes.

and then this other error:

and the graph is not shown.

Any idea what modification I need to do on my code above to get rid of those errors and get the graph rendered?

Thanks!

Answer 1

I found the solution.

On the code above, just replace: 0, // displayBlanksAs -> 'gap', // displayBlanksAs.

On latest versions like: "phpoffice/phpspreadsheet": "1.16", there is a constant defined for this: DataSeries::EMPTY_AS_GAP, // displayBlanksAs.

That constant is not present on earlier versions like: 1.10.1.

Thanks!