Can't write in an existing xlsx file: "Cell coordinate can not be zero-length string"

[verng95]

This is:

• a bug report

What is the current behavior?

PhpOffice\PhpSpreadsheet\Exception:
Cell coordinate can not be zero-length string

  at \vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Cell\Coordinate.php:39
  at PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString()
     (\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Cell\Coordinate.php:125)
  at PhpOffice\PhpSpreadsheet\Cell\Coordinate::absoluteCoordinate()
     (\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Cell\Coordinate.php:102)
  at PhpOffice\PhpSpreadsheet\Cell\Coordinate::absoluteReference()
     (\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx\DefinedNames.php:181)
  at PhpOffice\PhpSpreadsheet\Writer\Xlsx\DefinedNames->writeNamedRangeForPrintArea()
     (\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx\DefinedNames.php:48)
  at PhpOffice\PhpSpreadsheet\Writer\Xlsx\DefinedNames->write()
     (\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx\Workbook.php:56)
  at PhpOffice\PhpSpreadsheet\Writer\Xlsx\Workbook->writeWorkbook()
     (\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx.php:386)
  at PhpOffice\PhpSpreadsheet\Writer\Xlsx->save()
     (\src\Module\Container\Controller\Staff\ContainerMovementController.php:205)
  at App\Module\Container\Controller\Staff\ContainerMovementController->print()
     (\vendor\symfony\http-kernel\HttpKernel.php:152)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw()
     (\vendor\symfony\http-kernel\HttpKernel.php:74)
  at Symfony\Component\HttpKernel\HttpKernel->handle()
     (\vendor\symfony\http-kernel\Kernel.php:202)
  at Symfony\Component\HttpKernel\Kernel->handle()
     (\vendor\symfony\runtime\Runner\Symfony\HttpKernelRunner.php:35)
  at Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run()
     (\vendor\autoload_runtime.php:29)
  at require_once('\\vendor\\autoload_runtime.php')
     (\public\index.php:29)    

What are the steps to reproduce?

<?php

$reader = new Xlsx();
$xlsx = $reader->load('path to xlsx');

(new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($xlsx))->save('php://output');

What features do you think are causing the issue

• Writer

Which versions of PhpSpreadsheet and PHP are affected?

Version PHPSpreadsheet - 1.23.0. PHP - 8.0.1

File:
example.xlsx

[MarkBaker]

Thanks for the report, and for providing the example file.

The issue is actually in the Reader, which loads an invalid print area range that the writer then cannot save

<definedName name="_xlnm.Print_Area" localSheetId="3">'0_Stammdaten'!#REF!</definedName>

[verng95]

The issue is actually in the Reader, which loads an invalid print area range that the writer then cannot save

Can you tell me how to solve this problem? I tried setPrintArea, but it didn't help me.

[MarkBaker]

I'm working on the fix now; if it's an invalid range for the print area then that data won't get loaded to PhpSpreadsheet, so it won't corrupt our internal data structures

[MarkBaker]

Although as a temporary measure, you should be able to call:

$xlsx->getSheetByName('0_Stammdaten')->getPageSetup()->clearPrintArea();

before saving

[verng95]

Although as a temporary measure, you should be able to call:

I was in a hurry. Unfortunately, it didn't help. A broken file is recorded that does not open.

[MarkBaker]

$inputFileType = 'Xlsx';
$fileBaseName = 'printAreaExample';
$inputFileName = __DIR__ . "/../{$fileBaseName}.xlsx";

$reader = IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load($inputFileName);

$spreadsheet->getSheetByName('0_Stammdaten')->getPageSetup()->clearPrintArea();

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save("{$fileBaseName}Write.xlsx");

creates a valid file for me; what else might you be doing? And exactly what error is MS Excel giving when you try to open the saved file?

[verng95]

creates a valid file for me; what else might you be doing? And exactly what error is MS Excel giving when you try to open the saved file?

Error: The error may occur when an Excel file becomes corrupt. You may try repairing the corrupted Excel file manually by using the built-in 'Open and Repair' feature.

Attach excel:
4-10-2021.xlsx

[MarkBaker]

That's a totally unrelated problem. That's because you're sending a lot of html markup and javascript to the same output stream, beginning with:

<!-- The controller must return a &quot;Symfony\Component\HttpFoundation\Response&quot; object but it returned null. Did you forget to add a return statement somewhere in your controller? (500 Internal Server Error) -->

If I manually edit the resulting output to separate the two blocks (the PhpSpreadsheet stream and the Markup), I get a perfectly ok Excel file, and the following:

[verng95]

If I manually edit the resulting output to separate the two blocks (the PhpSpreadsheet stream and the Markup), I get a perfectly ok Excel file, and the following:

Hm.... Thanks.