Question 418136 in PHP Question posted in
Official manual can be found here.

PhpSpreadsheet only save the first worksheet as csv

I tried to save a specific worksheet from an XLSX as CSV.
First I tried to use setActiveSheetIndexByName() function to set active sheet and call the writer method of PhpOfficePhpSpreadsheetWriterCsv, but no luck. Somehow the writer save the first worksheet. I tried indexing by name, by number, but not a little difference.

Now there is a code I experimenting with:

$reader = PhpOfficePhpSpreadsheetIOFactory::createReader("Xlsx");
$reader->setReadDataOnly(true);

$spreadsheet = $reader->load('infile.xlsx');
foreach($spreadsheet->getAllSheets() as $sheet)
{
   $spreadsheet->setActiveSheetIndexByName($sheet->getTitle());
   $activeSheet = $spreadsheet->getActiveSheet();
   $title = $activeSheetgetTitle();
   echo $title . PHP_EOL;
   $writer = new PhpOfficePhpSpreadsheetWriterCsv($spreadsheet);
   $writer->save($title . '.csv', 2);
}

The console output is as I expected: the list of spreadsheet names.
The file system output is almost as I expected: a lot of CSV files with proper names.
However the content is the same. I mean bit by bit the same content as the first worksheet. No matter if I change the active sheet.

My question is, how can I save CSV file based on a speficict worksheet?

UPDATE:
If I reorder the worksheets on workbook, I got the CSV I need. But this is merely an instant hack, and it will not work when I’ll need to work with more worksheet.

Tags:

2

Answers


  1. 0

    The issue is that the PhpOfficePhpSpreadsheetWriterCsv is set to export the entire spreadsheet rather than just the active sheet. To fix this and ensure each worksheet is exported to its own CSV file, you need to create a new Spreadsheet object containing only the current sheet in the loop. Here’s the corrected code:

    $reader = PhpOfficePhpSpreadsheetIOFactory::createReader("Xlsx");
$reader->setReadDataOnly(true);

$spreadsheet = $reader->load('infile.xlsx');

foreach ($spreadsheet->getAllSheets() as $sheet) {
    // Create a new spreadsheet for the current sheet
    $singleSheetSpreadsheet = new PhpOfficePhpSpreadsheetSpreadsheet();
    $singleSheetSpreadsheet->removeSheetByIndex(0); // Remove default sheet
    $singleSheetSpreadsheet->addExternalSheet($sheet, 0); // Add the current sheet

    // Get the sheet title
    $title = $sheet->getTitle();

    // Output the title to console
    echo $title . PHP_EOL;

    // Write the single sheet to a CSV file
    $writer = new PhpOfficePhpSpreadsheetWriterCsv($singleSheetSpreadsheet);
    $writer->save($title . '.csv');
}
    Login or Signup to reply.
  2. 0
    Write a specific worksheet

    CSV files can only contain one worksheet. Therefore, you can specify which sheet to write to CSV:

    $writer->setSheetIndex(0); // default is 0

    Source: Reading and Writing to file – PhpSpreadsheet Docs

    $writer = new PhpOfficePhpSpreadsheetWriterCsv($spreadsheet);

$currentSheetIndex = $spreadsheet->getActiveSheetIndex();
$writer->setSheetIndex($currentSheetIndex); // or any other one

$writer->save("title.csv");
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search