PHPExcel search cell by value

Question

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?

score 13 · Accepted Answer · answered Jan 20 '15 at 12:47

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.

Be aware that by comparing the cell value with the search value using '==', any cell containing the number 0 will match any search string. This is a known PHP behavior (https://stackoverflow.com/questions/6843030/why-does-php-consider-0-to-be-equal-to-a-string) but maybe it's worth using '===' or a more elaborate comparison. — Simon, Jun 08 '17 at 13:54

score 1 · Answer 2 · edited May 23 '17 at 12:02

1

As there a re many different Excel Formats (2003, 2010 quirks, ooxml, etc.) you will have to look for a third party library to read excel files.

Find some examples in this question or in this question.

Edit: added a more current question.

edited May 23 '17 at 12:02

Community

1
1

answered Jan 20 '15 at 12:12

Kai Brummund

3,538
3
23
33

Yes, I've seen those posts. I was using PHPExcel, but i haven't found a solution there. I don't need to write something in excels, I have simple file, from which I need to search by my keyword phrase correct cell – Yuriy T. Jan 20 '15 at 12:18
If you access your data from PHP, you only access the "data". You do not have the functionalities that the Excel application itself provides (like searching, ordering, etc.). You will have to do that by yourself. (Foreach, or load all data from excel into an array and check for the key or something like that.) – Kai Brummund Jan 20 '15 at 12:22
I'm able to retrieve particular cell by using functions like getCell(B2), but still even using different loops i'm not able to make a proper search :( – Yuriy T. Jan 20 '15 at 12:28
1

You can just read the whole table into an array: $arr[getCell(B1)] = getCell(B2); And then check get your value with $arr["USA"]; ... (all pseudo code^^) – Kai Brummund Jan 20 '15 at 12:44

PHPExcel search cell by value

2 Answers2