calculate error for CONCATENATE

[hbh112233abc]

This is:

- [x] 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?

test.xlsx
the column B set =CONCATENATE(@A:A,"-",@C:C) and the table is like below:

A	B	C
a	a-1	1
b	b-2	2
c	c-3	3

What is the current behavior?

read the test.xlsx and the active sheet toArray, B column error:

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:

<?php

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

$file        = __DIR__ . '/test.xlsx';
$reader      = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load($file);
$sheetData   = $spreadsheet->getActiveSheet()->toArray();
var_dump($sheetData);

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

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

test.xlsx save as test.xls, read by PhpOffice\PhpSpreadsheet\Reader\Xls use 3min (too long) return the sheet data, the sheet data is the same with error calculate.

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.29.0
PHP 8.0.30

[oleibman]

This is a variation on a theme. The at-sign doesn't actually exist in the formula - Excel is adding it. For an explanation of what it does, see #3659 (comment). I still have been unable to figure out why it does or doesn't add it. This has come up several times (see issue #3901 or issue #3708 among others). Yours would be the first report where somebody likes this "feature". At any rate, it looks like PhpSpreadsheet is trying to calculate the formula that's actually there. and doesn't wind up with the correct result; and it also appears that it doesn't handle the case where a leading at-sign is actually there (Calculation engine throws exception). I'll look into it, but I wouldn't expect a quick resolution.

In case it helps, you can reformulate your B cells as the following to get the same result in Excel, and PhpSpreadsheet will handle it correctly:

=CONCAT(INDIRECT("A"&ROW()), "-", INDIRECT("C"&ROW()))

[oleibman]

I've stumbled upon an interesting result. Microsoft provides the following note about CONCATENATE - "In Excel 2016, Excel Mobile, and Excel for the web, this function has been replaced with the CONCAT function. Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT from now on. This is because CONCATENATE may not be available in future versions of Excel." Like Microsoft, we will continue to support it.

Now for the interesting part. The CONCATENATE function (and, it would appear, the ampersand concatenate operator) gives the result you see. OTOH, the CONCAT function gives the same result as PhpSpreadsheet. So, intentionally or not, CONCATENATE and CONCAT are not the same function in Excel. PhpSpreadsheet currently executes the same code for both. PhpSpreadsheet does seem to get the correct result for the concatenate operator when results are returned as arrays.

[oleibman]

The difference between CONCATENATE and CONCAT is how they handle arrays (usually in the form of cell ranges). I have reworked CONCATENATE as part of PR #3962 with other array-related changes. The PR is still in draft status, but I hope to "make it real" within a week or two.