PhpSpreadsheet - get row without iterating on each cell

Question

I'm using PhpSpreadsheet to easily read from a xls document and insert into a DB after some calculations. I succeeded using examples from the documentation, but I find it sooo complicated I'm sure I missed something and it can be done much more easily.

$worksheet = $this->getWorksheet("file.xls");
foreach ($worksheet->getRowIterator() as $row) {
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(FALSE);
  foreach ($cellIterator as $key => $cell) {
    $cellValue = $cell->getValue();

    if($key == 'A')
      $field1 = $cellValue;
    if($key == 'B') {
      $dateTime = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($cellValue);
      $date = $dateTime->format("Y-m-d");
    }
    if($key == 'C')
      $field2 = $cellValue;
    if($key == 'D')
      $field3 = $cellValue;
    if($key == 'E')
      $field4 = $cellValue;
  }
}

I would have expected something like $row->getCell("A")->getValue() to be available.

So... Have I missed something ?

Arthur Gbs · Answer 1 · 2020-12-03T13:43:06.017

3

Here is what I found

        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $spreadsheet = $reader->load("test.xlsx");
        $sheet = $spreadsheet->getSheet(0);

        $nb = 0;

        foreach ($sheet->getRowIterator() as $row) {

            echo $sheet->getCell("A$nb")->getValue();
            echo "<hr>";
            
            $nb++;
        }

edited Dec 03 '20 at 13:43

answered Dec 03 '20 at 13:27

Arthur Gbs

63
6

score 2 · Answer 2 · answered Apr 03 '18 at 22:05

2

See the docs on getting the values by column and row directly rather than testing the keys

From the example:

// Get the value from cell B5
$cellValue = $spreadsheet->getActiveSheet()->getCellByColumnAndRow(2, 5)->getValue();

Hope that helps

answered Apr 03 '18 at 22:05

jhoskins98

114
5

1

Hi, and thanks, but I saw this method and it doesn't help me - I need to get every row. So using this method, a way to do what I want would be to use a `for` loop instead of `getRowIterator` and then use `getCellByColumnAndRow` with the current index of the loop as the row... But I find it pretty bleak also. Not the ideal solution. – Jeremy Belolo Apr 04 '18 at 08:21

score 0 · Answer 3 · answered Jun 10 '22 at 10:28

You have to access somehow each cell. Example: for the case when you don't know the dimensions of rows and columns ( ... which can have combined letters past single alphabet letters), get iteration for each row and then for each column, and in the end you can have the data parsed into multidimensional array:

        $results = [];
        $reader = new Xlsx(); //   PhpOffice\PhpSpreadsheet\Reader\Xlsx
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load(storage_path('app/temp/' . $file));
        $sheet = $spreadsheet->getActiveSheet();

        foreach ($sheet->getRowIterator() as $index => $row) {
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells, even if a cell value is not set.
            $row_content = [];
            foreach ($cellIterator as $cell) {
                array_push($row_content, $cell->getValue());
            }

            $results[] = $row_content;
        }

score 0 · Answer 4 · answered Apr 29 '23 at 12:18

0

Use toArray method in order to get values of all cells.

  $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
  $spreadsheet = $reader->load("sample.xlsx");

  $spreadsheetArray = $spreadsheet->getActiveSheet()->toArray();

answered Apr 29 '23 at 12:18

Konst_

114
4
4

PhpSpreadsheet - get row without iterating on each cell

4 Answers4