Multiple Sheets Excel Export in Laravel
Did you know that more than 70 % of businesses still rely on excel spreadsheets to share data across departments, yet only a fraction can generate a multiple-sheet workbook directly from their web app? If you’ve ever wrestled with copying data into separate tabs or manually rebuilding a report, Laravel’s built‑in export tools can turn that headache into a single line of code—delivering a polished, multiple‑sheet excel file in seconds.
What “Multiple‑Sheet” Export Means for Your Laravel Project
When you hear excel in a dev context, many think of a single CSV or a flat table. That’s pretty much the old way. Today, a multiple‑sheet workbook lets you embed dozens of related datasets in one file—think monthly reports, inventory plus pricing tabs, or separate sheets for raw data and a summary that uses vlookup or xlookup formulas.
- Easy to read for non‑tech stakeholders.
- Pre‑formatted for analysis or pivot tables.
- Reduces the risk of copy‑paste errors.
For beginners, it looks like a clean report; for power users, it’s a launchpad for deeper analysis.
Setting Up the Environment – Packages & Configuration
First things first: laravel-excel (by Maatwebsite) is the de‑facto library for excel exports in Laravel. I’ve found that installing it is as easy as running a couple of composer commands.
composer require maatwebsite/excel
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Once published, open config/excel.php and make sure the default writer is set to 'Xlsx'. If you’re on Laravel 9+, no need to add aliases—Laravel’s auto‑service provider discovery handles it.
That’s all the plumbing; next we dive into the code that actually builds the workbook.
Step‑by‑Step Walkthrough: Exporting Multiple Sheets
Below is a complete, ready‑to‑run example that exports a workbook with three sheets: UsersSheet, OrdersSheet, and SummarySheet. The SummarySheet even contains a vlookup formula.
// app/Exports/MultiSheetExport.php
namespace App\Exports;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
class MultiSheetExport implements WithMultipleSheets
{
public function sheets(): array
{
return [
new UsersSheet(),
new OrdersSheet(),
new SummarySheet(),
];
}
}
// app/Exports/UsersSheet.php
namespace App\Exports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithHeadings;
class UsersSheet implements FromCollection, WithTitle, WithHeadings
{
public function collection()
{
return User::select('id', 'name', 'email')->get();
}
public function title(): string
{
return 'Users';
}
public function headings(): array
{
return ['ID', 'Name', 'Email'];
}
}
// app/Exports/OrdersSheet.php
namespace App\Exports;
use App\Models\Order;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithHeadings;
class OrdersSheet implements FromQuery, WithTitle, WithHeadings
{
public function query()
{
return Order::query()->select('id', 'user_id', 'total');
}
public function title(): string
{
return 'Orders';
}
public function headings(): array
{
return ['ID', 'User ID', 'Total'];
}
}
// app/Exports/SummarySheet.php
namespace App\Exports;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
class SummarySheet implements WithTitle, WithEvents
{
public function title(): string
{
return 'Summary';
}
public function registerEvents(): array
{
return [
AfterSheet::class => function(AfterSheet $event) {
// Simple formula that looks for a user ID in column A and pulls the name from the Users sheet
$event->sheet->setCellValue('A1', 'User ID');
$event->sheet->setCellValue('B1', 'Name');
$event->sheet->setCellValue('C1', 'Total Orders');
$event->sheet->setCellValue('A2', 1); // example User ID
$event->sheet->setCellValue('B2', '=VLOOKUP(A2,Users!A:B,2,FALSE)');
$event->sheet->setCellValue('C2', '=SUMIF(Orders!B:B, A2, Orders!C:C)');
},
];
}
}
// routes/web.php
use App\Exports\MultiSheetExport;
use Maatwebsite\Excel\Facades\Excel;
Route::get('/export', function () {
return Excel::download(new MultiSheetExport, 'report.xlsx');
});
When you hit /export, Laravel streams the workbook to the browser. The vlookup stays active, so users can interact with it when they open the file.
Why It Matters – Real‑World Impact on Teams & Data Accuracy
Sound familiar? A finance manager once spent an entire day copying raw data into a spreadsheet and then manually adding formulas. The result? Several typos that led to a budgeting error. With a multiple‑sheet excel export, that same data is ready in minutes, formulas are baked in, and the risk of human error drops dramatically.
- One‑click reporting for finance, sales, and ops.
- Stakeholders receive a ready‑to‑use file instead of raw JSON.
- Teams can build on top of the data with pivot tables or further formula work.
In my experience, the ability to generate a polished workbook instantly boosts cross‑department collaboration and turns data into a strategic asset.
Actionable Takeaways & Next Steps
Before you start coding, run through this quick checklist:
- ✓ Verify sheet order matches the business flow.
- ✓ Include headings and titles; they’re the first thing people notice.
- ✓ Test formulas with a small dataset to ensure they reference the correct sheet names.
Looking to scale? Laravel‑Excel supports streaming large datasets via FromQuery and ShouldQueue. Add styling with the WithStyles concern, or protect sheets with passwords if you need to lock down sensitive data.
Want to dive deeper? Check out the official docs, explore advanced formatting, or write unit tests for your export classes.
Frequently Asked Questions
How can I export more than one sheet to excel using Laravel?
Use the maatwebsite/laravel-excel package and implement the WithMultipleSheets interface in an export class. Each sheet is represented by its own class (e.g., UsersSheet, OrdersSheet) that returns a collection or query builder.
Can I include VLOOKUP or XLOOKUP formulas in the exported sheets?
Yes. Inside each sheet class you can add a WithEvents listener that writes formulas to specific cells (e.g., $event->sheet->setCellValue('C2', '=VLOOKUP(A2,Data!A:B,2,FALSE)')). The formulas remain active when the workbook is opened.
What is the best way to style headers across multiple sheets?
Implement the WithStyles or WithHeadings concern in each sheet class and apply a shared style array (bold, background color, auto‑size). You can also create a reusable trait that holds the styling logic to keep code DRY.
Is it possible to stream a large multi‑sheet export without running out of memory?
Absolutely. Use the FromQuery concern together with WithMapping and enable the ShouldQueue trait; Laravel‑Excel will stream rows to the XLSX writer, keeping memory usage low even for millions of rows.
How do I trigger the export from a Vue/React front‑end?
Create a simple GET/POST route that returns Excel::download(new MultiSheetExport, 'report.xlsx'). From the front‑end, call the endpoint via axios or a form submission, and the browser will automatically start the file download.
Related reading: Original discussion
Related Articles
What do you think?
Have experience with this topic? Drop your thoughts in the comments - I read every single one and love hearing different perspectives!
Comments
Post a Comment