OpenSpout Documentation

Writing Sheets

You can control which sheet data is written to by managing the 'current' sheet:

• getCurrentSheet(): Retrieves the sheet currently being written to.
• addNewSheetAndMakeItCurrent(): Creates a new sheet and switches context to it.
• setCurrentSheet($sheet): Switches the context back to a specific sheet object.
• getSheets(): Returns all sheets created in the current writer instance.
• getCurrentSheet()->setName('Name'): Customizes the name of the current sheet.

Note on Excel Sheet Names: You are responsible for ensuring names are not blank, $\le$ 31 characters, do not contain \ / ? * : [ ], and do not start/end with a single quote.

Reading Sheets

When iterating through a reader, you can access sheet metadata:

• $sheet->getName()
• $sheet->isVisible()
• $sheet->isActive() (the sheet that was active when the file was last saved)

// Writing example
$firstSheet = $writer->getCurrentSheet();
$writer->addRow($rowForSheet1);

$newSheet = $writer->addNewSheetAndMakeItCurrent();
$writer->addRow($rowForSheet2);

$writer->setCurrentSheet($firstSheet);
$writer->addRow($anotherRowForSheet1);

// Customizing name
$sheet = $writer->getCurrentSheet();
$sheet->setName('My custom name');

To read data from a specific sheet based on its position, iterate through the sheets using getSheetIterator() and check the sheet index using $sheet->getIndex(). Note that the index is 0-based (e.g., the 3rd sheet has an index of 2). Once the target index is matched, iterate through the rows and cells as needed, then break the loop to stop reading further sheets.

$reader = new \OpenSpout\Reader\XLSX\Reader();
$reader->open($filePath);

foreach ($reader->getSheetIterator() as $sheet) {
    // only read data from 3rd sheet
    if ($sheet->getIndex() === 2) { // index is 0-based
        foreach ($sheet->getRowIterator() as $row) {
            // do something with the row example grab cell 2
            $cells = $row->cells; //Load all the cells
            $cell_value = $cells[2]->getValue();
            echo "$cell_value \n";
        }
        break; // no need to read more sheets
    }
}

$reader->close();

You can stream the generation of an XLSX file directly to a user's browser without saving a temporary file on the server. This is achieved by using OpenSpout's openToBrowser() method inside a Symfony StreamedResponse callback.

1. Create a writer using WriterEntityFactory::createXLSXWriter().
2. Initialize a StreamedResponse with a callback.
3. Inside the callback, call $writer->openToBrowser('filename.xlsx').
4. Iterate through your data, creating rows with WriterEntityFactory::createRowFromArray($row) and adding them via $writer->addRow().
5. Close the writer with $writer->close().
6. Set the Content-Type header to application/vnd.ms-excel.

class MyStreamController extends Controller
{
    /**
     * @Route("/spreadsheet/stream-data")
     */
    public function streamDataAction(): StreamedResponse
    {
        $writer = WriterEntityFactory::createXLSXWriter();
        $data = [
            ['c1r1','c2r1','c3r1'],
            ['c1r2','c2r3','c3r4'],
        ];
        $response = new StreamedResponse(function () use ($writer, $data) {
            $writer->openToBrowser('filename.xlsx');

            foreach ($data as $row) {
                $writer->addRow(WriterEntityFactory::createRowFromArray($row));
            }

            $writer->close();
        });
        $response->headers->set('Content-Type', 'application/vnd.ms-excel');

        return $response;
    }
}

To avoid waiting for a large spreadsheet to be fully read into memory before sending a response to the browser, you can use Symfony's StreamedResponse. Instead of building a large string and returning a standard Response, you use a callback function within StreamedResponse to echo content chunks as they are processed by OpenSpout.

When reading a file, use OpenSpout\Reader\XLSX\Reader to iterate through sheets and rows. To ensure the browser receives data incrementally, use flush() periodically (e.g., every N rows) within the callback to push the echoed content to the client.

class MyStreamController extends Controller
{
    const FLUSH_THRESHOLD = 100;

    /**
     * @Route("/spreadsheet/stream")
     */
    public function readAction()
    {
        $filePath = '/path/to/static/file.xlsx';

        $response = new StreamedResponse();
        $response->headers->set('Content-Type', 'text/html');

        $response->setCallback(static function() use ($filePath): void {
            $reader = new \OpenSpout\Reader\XLSX\Reader();
            $reader->open($filePath);

            $i = 0;
            foreach ($reader->getSheetIterator() as $sheet) {
                echo '<table>';
                foreach ($sheet->getRowIterator() as $row) {
                    echo '<tr>';
                    echo implode(array_map(static function($cell): string {
                        return '<td>' . $cell . '</td>';
                    }, $row->cells));
                    echo '</tr>';

                    $i++;
                    if ($i % self::FLUSH_THRESHOLD === 0) {
                        flush();
                    }
                }
                echo '</table><br>';
            }

            $reader->close();
        });

        return $response;
    }
}