Creating multi-worksheet Excel files with Simple Excel

Published: Oct 13, 2021 by C.S. Rhymes

Recently I had to create a large data export for a project. I like using Spatie’s Simple Excel package to do this as it is very simple to use and works well when exporting large amounts of data to a CSV or Excel file with the ability to stream a download to the browser. This particular project had an additional requirement though, exporting multiple worksheet’s of data at once. Luckily, this package allows you to do this too.

The writer object

The Simple Excel package uses the box/spout package under the hood. In the readme it states that you can get to the underlying writer using ->getWriter().

$writer = SimpleExcelWriter::create($pathToCsv)->getWriter();

If we jump to the box/spout package docs, there is a section on Playing with sheets. The docs show we can see how to get the current sheet, set a name for the current sheet and how to create a new sheet.

Naming a worksheet

To name a worksheet we can use getCurrentSheet() to get the current sheet with the writer and then use setName() to set the name.

$writer = SimpleExcelWriter::streamDownload('your-export.xlsx')->getWriter()
$nameSheet = $writer->getCurrentSheet();
$nameSheet->setName('Names');

Creating a new worksheet

To create a new sheet we can use addNewSheetAndMakeItCurrent() and we can then use setName() once more to set the name of this new sheet.

$addressSheet = $writer->addNewSheetAndMakeItCurrent();
$addressSheet->setName('Addresses');

Bringing it all together

Now we know how to do the individual tasks we can bring it all together.

  • Create a streamDownload using SimpleExcelWriter
  • Get the writer, get the current sheet and name it ‘Names’
  • Add rows of data to the ‘Names’ sheet
  • Create a new sheet and make it the current sheet, before naming it ‘Addresses’
  • Add the header row to ‘Addresses’
  • Add rows of data to the ‘Addresses’ sheet
  • Finally, return the stream to the browser
use Spatie\SimpleExcel\SimpleExcelWriter;

$stream = SimpleExcelWriter::streamDownload('your-export.xlsx');

$writer = $stream->getWriter();

// Set the name of the current sheet to Names
$nameSheet = $writer->getCurrentSheet();
$nameSheet->setName('Names');

// Add rows to the Names sheet
$stream->addRows([
    ['first_name' => 'Boaty', 'last_name' => 'Mc Boatface'],
    ['first_name' => 'Dave', 'last_name' => 'Mc Dave'],
]);

// Create a new sheet and set the name to Addresses
$addressSheet = $writer->addNewSheetAndMakeItCurrent();
$addressSheet->setName('Addresses');

// Manually add header rows to the Addresses sheet
$stream->addRow(['house_number', 'postcode']);

// Add rows to the Addresses sheet
$stream->addRows([
    ['house_number' => '1', 'postcode' => 'AB1 2BC'],
    ['house_number' => '2', 'postcode' => 'AB1 2BD'],
]);

return $stream->toBrowser();

For more information on creating exports in Laravel, check out Using Laravel Resource Collections with exports.

When creating a single worksheet, the Simple Excel package normally creates the header row for us, but it seems when you create a new sheet you need to define the new headers for your data.

Here are a couple of screenshots of the outputted Excel file:

The Names Excel worksheet

The Addressed Excel worksheet

Photo by Wilfred Iven on StockSnap

PHP Laravel Tutorial

Share

Latest Posts

Considerations for Incremental Static Regeneration in Next.js
Considerations for Incremental Static Regeneration in Next.js

Next.js offers a feature called Incremental Static Regeneration (ISR) that allows you to generate a static page when the page is first visited, rather than generating a static copy at build time. This is a really handy feature as it allows you to reduce your build time, but still benefit from having a cache of a page generated so future visitors will have a faster response time.

Mocking axios in Jest tests with Typescript
Mocking axios in Jest tests with Typescript

Recently I wanted to write a test for a React component that uses axios to retrieve information from an API. To do this I had to mock jest, but I ran into some issues with the types as I was using typescript. This article explains how I managed to get it to work.

Using GitHub Dependabot with a Monorepo
Using GitHub Dependabot with a Monorepo

GitHub offers a dependabot service that can let you know of any potential security issues with your dependencies and automatically create a Pull Request for you. This works great without any configuration if you have a repo that contains npm, composer or gem dependencies, but you may need additional configuration if your lock files aren’t in the root directory, or in separate directories in the case of a monorepo.

How NOT to make a website

How NOT to make a Website

By C.S. Rhymes

From £8.99

Nigel's Intranet Adventure

Nigel's Intranet Adventure

By C.S. Rhymes

From £2.69