Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PhpSpreadsheet - get row without iterating on each cell

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 ?

like image 409
Jeremy Belolo Avatar asked Apr 01 '18 09:04

Jeremy Belolo


2 Answers

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++;
        }
like image 193
Namicom Avatar answered Sep 28 '22 09:09

Namicom


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

like image 22
jhoskins98 Avatar answered Sep 28 '22 10:09

jhoskins98