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 \PhpOffice\PhpSpreadsheet\Spreadsheet();

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 \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues('String', "'Data'!A2:A4", null, 3));
$dataSeriesValues = array(new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues('Number', "'Data'!B2:B4", null, 3));
$chartSeries = new \PhpOffice\PhpSpreadsheet\Chart\DataSeries(
    \PhpOffice\PhpSpreadsheet\Chart\DataSeries::TYPE_BARCHART, // plotType
    \PhpOffice\PhpSpreadsheet\Chart\DataSeries::GROUPING_CLUSTERED, // plotGrouping
    range(0, count($dataSeriesValues) - 1), // plotOrder
    [], // plotLabel
    $xAxisTickValues, // plotCategory
    $dataSeriesValues // plotValues
);
$chartSeries->setPlotDirection(\PhpOffice\PhpSpreadsheet\Chart\DataSeries::DIRECTION_COLUMN);
$plotArea = new \PhpOffice\PhpSpreadsheet\Chart\PlotArea(null, array($chartSeries));
$title = new \PhpOffice\PhpSpreadsheet\Chart\Title('Clients');
$yAxisLabel = new \PhpOffice\PhpSpreadsheet\Chart\Title('');
$charts = new \PhpOffice\PhpSpreadsheet\Chart\Chart(
    '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 \PhpOffice\PhpSpreadsheet\Writer\Xlsx($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!

try to open the file created with a text editor. Inside you should find some usefull informations on what was wrong durying the creation Usually this error is due to some error messages being output instead of the expected content in the file. — Lelio Faieta, Jan 07 '21 at 09:47
Also, why not use any more recent version? v1.10.1 is more than one year old — Nico Haase, Jan 07 '21 at 10:08
Following link solved above issue https://github.com/jmcnamara/XlsxWriter/issues/739 — Tokci, Aug 30 '23 at 17:07

score 1 · Answer 1 · answered Jan 07 '21 at 10:26

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!

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

1 Answers1