Phpspreadsheet: Shorthand table references are not supported

[garnold]

This is:

- [🐛] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

The formula =VLOOKUP(D2,Sheet2!A:B,2,FALSE) is valid in Excel, but throws an error in PhpSpreadsheet.

Changing the formula to include the row, ie. =VLOOKUP(D2,Sheet2!A$1:B$10,2,FALSE) resolves the error.

What is the current behavior?

Exception is thrown: Sheet1 !C2 -> Invalid cell coordinate A

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

Given Example.xlsx

<?php

require __DIR__ . '/vendor/autoload.php';

            $excel = \PhpOffice\PhpSpreadsheet\IOFactory::load('Example.xlsx');
            foreach ($excel->getActiveSheet()->toArray() as $fields) {
                fputcsv(STDOUT, $fields);
            }

Which versions of PhpSpreadsheet and PHP are affected?

• PHP: All
• PhpSpreadsheet 1.3.1

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[seyfer]

I faced the same issue I guess.
dates!B3 -> Formula Error: An unexpected error occured
for formula
=EOMONTH(calendar_table[[#This Row],[start]],0)

[MarkBaker]

Not the same issue: Your formula uses a 'structured reference' ([[#This Row],[start]]), a formula operation only introduced in Excel 2016 and supported in higher versions of MS Excel (https://support.office.com/en-us/article/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e?ocmsassetID=HA010155686&CorrelationId=cd2a1ecc-ba3e-4c8f-be72-6d16eb7e0714&ui=en-US&rs=en-US&ad=US), which isn't supported by the older calculation engine.

The original issue used a 'column range' (A:B); a totally different feature of MS Excel, that has been around a lot longer... the current PHPSPreadsheet calculation engine supports 'range references' (A1:B2) but not row or column references (1:2 or A:B).

I have been working for some time on a complete rewrite of the calculation engine; but it is still a long way from being ready, and while it is designed to support both structured references and row/column ranges in formulae (as well as a whole range of other new functionality), it's still a long way from being production ready.

[seyfer]

@MarkBaker thank you for the descriptive answer. Now I can address it to my PM as he wanted to use template file for excel with this functionality and Auto tables, which is not supported too.

Shall I create another issue for my case? Or doesn't make sense?

[JakobEichler]

Hi I am writing a value to a cell with this code.

                $sheetAnalysis->setCellValue(
                    Coordinate::stringFromColumnIndex( $col ) . $row,
                    '=Votes.' . strtoupper(Coordinate::stringFromColumnIndex( 2+$membersCount+1 )) . ( ( $col-2 ) * $rowsPerCompany + 4 + $itemCount-1 )
                );

The output in the Excel file for example is: =votes.j5

Despite I used strtoupper a lower case varriant of my value is written to the generated sheet.
In case of a lower-case letter libreoffice calc outputs a 0 and in case of an upper case letter, I see my desired referenced value.

How can I reference my value correctly with Spreadsheet?