John Davidson

Message:

I am using PHPSpreadsheet to read a large sheet (11000 rows) but wish to ignore rows that have a cell with a certain value. These rows can be ignored when reading the file and then using toArray() reducing my array to far less rows. PHPSpreadsheet is unable to successfully complete on so many rows so I need to reduce this somehow.

---

My Answer:
You can achieve this by using a loop to iterate through each row in the spreadsheet and checking if the cell in the specified column contains the certain value that you want to ignore. If the cell does not contain the value, you can add the row to a new array that will be used to store only the rows that you want to keep.

Here is an example code snippet to demonstrate this:

php
use PhpOffice\PhpSpreadsheet\IOFactory;

// Load the spreadsheet file
$spreadsheet = IOFactory::load('your_spreadsheet.xlsx');
$worksheet = $spreadsheet->getActiveSheet();

// Define the column index that you want to check for the certain value
$columnIndex = 'A'; // Change this to the column index that you want to check

// Initialize an empty array to store the rows that you want to keep
$filteredRows = [];

// Iterate through each row in the spreadsheet
foreach ($worksheet->getRowIterator() as $row) {
$cellValue = $worksheet->getCell($columnIndex . $row->getRowIndex())->getValue();

// Check if the cell contains the certain value that you want to ignore
if ($cellValue !== 'certain_value_to_ignore') {
// Add the row to the filteredRows array
$filteredRows[] = $worksheet->rangeToArray('A' . $row->getRowIndex() . ':' . $worksheet->getHighestColumn() . $row->getRowIndex())[0];
}
}

// Now $filteredRows contains only the rows that do not have the certain value in the specified column


You can then use the `$filteredRows` array to work with the filtered data instead of the entire spreadsheet. This should help reduce the memory usage and improve the performance of your script.