Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

HLookup::hLookupSearch(..) sends wrong arguments to LookupBase::checkMatch(..). #2123

Closed
majermi4 opened this issue May 27, 2021 · 3 comments
Closed

HLookup::hLookupSearch(..) sends wrong arguments to LookupBase::checkMatch(..). #2123

majermi4 opened this issue May 27, 2021 · 3 comments

Comments

@majermi4
Copy link

This is:

- [X] a bug report

What is the expected behavior?

Calling $cell->getCalculatedValue() ($cell is instance of PhpOffice\PhpSpreadsheet\Cell) should work but it fails with the latest master.

What is the current behavior?

After $cell->getCalculatedValue() is called, the program fails with following error:

TypeError : PhpOffice\PhpSpreadsheet\Calculation\LookupRef\LookupBase::checkMatch(): Argument #4 ($rowKey) must be of type int, string given, called in 
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef/HLookup.php on line 74
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef/LookupBase.php:25
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef/HLookup.php:74
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef/HLookup.php:45
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:4776
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:3469
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:3262
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:259
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:5192
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:4669
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:3469
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:3262
 xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:259

Here is the $cell variable value before $cell->getCalculatedValue() is called:

Screenshot 2021-05-27 at 17 25 14

And here are the values from a breakpoint right before the error is triggered at
HLookup.php:73, PhpOffice\PhpSpreadsheet\Calculation\LookupRef\HLookup::hLookupSearch()

Screenshot 2021-05-27 at 17 30 04

What are the steps to reproduce?

Unfortunately, I cannot simply share the the excel file that fails because it is from one of our customers but let me know if any more information is needed apart from what was shared above. ;-)

Which versions of PhpSpreadsheet and PHP are affected?

Tested on current master branch with PHP 8.0 and 7.4.
@majermi4
Copy link
Author

This is still and issue for us so I created a test excel sheet to demonstrate the problem.

Running this code:

$spreadsheet = IOFactory::load('./hlookup-test.xlsx');
$sheet = $spreadsheet->getSheet(0);
$cellVal = $sheet->getCell('H8')->getCalculatedValue();

On this excel spreadsheet: hlookup-test.xlsx

Screenshot 2021-06-14 at 16 49 22

Results in:

TypeError {#3754
  #message: "Argument 4 passed to PhpOffice\PhpSpreadsheet\Calculation\LookupRef\LookupBase::checkMatch() must be of the type int, string given, called in xxx/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef/HLookup.php on line 74"
  #code: 0
  #file: "./vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef/LookupBase.php"
  #line: 25
  trace: {
    ./vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef/LookupBase.php:25 { …}
    ./vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef/HLookup.php:74 { …}
    ./vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/LookupRef/HLookup.php:45 { …}
    PhpOffice\PhpSpreadsheet\Calculation\LookupRef\HLookup::lookup() {}
    ./vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:4846 { …}
    ./vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:3539 { …}
    ./vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:3332 { …}
    ./vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:259 { …}
    ./src/MyNamespace/Command/TestExcel.php:30 {
      MyNamespace\Command\TestExcel->execute(InputInterface $input, OutputInterface $output): void
      › $sheet = $spreadsheet->getSheet(0);
      › $cellVal = $sheet->getCell('H8')->getCalculatedValue();
      › 
    }

ping @MarkBaker let me know if I should provide any more information ;-)

@fabioestevesrs
Copy link

Hi!

I had the same problem.

  • Error:
    Fatal error: Uncaught TypeError: Argument 4 passed to PhpOffice\PhpSpreadsheet\Calculation\LookupRef\LookupBase::checkMatch() must be of the type int, string given

  • Data organized in method extractCellRange (class vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php):
    array(46) {
    [2]=>
    array(7) {
    ["B"]=>
    string(4) "teste"
    ["C"]=>
    string(25) "teste1"
    ["D"]=>
    string(16) "teste1"
    }
    }

Also waiting for a solution to the problem. =)

oleibman added a commit to oleibman/PhpSpreadsheet that referenced this issue Oct 12, 2021
@oleibman
Corrections for HLOOKUP Function
b088cf6 
See issue PHPOffice#2123. HLOOKUP needs to do some conversions between column numbers and letters which it had not been doing.

HLOOKUP tests were performed using direct calls to the function in question rather than in the context of a spreadsheet. This contributed to keeping this error obscured even though there were, in theory, sufficient test cases. The tests are changed to perform in spreadsheet context. For the most part, the test cases are unchanged. One of the expected results was wrong; it has been changed, and a new case added to cover the case it was supposed to be testing.

After getting the HLOOKUP tests in order, it turned out that a test using literal arrays which had been succeeding now failed. The array constructed by the literals are considerably different than those constructed using spreadsheet cells; additional code was added to handle this situation.
@oleibman oleibman mentioned this issue Oct 12, 2021
Merged
5 tasks
oleibman added a commit that referenced this issue Oct 24, 2021
@oleibman
Corrections for HLOOKUP Function (#2330)
3db0b2a 
See issue #2123. HLOOKUP needs to do some conversions between column numbers and letters which it had not been doing.

HLOOKUP tests were performed using direct calls to the function in question rather than in the context of a spreadsheet. This contributed to keeping this error obscured even though there were, in theory, sufficient test cases. The tests are changed to perform in spreadsheet context. For the most part, the test cases are unchanged. One of the expected results was wrong; it has been changed, and a new case added to cover the case it was supposed to be testing.

After getting the HLOOKUP tests in order, it turned out that a test using literal arrays which had been succeeding now failed. The array constructed by the literals are considerably different than those constructed using spreadsheet cells; additional code was added to handle this situation.
@PowerKiKi
Copy link
Member

Fixed in 1.19.0

@ytilotti ytilotti mentioned this issue Nov 10, 2021
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@PowerKiKi @majermi4 @fabioestevesrs