How do you read cells in PhpOffice\PhpSpreadsheet using the IReadFilter in PHP?

6.5k views Asked by At

I am trying to read two columns from a spreadsheet across multiple sheets. An example of this would be:

Sheet One:        Sheet Two:        Sheet Three:        ETC...
A ..... V         A ..... V         A ..... V           <-- Two Columns I need
1       5         32      9         54      1           <-- Rows Below
2       8         33      2         55      3
3       9         34      7         56      8

So far, I am loading the Spreadsheet using PhpOffice\PHPSpreadsheet and the documentation to learn how to do this.

After compiling all the information from the documentation together, I have come up with this:

class CustomXlsxReader implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    public function readCell($column, $row, $worksheetName = '') {
         return in_array($column, range('A','V'));
    }
}

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setLoadSheetsOnly(["A Coy", "B Coy", "C Coy", "S Coy", "HQ Coy"]);
$reader->setReadDataOnly(true);
$reader->setReadFilter(new CustomXlsxReader());
$spreadsheet = $reader->load($_FILES['xlsxFile']['tmp_name']); // I do not want to physically save the file as the data will be converted to a database for further distribution

I tried to find the reading cell section of the documentation and found this:

$worksheet = $spreadsheet->getActiveSheet(); // Not sure how to change between these

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
        foreach ($cellIterator as $cell) {
            $cell = $cell->getValue(); // Not sure what column this is looping through
        }
    }

I can see that the \PhpOffice\PhpSpreadsheet\Reader\IReadFilter has the method to read the rows, however, the $spreadsheet instance does not have this method and I can not, for the life of me, find how to use this in the documentation.

How can I now read columns A and V from each sheet within my spreadsheet? I want to loop through the sheets (A coy, B coy, etc...) and read the columns A and V for each. array_merge would not conflict with any data so if its not possible to specifically read sheet by sheet, then a merged solution would be great also.

Many thanks in advance.

1

There are 1 answers

1
Mark Baker On BEST ANSWER

The spreadsheet doesn't have these methods, because they're accessed in the Reader, not in the Spreadsheet; the ReadFilter is simply telling the Reader to load only cell data for columns A-V when it reads the file into the Spreadsheet object

$worksheet = $spreadsheet->getActiveSheet(); // Not sure how to change between these
// Use `setActiveSheetIndex(<worksheetindex>)`
//  or `setActiveSheetIndexByName(<worksheetname>)
//  to set the selected worksheet

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
        foreach ($cellIterator as $cell) {
            $cell = $cell->getValue(); // Not sure what column this is looping through
            // The $cell->getColumn() method will tell you the column
        }
    }