Fixes a bug when doing a HLOOKUP on a single row.
```php
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
/**
* Single row.
*/
$singleRow = "=HLOOKUP(10, {5, 10, 15}, 1, 0)";
$sheet->getCell('A1')->setValue($singleRow);
// Should echo 10, but echos '#N/A' and some PHP notices and warnings.
echo $sheet->getCell('A1')->getCalculatedValue() . PHP_EOL;
/**
* Multiple rows.
*/
$multipleRows = "=HLOOKUP(10, {5, 10, 15; 20, 25, 30}, 1, 0)";
$sheet->getCell('A2')->setValue($multipleRows);
// Should echo: 10 and also does.
echo $sheet->getCell('A2')->getCalculatedValue() . PHP_EOL;
```
Co-authored-by: Mark Baker <mark@lange.demon.co.uk>
Sheet titles containing " " or "!" will be quoted in formulas. This commit
fixes the lookup of sheets with this kind of title by trimming the quotes
during the lookup.
Without this any defined range referencing a sheet with " " or "!" in the title
name will be lost when reading the workbook from file.
Fixes#928
Closes 930
* Highlight VLOOKUP bug with a new test
* Remove useless statements + fix VLOOKUP bug
Please not that we have still inconsistencies in Excel (See LOOKUP and
VLOOKUP.php test files)
* Base strtolower on our StringHelper in LookupRef
- Fix ISFORMULA() function to work with a cell reference to another worksheet
- Added calculation engine support for the new functions that were added in MS Excel 2013 and MS Excel 2016
- Text Functions
- CONCAT() Synonym for CONCATENATE()
- NUMBERVALUE() Converts text to a number, in a locale-independent way
- UNICHAR() Synonym for CHAR() in PHPSpreadsheet, which has always used UTF-8 internally
- UNIORD() Synonym for ORD() in PHPSpreadsheet, which has always used UTF-8 internally
- TEXTJOIN() Joins together two or more text strings, separated by a delimiter
- Logical Functions
- XOR() Returns a logical Exclusive Or of all arguments
- Date/Time Functions
- ISOWEEKNUM() Returns the ISO 8601 week number of the year for a given date
- Lookup and Reference Functions
- FORMULATEXT() Returns a formula as a string
- Engineering Functions
- ERF.PRECISE() Returns the error function integrated between 0 and a supplied limit
- ERFC.PRECISE() Synonym for ERFC
- Math and Trig Functions
- SEC() Returns the secant of an angle
- SECH() Returns the hyperbolic secant of an angle
- CSC() Returns the cosecant of an angle
- CSCH() Returns the hyperbolic cosecant of an angle
- COT() Returns the cotangent of an angle
- COTH() Returns the hyperbolic cotangent of an angle
- ACOT() Returns the cotangent of an angle
- ACOTH() Returns the hyperbolic cotangent of an angle
- Financial Functions
- PDURATION() Calculates the number of periods required for an investment to reach a specified value
- RRI() Calculates the interest rate required for an investment to grow to a specified future value
Arne Jørgensen