Unable to filter visible rows

[1000camels]

I am trying to read an XSLX file, filter row 2, remove any rows that are filtered (hidden) and write each worksheet to CSV.

I have found several examples and have strung them together to create the following:

$xslx_file = 'data/20210324 DATACheeseClub Import.xlsx';
print 'Reading '.$xslx_file."\n";

$reader = new Xlsx();
//$reader->setReadFilter( new BasicFilter() );
$reader->setReadDataOnly(true);
$reader->setReadEmptyCells(false);
$spreadsheet = $reader->load($xslx_file);

$allSheets = $spreadsheet->getSheetNames();

foreach( $allSheets as $sheetIndex => $sheet ) {
if($sheet == 'OLD') continue;

//$writer->setSheetIndex($sheetIndex);

// filter hidden rows
$data_sheet = $spreadsheet->getSheetByName($sheet);
$data_array = $data_sheet->toArray();
$data = [];

$highestColumn = $spreadsheet->getActiveSheet()->getHighestColumn();

foreach ($data_sheet->getRowIterator() as $row_id => $row) {
if ($data_sheet->getRowDimension($row_id)->getVisible()) {
// I guess you don't need the Headers row, note that now it's row number 1
if ( $row_id == 1 || $row_id > 2 ) {
$data[] = $spreadsheet->getActiveSheet()->rangeToArray('A' .$row->getRowIndex().':'.'BB'.$row->getRowIndex());
}
}
}

$spreadsheet->getActiveSheet()->fromArray( $data );

print "creating csv for ".$sheet."\n";
$writer = new Csv($spreadsheet);
$writer->setUseBOM(true);
$writer->save("csv/".$sheet.".csv");
}

For some reason, column A is completely blank and I see this error:

Warning: mb_substr() expects parameter 1 to be string, array given in vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/StringHelper.php on line 481

Any help is appreciated.

[oleibman]

You have several problems. The most important, and the one generating the warning messages, is that your assignment to data[] returns a 2-dimensional array - index 0, the only one used, points to another array - and you need it to be 1-dimensional later, so you just want to use index 0. Additionally, you need to be using data_sheet, not getActiveSheet, in the loop which assigns data[], and you need to be using a separate variable for the input and output Spreadsheet objects. I believe the following will work for you. Note that you can get the code properly formatted by starting it with a line consisting of 3 backticks followed by space followed by php, and ending it with a line consisting of 3 backticks.

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

$xslx_file = 'issue2081.xlsx';
print 'Reading '.$xslx_file."\n";

$reader = new Xlsx();
//$reader->setReadFilter( new BasicFilter() );
$reader->setReadDataOnly(true);
$reader->setReadEmptyCells(false);
$spreadsheet = $reader->load($xslx_file);

$allSheets = $spreadsheet->getSheetNames();

foreach( $allSheets as $sheetIndex => $sheet ) {
    if($sheet == 'OLD') continue;

    //$writer->setSheetIndex($sheetIndex);

    // filter hidden rows
    $data_sheet = $spreadsheet->getSheetByName($sheet);
    $data = [];

    $highestColumn = $spreadsheet->getActiveSheet()->getHighestColumn();

    foreach ($data_sheet->getRowIterator() as $row_id => $row) {
        if ($data_sheet->getRowDimension($row_id)->getVisible()) {
          // I guess you don't need the Headers row, note that now it's row number 1
            if ( $row_id == 1 || $row_id > 2 ) {
                $data[] = $data_sheet->rangeToArray('A' .$row->getRowIndex().':'.'BB'.$row->getRowIndex())[0];
            }
        }
    }

    $spreadsheet2 = new Spreadsheet();
    $spreadsheet2->getActiveSheet()->fromArray( $data );

    $writer = new Csv($spreadsheet2);
    $writer->setUseBOM(true);
    $filename = "issue2081.$sheet.csv";
    $writer->save($filename);
    print "saved $filename\n";
}

[1000camels]

Thank you.
That worked great!