3 years ago

Category: Laravel

Creating multi-worksheet Excel files with Simple Excel | C.S. Rhymes C.S. Rhymes HomeBooksNigel's Intranet AdventureHow NOT to make a WebsiteHow NOT to use a SmartphoneMy WorkThemesPackagesSitesDemosStoriesSponsorsAboutBlogSponsorCreating multi-worksheet Excel files with Simple ExcelPublished: Oct 13, 2021 by C.S. RhymesRecently 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.

Luckily, this package allows you to do this too.The writer objectThe Simple Excel package uses the box/spout package under the hood.

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 worksheetTo 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 worksheetTo 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 togetherNow we know how to do the individual tasks we can bring it all together.

useSpatie\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'=> ,'postcode'=>'AB1 2BC'],['house_number'=> ,'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 StockSnapPHPLaravelTutorialSharePlease enable JavaScript to view the comments powered by Disqus.Latest PostsCreating multi-worksheet Excel files with Simple ExcelRecently I had to create a large data export for a project.