Phpspreadsheet: How I can get all rows as array?

[olaferlandsen]

In PHPExcel I'm using the next code:

$file    = PHPExcel_IOFactory::createReader('Excel2007');
$array = $objReader->load('file.xls');

How can I do the same in PhpSpreadsheet?

PS: I know this project is still unstable, but I would like to use this and not PHPExcel

[MarkBaker]

Perhaps as simply as

$file = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Excel2007');
$array = $file->load('file.xls');

or

$array = \PhpOffice\PhpSpreadsheet\IOFactory::load('file.xls');

Note that this doesn't return an array, nor did it with PHPExcel, i returns a Spreadsheet object

And it' always better to let PHPExcel/PHPSpreadsheet identify the filetype itself rather than explicitly specifying a Reader, because not all spreadsheet files are what they claim to be

[olaferlandsen]

Well, you example output a object. I need a Array.

[MarkBaker]

Well, you example output a object. I need a Array.

Yes, that's what I told you.

If you want an array, then you need to select which worksheet you want, and the use the toAarray() method, which will give you an array of the data on that worksheet.... exactly the same as you needed to do with PHPExcel. And all of this is demonstrated in the examples

[olaferlandsen]

Thanks @MarkBaker I found a solution thank you.
Example:

$spreadsheet = PhpSpreadsheet\IOFactory::load( 'myfile.wherever' );
$worksheet = $spreadsheet->getActiveSheet();
$rows = [];
foreach ($worksheet->getRowIterator() AS $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
    $cells = [];
    foreach ($cellIterator as $cell) {
        $cells[] = $cell->getValue();
    }
    $rows[] = $cells;
}

Use "$rows" as aray.
Thanks!

[MarkBaker]

Although

$worksheet = $spreadsheet->getActiveSheet();
$rows = $worksheet->toArray();

is even easier

But be aware that building an array of cells in PHP memory when you already have the worksheet in PHP memory can use a lot of memory

[scottlaurent]

for anyone dealing with this in the future, the method provided by @olaferlandsen actually is the only solution in certain cases:

1. When you have static values in all of your cells (like a typical CSV) - then the toArray() method works best.

2. However when you have formula-based fields like '=HYPERLINK(whatever)' where you need to parse something in the formula itself - then the iteration method works better.

[emotionmedia]

Thanks @olaferlandsen

Thats what exactly i wanted

[stringaro-leo]

Although

$worksheet = $spreadsheet->getActiveSheet();
$rows = $worksheet->toArray();

is even easier

But be aware that building an array of cells in PHP memory when you already have the worksheet in PHP memory can use a lot of memory

This answer doesn't keep formula.....

But this one https://github.com/PHPOffice/PhpSpreadsheet/issues/97#issuecomment-280892439 does the job.

[WalterOkumu]

Hi, I'm new to this so please forgive me if I ask a silly question. I'm able to import from an xlsx file, but when I put the data into an array, it enters the data as rows instead of columns.
I have 3 arrays, Name, Date and Number
For example:

John | 01/01/2016 | 100
Micheal | 02/01/2016 | 200
Margaret | 03/01/2016 | 300

I end up getting
Name -> John 01/01/2016 100
Date -> Micheal 02/01/2016 200
Number -> Margaret 03/01/2016 300

Instead of:
Name -> John Micheal Margaret
Date -> 01/01/2016 02/01/2016 03/01/2016
Number -> 100 200 300

How can I fix this? What am I doing wrong?

[flip111]

This should be first item in https://phpspreadsheet.readthedocs.io/en/latest/faq/ looking at the amount of upvotes. Also should be first example in documentation.