Get field data when using Spout to read an Excel file

Question

I'm using Spout to read an Excel file. Here is what my Excel looks like:

I tried doing this with some basic code from the Spout documentation:

$filePath = '/Users/nhathao/Desktop/employee-test.xlsx';
    $reader = ReaderEntityFactory::createXLSXReader();
    //$reader = ReaderFactory::create(Type::XLSX);
    $reader->open($filePath);

    foreach ($reader->getSheetIterator() as $sheet) {
        foreach ($sheet->getRowIterator() as $row) {
            echo "<pre>";
            print_r($row->getCells());
            echo "</pre>";
        }
    }

When I run that, I get the following data from the cells:

Is there any way to access these fields? Could I use something like $row->email? I want to get that value and store it in a variable to compare it with my value in database.

Hope you can help me!

Thank you very much!

score 12 · Accepted Answer · answered Aug 16 '19 at 07:26

12

$row->getCells returns an array of Cells. You can access the value of each cell by calling $cell->getValue() on it.

In your case, if you want the email field, there's no magic mapping. If the email column is the 5th column, then you can do:

$cells = $row->getCells();
$emailCell = $cells[4]; // 4 because first column is at index 0.
$email = $emailCell->getValue();

Hope that helps!

answered Aug 16 '19 at 07:26

Adrien

1,929
1
13
23

Or we can use $cell[4]->value for shorter. I found this last night. Anyway, thanks for your help! – Tomato Aug 16 '19 at 09:44
Another shorthand is: `$row->getCellAtIndex(4)->getValue()` – madflow Oct 31 '19 at 14:48

score 5 · Answer 2 · edited Sep 18 '19 at 10:57

5

You can get value simply converting a row to an array. Assume you want to get the value of name. As you see the index of name is 1. What you have to do is:

foreach ($reader->getSheetIterator() as $sheet) {
    foreach ($sheet->getRowIterator() as $row) {
        $value = $row->toArray();
        $name = $value[1];
    }
}

Hope it'll help you.

edited Sep 18 '19 at 10:57

Pavel Smirnov

4,611
3
18
28

answered Sep 18 '19 at 10:54

Najmus Sakib

737
8
12

Get field data when using Spout to read an Excel file

2 Answers2