We use cookies on this site to enhance your user experience
By clicking the Accept button, you agree to us doing so. More info on our cookie policy
We use cookies on this site to enhance your user experience
By clicking the Accept button, you agree to us doing so. More info on our cookie policy
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 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.
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');
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');
Now we know how to do the individual tasks we can bring it all together.
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:
Photo by Wilfred Iven on StockSnap
Share
Latest Posts
I have seen a lot of threads from indie authors asking for advice with marketing their ebook. If I’m honest, I have never really had a strategy for marketing my books, I just assumed that I could put it out there and people would find it. In the real world, that does not seem to be the case.
This post follows on from my last post about using your website to promote your ebooks. The first step of the article explains that you need to make a website, but didn’t go into too much detail. This post aims at explaining how you can build a landing page for your book with Bulma Clean Theme.
I’m a web developer by trade and a part-time author, so here are a few things that I have done to help promote my books and ebooks using my website and my tech know how from my day job.