PHPExcel search cell by value

Topics: Developer Forum, User Forum
Jan 20, 2015 at 12:07 PM
Hi,

I'm wondering, how it's possible to help with such issue.
Let's say, I have an excel with such info in it (it could be much more info):
Country Currency
Germany EUR
USA USD
Russia RUB

and I'm entering in input form "USA", and I want to see the result USD from excel.
Is there some kind of function in PHP, which allows to search for a value in excel?

Or at least, if there existing such function, which returns in which cell (e.g. B2) such value exists?

Thank you very much in advance!
Coordinator
Jan 20, 2015 at 10:10 PM
There's nothing built-in to PHPExcel to do a search, but it's pretty straightforward to write something yourself based around the iterators.... take a look at 28iterator.php in /Examples
    $foundInCells = array();
    $searchValue = 'USA';
    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
        $ws = $worksheet->getTitle();
        foreach ($worksheet->getRowIterator() as $row) {
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(true);
            foreach ($cellIterator as $cell) {
                if ($cell->getValue() == $searchValue) {
                    $foundInCells[] = $ws . '!' . $cell->getCoordinate();
                }
            }
        }
    }
    var_dump($foundInCells);
Of course, if you're only wanting to search a specific column in a specific worksheet, you can simplify this a great deal, e.g. using rangeToArray() and then searching the array using standard PHP array functions.