Invalid cell coordinate with dot in sheet name and filter

[misel228]

This is:

- [x] a bug report
- [ ] a feature request
- [x] **not** a usage question 

What is the expected behavior?

The ODS address from the filter range is properly translated from
'test.bug'.A1:'test.bug'.A2 ($openOfficeAddress) to

'test.bug'!A1:A2 ($excelAddress)

What is the current behavior?

The address range above is translated to this address range

string(20) "'test!bug'!A1:bug'A2"

and then triggers an "Invalid cell coordinate BUG'A2 ..."

What are the steps to reproduce?

Create an ODS file with a sheet name that contains a dot '.' and has >=1 auto filter set (see example file)

<?php

require 'vendor/autoload.php';
$file_name = 'bugtest.ods';

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($file_name);
$spreadsheet = $reader->load($file_name);

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

As far as I can tell, the error is in one of the Ods\FormulaTranslator method "convertToExcelRange". One of the regular expressions need to be modified to match the single-quote escaped notation with dots.

PhpSpreadsheet/src/PhpSpreadsheet/Reader/Ods/FormulaTranslator.php

Line 16 in 7456b46

$excelAddress = (string) preg_replace(

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

I have only tested the ODS file format

Which versions of PhpSpreadsheet and PHP are affected?

Tested in PHPSpreadSheet 3.7 with PHP 8.2.26

bugtest.ods

[misel228]

I can work with it by simply renaming the sheet. But I think - at the very least, there should be a warning that dots are not supported (yet).

[oleibman]

I do not see convertToExelRange. I think the problem you are reporting is in convertToExcelAddressValue. I think I have a solution for that, but, unsurprisingly, convertToExcelFormulaValue has the same problem, and the solution for Formula seems more difficult.

[misel228]

I do not see convertToExelRange. I think the problem you are reporting is in convertToExcelAddressValue.

My apologies. It was quite late last night but you're right. The function I meant is called "convertToExcelAddressValue"