PhpSpreadSheet: How to save Workbook sheets in individual CSV files

Question

I'm using PhpSpreadSheet and I need to save the sheets contained in a workbook as individual CSV files.

I've tried to use $reader->setLoadAllSheets(); but at the end I always have a single CSV file containing the first sheet of the workbook.

Here's an example of my code:

    $excel = 'excelfile.xlsx';
    $name = 'newCsvName';

    //Read the file
    $reader = new Xlsx();
    $reader->setReadDataOnly(true);
    $reader->setLoadAllSheets();
    $spreadsheet = $reader->load($excel);

    //Write the CSV file
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
    $writer->setDelimiter(";");
    $csvPath = 'csv_files/' . $dir . '/' . $name .'.csv';
    $writer->save($csvPath);

Answer 1

This is how I solved it. I first load every sheet contained into the excel file and then save a new CSV file with the info. I'm sure there should be a better way, but it works fine.

$excel = 'excelfile.xlsx';
$name = 'newCsvName';
$reader = new Xlsx();
$reader->setReadDataOnly(true);

//Get all sheets in file
$sheets = $reader->listWorksheetNames($excel);

//Loop for each sheet and save an individual file
foreach($sheets as $sheet){
   //Load the file
   $spreadsheet = $reader->load($excel);

   //Write the CSV file
   $writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
   $writer->setDelimiter(";");
   $csvPath = 'csv_files/' . $dir . '/' . $name.'_'.$sheet.'.csv';
   $writer->save($csvPath);
}

Answer 2

The answer above is missing a vital line:

$reader->setLoadSheetsOnly([$sheet]);

This makes the reader load the specific sheet which then allows the loop to open the specific sheet and write it to the CSV, otherwise you will find this code creates each sheet name but with the contents from the first sheet of the document.

$excel = 'excelfile.xlsx';
$name = 'newCsvName';
$reader = new Xlsx();
$reader->setReadDataOnly(true);

//Get all sheets in file
$sheets = $reader->listWorksheetNames($excel);

//Loop for each sheet and save an individual file
foreach($sheets as $sheet){

   //Load the file
   $reader->setLoadSheetsOnly([$sheet]);
   $spreadsheet = $reader->load($excel);

   //Write the CSV file
   $writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
   $writer->setDelimiter(";");
   $csvPath = 'csv_files/' . $dir . '/' . $name.'_'.$sheet.'.csv';
   $writer->save($csvPath);
}