mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-09-09 01:26:51 +00:00
oleibman
4b04cc1c8d
Fix #2581 (not obvious - see next paragraph for explanation). This continues the work of PR #2902 (and also PR #3467) to have errors propagated through function calculations rather than treating them as strings. All text functions, and the concatenation operator, are addressed in this PR. In the original issue, the spreadsheet being loaded uses the result of an unimplemented function as an argument to another function. When `getCalculatedValue` is used on the cell in question, the result is returned as `#VALUE!`. If the cell had just contained a function call to the unimplemented function, getCalculatedValue would have recognized the situation and returned oldCalculatedValue as the result. Not perfect, but good enough most of the time. User would like oldCalculatedValue returned here as well, which seems like a reasonable request. PhpSpreadsheet always returns `#Not Yet Implemented` as the result for a function which it knows about but which is not yet implemented. That is the key to the `Cell` class being able to substitute oldCalculatedValue in the first place. However, in order to do that for the issue in question, that result has to be propagated to any functions for which the result is an argument. I don't want to add unimplemented to the list of known error codes, but I am willing to add a parameter to `ErrorValue::isError` to indicate whether that value should be considered an error (default is "no"). The first use of that new parameter would be by the text functions. They go through a common Helper routine, so it is pretty easily implemented. And, as it turns out, most of the text functions do not currently propagate errors, e.g. if A1 results in a value error, `=LEFT(A1,2)` will result in `#V` rather than `#VALUE!`. With this PR, they will now be handled correctly.
97 lines
5.3 KiB
PHP
97 lines
5.3 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\TextData;
|
|
|
|
class ErrorPropagationTest extends AllSetupTeardown
|
|
{
|
|
public function testErrorPropagation(): void
|
|
{
|
|
$sheet = $this->getSheet();
|
|
$sheet->getCell('A1')->setValue('=ABS("X")');
|
|
self::assertSame('#VALUE!', $sheet->getCell('A1')->getCalculatedValue());
|
|
$sheet->getCell('A2')->setValue('=SQRT(-1)');
|
|
self::assertSame('#NUM!', $sheet->getCell('A2')->getCalculatedValue());
|
|
$sheet->getCell('A3')->setValue('=3/0');
|
|
self::assertSame('#DIV/0!', $sheet->getCell('A3')->getCalculatedValue());
|
|
$sheet->getCell('A4')->setValue('=XXXX()');
|
|
self::assertSame('#NAME?', $sheet->getCell('A4')->getCalculatedValue());
|
|
$sheet->getCell('A5')->setValue('=ABS("X")');
|
|
self::assertSame('#VALUE!', $sheet->getCell('A5')->getCalculatedValue());
|
|
|
|
$sheet->getCell('B1')->setValue('=UPPER(A1)');
|
|
self::assertSame('#VALUE!', $sheet->getCell('A1')->getCalculatedValue());
|
|
$sheet->getCell('B2')->setValue('=LOWER(A2)');
|
|
self::assertSame('#NUM!', $sheet->getCell('A2')->getCalculatedValue());
|
|
$sheet->getCell('B3')->setValue('=PROPER(A3)');
|
|
self::assertSame('#DIV/0!', $sheet->getCell('A3')->getCalculatedValue());
|
|
|
|
$sheet->getCell('C2')->setValue('=CHAR(A2)');
|
|
self::assertSame('#NUM!', $sheet->getCell('C2')->getCalculatedValue());
|
|
$sheet->getCell('C3')->setValue('=CODE(A3)');
|
|
self::assertSame('#DIV/0!', $sheet->getCell('C3')->getCalculatedValue());
|
|
|
|
$sheet->getCell('D1')->setValue('=CONCATENATE(A1,A1)');
|
|
self::assertSame('#VALUE!', $sheet->getCell('D1')->getCalculatedValue());
|
|
$sheet->getCell('D2')->setValue('=TEXTJOIN(",",TRUE,A2,A3)');
|
|
self::assertSame('#NUM!', $sheet->getCell('D2')->getCalculatedValue());
|
|
$sheet->getCell('D3')->setValue('=REPT(A3,3)');
|
|
self::assertSame('#DIV/0!', $sheet->getCell('D3')->getCalculatedValue());
|
|
$sheet->getCell('D4')->setValue('=CONCAT(A4,A4)');
|
|
self::assertSame('#NAME?', $sheet->getCell('D4')->getCalculatedValue());
|
|
$sheet->getCell('D5')->setValue('="X"&A4');
|
|
self::assertSame('#NAME?', $sheet->getCell('D5')->getCalculatedValue());
|
|
$sheet->getCell('D6')->setValue('=A2&"X"');
|
|
self::assertSame('#NUM!', $sheet->getCell('D6')->getCalculatedValue());
|
|
|
|
$sheet->getCell('E1')->setValue('=LEFT(A1)');
|
|
self::assertSame('#VALUE!', $sheet->getCell('E1')->getCalculatedValue());
|
|
$sheet->getCell('E2')->setValue('=RIGHT(A2)');
|
|
self::assertSame('#NUM!', $sheet->getCell('E2')->getCalculatedValue());
|
|
$sheet->getCell('E3')->setValue('=MID(A3,2,2)');
|
|
self::assertSame('#DIV/0!', $sheet->getCell('E3')->getCalculatedValue());
|
|
$sheet->getCell('E4')->setValue('=TEXTBEFORE(A4,"M")');
|
|
self::assertSame('#NAME?', $sheet->getCell('E4')->getCalculatedValue());
|
|
$sheet->getCell('E5')->setValue('=TEXTAFTER(A5,"U")');
|
|
self::assertSame('#VALUE!', $sheet->getCell('E5')->getCalculatedValue());
|
|
|
|
$sheet->getCell('F1')->setValue('=VALUETOTEXT(A1)');
|
|
self::assertSame('#VALUE!', $sheet->getCell('F1')->getCalculatedValue());
|
|
$sheet->getCell('F2')->setValue('=DOLLAR(A2)');
|
|
self::assertSame('#NUM!', $sheet->getCell('F2')->getCalculatedValue());
|
|
$sheet->getCell('F3')->setValue('=FIXED(A3)');
|
|
self::assertSame('#DIV/0!', $sheet->getCell('E3')->getCalculatedValue());
|
|
$sheet->getCell('F4')->setValue('=TEXT(A4,"M")');
|
|
self::assertSame('#NAME?', $sheet->getCell('F4')->getCalculatedValue());
|
|
$sheet->getCell('F5')->setValue('=VALUE(A2)');
|
|
self::assertSame('#NUM!', $sheet->getCell('F5')->getCalculatedValue());
|
|
$sheet->getCell('F6')->setValue('=NUMBERVALUE(A3)');
|
|
self::assertSame('#DIV/0!', $sheet->getCell('F6')->getCalculatedValue());
|
|
|
|
$sheet->getCell('G1')->setValue('=REPLACE("oldtext",2,2,A1)');
|
|
self::assertSame('#VALUE!', $sheet->getCell('G1')->getCalculatedValue());
|
|
$sheet->getCell('G2')->setValue('=SUBSTITUTE(A2,"U","V")');
|
|
self::assertSame('#NUM!', $sheet->getCell('G2')->getCalculatedValue());
|
|
|
|
$sheet->getCell('H1')->setValue('=FIND(A1, "U")');
|
|
self::assertSame('#VALUE!', $sheet->getCell('H1')->getCalculatedValue());
|
|
$sheet->getCell('H2')->setValue('=SEARCH(A2,"U")');
|
|
self::assertSame('#NUM!', $sheet->getCell('H2')->getCalculatedValue());
|
|
|
|
$sheet->getCell('I1')->setValue('=LEN(A1)');
|
|
self::assertSame('#VALUE!', $sheet->getCell('I1')->getCalculatedValue());
|
|
$sheet->getCell('I2')->setValue('=EXACT(A2,A2)');
|
|
self::assertSame('#NUM!', $sheet->getCell('I2')->getCalculatedValue());
|
|
$sheet->getCell('I3')->setValue('=T(A3)');
|
|
self::assertSame('#DIV/0!', $sheet->getCell('I3')->getCalculatedValue());
|
|
$sheet->getCell('I4')->setValue('=TEXTSPLIT(A4,"M")');
|
|
self::assertSame('#NAME?', $sheet->getCell('I4')->getCalculatedValue());
|
|
|
|
$sheet->getCell('J1')->setValue('=TRIM(A1)');
|
|
self::assertSame('#VALUE!', $sheet->getCell('J1')->getCalculatedValue());
|
|
$sheet->getCell('J2')->setValue('=CLEAN(A2)');
|
|
self::assertSame('#NUM!', $sheet->getCell('J2')->getCalculatedValue());
|
|
}
|
|
}
|