mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-25 20:18:21 +00:00
oleibman
4bf4278a39
Fix #2934. Null is passed to StringHelper::strtolower which expects string. Same problem appears to be applicable to HLOOKUP. I noted the following problem in the code, but will document it here as well. Excel's results are not consistent when a non-numeric string is passed as the third parameter. For example, if cell Z1 contains `xyz`, Excel will return a REF error for function `VLOOKUP(whatever,whatever,Z1)`, but it returns a VALUE error for function `VLOOKUP(whatever,whatever,"xyz")`. I don't think PhpSpreadsheet can match both behaviors. For now, it will return VALUE for both, with similar results for other errors. While studying the returned errors, I realized there is something that needs to be deprecated. `ExcelError::$errorCodes` is a public static array. This means that a user can change its value, which should not be allowed. It is replaced by a constant. Since the original is public, I think it needs to stay, but with a deprecation notice; users can reference and change it, but it will be unused in the rest of the code. I suppose this might be considered a break in functionality (that should not have been allowed in the first place).
197 lines
3.3 KiB
PHP
197 lines
3.3 KiB
PHP
<?php
|
|
|
|
function densityGrid(): array
|
|
{
|
|
return [
|
|
['Density', 'Viscosity', 'Temperature'],
|
|
[0.457, 3.55, 500],
|
|
[0.525, 3.25, 400],
|
|
[0.616, 2.93, 300],
|
|
[0.675, 2.75, 250],
|
|
[0.746, 2.57, 200],
|
|
[0.835, 2.38, 150],
|
|
[0.946, 2.17, 100],
|
|
[1.090, 1.95, 50],
|
|
[1.290, 1.71, 0],
|
|
];
|
|
}
|
|
|
|
return [
|
|
[
|
|
'#N/A',
|
|
1,
|
|
densityGrid(),
|
|
2,
|
|
false,
|
|
],
|
|
[
|
|
'#REF!',
|
|
1,
|
|
'HELLO WORLD',
|
|
2,
|
|
false,
|
|
],
|
|
[
|
|
100,
|
|
1,
|
|
densityGrid(),
|
|
3,
|
|
true,
|
|
],
|
|
[
|
|
'#N/A',
|
|
0.70,
|
|
densityGrid(),
|
|
3,
|
|
false,
|
|
],
|
|
[
|
|
'#N/A',
|
|
0.100,
|
|
densityGrid(),
|
|
2,
|
|
true,
|
|
],
|
|
[
|
|
1.71,
|
|
2,
|
|
densityGrid(),
|
|
2,
|
|
true,
|
|
],
|
|
[
|
|
5,
|
|
'x',
|
|
[
|
|
[
|
|
'Selection column',
|
|
'Value to retrieve',
|
|
],
|
|
['0', 1],
|
|
['0', 2],
|
|
['0', 3],
|
|
['0', 4],
|
|
['x', 5],
|
|
['x', 6],
|
|
['x', 7],
|
|
['x', 8],
|
|
['x', 9],
|
|
],
|
|
2,
|
|
false,
|
|
],
|
|
[
|
|
'#N/A',
|
|
'10y2',
|
|
[
|
|
['5y-1', 2.0],
|
|
['10y1', 7.0],
|
|
['10y2', 10.0],
|
|
],
|
|
2.0,
|
|
],
|
|
[
|
|
'#VALUE!',
|
|
'10y2',
|
|
[
|
|
['5y-1', 2.0],
|
|
['10y1', 7.0],
|
|
['10y2', 10.0],
|
|
],
|
|
-5,
|
|
],
|
|
[
|
|
'#REF!',
|
|
'10y2',
|
|
[
|
|
],
|
|
2.0,
|
|
],
|
|
[
|
|
'#REF!',
|
|
'10y2',
|
|
[
|
|
[2.0],
|
|
[7.0],
|
|
[10.0],
|
|
],
|
|
2.0,
|
|
],
|
|
[
|
|
3.50,
|
|
'Cornflakes',
|
|
[
|
|
['Item Description', 'Price'],
|
|
['Tinned Tomatoes', 0.90],
|
|
['Tinned Tuna', 1.50],
|
|
['Cornflakes', 3.50],
|
|
['Shortcake Biscuits', 1.00],
|
|
['Toothpaste', 4.10],
|
|
['Tinned Baked Beans', 0.99],
|
|
['White Sliced Bread', 0.80],
|
|
],
|
|
2,
|
|
false,
|
|
],
|
|
[
|
|
'E',
|
|
0.52,
|
|
[
|
|
['Lower', 'Upper', 'Grade'],
|
|
[0.00, 0.44, 'F'],
|
|
[0.45, 0.54, 'E'],
|
|
[0.55, 0.64, 'D'],
|
|
[0.65, 0.74, 'C'],
|
|
[0.75, 0.84, 'B'],
|
|
[0.85, 1.00, 'A'],
|
|
],
|
|
3,
|
|
true,
|
|
],
|
|
[
|
|
'E',
|
|
0.52,
|
|
[
|
|
['Lower', 'Upper', 'Grade'],
|
|
[0.00, 0.44, 'F'],
|
|
[0.45, 0.54, 'E'],
|
|
[0.55, 0.64, 'D'],
|
|
[0.65, 0.74, 'C'],
|
|
[0.75, 0.84, 'B'],
|
|
[0.85, 1.00, 'A'],
|
|
],
|
|
3,
|
|
null,
|
|
],
|
|
'issue2934' => [
|
|
'Red',
|
|
102,
|
|
[
|
|
[null, null],
|
|
[102, 'Red'],
|
|
],
|
|
2,
|
|
false,
|
|
],
|
|
'string supplied as index' => [
|
|
'#VALUE!',
|
|
102,
|
|
[
|
|
[null, null],
|
|
[102, 'Red'],
|
|
],
|
|
'xyz',
|
|
false,
|
|
],
|
|
'num error propagated' => [
|
|
'#NUM!',
|
|
102,
|
|
[
|
|
[null, null],
|
|
[102, 'Red'],
|
|
],
|
|
'=SQRT(-1)',
|
|
false,
|
|
],
|
|
];
|