1
Vote

VLOOKUP fails due to locale issues

description

Excel (at least in its german version) seems to sort german umlauts in a different manner than PHPs uasort(). This can cause big trouble when using VLOOKUP with $not_exact_match == true.

AFAIKS, a quick bugfix would be to use uasort() in LookupRef.php, line 653 in any case, regardless of the value of $not_exact_match (besides, the following code does not seem to efficient to me; perhaps some binary search could speed up VLOOKUP references quite a lot).

I have a small test file to reproduce the problem, however the upload does not work right now... I'll try again later on.

file attachments

comments

SlowFox wrote Oct 11, 2012 at 10:47 AM

The following script can reproduce the problem in connection with the attached excel file. In Excel B1, B2 and B3 all show "ST", but PHPExcel sorts different ("Stück" after "Stunde") and thus evaluates B2 and B3 to "HUR".

<?php

require_once 'phpexcel/Classes/PHPExcel.php';


$type = PHPExcel_IOFactory::identify('test.xlsx');
$reader = PHPExcel_IOFactory::createReader($type);
$handle = $reader->load('test.xlsx');
$sheet = $handle->getSheet();

$rowIterator = new PHPExcel_Worksheet_RowIterator($sheet);

foreach ($rowIterator as $row) {
    $cellIterator = $row->getCellIterator();
    foreach ($cellIterator as $cell) {
            var_dump($cell->getValue());
            var_dump($cell->getCalculatedValue());
            echo "\n";
    }
}

?>

Sign in to add a comment or to set email notifications